Using Dlookup on forms - Please help!!!

[Guest]

Hi,

Basically im trying to add a dlookup field to the form 'frminvoice', where
the user selects a 'Client name' from the combo box linked to the table
'tblclients', which is then used to find 'Address 1' in the table
'tblClients', and inserted into the address 1 field of the form.

Iv tried everything but cant get it to work.

My current code is =DLookUp(tblClient!"[Address 1]",["tblClient"], "[Client
Name] = " _ & Forms! [frminvoices] ! [Client Name] )

Any help would be gratefully appreciated.

Kind regards,
Michael Jacques

 

[Guest]

DLookUp("[Address 1]","tblClient", "[Client Name] = '" & Forms! [frminvoices]
! [Client Name] & "'")

 

[Douglas J Steele]

Get rid of the tblClient! in front of "[Address 1]"

As well, since presumably Client Name is a text field, you need to enclose
the name being passed in quotes. Normally you can use single quotes to do
this, but since names might include apostrophes, it's better to use double
quotes:

=DLookUp("[Address 1]",["tblClient"], "[Client Name] = " _
& Chr$(34) & Forms![frminvoices]![Client Name] & Chr$(34))

 

[Van T. Dinh]

--
HTH
Van T. Dinh
MVP (Access)

 

[Van T. Dinh]

In addition to DLookUp, there may be simpler ways you can do this:

1. Join the tblClients to the RecordSource of your Form and select the
[Address 1] Field into the RecordSource. Bind the TextBox to the Field
[Address 1]. As soon as you select a Client (which identifies the [Address
1] Field), the TextBox on the Form will be filled with the [Address 1] value
from the tblClient.

This is how Billing Address details are filled in in the Orders Form /
Orders Subform of the sample database NorthWind.

2. Add the Field [Address 1] to the RowSource of your ComboBox Client and
use the ComboBox_AfterUpdate Event to "push" the value of [Address 1] from
the RowSource to the TextBox.

This is how Delivery Address details are filled in the same sample mentioned
above.