ACCESS FEED: [MS_AccessPros] How to open a continuous form with a certain order using order by

Jumat, 29 Juni 2012

[MS_AccessPros] How to open a continuous form with a certain order using order by

Hi all!

I've been working on opening another form with the records filtered and ordered based upon entries unbound boxes in the 1st form. The filtering is working beautifully thanks to John's help eons ago!

Now I'm trying to have similar unbound boxes that will determine the order of the records. I've created comboboxes where column 0 (width = 0) has the same name as the field in the form.

I've but the code for the ordering below. Also since I used OpenArgs I put what the code for that from the 2nd form (the one being opened).

Thanks again!
Connie

Code from first form "LookingEvents" where a button is clicked to open Search_AllListings from with filtered and ordered records:

'Create OrderBy string to be used in the OpenArgs and then in Form Open
If Nz(ordr1) <> 0 Then
strOrder = strOrder & [ordr1].Column(0) & ", "
End If
If Nz(Ordr2) <> 0 Then
strOrder = strOrder & [Ordr2].Column(0) & ", "
End If
If Nz(Ordr3) <> 0 Then
strOrder = strOrder & [Ordr3].Column(0) & ", "
End If

'See if the string has more than 2 characters (a trailng ", ") to remove message
lngLen2 = Len(strOrder) - 2
If lngLen2 <= 0 Then
MsgBox "No criteria have been entered"
Else 'Yep: there is something there, so remove the " AND " at the end.
strOrder = Left$(strOrder, lngLen2)
End If

'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
'Debug.Print strOrder
'Open form with where and Order statement
DoCmd.OpenForm "Search_AllListings", , , strWhere, , , strOrder

Code from Search_AllListings form:
Dim strOrder As String
If Not IsNull(OpenArgs) Then
Me.OrderBy = Me.OpenArgs
If Me.OrderByOnLoad = False Then
Me.OrderByOnLoad = True
End If
End If