Cascading Combo on Sub Form

[RussG]

Hi - Hoping someone can help.

I have a main order form which holds orderID , customer details, date etc

I plan to add a subform which contains the ordered items , much like the Northwind order form.

On my subform, because there are so many products I have added a cascading combo box, so I can select a category and filter the relevant products. This uses the parameter [forms]![frmOrderSub]![Category].

I have placed a text box on top of the product combo to fix the issue with cascading combos on continuous forms.

And it all works great.

However, when I add the frmOrderSub to the main Order form and switch to form view the parameter pops up [forms]![frmOrderSub]![Category].

If I click ok and try and choose a catagory on the subform I get:

You Can't assign a value to this object

The object may be a control on a read only form
The object may be on a form that is open in design view
The value may be too large for this field

Any suggestions would be greatly appreciated.

Cheers
RussG

 

[boblarson]

When a form is used as a subform the syntax to refer to it changes. Find the Parameter and change it to:

[forms]![frmOrderSub].[Form]![Category]

 

[RussG]

Thanks for your reply Bob.

Tried your suggestion but the same thing happens

[Forms]![frmOrdersSub].[Form]![Category]

Still works OK on the subform.

I could open the subform as a linked form but I am curious to find the answer to why this won't work.

RussG

 

[boblarson]

Can you post the db? My brain isn't working too well this morning (working on learning SQL stuff and I'm on overload), so visually would be much better for me.

 

[RussG]

Hi Bob - I use WinRar, I'll have to get a copy of winzip. I will upload shortly.

Thanks
RussG

 

[boblarson]

Hi Bob - I use WinRar, I'll have to get a copy of winzip. I will upload shortly.

Thanks
RussG

Russ - I like Ultimate Zip better than WinZip. Check it out.

 

[RussG]

Bob - attached is DB as suggested, I have stripped out all the unnecessary stuff.

Thanks
RussG

 

[Dreamweaver]

[Forms]![frmMainOrderformname]![frmOrdersSub].[Form]![Category]

 

[Dreamweaver]

This is what the builder would have done for ya right click and select build learn how to use that and most of your problems will go away

[Forms]![frmOrder]![frmOrdersSub].[Form]![Category]

 

[boblarson]

I have an interview in a few so I have to concentrate on getting ready for that, but there are some serious issues with the db design anyway and I'll address those when I am able. I'll post back with some info and a revised db as soon as possible.

 

[RussG]

Thanks for your help chaps , that is working now - also I had not set a link between the 2 forms, also fixed that now.

Would be interested in your comments on design Bob (suspect its to do with the Category table)

RussG