Solved

MS Access DoCmd Find Record based on textfield value

Posted on 2010-09-18
8
4,407 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
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…

828 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