Hi again. I've used the "Stop" plus F8 method to go through my code to
see what my search button is actually doing. It does everythinbg right
but no results are being displayed in the detail part of the form. I
must be missing something small what might it be.
Here is a copy of the code i have behind my search button:
Private Sub Search_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you
can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one
onwards; _
End date only = all dates up to (and
including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria
string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates in a JET query string.
'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.
'***********************************************************************
'Number field example. Do not add the extra quotes.
If Not IsNull(Me.cbofilterCableType) Then
strWhere = strWhere & "([Cable Type] Like "" * " &
Me.cbofilterCableType & " * "") AND "
End If
If Not IsNull(Me.cbofilterSiteDrumNo) Then
strWhere = strWhere & "([Site Drum No] Like "" * " &
Me.cbofilterSiteDrumNo & " * "") AND "
End If
If Not IsNull(Me.cbofilterManufacturer) Then
strWhere = strWhere & "([Manufacturer] Like "" * " &
Me.cbofilterManufacturer & " * "") AND "
End If
If Not IsNull(Me.cbofilterSize) Then
strWhere = strWhere & "([Size] Like "" * " & Me.cbofilterSize &
" * "") AND "
End If
If Not IsNull(Me.cbofilterCores) Then
strWhere = strWhere & "([Cores] Like "" * " & Me.cbofilterCores
& " * "") AND "
End If
If Not IsNull(Me.cbofilterConductor) Then
strWhere = strWhere & "([Conductor] Like "" * " &
Me.cbofilterConductor & " * "") AND "
End If
If Not IsNull(Me.cbofilterConductorType) Then
strWhere = strWhere & "([Conductor Type] Like "" * " &
Me.cbofilterConductorType & " * "") AND "
End If
If Not IsNull(Me.cbofilterStandard) Then
strWhere = strWhere & "([Standard] Like "" * " &
Me.cbofilterStandard & " * "") AND "
End If
If Not IsNull(Me.cbofilterVoltageRange) Then
strWhere = strWhere & "([Voltage Range] Like "" * " &
Me.cbofilterVoltageRange & " * "") AND "
End If
If Not IsNull(Me.cbofilterOutsideDiammeter) Then
strWhere = strWhere & "([Outside Diammeter] Like "" * " &
Me.cbofilterOutsideDiammeter & "*"") AND "
End If
' If Not IsNull(Me.cbofilterArmoured) Then
' strWhere = strWhere & "([Armoured] Like "" * " &
Me.cbofilterArmoured & " * "") AND "
' End If
If Not IsNull(Me.cbofilterLocation) Then
strWhere = strWhere & "([Location] Like "" * " &
Me.cbofilterLocation & " * "") AND "
End If
'Yes/No field and combo example. If combo is blank or contains
"ALL", we do nothing.
If Me.cbofilterArmoured = -1 Then
strWhere = strWhere & "([Armoured] = True) AND "
ElseIf Me.cbofilterArmoured = 0 Then
strWhere = strWhere & "([Armoured] = False) AND "
End If
'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ")
to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove
the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line.
Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere
'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
Any helpful tips would be great,
thanks
Martin
