Where do I put DLookup?

[Sharon]

My MainForm feeds off of the AuditCklst Query. I want
the Contact Name from another table or form or I don't
care where from...identified by the account# , to appear
in the MainForm.

Can I pull the Contact Name directly into my Main Form?
If so, where do I put the Dlookup? In the VB code? In
the control source for an unbound text box? Where do I
pull the Contact Name from? The Contact Table? The
Contact Form? The Contact Query? Following is one
attempt I made in the Control Source box.

ContactName = DLookup
("[first_name]", "[last_name]", "[account]=" & Forms!
frmCompRevContacts![account])

 

[fredg]

My MainForm feeds off of the AuditCklst Query. I want
the Contact Name from another table or form or I don't
care where from...identified by the account# , to appear
in the MainForm.

Can I pull the Contact Name directly into my Main Form?
If so, where do I put the Dlookup? In the VB code? In
the control source for an unbound text box? Where do I
pull the Contact Name from? The Contact Table? The
Contact Form? The Contact Query? Following is one
attempt I made in the Control Source box.

ContactName = DLookup
("[first_name]", "[last_name]", "[account]=" & Forms!
frmCompRevContacts![account])

Sharon,
What is [Last Name]?
Is it a Table name or, more likely, a Field name?

The syntax for DLookUp is quite specific.
=DLookUp("[WhatFieldName]","FromWhatTableName","[WhereSomeField] = " &
[ThisField])

So, putting all the above together, assuming you want the result to show
the [Contact Name] from the [Contact Table] where the Account equals the
Account shown on this form....

As the control source of an Unbound text control in the Main Form,
write:
= DLookUp("[ContactName]","[ContactTable]","[Account] = " &
Me![Account])

The above assumes [Account] is a Number Datatype.
If it is a Text Datatype then use, as the Where clause,
"[Account] = '" & Me![Account] & "'")

If you need a last name and a first name from 2 different fields, you
will need to have 2 separate DLookUps, one for each of the names.

 

[Greg Casper]

Sharon:

Try this:

ContactName = (DLookup("[first_name]",
"YOURCONTACTTABLENAMEHERE","[account=]" &
Forms!frmCompRevContacts![account]) & " " & DLookup("[last_name]",
"YOURCONTACTTABLENAMEHERE","[account=" &
Forms!frmCompRevContacts![account]))

 

[Steve Schapel]

Sharon,

If you want the Contact Name to appear on the MainForm, your DLookup
would go in the controlsource of an unbound textbox. Assuming that
first_name and last_name and account are all fields in your Contact
table, and assuming that account is a number data type, your DLookup
expression would be...
=DLookup"[first_name] & ' ' & [last_name]","Contact","[account]=" &
[account])
.... or, you could do it like this...
=DLookup"[first_name] & ' ' &
[last_name]","Contact","[account]=[Forms]![frmCompRevContacts]![account]")
If account is a text data type, you will need...
=DLookup"[first_name] & ' ' & [last_name]","Contact","[account]='" &
[account] & "'")

Another approach, though, might be to include the Contact table in the
AuditCklst query, joined obviously on the account field, and then you
can directly show the contact name on the form.

- Steve Schapel, Microsoft Access MVP