data grab

[dott]

is it possible to have the source of a text box set up as an SQL expression?

What i want to do is when the records from a particular table are displayed on a form, it goes out and grabs a field from another table which is related to it.

so the sql would be something like

SELECT [Phone Number] FROM CONTRACTS, CONTRACTORS
WHERE CONTRACTORS.NAME = CONTRACTS.NAME AND
CONTRACTORS.NAME = me.cname;

 

[Rich]

Use DLookup or a combo box

 

[RichardJ]

You cant add the SQL straight to the TextBox control but there are a couple of alternatives....

You can use Dlookup in the text box control source and point to your table.

Or you could run code on the refresh of the form to populate the text box.


Hope that helps

 

[dott]

some code

can you guys tell me what's wrong with this, it is what i have in the control source for me phone number text box:

=DLookUp("[Phone Number]","contractors","[Contractor Name] = '" & [me].[combo27] & "'")

 

[Rich]

Why not just add the phone number to the first combo?

 

[dott]

hmm i dont quite follow but i used Forms!blah!blah instead of me.blah and it worked

What did you mean by adding it to the combo though?

 

[RichardJ]

Modify the recordsource in Combo27 (Nice name!) so that it brings back the information for you.

Then you can reference it by pointing to Combo27.column(2) or whatever you column your query returns it to.