Sorting Records

[Duane]

I am using search predicate to populate a form. I am wondering if it is
possible to set the sort order when using this method.

If a user enters data in the Number field I would like to sort by number,
however, if the user enter criteria in the lock field I would like the
records sorted by the lock field.

Here is the code I am using:

varWhere = Null

'''If specified a prisoner number
If Not IsNothing(Me.PNumber) Then
'''build the predicate
varWhere = "[txtNumber] LIKE '" & Me.PNumber & "*'" & " AND " &
"[txtUnit] Like '" & Me.Unit & "*'"
End If

'''If specified a lock
If Not IsNothing(Me.Lock) Then
'''build the predicate
varWhere = (varWhere + " OR ") & "[txtLock] Like '" & Me.Lock & "*'"
& " AND " & "[txtUnit] Like '" & Me.Unit & "*'"
End If

If IsNothing(varWhere) Then
MsgBox "You must enter at least one search criteria.",
vbInformation, gstrAppTitle
Exit Sub
End If

If IsFormLoaded("frmTickler") Then
If vbYes = MsgBox("The Tickler window is already open. This search
" & _
"will cancel any pending edits in that window, close it, and " &
_
"attempt to reopen with the criteria you specified." & _
vbCrLf & vbCrLf & "Are you sure you want to proceed?", _
vbQuestion + vbYesNo + vbDefaultButton2, gstrAppTitle) Then
' Close using the form's Cancel routine
DoCmd.Close acForm, "frmTickler"
Else
Exit Sub
End If
End If

'''Open the Tickler form hidden to see if there are any matching records
DoCmd.OpenForm FormName:="frmTickler", WhereCondition:=varWhere,
WindowMode:=acHidden
If Forms!frmTickler.RecordsetClone.RecordCount = 0 Then
'''No records found
MsgBox "There are no prisoners that match the specified criteria!", _
vbInformation, gstrAppTitle
DoCmd.Close acForm, "frmTickler"
Exit Sub
Else
DoCmd.OpenForm "frmTickler", acNormal
End If
'''Close me
DoCmd.Close acForm, Me.Name


Thanks in advance....

 

[Allen Browne]

You can sort the records in the form by setting its OrderBy property, and
setting OrdreByOn as well. Example:
With Forms("frmTickler")
.OrderBy = "Lock"
.OrderByOn = True
End With

However, there are several other issues with your search, e.g.:
- You need to use IsNull(), not IsNothing().

- If the criteria is left Null, using wildcards will not return all records.
Those where the field is null will be excluded.