Show data from query in a text box

[jeffyyy]

OK, I have a form based on the table [Residents]. I also have a table that is called [Units]. They are related by the field Res_Id. In the units table, I have all of the residents current and previous unit and am using the query to select only the "active" unit (as defined by a yes/no checkbox). So the query works. Now, on the form I need it to grab the room number of the current resident selected on the form from the query and display it in a textbox.

Hopefully I explained all the enough. Thanks for the help!

[slave138]

From your description it sounds like you could set the Text Box's Control Source to use the Room number from the query.

[jeffyyy]

I tried that, but I get the #Name error. I would assume that is because the form is based on the table residents and not the query I am getting the room number from.

[slave138]

You could create a query which uses the residents table and the other query and set it as the data source for the form.

If the current query references the residents table you could edit it to include the rest of the the fields from residents and then use that query as the form's record source.

Another way would be to use the DLookup function. The best method really depends on how the DB is structured.

[jeffyyy]

How would I go about using the Dlookup function? I tried creating a query to include all that information and changed the form to be based off of the query, and it caused all sorts of problems with a couple of my dropdowns.

Thanks for the help!

[slave138]

In the Control Source of the field type in something like:

=DLookup("RoomNumber", "qryName", "ResID=" & [ResID])

In this sample:
- "RoomNumber" refers to a field in the query which contains the Room Number you want.
- "qryName" refers to the name of the query where the Room Number is found.
- It assumes ResID is a number.

If RedID is a string use this format to wrap the data in quotes:

=DLookup("RoomNumber", "qryName", "ResID=" & Chr(34) & [ResID] & Chr(34))

[slave138]

Personally, I would use the query for the form and try to figure out the problems with the combo boxes because DLookup can cause a bit of lag if you have a lot of records.

[jeffyyy]

I just implemented the DLookup, and it is a little laggy, but nothing too bad. I do not expect a lot of records in that table, as we do not move residents (or get new residents) very frequently, so a little lag on that form is not a huge deal.

THANKS for your help!

[slave138]

My pleasure. Have a good weekend.