DLookup - Syntax

M

magpie

I need to look up data from other tables to a form, but I cannot seem to get
this right.

I have entered an expression in the control source for an unbound text box
called 'Chassis number', to look at the value entered in a data entry box
called 'Sequence number' and enter the corresponding value in a table
'Sequence numbers', whixh is not the main table for the form (This is
'Stocktake res').

The expression I have entered is:

=DLookup("[Chassis number]","Sequence numbers","[Sequence number] =" _ &
Forms![Stocktake res]!Sequence number)

but the syntax isn't correct. I have used the help screen, but am unsure
about the underscore and gaps in the example given.

The form is just called Form1.

I should be grateful for any help with this - especially as I need to look
up a lot of other fields and tables once this is solved!

Thank you

magpie
 
A

Amy Blankenship

If you're going to use the underscore, the & needs to be on the NEXT line of
code.

HTH;

Amy
 
T

Tatakau

there is no need for the underscore. the underscore is just used when you
want to continue writing code from a statement on the next line, to make it
look pretty. Try it all on one line. The format is:

=DLookup("[field name]","table name","WHERE conditions")

For example:

=DLookup("[phone number]","people","[first name] = 'John' AND [last name] =
'Smith'")

or:

=DLookup("[Chassis number]","Sequence numbers","[Sequence number]=" &
me![sequence number])

I don't know quite what you're trying to do with the whole "Forms![stocktake
res]!sequence number" deal, but that doesn't seem quite right. Not that I
can tell without looking at your code, but try using "me!ControlName".

Also, you can't use this in a control source to enter data into your tables.
If you want to add data to a table automatically, you will need to use SQL.

DoCmd.RunSQL "UPDATE TableName SET lastname=" & me!lastname & " WHERE
phonenumber=" & me!phonenumber & ";"

ehh... you can probably figure it out. if not, you can always come back to
the forums!

hth,

Nick
 
G

George Nicholson

The form is just called Form1.


In that case, I'd try something closer to:
Forms![Form1].[Sequence number])

HTH,
 
M

magpie

Thank you - I am using expression builder and ma unsure how this should
appear. Presumably I delete the underscore - if so, should there be gaps
before and after the ampersand?

I have now written a query in the hope of simplifying this, but it still
doesn't work. The expression now in design view is:


=DLookUp("Chassis number","Form Query 01")

The query looks up the chassis number and sequence number from another
table. The sequence number is entered on the form.

Thank you again for your help


Amy Blankenship said:
If you're going to use the underscore, the & needs to be on the NEXT line of
code.

HTH;

Amy

magpie said:
I need to look up data from other tables to a form, but I cannot seem to
get
this right.

I have entered an expression in the control source for an unbound text box
called 'Chassis number', to look at the value entered in a data entry box
called 'Sequence number' and enter the corresponding value in a table
'Sequence numbers', whixh is not the main table for the form (This is
'Stocktake res').

The expression I have entered is:

=DLookup("[Chassis number]","Sequence numbers","[Sequence number] =" _ &
Forms![Stocktake res]!Sequence number)

but the syntax isn't correct. I have used the help screen, but am unsure
about the underscore and gaps in the example given.

The form is just called Form1.

I should be grateful for any help with this - especially as I need to look
up a lot of other fields and tables once this is solved!

Thank you

magpie
Click to expand...
Click to expand...
 
A

Amy Blankenship

magpie said:
Thank you - I am using expression builder and ma unsure how this should
appear. Presumably I delete the underscore - if so, should there be gaps
before and after the ampersand?

I have now written a query in the hope of simplifying this, but it still
doesn't work. The expression now in design view is:


=DLookUp("Chassis number","Form Query 01")

The query looks up the chassis number and sequence number from another
table. The sequence number is entered on the form.

Thank you again for your help
Click to expand...

=DLookup("[Chassis number]","Sequence numbers","[Sequence number] =" &
Forms![Stocktake res]!Sequence number)
 
Top