Main Form / Sub Form Search

[doodle]

windows xp
access 97

I have a main form with a search option. It allows the user to seelct
if they want to search by Customer, Machine Serial # or Order #. Then
select the option, type the value they want to search for into a text
box (txtSearch), then click on a command button (cmdSearch).

First it checks to see which option they chose, then it checks the
tables to see if there are any matches, then prompts the user to tell
them the number of matches, then filters the forms to the matches.

Everything works dandy except:

1. The filter is not working. It is prompting for a parameter for this
portion of the code:
Me.Filter = "[txtMachineSN]=" & "'" & Me![txtSearch] & "'" (Prompts for
txtMachineSN)

2. I want to change the customer filter to search for records LIKE the
value that they entered in txtSearch.

3. The last option, order #, is supposed to search the subform for
order numbers that match txtSearch and filter the main form for
matches. Right now it is prompting for a parameter (MachineSN) and then
filters the main form to an empty record.

thanks for your help,


-doodle

here is my code:

Private Sub cmdSearch_Click()
'On Error GoTo tagError

Dim myStr As String
Dim i As Integer
Dim cntX As Integer

i = Me.frmSearch.Value


If IsNull(Me.txtSearch) Then
MsgBox Prompt:="You must enter a value to search for.",
Buttons:=vbOKOnly
Else
Select Case i
Case Is = 1 'customer
'Checks to see if there are any matching records in the
table
cntX = DCount("[Customer]", "tblJL_Main", "[Customer]=" &
"'" & Me![txtSearch] & "'")
myStr = "There are " & cntX & " records that match your
search."
'Tells the user how many matches there are
MsgBox myStr
'Filters form for matches
Me.Filter = "[txtCustomer]=" & "'" & Me![txtSearch] & "'"
Me.FilterOn = True

Case Is = 2 'serial #
'Checks to see if there are any matching records in the
table
cntX = DCount("[MachineSN]", "tblJL_Main", "[MachineSN]=" &
"'" & Me![txtSearch] & "'")
myStr = "There are " & cntX & " records that match your
search."
'Tells the user how many matches there are
MsgBox myStr
'Filters form for matches
Me.Filter = "[txtMachineSN]=" & "'" & Me![txtSearch] & "'"
Me.FilterOn = True

Case Is = 3 'order number
'Checks to see if there are any matching records in the
table
cntX = DCount("[OrderNum]", "tblJL_OrderNum", "[OrderNum]="
& "'" & Me![txtSearch] & "'")
myStr = "There are " & cntX & " records that match your
search."
'Tells the user how many matches there are
MsgBox myStr
'Filters form for matches
Me.sbfmJL_OrderNum.Form.Filter = "[txtOrderNum]=" & "'" &
Me![txtSearch] & "'"
Me.FilterOn = True
End Select

End If
Exit Sub

tagError:
MsgBox Err.Description
Me.FilterOn = False

End Sub

 

[doodle]

I fixed my first two issues. I am still working on the third and would
love some help.

My code is breaking on this line and prompting the user for a
machineSN?

'Filters form for matches
Me.sbfmJL_OrderNum.Form.Filter = "[OrderNum] Like ""*" &
[txtSearch] & "*"""
Me.FilterOn = True

Here is all of my code, (revised from last time):

Private Sub cmdSearch_Click()
'On Error GoTo tagError

Dim myStr As String
Dim i As Integer
Dim cntX As Integer

i = Me.frmSearch.Value


If IsNull(Me.txtSearch) Then
MsgBox Prompt:="You must enter a value to search for.",
Buttons:=vbOKOnly
Else
Select Case i
Case Is = 1 'customer
'Checks to see if there are any matching records in the
table
cntX = DCount("[Customer]", "tblJL_Main", "[Customer]=" &
"'" & Me![txtSearch] & "'")
myStr = "There are " & cntX & " records that match your
search."
'Tells the user how many matches there are
MsgBox myStr
'Filters form for matches
Me.Filter = "[Customer] Like ""*" & [txtSearch] & "*"""
Me.FilterOn = True

Case Is = 2 'serial #
'Checks to see if there are any matching records in the
table
cntX = DCount("[MachineSN]", "tblJL_Main", "[MachineSN]=" &
"'" & Me![txtSearch] & "'")
myStr = "There are " & cntX & " records that match your
search."
'Tells the user how many matches there are
MsgBox myStr
'Filters form for matches
Me.Filter = "[MachineSN] Like ""*" & [txtSearch] & "*"""
Me.FilterOn = True

Case Is = 3 'order number
'Checks to see if there are any matching records in the
table
cntX = DCount("[OrderNum]", "tblJL_OrderNum", "[OrderNum]="
& "'" & Me![txtSearch] & "'")
myStr = "There are " & cntX & " records that match your
search."
'Tells the user how many matches there are
MsgBox myStr
'Filters form for matches
Me.sbfmJL_OrderNum.Form.Filter = "[OrderNum] Like ""*" &
[txtSearch] & "*"""
Me.FilterOn = True
End Select

End If
Exit Sub

tagError:
MsgBox Err.Description
Me.FilterOn = False

End Sub

 

[doodle]

fixed with this:

Private Sub cmdSearch_Click()
'On Error GoTo tagError

Dim myStr As String
Dim i As Integer
Dim cntX As Integer

i = Me.frmSearch.Value


If IsNull(Me.txtSearch) Then
MsgBox Prompt:="You must enter a value to search for.",
Buttons:=vbOKOnly
Else
Select Case i
Case Is = 1 'customer
'Checks to see if there are any matching records in the
table
cntX = DCount("[Customer]", "tblJL_Main", "[Customer] Like
""*" & [txtSearch] & "*""")
myStr = "There are " & cntX & " records that match your
search."
'Tells the user how many matches there are
MsgBox myStr
'Filters form for matches
Me.Filter = "[Customer] Like ""*" & [txtSearch] & "*"""
Me.FilterOn = True

Case Is = 2 'serial #
'Checks to see if there are any matching records in the
table
cntX = DCount("[MachineSN]", "tblJL_Main", "[MachineSN]=" &
"'" & Me![txtSearch] & "'")
myStr = "There are " & cntX & " records that match your
search."
'Tells the user how many matches there are
MsgBox myStr
'Filters form for matches
Me.Filter = "[MachineSN] Like ""*" & [txtSearch] & "*"""
Me.FilterOn = True

Case Is = 3 'order number
'Checks to see if there are any matching records in the
table
cntX = DCount("[OrderNum]", "tblJL_OrderNum", "[OrderNum]="
& "'" & Me![txtSearch] & "'")
myStr = "There are " & cntX & " records that match your
search."
'Tells the user how many matches there are
MsgBox myStr
'Filters form for matches
'Me.sbfmJL_OrderNum.Form.Filter = "[OrderNum] Like ""*" &
[txtSearch] & "*"""
Me.Filter = "[OrderNum] Like ""*" & [txtSearch] & "*"""
Me.FilterOn = True
End Select

End If
Exit Sub