Filtering values for one combo box off of another

[mayestom]

I know I have seen this asked before or a variation of it, but the poster had already known where to start and had some structure in the form. But the difference with me is that I don’t know where to begin. With that said…
In my subform, I have a field named Container Type (I know you don’t need to tell me about Reserve names for Access, but I can’t change the names) where when you choose one of the values (a combo box), the values for a second field (Container Code) changes (another combo box) based on the first selection. Then, based on the selection from the second field five other fields are automatically filled in. Is this possible and if so, would someone be able to help me implement this in my form?
Thank you.
Using:
Access 2010
Windows 7

 

[GinaWhipp]

You mean like Cascading Combos?

 

[mayestom]

Hi Gina! Thank you for replying to my post.

Yes, Cascading Combos (didn't know the name of it) is what I think I am looking for and I think I found an example of it from an example db that a user named pbaldy used to answer a post with a similar question on this forum. The example he has is a simple form that when you select a state in a combo box, the next combo box changes the values to list the cities in that state. Which is basically what I'm looking to do but with a couple extra steps added afterwords. So... 1) I'm not sure how to adapt it to my db and 2) I'm not sure how to add the extra steps on afterwords. The db I'm talking about currently is not the same one you just helped me with.

 

[GinaWhipp]

Well, going to suggest you start by setting the Combo Boxes till you get stuck and then post back. In other words get what you can get working working and then post what's not working.

 

[mayestom]

He has two ways of doing it and I'm not sure which way to emulate. (And it was erroneous of me saying it was simple. Sorry pbaldy if you are reading this). He states:
"One is to have the row source of the second combo refer to the first in its criteria, then requery the second combo in the After Update event of the first."

Private Sub cboState1_AfterUpdate()
  Me.cboCities1 = vbNullString
  Me.cboCities1.Requery
End Sub

or
"The second method modifies the row source of the second combo when the first changes."

Dim strSource As String

strSource = "SELECT City " & _
                   "FROM Cities " & _
                   "WHERE State = '" & Me.cboState2 & "' ORDER BY City"
Me.cboCities2.RowSource = strSource
Me.cboCities2 = vbNullString

But either way, I'm confused as to what "Me" is and which way to do it? And I'm sorry Gina, I don't mean for you to be helping me adapt someone else's work to my db. If you have work that you think I should try and adapt please let me know.

 

[GinaWhipp]

No problem.. code is code and it's posted to help.

Me.

is referring to the Form not a person or Control. I think you should study something a bit easier regarding Cascading Combos...

http://www.fontstuff.com/access/acctut10.htm

http://downloads.btabdevelopment.com/Samples/combos/CascadingCombosSample.zip

...because I think you are not understanding the concept. I am also going to give you some reading for you to do in your spare time

Jeff Conrad's resources page...
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page...
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP)...
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials...
http://allenbrowne.com/links.html#Tutorials

UtterAccess Newcomer's Reading List
http://www.utteraccess.com/forum/Newcomer-s-Reading-List-t1998783.html
Help with Normalization
http://www.access-programmers.co.uk/forums/showpost.php?p=1146957&postcount=2

Sample data models...
http://www.databasedev.co.uk/table-of-contents.html
http://www.databaseanswers.org/data_models/

Naming Conventions…
http://www.access-diva.com/d1.html

Other helpful tips…

Setting up a Model Database
http://www.access-diva.com/d11.html

My Database Standards...
http://regina-whipp.com/blog/?p=102

 

[mayestom]

I understand but the form name is CascadingComboSample and the table name is Cities.
I guess I should do some more reading.

 

[mayestom]

I've added the combo boxes and added the code to them. But when ever I select an option in the first combo box it takes me to the front of the table. And the cascading combo box is not registering anything. And it's made the subform not register a record in the respective table.
If you would like to take a look at it I've attached a copy of the db. The form name is "Program Customer Model Year form" The Combo box is named cboICContainerType the cascading combo box is named cboICContainerCode (all the way at the bottom of the subform). The head of the form is working off of the Program Customer ModelYear table and the subform is off of the Packaging information charts - Copy Of table. The data that the container info is coming from is called ContainerType.

 

[GinaWhipp]

Hmm, please do because one of us is not understand... either I don't get your question or you're speed reading and don't understand the explanation. I'll be here when you finish and let's see if we can get this sorted out...

 

[GinaWhipp]

Oh, I see we must have both been typing at the same time. As soon as I get a minute I will look at your sample.

 

[mayestom]

Now I'm trying to emulate the second example from the link you sent:
http://www.fontstuff.com/access/acctut10.htm
because I have a single table that provides the data for the Row Source for both combo boxes.
tick tock, tick tock and a little :banghead: later...
AH HA! I did it! By following the second example that you sent and adapting that code to my db I was able to pick from a list in the first combo box and the values in the second combo box changed depending on the first value!
Code that I used for the After Update on cboICContainerType (the first combo box):

Private Sub cboICContainerType_AfterUpdate()
    cboICContainerCode.RowSource = "Select [ContainerType].[Container_Code] " & _
    "FROM [ContainerType] " & _
    "Where [ContainerType].[Container_Type] = '" & [cboICContainerType].Value & "' " & _
    "ORDER BY [ContainerType].[Container_Code];"
End Sub

So the Row Source of cboICContainerCode (the dependent combo box) is left empty and the After Update fills it in when ever the user makes a selection, correct? And I set the Control Source for both boxes and now they write to the record whenever I make a selection!
Am I on the right track (hope, hope)?

 

[GinaWhipp]

Well, just as I was about to type I reply I saw yours!

I *think* you are on the right track. The reason I'm not quite sure is because I'm not quite sure of your desred goal but if you are getting what you want then yes!

 

[mayestom]

You're right. Okay, a little back ground: I have an Excel spreadsheet that uses vlookup's. So when you select an option in one cell the selection in the next cell changes based on the first cell. Then when you select the option in the second cell, values in five other cells are automatically inputed. So I'm trying to copy what in Access.

 

[GinaWhipp]

Then yes, you are on the right track...

 

[mayestom]

Great, but now I need to get the other five fields to fill in once I choose a selection from the second combo box. Would those be text boxes with a Control Source = to the selection of the second combo boxes column that I need? ex: =[cboICContainerCode].[column](1)

 

[GinaWhipp]

Not sure I understand you can follow the same patter for the balance of the Combo Boxes. You would just put the Criteria in a hidden field and only have the data you want to show in the first column.

 

[mayestom]

I wouldn't make a selection for the other five fields. They would be filled in as a result of the selection from the second combo box.

 

[GinaWhipp]

Oh, :banghead: trying to do too much at one time. Then yes using the FieldName.Column(1) would work.

 

[mayestom]

Yes, you're right. Trying to do to much at one time. My brain is starting to melt. I'm lossing track of what to do
The Row Source of the first combo only has one field that it's grouped by, the Row Source for the second combo is empty. Would I add the fields I need to the Row Source of the first combo then reference those column's in the Control Source of the new text boxes? I'm confused, I'm sorry.

 

[GinaWhipp]

Yes, you could do it that way. But remember if the Combo Box is empty so will your Text Boxes. They can only show data if there is something to show.