Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Access DoCmd Find Record based on textfield value

Posted on 2010-09-18
8
Medium Priority
?
5,630 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

705 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