Solved

MS Access DoCmd Find Record based on textfield value

Posted on 2010-09-18
8
4,596 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

735 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