Display Field from unrelated table in form

  • Thread starter Thread starter PC
  • Start date Start date
P

PC

Hi,

I want to display a field from an unrelated table on a form.

Data is displayed on a form when a particular Invoice number is chosen from
a lookup field. Supplier account codes are displayed (The account Code is
what is stored in the Related table [tblInvoice] having originally been
selected from another table [tblSuppliers] with which it has a Many-toOne
Relationship) but I would like to display the account name beside the
account code

I have tried using a Dlookup on a text box
"=DLookUp("[SupName]","tblSuppliers","[txtSupAccountCode]"=Forms!frmInvoiceR
eturned!Supplier)" but this doesn't seem to work.

Any ideas

Thanks
 
You placed a quote in the wrong place and left out an "&".

Try:
=DLookUp("[SupName]","tblSuppliers","[txtSupAccountCode] = " &
Forms!frmInvoiceReturned!Supplier)

The above assumes [txtSupAccountCode] is a Number datatype.

If it is a Text datatype then use:
"[txtSupAccountCode] = '" & Forms!frmInvoiceReturned!Supplier & "'")
 
Thanks Fred. That worked a treat

Fredg said:
You placed a quote in the wrong place and left out an "&".

Try:
=DLookUp("[SupName]","tblSuppliers","[txtSupAccountCode] = " &
Forms!frmInvoiceReturned!Supplier)

The above assumes [txtSupAccountCode] is a Number datatype.

If it is a Text datatype then use:
"[txtSupAccountCode] = '" & Forms!frmInvoiceReturned!Supplier & "'")

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


PC said:
Hi,

I want to display a field from an unrelated table on a form.

Data is displayed on a form when a particular Invoice number is chosen from
a lookup field. Supplier account codes are displayed (The account Code is
what is stored in the Related table [tblInvoice] having originally been
selected from another table [tblSuppliers] with which it has a Many-toOne
Relationship) but I would like to display the account name beside the
account code

I have tried using a Dlookup on a text box
Click to expand...
"=DLookUp("[SupName]","tblSuppliers","[txtSupAccountCode]"=Forms!frmInvoiceR
eturned!Supplier)" but this doesn't seem to work.

Any ideas

Thanks
Click to expand...
Click to expand...
 
How would this need to be changed if you wanted to UPDATE the contents of a
field in the tblSuppliers Table that is being displayed on the Form?


Fredg said:
You placed a quote in the wrong place and left out an "&".

Try:
=DLookUp("[SupName]","tblSuppliers","[txtSupAccountCode] = " &
Forms!frmInvoiceReturned!Supplier)

The above assumes [txtSupAccountCode] is a Number datatype.

If it is a Text datatype then use:
"[txtSupAccountCode] = '" & Forms!frmInvoiceReturned!Supplier & "'")

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


PC said:
Hi,

I want to display a field from an unrelated table on a form.

Data is displayed on a form when a particular Invoice number is chosen from
a lookup field. Supplier account codes are displayed (The account Code is
what is stored in the Related table [tblInvoice] having originally been
selected from another table [tblSuppliers] with which it has a Many-toOne
Relationship) but I would like to display the account name beside the
account code

I have tried using a Dlookup on a text box
Click to expand...
"=DLookUp("[SupName]","tblSuppliers","[txtSupAccountCode]"=Forms!frmInvoiceR
eturned!Supplier)" but this doesn't seem to work.

Any ideas

Thanks
Click to expand...
Click to expand...
 
How would this need to be changed if you wanted to UPDATE the contents of a
field in the tblSuppliers Table that is being displayed on the Form?

Fredg said:
You placed a quote in the wrong place and left out an "&".

Try:
=DLookUp("[SupName]","tblSuppliers","[txtSupAccountCode] = " &
Forms!frmInvoiceReturned!Supplier)

The above assumes [txtSupAccountCode] is a Number datatype.

If it is a Text datatype then use:
"[txtSupAccountCode] = '" & Forms!frmInvoiceReturned!Supplier & "'")

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


PC said:
Hi,

I want to display a field from an unrelated table on a form.

Data is displayed on a form when a particular Invoice number is chosen from
a lookup field. Supplier account codes are displayed (The account Code is
what is stored in the Related table [tblInvoice] having originally been
selected from another table [tblSuppliers] with which it has a Many-toOne
Relationship) but I would like to display the account name beside the
account code

I have tried using a Dlookup on a text box
Click to expand...
Click to expand...
"=DLookUp("[SupName]","tblSuppliers","[txtSupAccountCode]"=Forms!frmInvoiceR
Click to expand...
 
Back
Top