Text box to display value based on a query

[ittechguy]

I've tried setting the control source of a text box to get its values from a query by doing txtBuildingName.controlsource = "select tblbuildings.buildingfk, tblbuuldings.buildingname from tblbuildings where buildingfk = [txtBuildingID].



It's not working.

Basically, I need txtBuildingName to display the name of the building id which is stored in txtBuildingID. So if txtBuildingID is "1" and that is listed in tblbuildings as building "A" then "A" should show up in txtBuildingName.

[JoeM]

What is the Control Source of the Form/Report?
If it is the query that the field resides in, you can just click and drag that field from the Field Box to your Form/Report.

If it is not in the Control Source that the Form/Report is based on, you can either:
- add it to the Query that the Form/Report is based on
or
- use the DLOOKUP function to look it up from another Table/Query
(see http://www.techonthenet.com/access/f...in/dlookup.php)

[pbaldy]

Or if the ID is (or can be) a combo box this is another option:

BaldyWeb - Autofill

[ittechguy]

Idk why I didn't even think about adding building name and room name to the query.

What is the preferred method? The query is already getting long and it's the recordsource for the entire form. I have to add a couple more tables to it so it's going to get even larger. But I guess adding names to the query is likely better than dlookup.

[JoeM]

Yes, DLOOKUPs can be tricky/fluky (and subject to errors), so I would say adding them to the query is preferential.
I wouldn't be too concerned about the size of your query, other than that if it gets too long, it may be indicative that maybe you should be using some Form/Subform or Report/Subreport relationships.