SETTING A TXT BOX VALUE IN CODE FROM A QUERY 1

[paws]

I need to know how to select a specific value from a query and put it into a txt box on a form through code or an expression builder.

Any help would be appreciated.

Thanks
Jamie

 

[RickSpr]

Is the query being used as the form's Record Source property? If so, just drop down the text box's Control Source list and choose the query field.

Otherwise, it would be easiest to use a DLookup function:
DLookup(name of query column, query name)

For this to work, the query should return only a single row. You won't get an error, but if there are several rows with different values in the column you're retrieving, it's undefined which of the values you'll get.

Where to put this DLookup function depends on whether the value to be displayed depends on the record being shown on the form. If the value doesn't depend on the record, you can use the DLookup in the form's Open event. If your text box is named, say, 'txtInfo', you'd put this statement in the Form_Open event procedure:
txtInfo = DLookup(...)

If the value does depend on the record, you would instead put the statement into the Form_Current event procedure, so it will be recalculated each time the user moves to another record. (You could put it in Form_Current even if the value doesn't depend on the record, and it would work, but using Form_Open in this case is more efficient.) Rick Sprague