#Name? error on calculated field. Yes I googled :-)

[Beemerang]

Hi All

I am stumped! I have a combobox which looks up and stores the primary key for a school. I have another unbound field into which I have inserted the following SQL code (extracted from a query which works perfectly).

SELECT a01_Corridor.CorridorID
FROM a01_Corridor INNER JOIN a05_Schools ON a01_Corridor.CorridorID = a05_Schools.Corridor
WHERE (((a05_Schools.SchoolName)=[Forms]![fe04_Inst4MaterialTxns]![School]));

This works perfectly in the query window but if I paste this exact code as the unbound control's contro lsource I get a #Name? error.

Please help?

Beem

 

[nanscombe]

Control Source would be which field of a table it was linked to, an unbound field doesn't have one.

Rowsource is where you define the data for the Combobox.

 

[Beemerang]

Thanks Nigel. I am using an unbound textbox, is there any way to stick to this or does it have to be a combobox?

 

[nanscombe]

Queries, or the SQL behind them, are usually used as the Rowsource for Comboboxes or Listboxes to display multiple values.

Textboxes can only show one value at a time.

What are you trying to achieve, a list of all CorridorIDs?

For display purposes you could try using a Listbox with the Rowsource you've created.

 

[Galaxiom]

SQL can't be used as a ControlSource.

If you are only expecting one value back then use a DLookup.

 

[nanscombe]

Logically you would expect schools to have more than one corridor which is why I asked whether a list of values was required.

 

[Galaxiom]

I see what you mean. I didn't read that closely.

 

[Beemerang]

Thanks Nanscombe and Galaxiom,

Nanscombe, you were right in that I had to use a list control and it worked fine. I wanted the control to be automatically updated based on the value of another dropdown field and I had read that using a query would be more efficient than Dlookup.

Anyway, its working now, so thanks to everyone for their responses, I appreciate it!

Beem

 

[nanscombe]

You're welcome.