Hi All,
I am hoping someone out there will be kind enough to find out where my
code is going wrong. The current code is inefficiant but hopefully it
will convey the data I require to be filtered.
Basically I have a popup form which has 6 optional controls to filter
records in another form. The code below does not work. Can anyone
suggest some correctons or alternatives.
Thanks,
Nathan
Private Sub Search_Click()
Dim varFilter1 As String
Dim varFilter2 As String
Dim varFilter3 As String
Dim varFilter4 As String
Dim varFilter5 As String
Dim varFilter6 As String
varFilter1 = "[LoadsheetNo] = #" & Forms!frmSearch![Filter1] & "#"
varFilter2 = "[CarrierConnote] = #" & Forms!frmSearch![Filter2] & "#"
varFilter3 = "[Date] = #" & Forms!frmSearch![Filter3] & "#"
varFilter4 = "[CarrierName] = Forms!frmSearch![Filter4]"
varFilter5 = "[SendingStoreNo] = #" & Forms!frmSearch![Filter5] & "#"
varFilter6 = "[ReceivingStoreNo] = #" & Forms!frmSearch![Filter6] &
"#"
Dim strSQL As String, intCounter As Integer
'Build SQL String
If Application.CurrentProject.AllForms("frmLoadsheets Search").IsLoaded
Then
DoCmd.Close acForm, "frmLoadsheetsSearch"
DoCmd.OpenForm "frmLoadsheetsSearch", acViewNormal
For intCounter = 1 To 6
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & Chr(34) & "Filter" & intCounter & Chr(34) &
" And "
End If
Next
If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Forms![frmLoadsheetsSearch].Filter = "[LoadsheetNo] = #" &
Forms!frmSearch![Filter1] & "#"
Forms![frmLoadsheetsSearch].FilterOn = True
GoTo Endcode
End If
Else
DoCmd.OpenForm "frmLoadsheetsSearch", acViewNormal
For intCounter = 1 To 6
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & Chr(34) & "Filter" & intCounter & Chr(34) & " And "
End If
Next
If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Forms![frmLoadsheetsSearch].Filter = strSQL
Forms![frmLoadsheetsSearch].FilterOn = True
End If
End If
Endcode:
End Sub