combo box query problem

[Matt Brown]

Hi,

I can't seem to get a combo box to filter another combo box via a query in the row source on a subform.

I have two combos:

SelectCategory

and

SelectItems

In the row source for the SelectItems i have the following query:

SELECT DISTINCT tblTrainingRecordSubForm.Item, tblTrainingRecordSubForm.Category
FROM tblTrainingRecordSubForm
WHERE (((tblTrainingRecordSubForm.Category)=[Forms]![frmTrainingRecords]![frmTrainingRecordSubForm].[SelectCategory]))
ORDER BY tblTrainingRecordSubForm.Item;

This doesn't seem to work.
I always thought that you had to reference the subform item by its parent form.

Everytime i run the form i get the parameter box appear.
Obviously its not finding the other combo box.

Where is my mistake, i have tried a few combinations but to no avail.

Please can someone help me with this.

many thanks

Matt

 

[Mile-O]

Try this:

[Forms]![frmTrainingRecords]![Forms]![frmTrainin
gRecordSubForm].[SelectCategory]

 

[Rich]

Do you really have a table named
tblTrainingRecordSubForm ?

 

[Matt Brown]

Mile-o-Phile
Tried your sql, seem to get the param box again.

SELECT DISTINCT tblTrainingRecordSubForm.Item, tblTrainingRecordSubForm.Category
FROM tblTrainingRecordSubForm
WHERE (((tblTrainingRecordSubForm.Category)=[Forms]![frmTrainingRecords]![Forms]![frmTrainingRecordSubForm].[SelectCategory]))
ORDER BY tblTrainingRecordSubForm.Item;


Any other thoughts.
Both combos are on the same subform.



Rich,

Yes fraid so, and i know what your thinking!!!

I have inherited this db from a former collegue who has now left.

Once i have sorted this out i will sort out the table naming and update where appropiate.


Matt

 

[Mile-O]

If they are both on the same subform, try eliminating the parent from the RowSource:

[Forms]![frmTrainingRecordSubForm].[SelectCategory]

 

[Rich]

Use the code builder on the criteria line of the query, it will create the correct references for you, are you refering to the bound column of the combo?

 

[Matt Brown]

Hi Guys,

Okay this may be getting somewhere.
I used the expression builder and have come up with the following sql:

SELECT DISTINCT tblTrainingRecordSubForm.Category, tblTrainingRecordSubForm.Item
FROM tblTrainingRecordSubForm
WHERE (((tblTrainingRecordSubForm.Category)=[Forms]![frmTrainingRecordSubForm]![SelectCategory]))
ORDER BY tblTrainingRecordSubForm.Item;

If i load the 'frmTrainingRecordSubForm' on its own and try the combos they both work fine. As soon as i load the 'frmTrainingRecord' the 'SelectItem' combo fires up a param box.

Almost as though it cant see the subform(itself) when in the main form, would this be back to the mainform referencing in the sql?

Matt

 

[Rich]

You haven't referenced the main form, when using the code builder select the main form first, then use the + to expand down to the sub and the control on it

 

[Matt Brown]

Wey hey

It works now.

Did a couple of things including removing the subform from the main form and re-linking again.

The "+" in the expression builder does not exist, must be a non Access97 thing as these were not present but managed to get the syntax right anyway:

SELECT DISTINCT tblTrainingRecordSubForm.Item, tblTrainingRecordSubForm.Category
FROM tblTrainingRecordSubForm
WHERE (((tblTrainingRecordSubForm.Category)=[Forms]![frmTrainingRecords]![frmTrainingRecordSubForm]![SelectCategory]))
ORDER BY tblTrainingRecordSubForm.Item;

Did try this before but didnt seem to work, after relinking it seemed to sort itself out.

Thanks guys for your patience on this , much appreciated.

Now to sort those pesky table/form names......

Matt

 

[Rich]

They are available in 97, you have to have the Main form open in design view.
Glad you got it sorted now