Mike Rudolph
asked on
MS Access DoCmd Find Record based on textfield value
Experts,
I have a text box on my form (txtLastName). I would like to tie an event to a button that would search the table and find any records that matched the value in the textbox. For example, if the user enters 'Smith' in the textbox the DoCmd will search my query and filter records based on 'Smith' (value in textfield).
Thanks!
I have a text box on my form (txtLastName). I would like to tie an event to a button that would search the table and find any records that matched the value in the textbox. For example, if the user enters 'Smith' in the textbox the DoCmd will search my query and filter records based on 'Smith' (value in textfield).
Thanks!
If you didnt want the filtering but wanted to find the record you can use the DoCmd like you said
DoCmd.FindRecord "find what", match type, case match, direction, date check format, current field only, find first
so in your case you could do
DoCmd.FindRecord Me.txtLastName, , , acDown, , acCurrent, true
if you wanted on the change remember to use the afterupdate event
DoCmd.FindRecord "find what", match type, case match, direction, date check format, current field only, find first
so in your case you could do
DoCmd.FindRecord Me.txtLastName, , , acDown, , acCurrent, true
if you wanted on the change remember to use the afterupdate event
In the onclick of your button add this code
Sub cmbYourButtonName_OnClick( )
Dim sFilter As STring
If Nz(Me.txtLastName, "") <> "" Then
sFilter = "LastName=" & Me.txtLastName"'"
End If
Me.Form.filter = sFilter
Me.Form.FilterOn = True
End Sub
Sub cmbYourButtonName_OnClick(
Dim sFilter As STring
If Nz(Me.txtLastName, "") <> "" Then
sFilter = "LastName=" & Me.txtLastName"'"
End If
Me.Form.filter = sFilter
Me.Form.FilterOn = True
End Sub
ASKER
rockiroads:
Your first example worked but the second example (search for only one record) gave me a Run-Time error of 2162 (A macro set to one of the current field's properties failed because of an error in a Find Record action argument)
I am using the buttons (cmdSearchLname) On Click event (no afterupdate event for buttons) to run this code. Any ideas. Also, how would I disply a message if the txtLastName field was left blank to force the user to enter a value.
Thanks!
Your first example worked but the second example (search for only one record) gave me a Run-Time error of 2162 (A macro set to one of the current field's properties failed because of an error in a Find Record action argument)
I am using the buttons (cmdSearchLname) On Click event (no afterupdate event for buttons) to run this code. Any ideas. Also, how would I disply a message if the txtLastName field was left blank to force the user to enter a value.
Thanks!
ASKER
t_hungate:
When I tried your code I get a syntax error on this line:
sFilter = "LastName=" & Me.txtLastName"'"
When I tried your code I get a syntax error on this line:
sFilter = "LastName=" & Me.txtLastName"'"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!
No worries :)
Remember the direction. I have used acDown but there is also 2 others acSearchAll and acUp
just do f1 help on FindRecord for more info
Remember the direction. I have used acDown but there is also 2 others acSearchAll and acUp
just do f1 help on FindRecord for more info
for example your table is based on tableA and txtLastName is not bounded to any field because you are using it as a search
On entering the name use the AfterUpdate event of the txtbox or have a button to do the search eg cmdSearch
Me.Filter = "Surname = '" & Me.txtLastName & "'"
Me.FilterOn = true
this then filters your current records to display only those with a surname of what you entered