Dlookup to populate a form

[David]

I am trying to use Dlookup to populate a text box on a form, but
haven't had any good luck so far. I've looked here at the posts
and have used the Access help for examples. Northwind is way over my
head. I think that I'm coming up short in the Criteria part of the
function.

I have a combo box that looks to the 'Student' table,
'Student_ID' field to list the students, and then places the
selected value in the 'Student_ID' text box on the 'Order'
form. The combo box part works well. The text boxes on the
'Order' form are bound to an 'OrderEntry' table.

What I am trying to do is to fill in the 'LastName' text box in the
'Order' form using the data from the 'Student' table,
'LastName' field. I think that the criteria for the lookup would
be the value in the 'Student_ID' combo box on the form, using an
event procedure triggered by the AfterUpdate event.

What I would like is to have an example of the Dlookup function as it
applies to my application.

Many thanks for your time and any help given.

David

[ruralguy via AccessMonster.com]

Try this for size. Base you "Order" form on a query that joins the
"OrderEntry" table to the "Student" table by the "Student_ID" field and
include the "LastName" field in the returned fields. The bind your
txtLastName control to the LastName field and when you select the student
with the ComboBox, the txtLastName control will just magically complete
itself.

David wrote:

>I am trying to use Dlookup to populate a text box on a form, but
haven't had any good luck so far. I've looked here at the posts
and have used the Access help for examples. Northwind is way over my
head. I think that I'm coming up short in the Criteria part of the
function.

I have a combo box that looks to the 'Student' table,
'Student_ID' field to list the students, and then places the
selected value in the 'Student_ID' text box on the 'Order'
form. The combo box part works well. The text boxes on the
'Order' form are bound to an 'OrderEntry' table.

What I am trying to do is to fill in the 'LastName' text box in the
'Order' form using the data from the 'Student' table,
'LastName' field. I think that the criteria for the lookup would
be the value in the 'Student_ID' combo box on the form, using an
event procedure triggered by the AfterUpdate event.

What I would like is to have an example of the Dlookup function as it
applies to my application.

Many thanks for your time and any help given.

David

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200612/1

[AccessVandal via AccessMonster.com]

Hi David,

Assuming the table “Student� has a column named “LastName� and in your
“Student_ID� ComboBox, your rowsource should look like,

“SELECT [Student_ID], LastName FROM Student�

Make sure the combo Format tab properties – ColumnCount = 2.

Since combo “Student_ID� and TextBox “ LastName� were bound to the OrderEntry
table.

In the AfterUpdate event of your ComboBox, try this

Me.LastName = DlookUp(“LastName�, Student�, “[Studen_ID] = � & Me.Student_ID)

'if Student_ID datatype is text, use the quotes like .. Student_ID = '" & me.
Student_ID & "'")

Or

Me.LastName = Me.Student_ID.Column(1)

‘.Column(0) is the Query grid Field “Student_ID�
‘.Column(1) is the Query grid Field “LastName�
‘To use this, you need to set the “Student_ID combobox ColumnCount = 2 in the
combobox properties.

>David wrote:

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200612/1

[Geoff G]

Try

DLookUp("LastName","Student","Student_ID = " & Me.[Name of your combo
box])

This should work if your Student_ID is a number field

If it's a text field try

DLookUp("LastName","Student","Student_ID = ' " & Me.[Name of your combo
box] & " ' ")

Geoff