Displaying a Query Result in a table.

[bennybee]

I am running a query on a value in a form.
and i want the result to be displayed in another field.

The query which i have is this:

SELECT Premises.[Premises Name]
FROM Premises INNER JOIN Meeting_Agenda ON Premises.Premises_Number = Meeting_Agenda.Premises_Name
WHERE (((Meeting_Agenda.Meeting_Agenda_Number)=[Forms]![Hearing_Agenda]![Meeting_Infor]));

when i have the form open, and i get a value for the Meeting_Infor field, and then i run the query in the back ground (physically go out and select queries - then this query), i get the result that i want... a premises name.

but when i assign the query to a text box, and set its control source to this query... it comes up with #Name? all the time, and never displays the name of the place.

why is it that it will display the right name in the background when i run the query outside the form (it even updates when i select a new value for Meeting_Infor in the form). but when i try to assign the value to a field inside the form, it gives this error ??

 

[Mile-O]

A textbox won't accept a query in it's ControlSource.
It will accept a field from a query as its ControlSource.

A form accepts a query as its RecordSource.

A form represents one record at a time. Therefore a textbox can only display one field of one record at a time.

A query can return multiple records.

Can you see what you are doing wrong now?

 

[bennybee]

but the query only ever returns one result. one record.
cause its based on the primary key of the table. so when i query it, it will only return one record.

so what you are saying is that i need to set its value to the field of the query that i want, rather than the query itself.
i tried:
=[Premises Query]![Premises Name]

but still gives me the same error...

what do i need to do to get this thing to work/

the query will only ever return one record as its result, so i dont see why the field wont display this one unique record..

 

[Rich]

Unless the form is bound to the query you cannot set the control source of a text box to it. Use DLookUp as one methodof returning the value

 

[bennybee]

yeah i was thinking that, but instead i got around it using a list box instead of a text box.

it works quite nicely, giving the details which i want