DLookup in Forms

J

Jez

Hi, I have a textbox in which I enter a Person Pay ID, I want to try and auto
populate several other textboxes with Name and Workplace, I understand I need
to use a DLookup, but unsure on how it works

How can this be done?

Jez
 
S

Scott McDaniel

Hi, I have a textbox in which I enter a Person Pay ID, I want to try and auto
populate several other textboxes with Name and Workplace, I understand I need
to use a DLookup, but unsure on how it works
Click to expand...

You can use DLookup, or you can open a Recordset to get the data. Use the AfterUPdate event of the textbox:

Sub YourTextbox_AfterUpdate()
Dim rst As DAO.REcordset

Set rst = Currentdb.OpenRecordset("SELECT * FROM YourPersonTable WHERE PersonIDFIeld=" & Me.YourTextbox)
If Not(rst.EOF and rst.BOF) Then
'/now populate the textboxes
Me.txtName = rst("sName")
Me.txtWorkplace = rst("sWorkPlace")
End IF

Set rst = Nothing
End Sub

There are some potential issues with this, however. If the form is bound, then realize that doing this ANYTIME the ID is
updated will also update the underlying table data ... this is probably what you want to do anyway, but just be aware of
what's going on.

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com
 
J

Jez

Scott,
Thanks for the reply, I understand what its doing apart from the lines like
this
Me.txtName = rst("sName")
I am not sure what the ("sName") is to mean?
I have put this into the code and it doesnt seem to work.

Jez
 
D

Douglas J. Steele

Scott's assuming that there's a field sName in the table that contains the
data you want to display in the text box named txtName. If your field name
is something different, replace sName with that field name (still within
quotes).
 
J

Jez

Thanks for this it works, but I have now tried it on another textbox for
using another table and I keep getting a run time error (3075) witha a
message of "Syntax Error (Missing Operator) in querry expression
The error picks up on this line below on

Set rst = Currentdb.OpenRecordset("SELECT * FROM YourPersonTable WHERE
PersonIDFIeld=" & Me.YourTextbox)

It seems to be the section after WHERE. When I have tried the (CTRL+G)
Intermediate window it shows the txtbox with a value which it should and the
place where I want to put in as a null value

Jez
 
D

Douglas J. Steele

Is PersonIDField text? If so, you need quotes around what you're passing as
an argument:

Set rst = Currentdb.OpenRecordset( _
"SELECT * FROM YourPersonTable WHERE " & _
"PersonIDFIeld=" & Chr$(34) & Me.YourTextbox & Chr$(34))

Chr$(34) corresponds to "
 
Top