Filtering values for one combo box off of another

[mayestom]

When I try to add the fields that I want, they get Grouped by as well and the first combo box gets screwed up. By that I mean the selection is not grouped by it just lists all the fields in that table. So instead of three options (EXP, RET, SEQ) I get 83, which is the total number of records in that table.
But on the positive side the text box is displaying the proper value

 

[GinaWhipp]

Hmm, perhaps you need to Group the first Combo Box?

 

[mayestom]

Not sure what you mean? I am grouping in the first combo box. This is the statement for the first combo box:

SELECT ContainerType.Container_Type, ContainerType.Container_Dimensions FROM ContainerType GROUP BY ContainerType.Container_Type, ContainerType.Container_Dimensions;

Which I understand why I'm getting 83 values in my combo list but that was the only way I could bring in Container_Dimensions.

 

[GinaWhipp]

I was basing my statement on this...

So instead of three options (EXP, RET, SEQ) I get 83

..and it sounded like you didn't want that.

 

[mayestom]

You're correct, I don't want that. I should only have those three options (EXP, RET and SEQ).

 

[GinaWhipp]

Hmm, can upload a recent version, let me see if I can do something about that.

 

[mayestom]

Okay I've attached the most recent copy. The form is called "Program Customer Model Year form". In the subform the main combo box is called IC_Type the dependent combo box is called IC_Container_Code and the text box is called IC_Container_Dim I know how crazy the whole thing looks but that is how many fields the Excel spreadsheet is that I'm trying to copy and those are the names that I have to work with.

 

[GinaWhipp]

Thunderstorm warning ... I may go off line for a bit!

 

[GinaWhipp]

Okay, look at Query1 and then read the After_Update event. Sloppy but you can see what I did to get what you wanted, you can clean it up and make it look pretty, the code that is

 

[mayestom]

I see in the After Update you've added:

Me.cboICContainerCode.Requery

But I'm not sure I'm following what happened. How does Query1 fit in? I see SQL code for it but I'm not sure how it fits in to the form? The form already has a query in the Row Source. Now the Row Source is back to it's original Group By Container_Type only. How is this all working?

 

[GinaWhipp]

Look at the Row Source for the second Combo Box and it should make sense then... well, maybe.

 

[mayestom]

Oh, Ok I think I'm starting to get it. It is making more sense. So the first combo the Row Source was still able to be Grouped By Container_Type. But the second combo with the Query1 you were able to bring in Container_Dimensions. I know there is a lot more to it, I'm just tired and need sleep.
And when I add in more fields to that table, ContainerType, I would add them into that query in order to be able to reference them in the subform? Why is there no design grid in the query?

 

[GinaWhipp]

Whatever you add to the table should automatically be reflected in the query so nothing for you to add. Not sure what you mean by no design grid. Hmm, get some sleep tomorrow is a new day with fresh coffee and clear minds!

 

[mayestom]

Yes you are right I need sleep. I will look over more closely tomorrow.
Thank you Gina.

 

[mayestom]

OK found the design grid for query1. Now, like you said, when I add fields to the table it will automatically be added to the query and I should be able to reference the new field in the subform (and I wouldn't need to bring that field down in the design grid to do that?). But what if I need to bring in a field from another table? Would I add that table to query1?

 

[mayestom]

Curious, in the After Update procedure code in the first combo box, why is there an apostrophe before the code "cboICContainerCode.RowSource" or does it make a difference?

Me.cboICContainerCode.Requery
   ' cboICContainerCode.RowSource = "ContainerType.Container_Code " & _
    "FROM [ContainerType] " & _
    "Where [ContainerType].[Container_Type] = '" & [cboICContainerType].Value & "' " & _
    "ORDER BY [ContainerType].[Container_Code];"

 

[GinaWhipp]

Yes, you would need to add that to Query1. However, I would make a copy so you can make sure you get the desired results first.

 

[GinaWhipp]

Because that is no longer being used. Doing that *silences* the cdoe from running. You can actually remove everything after the first line!

 

[mayestom]

I thought so, the change in color should have been another clue for me. So that's kind of like the // in Java when you want to add a note in the code. Now I know what you mean by cleaning it up and making it look pretty

Ok I'm going to start adding other fields/tables to reference in the subform and then also replicate exactly what we have done here to the IC fields and apply that to the OG fields in the subform (OG_Type, OG_Container_Code...). So this should be a good test for me to make sure I understand what you have done for me.

And what you have done for me is so much. Thank you Gina. I appreicate all your time and effort. Again! Please don't venture far as I know I will have more questions (jk)

 

[GinaWhipp]

Yes, that is the same thing as in Java.

Okay, you add everything and post back if you run into problems.

No biggie, that is why we are here