Newbie DLookUp

[Guest]

I am trying to set up a DLookUp in Access 2000. What I am trying to do is
automatically populate the Name/Address/City, State, & Zip when you enter the
client number in a form.

The form [Engineer_AP] currently has fields names:
Engineer (5 digit Alpha), Name, Address, City, State, & Zip.

The table that I am trying to get this from [Consultants] has the same fields.

The DLookUp that I am using is as follows:

=DLookUp("[Name]","[Consultants]","[Engineer] = " &
[Forms]![Engineer_AP]![Engineer])

all I get is a flashing #Error in the form. I have tested the Lookup up
(using the CTRL+G command) to the =DLookUp("[Name]","[Consultants]") and
received the first Consultants name as suggested in the help, but can not get
any further. Any assistance would be appreciated.

 

[Rick Brandt]

I am trying to set up a DLookUp in Access 2000. What I am trying to
do is automatically populate the Name/Address/City, State, & Zip when
you enter the client number in a form.

The form [Engineer_AP] currently has fields names:
Engineer (5 digit Alpha), Name, Address, City, State, & Zip.

The table that I am trying to get this from [Consultants] has the
same fields.

The DLookUp that I am using is as follows:

=DLookUp("[Name]","[Consultants]","[Engineer] = " &
[Forms]![Engineer_AP]![Engineer])

all I get is a flashing #Error in the form. I have tested the Lookup
up (using the CTRL+G command) to the
=DLookUp("[Name]","[Consultants]") and received the first Consultants
name as suggested in the help, but can not get any further. Any
assistance would be appreciated.

If [Engineer] is text then you need quotes around the criteria...

=DLookUp("[Name]","[Consultants]","[Engineer] = '" &
[Forms]![Engineer_AP]![Engineer] & "'")

 

[fredg]

I am trying to set up a DLookUp in Access 2000. What I am trying to do is
automatically populate the Name/Address/City, State, & Zip when you enter the
client number in a form.

The form [Engineer_AP] currently has fields names:
Engineer (5 digit Alpha), Name, Address, City, State, & Zip.

The table that I am trying to get this from [Consultants] has the same fields.

The DLookUp that I am using is as follows:

=DLookUp("[Name]","[Consultants]","[Engineer] = " &
[Forms]![Engineer_AP]![Engineer])

all I get is a flashing #Error in the form. I have tested the Lookup up
(using the CTRL+G command) to the =DLookUp("[Name]","[Consultants]") and
received the first Consultants name as suggested in the help, but can not get
any further. Any assistance would be appreciated..

1) If [Engineer] is Text datatype, then:
=DLookUp("[Name]","[Consultants]","[Engineer] = '" &
Me![Engineer] & "'")

2) Make sure the name of this control is not the same as the name of
any field or table used in it's control source expression.

3) Name is a reserved Access/VBA/Jet word and should not be used as a
field name.
See the Microsoft KnowledgeBase article for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

4) A better solution might be to use a combo box that includes all the
above fields in it's row source. Then simply code the AfterUpdate
event of the combo box:
[NameControl] = Me!ComboName.column(1)
[AddressControl] = Me!ComboName.column(2)
[CityControl] = Me!ComboName.column(3)
etc.

Note: Combo Boxes are Zero based, so Column(1) is actually the 2nd
column.