Filtering a multiple item form

[Erica]

Hello,
I have VBA code that works perfectly for filtering a form by one or
more fields. I am trying to get the same code to work on a multiple
items form - where the records are listed one after the other like a
data sheet, rather than seeing one entire record at a time. This does
not work. Nothing happens when I click on my command button. Is
there something inherently different about this sort of form? Here is
the code that works with the simple form:

Option Compare Database

Private Sub cmdBSSearch_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.txtBSAuthor) Then
strWhere = strWhere & "([Author] Like ""*" & Me.txtBSAuthor & "*"")
AND "
End If

If Not IsNull(Me.txtBSTitle) Then
strWhere = strWhere & "([Title] Like ""*" & Me.txtBSTitle & "*"") AND
"
End If

If Not IsNull(Me.txtBSSubject) Then
strWhere = strWhere & "([Index_Terms] Like ""*" & Me.txtBSSubject &
"*"") AND "
End If

If Not IsNull(Me.txtBSLocation) Then
strWhere = strWhere & "([Location] Like ""*" & Me.txtBSLocation &
"*"") AND "
End If

If Not IsNull(Me.txtBSPubDate) Then
strWhere = strWhere & "([Pub_Date] Like ""*" & Me.txtBSPubDate & "*"")
AND "
End If

If Not IsNull(Me.txtBSCallNumber) Then
strWhere = strWhere & "([Call_Number] Like ""*" & Me.txtBSCallNumber &
"*"") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Private Sub cmdBSReset_Click()
Dim ctl As Control
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox
ctl.Value = Null
End Select
Next
Me.FilterOn = False
End Sub
Private Sub Form_BeforeInsert(Cancel As Integer)
Cancel = True
MsgBox "Ron Whealan prohibits you from adding records to this
database.", vbInformation, "Permission denied."
End Sub


Private Sub Form_Open(Cancel As Integer)
Me.Filter = "(False)"
Me.FilterOn = True
End Sub

Thanks in advance for your help.

 

[BruceM via AccessMonster.com]

The form's View should not matter.

Nothing jumps out at me, except maybe that I wonder about using wildcards
rather than date delimiters (#) with what seems to be a date field.

Try adding Option Explicit just below Option Compare Database, then try
compiling the code. This will ensure nothing is being treated inadvertently
as a variable.

Have you tried stepping through the code? Click the vertical bar to the left
of the code window. This should place a dot in the bar, and highlight the
line of code. Then try running the code, pressing F8 to step through the
code one line at a time. Hover the mouse over variables, field name, etc. to
see their values.

Another thing to try is to add:
Debug.Print strWhere
before applying the filter. You could add it at several places in the code,
if you wish. After running the code, press Ctrl + G to open the immediate
window. You can review whether the filter string is as you expect.

One other troubleshooting idea is to comment out all but one of the If Not
IsNull ... End If blocks of code, and see if anything happens differently
with just one field being tested in strWhere. If it works, try adding the
others one at a time. If it doesn't work, try a different block of code.

Hello,
I have VBA code that works perfectly for filtering a form by one or
more fields. I am trying to get the same code to work on a multiple
items form - where the records are listed one after the other like a
data sheet, rather than seeing one entire record at a time. This does
not work. Nothing happens when I click on my command button. Is
there something inherently different about this sort of form? Here is
the code that works with the simple form:

Option Compare Database

Private Sub cmdBSSearch_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.txtBSAuthor) Then
strWhere = strWhere & "([Author] Like ""*" & Me.txtBSAuthor & "*"")
AND "
End If

If Not IsNull(Me.txtBSTitle) Then
strWhere = strWhere & "([Title] Like ""*" & Me.txtBSTitle & "*"") AND
"
End If

If Not IsNull(Me.txtBSSubject) Then
strWhere = strWhere & "([Index_Terms] Like ""*" & Me.txtBSSubject &
"*"") AND "
End If

If Not IsNull(Me.txtBSLocation) Then
strWhere = strWhere & "([Location] Like ""*" & Me.txtBSLocation &
"*"") AND "
End If

If Not IsNull(Me.txtBSPubDate) Then
strWhere = strWhere & "([Pub_Date] Like ""*" & Me.txtBSPubDate & "*"")
AND "
End If

If Not IsNull(Me.txtBSCallNumber) Then
strWhere = strWhere & "([Call_Number] Like ""*" & Me.txtBSCallNumber &
"*"") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Private Sub cmdBSReset_Click()
Dim ctl As Control
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox
ctl.Value = Null
End Select
Next
Me.FilterOn = False
End Sub
Private Sub Form_BeforeInsert(Cancel As Integer)
Cancel = True
MsgBox "Ron Whealan prohibits you from adding records to this
database.", vbInformation, "Permission denied."
End Sub

Private Sub Form_Open(Cancel As Integer)
Me.Filter = "(False)"
Me.FilterOn = True
End Sub

Thanks in advance for your help.