Syntax for Control SOurce being a query

[Rich_Preston]

Hi,
This is probably a pretty basic question, but I just haven't been able to find the answer in the online help or several books.

I have a form with no table bound to it. The form has several fields that should be populated by the result of queries. I can't seem to get the right control source syntax to display the fields from the queries in the text boxes.

Many thanks,
Rich

 

[joeyreyma]

you can use query as recordsource like the same with tables. put the query name in the RECORDSOURCE property of the form and assign each control (textbox,...) the ControlSource (fields,...) of your query.

if you're doing it in code, say on Form_Open(), you can assign it like this:

Me.RecordSource = "QueryName"
Me!Control1.ControlSource = "QueryName.Field1"
Me!Control2.ControlSource = "QueryName.Field2"
...
...
...

 

[Rich_Preston]

Hi,

Thanks for the reply. I have no problem if I specify a record source at the form level, and then assign the query fields to the control source of the text boxes, just as you suggested.

The problem is that each text box has a slightly different query, retrieving the same field from different records in a table. That is, there is not a single record source for the form.

It would be similar to having one text box called 'Rich's age' and another called 'Cathy's age'. THe first would be based on the query 'select age from employees where employee = 'rich'. THe other text box would have a different where clause in its query.

As soon as I remove the record source from the form and try to use just the control source, I get #Name? in the text boxes.

Any ideas?

Thanks again!
Rich

 

[joeyreyma]

bound forms has a value in the recordsource property. same with bound controls, in which controlsource property has value also.

however, controlsource property is dependent on your recordsource property. if you do not specify a recordsource (table, query, sql) in your form, your control can't have value in the controlsource (fields) property or else #Name will come out. this states that it cannot find the collection in which this bound control is a member.

note: if you have recordsource, you can have controlsource. if you do not have recordsource, do not put a value on every controlsource property of your control. if you want to put values in each control, do it in code.