cascading combo boxes - again.

[paddleman44]

Good Day.

I will apologize in advance for yet another thread on this issue but, despite many searches and attempts, I have yet to solve my problem. So....

I have two (bound) combo boxes on a subform. I want to limit the picks of the second based on the first. I have opted for the OnChange ....requrery route to have the second combo query.

...here is the thing. I have designed my subform, and when I test it as a standalone form (i.e. not part of the mainform) the boxes works exactly as desired, but only when I reference cbo1 as: Forms!sfrmName.cbo1 --- it does not work if I use Me!cbo1 in the parameter line of the query for the Row Source of cbo2.

Then, when I put the form into the mainfrm, the query will not work. I have tried many ways to reference:

Me!cbo1
Forms!sfrmName.cbo1
Forms!frmMain.sfrmName.Form!cbo1

..I am also familiar with www.mvps.orgs primer on how to reference controls.


What am I missing????????

All help is appreciated.

DJ

 

[boblarson]

First, use the AFTER UPDATE event of the combo box to requery, not the On Change.

Second, the syntax for in the query criteria would be:

[Forms]![YourSubFormContainerNameHere]![Form]![ComboBoxName]

with the brackets and bangs as shown.

 

[paddleman44]

Thanks for the reply.

You are quite right, the Requery is on the AfterUpdate event.

I tried your recommended syntax and the query does not work.

When I substitute a period for the second bang i.e.

[Forms]![YourSubFormContainerNameHere].[Form]![ComboBoxName]

The subforms works perfectly when I open the subform independently.

Unfortunately, it requests the parameter for that criterion when I use it as a subform on a mainform.

The subform is on a tabbed page, if that makes a difference.

DJ

 

[ajetrumpet]

Paddleman, here are a few additions you might want to consider...

Bob's Link for subform syntax will give you the correct way to reference a control of any kind (on the subform) when your subform is nested inside the main form.

As far as being "stand-alone", it is treated as any other form if it is indeed a separate object. Does your subform actually appear in the "Form Objects" list of the Database window?

As Bob will say too...there are different elements of a subform that you need to be aware of. The object, the container, and the recordsource. All are different entities and serve different purposes. The actual "form control" that is used for referencing the subform is called the container. As I've heard, a common problem when referencing subforms involves the use of the "object" name rather than the "container" name.

Also, have you checked out the FAQ on Cascading? It applies to "stand-alone" objects (as you call them).

 

[paddleman44]

ajetrumpet

Thanks for the reply.

I have the mvps.org syntax line tacked to my wall, and have for some time as it is an excellent reference.

I appreciate (or think I do) the difference between containers and objects ---the container is the name given the subform when adding it to a mainform. In my case, I use the default which seems to be the object name (if this is incorrect, then please let me know as that would mean I do not understand the difference between container and object!!).

Yes, the subform is in the form objects list of the database window. When I refer to the combo boxes cascading properly when I open the subform "stand-alone", I mean when I open the subform as an independent form from the database window.

What I find interesting (read "perplexing, frustrating, etc") is that the cbo's only work when it are reference:

[Forms]![sfrmName].[Form]![cbo1]

and not:

[Me]![cbo1]

Reading the syntax primer .... when referencing a control on a subfrom from that subform, the Me!ControlName syntax should work.

I am using Access2003, and the database is Access2k .......

.......again, your help is appreciated.

Thanks.

DJ

 

[ajetrumpet]

I appreciate (or think I do) the difference between containers and objects ---the container is the name given the subform when adding it to a mainform.

Yes, that is correct, although it is given TWO names, one to reference the container (control), and one to reference the actual object (source of the container's data). By default (I think), Access assigns the same name to these two entities.

What I find interesting (read "perplexing, frustrating, etc") is that the cbo's only work when it are reference:

[Forms]![sfrmName].[Form]![cbo1]

and not:

[Me]![cbo1]

I have just tested this, and you're right!! But, I don't think it is an error on the author's part. How did you create your subform...??
1) Drag and drop the object onto the main form from the Database window.
2) Add a subform control from the design tools.
3) Create an autoform with a one-to-many relationship already in place.

Did you know that taking route #3 will not produce a separate subform object? That is because it's not a subform. It is a "child table". That is very strange to me, as it is difficult to distinguish it from a subform.

Anyway, as far as the referencing goes, you're right. But, for me, I can count the number of times I have used "Me!" with anything (on just one hand).
If you want a simpler way to reference with cascades, just enclose the referenced control's name in [ ]. I'm not really sure why this works, but it sure is a great shortcut!

 

[paddleman44]

Thanks again.

I created my subform as a form from the database window.

I added the subform to the mainform using the toolbox.

DJ

 

[paddleman44]

I got it working on both standalone subfrom and when it is part of the mainform.

Perhaps this is what you were getting at with your last sentence, ajetrumpet.

All I did was reference [cbo1] with out any of the reference to forms.....

Thanks again to forum.

DJ