Link

[Lee]

I have a main form where orders are placed for the user, I need to
display the supplier account number on this main form. So I created a
text box where the control source is SELECT DISTINCT AccountNumber FROM
Suppliers WHERE
(((AccountNumber)=forms!AddAnOrderAndDetails!Custo merID)); without any
effect. Any ideas to make this work?

[Allen Browne]

You can't use a SQL statement in a ControlSource.

Use DLookup(), e.g.:
=DLookup("AccountNumber", "Suppliers", "AccountNumber = " &
Nz(forms!AddAnOrderAndDetails!CustomerID,0))

More info:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Lee" <da********@hotmail.comwrote in message
news:11**********************@51g2000cwl.googlegro ups.com...

>I have a main form where orders are placed for the user, I need to
display the supplier account number on this main form. So I created a
text box where the control source is SELECT DISTINCT AccountNumber FROM
Suppliers WHERE
(((AccountNumber)=forms!AddAnOrderAndDetails!Custo merID)); without any
effect. Any ideas to make this work?