returning matching records based on textbox entry 1

[lowtek]

(Please excuse me if this is the wrong place to post this type of question--if there is a more appropriate place for this type of question, I would greatly appreciate being directed there. Thank you.)

I am an MSAccess brand-newbie (who, by the way, is hard pressed to find any Access forums on the 'net). I have searched MSDN and found lots of useful info...BUT...I need help with something that I have not been able to find information on how to do by trying out the sample databases or other documentation:
I have a form with several textboxes. Only one textbox is input-able. What I would like to do is enter a number in this textbox (employee number, which is stored in a table), and return the matching recordset from selected fields of the same table(which populates the other textboxes--these textboxes are locked). All of the examples I have seen use the navigation at the bottom-left to navigate records, which I don't like, because "record 1" is meaningless as far as which record I am looking for specifically. Any help would be greatly appreciated.

 

[jebry]

Hi!

There are a couple of ways to do this in code. In the After Update event procedure of the text box you can use the following code:

Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "EmployeeNumber = " & Me!YourTextBox.Value

If rst.NoMatch = True Then
Call MsgBox("The employee number you entered does not exist&quot
Else
Me.Bookmark = rst.Bookmark
End If

Set rst = Nothing

The code above assumes that Employee Number is numeric like it sounds. It also assumes that the record source of the form is the table in question. As an alternative you can use:

Dim rst As DAO.Recordset
Dim sql As String

sql = "Select * From YourTable Where EmployeeNumber = " & Me!YourTextBox
Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynaset)
If rst.EOF = True And rst.BOF = True Then
Call MsgBox("The Employee Number you entered does not exist&quot
Else
Me!TextBox1 = rst!FirstField
Me!TextBox2 = rst!SecondField
etc.
End If

Set rst = Nothing

Of course, in both examples you will need to use the field names and text box names that actually exist in your table and form.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[lowtek]

Jebry,

Thank you so much for the help, I really appreciate it a lot!!! As my "project" comes along, I'll more than likely be posting a bit more questions.

Rey

 

[maetrix]

This was exactly what I was looking for. The second example was the key actually. Thanks again.

--Toby--
sapere aude: Dare to be wise