Combo box selection generates variables in another combo box

[DanicaDear]

Dealing with electric utility rubber gloves.....

tblCOMMODITY contains fields CLASS, SIZE, & COMMODITY_NUM (PK). A class combined with a size make a commodity number. I don't want the user selecting gloves by commodity number because they are 7 digit numbers that make no literal sense. DB users will select what a customer needs by two combo boxes, first by class, then by size.

Question 1. Class 3 only has 4 sizes available. Class 2 has 8 sizes available. I'd really like to have my second combo box values be dependent on what is selected in the first combo box, so that they don't make combinations that aren't available. How do I do it? A recent post posed a similar question: http://bytes.com/topic/visual-basic-...-box-selection but the link provided was more than I could comprehend. Can anyone offer another approach?

Question 2. When the two combo box selections are made, I'd like to automatically pull in the COMMODITY_NUM, which is the PK. Can I handle that with dlookup or something similar?

Thanks so much!

[TheSmileyCoder]

You add an AfterUpdate event handler to the first combobox,

Private Sub cmb_Class_AfterUpdate()
  cmb_Size.Requery
End Sub

You then make the 2nd combobox query include the first combobox as a selection criteria, which would look something like this:

Forms!Frm_Order!cmb_Class

To store the Primary key you should always have your combobox query contain the primary Key, and simply hide it.
You do this by setting the Number of columns in the combobox's properties, and by setting the first column width's to 0. That way you display the user friendly value, but store the primary key.

[ADezii]

Hello Danica. I've created a Simple Demo that should point you in the right direction, just Download it.

[DanicaDear]

TheSmileyOne and ADezii,
Thank you SO MUCH for your replies! When I read Smiley's reply I knew I was still not out of the woods because I don't yet know what a Combobox query is. However I figured with some research I could get closer to my answer. ADezii, you always go the extra step for me and I feel so undeserving. You people are SO NICE and just when I think my software writing is a lost cause someone steps up and keeps me rolling. I cannot say enough nice words for your support and encouragement (and expertise!). :-) I will be away from work for the next week so it will be until then that I get all this worked out and report my success! Thanks so much until then....
Danica

PS. ADezii, your program demonstrates exactly what I was trying to do. I will investigate it thoroughly and also reply to another post so I can help out someone else trying to do something similar. ;-)

[ADezii]

The pleasure is all ours, DanicaDear.

[DanicaDear]

ADezii,
I finally got the class combobox working (it only took 5 hours, lol) and after much, much research, I have to say I'm sort of stuck again.

First, I've checked every box and label that I know possible and I have my DB set up just like yours as far as I can tell. The only difference, which I'm thinking may be a big one, is you use number data type in your table where I used text. I used text because some of the sizes have an "L" behind them (size 10L). I read in a book that unless numbers represent a quantity, to use text as the datatype, and that's what I did. I think this is a problem (it might be the only problem) because I get this error when trying to punch data into my form: "Data type mismatch in criteria expression" Upon further research, I am wondering if the problem lies in my code, which is used to compare or select based on numeric matches rather than text matches? Unfortunately, I'm not slick enough to correct it, although I will admit I have tried. :-(

I now have some understanding of how this is working...we are really just using the (hidden) commodity number, or column 1, to select things. This has been what I have learned from this exercise. At first I thought my error was somewhere in this area so I have researched and tried different things. Came out knowing lots more, but with a problem I still can't fix.

Here is the code from your DB that I am trying to use, just as a time saver. Can you comment on how it might change if my datatypes are text rather that number?

Private Sub cboClass_AfterUpdate()
If Not IsNull(Me![cboCLASS]) Then
  Me![cboSIZE].RowSource = "SELECT [SIZE] FROM tblCOMMODITY 
WHERE [CLASS] = " & Me![cboCLASS]
End If
End Sub
 
Private Sub cboSize_AfterUpdate()
If Not IsNull(Me![cboCLASS]) And Not IsNull(Me![cboSIZE]) Then
  Me![txtCommodityNum] = DLookup("[COMMODITY_NUM]", "tblCOMMODITY",
 "[CLASS] = " & Me![cboCLASS] & " And [SIZE] = " & Me![cboSIZE])
End If
End Sub

Thanks!!! Danica

[TheSmileyCoder]

If your using strings, you have to inform access of that. The way you do that is in criteria expressions such as the one mentioned:

"[CLASS] = " & Me![cboCLASS] & ""

is to add ' at the beginning and end of string literal.
Example:

"[CLASS] = '" & Me![cboCLASS] & "'"

[DanicaDear]

Well well well, what do you know...IT'S WORKING! (almost)

This has definetely solved my biggest problem, and I said if you guys said it had something to do with those quote marks I was having a party tonight! Woohoo!

So I have the size matched up with the class...but it's not pulling in my commodity number. I know it's more quote marks but I've tried different combinations and my guesses aren't working. Can you help me once more?
This is the line where I think the problem is:

  Me![txtCommodityNum] = DLookup("[COMMODITY_NUM]", "tblCOMMODITY", 
 "[CLASS] = " & Me![cboCLASS] & " And [SIZE] = " & Me![cboSIZE]) 
 

When I choose the class and size combination, I get a Visual Basic run-time error: Data type mismatch in criteria expression

I don't know after what the & " ' " is required. Please excuse my ignorance but I just don't know how it works. This code is not "English" and that's the only language I speak. LOL.

[ADezii]

Me![txtCommodityNum] = DLookup("[COMMODITY_NUM]", "tblCOMMODITY", "[CLASS] = '" & Me![cboCLASS] & _
                       "' And [SIZE] = '" & Me![cboSIZE] & "'")

[DanicaDear]

OMG....you people are A-W-E-S-O-M-E! And just think, it's 10 minutes til 5 so I can close this out before the weekend (and therefore have a nice one...I tend to overworry about my programming, lol).
Actually, ADezii, I *thought* I had tried the combination before I replied back with that problem but I guess I missed something.
Thanks to you all!!!!