D
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
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