Setting sort order

[RMTechie]

When a form loads, I want to set the subform's sort order based on the
value of a text box on the parent form.

For instance, if the field txtGender on my parent form, "frm_Parent",
is "MALE" then I want to set the sort order of the subform,
frm_Parent_Subform, to SortOrderMale and otherwise (Else?) to
SortOrderOther.

So, when frm_Parent opens, I want it to sort the subform based on
whether or not txtGender is "MALE"

I had this is in the subform's Open event but couldn't get it to work:

If Forms![frm_Parent].[txtGender] = "MALE" Then
Me.OrderBy = "SortOrderMale"
Else
Me.OrderBy = "SortOrderOther"
End If

I'm also unsure which event to place such a thing in, whether in the
Form or Subform, Open or Load, etc.

I'd appreciate help! Thanks!

 

[Stuart McCall]

When a form loads, I want to set the subform's sort order based on the
value of a text box on the parent form.

For instance, if the field txtGender on my parent form, "frm_Parent",
is "MALE" then I want to set the sort order of the subform,
frm_Parent_Subform, to SortOrderMale and otherwise (Else?) to
SortOrderOther.

So, when frm_Parent opens, I want it to sort the subform based on
whether or not txtGender is "MALE"

I had this is in the subform's Open event but couldn't get it to work:

If Forms![frm_Parent].[txtGender] = "MALE" Then
Me.OrderBy = "SortOrderMale"
Else
Me.OrderBy = "SortOrderOther"
End If

I'm also unsure which event to place such a thing in, whether in the
Form or Subform, Open or Load, etc.

I'd appreciate help! Thanks!

You need to follow the 'Me.OrderBy = ...' line with:

Me.OrderByOn = True

Also the code would work best in the parent form's OnCurrent event, which
fires when you navigate to another record:

If Me.txtGender = "MALE" Then
With Me.SubformControlName.Form
.OrderBy = "MALE"
.OrderByOn = True
End With
Else
With Me.SubformControlName.Form
.OrderBy = "FEMALE"
.OrderByOn = True
End With
End If

 

[Graham Mandeno]

Use the AfterUpdate event of the txtGender textbox, so that when its value
changes, the SortOrder changes.

You will need to refer to the form object contained in the subform control:

With Me![subform control name].Form
If txtGender = "MALE" then
.SortOrder = "SomeField"
Else
.SortOrder = "SomeOtherField"
End If
.SortOrderOn = True ' don't forget this step!
End With