Combo box on form that becomes a subform

[KerryL]

Using the FAQ provided by RickSpr I was able to limit the selection contents of a combo box based on what is previously selected in another combo box. It works just great when I test it on its own form. However, once I copy the form onto another (making it a subform) the second combo box no longer works properly. Instead, I receive the following error message:

Enter Parameter Value
Forms!sfrmRequestDetails.cboCategory


Here are the form and field names:

Main form = frmCustomerRequests
Sub form = sfrmRequestDetails
Combo box 1 = cboCategory (on subform)
Combo box 2 = cboProduct (on subform)
(Categoy selection in cbo#1 dictates which products can be chosen in cbo#2.)

Current select stmt of Combo box 2 =
SELECT [tblProducts].[ProductName] FROM tblProducts WHERE ((([tblProducts].[CategoryName])=[forms]![sfrmRequestDetails].[cboCategory]));

Can someone explain why this works fine until I place the subform on a primary form?

Thank you,
KerryL

 

[jgoodman00]

Surely the WHERE part of your statement needs to be
[forms]![frmCustomerRequests]![sfrmRequestDetails]

The other way is referencing only the subform, & as such would only work when the subform is not a subform (i.e. it is a mainform)


I might be barking up completely the wrong tree though, because this seems far too simple an answer... James Goodman

 

[KerryL]

James,

You're right, it was too simple an answer. It didn't work. I receive the same error message, except that now it includes the name of the primary form as well.

It acts as if it's looking in the cboCategory combo box for the value it needs but doesn't recognize that it's there. But the same exact SELECT stmt works fine when I run it in its own form.

Grrrrrrr!

I'll play around with it some more but I'm stumped.

 

[jgoodman00]

It will work as either one or the other. If it is set to reference [forms]![frmCustomerRequests]![cboCategory] it will look at the value stored in cboCategory on form frmCustomerRequest. Obviously, if this form then becomes a subform it will need to be referenced in the form of [forms]![frmCustomerRequests]![sfrmRequestDetails]![cboCategory]

If it is set to be referenced from a subform & you open it as a main form it will again no longer work.

Are you also sure of the value your combo box is holding? Usually combo boxes hold integer id values, not the text themselves. Whilst this won't produce the error you are getting, it would mean incorrect, if any records are returned if you then reference the value stored in this control from another control James Goodman

 

[jgoodman00]

You can also try using the immediate window to ascertain what value, if any is stored in the combo box. If it successfully returns a value you also know that the reference to the control is correct, although I suspect this will be incorrect...

in the format of:


?[forms]![sfrmRequestDetails]![cboCategory]

or

?[forms]![frmCustomerRequests]![sfrmRequestDetails]![cboCategory]

James Goodman