Solved

MS Access DoCmd Find Record based on textfield value

Posted on 2010-09-18
8
3,816 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:shogun5
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:shogun5
Comment Utility
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 250 total points
Comment Utility
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
Comment Utility
Thanks!
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now