Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS Access DoCmd Find Record based on textfield value

Posted on 2010-09-18
8
Medium Priority
?
6,098 Views
Last Modified: 2012-05-10
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!
0
Comment
Question by:shogun5
  • 4
  • 3
8 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 33710154
If you have a form which is bounded to your table then there is no need to perform a docmd

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

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33710163
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
0
 
LVL 10

Expert Comment

by:t_hungate
ID: 33710164
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
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:shogun5
ID: 33710175
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!
0
 

Author Comment

by:shogun5
ID: 33710177
t_hungate:

When I tried your code I get a syntax error on this line:

sFilter = "LastName=" & Me.txtLastName"'"

0
 
LVL 65

Accepted Solution

by:
rockiroads earned 1000 total points
ID: 33710185
If on the button then set focus to the textbox
eg

    Me.txtLastName.SetFocus
    DoCmd.FindRecord Me.txtLastName.Value, , , acDown, , acCurrent, False

I changed last arg to false so it can find the next record
0
 

Author Closing Comment

by:shogun5
ID: 33710232
Thanks!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33710273
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
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Suggested Courses

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question