setting order to mutiple fields (1 Viewer)

[John Sh]

I think I have a syntax problem.
If I use the following code the system works fine. I can scroll through the records as expected.

DoCmd.SetOrderBy "boxno" & "," & "family" & "," & "accessionnumber"
    Me.OrderByOn = True

If I use the following code I get a message " Can't go to that record" even before the form has loaded.

sortOrder = "boxno" & "," & "family" & "," & "accessionnumber"
    DoCmd.SetOrderBy sortOrder
    Me.OrderByOn = True

Ideally I would like to be able to change the sort order via a subform that loads the "sortorder" variable with the required fields.
These fields are selected via three comboboxes and combined with the following code

Private Sub btnSort_Click()
    If Me.cboSort1 <> "" Then
       sortOrder = cboSort1
   End If
   If Me.cboSort2 <> "" Then
        sortOrder = sortOrder & ", " & cboSort2
    End If
    If Me.cboSort3 <> "" Then
        sortOrder = sortOrder & ", " & cboSort3
    End If
    DoCmd.Close acForm, Me.Name
End Sub

The sort order is then changed in the parent form's oncurrent event

 

[June7]

If the second code snip is in same event as the first, it should work just as well.

I don't understand use of subform to provide filter for main form. Code closing a subform does not make sense.

Might provide db for analysis. Follow instructions at bottom of my post.

 

[arnelgp]

is it a "subform" or a "pop-up" form?

when you open the "sortorder" form, pass the calling form's name to it, so that when you close the "sortorder" form
you can "refresh" the calling form.

see this demo

 

[John Sh]

Junee7. I've fixed my problem. It isn't in the sorting but the move through the records that I was attempting afterwards.
My main form is a single form and doesn't take kindly to moving through multiple fields being ordered.
I'll have to do a bit more work on this, so I thank you for your time.
John

<is it a "subform" or a "pop-up" form?>
It is actually a pop-up form.

 

[MajP]

Are you sure that works?
An empty combobox should have a value of Null not "". I would be surprised that worked. At least I would do
Me.cboSort1 & "" <> ""