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!!!!

[ADezii]

You are quite welcome. I guess TheSmileyOne and myself are not invited to your party! (LOL)

[TheSmileyCoder]

/me goes to the basement to grab a cold one.....ahhhh

A few examples of literals
Number Literals:

1 
2
3.1 (Depending on regional settings :P)
4.12414
31414
2352525
252525

String Literals:
'test'
'The Smiley One'
'a'

Date Literals:
#12/07/2010#
#07/07/2010#

Notice how number literals require no markings around them, where the string literal requires the ' and hte date literal requires the #.

[DanicaDear]

Because I stored numbers in a text field, the 11 shows up before the 7. I am working on a form with this code (which was provided to me in this string, but here it is altogether and working)

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

How can I incorporate something into this to make the text values go in numeric order? I have read about the Val(), Clng(), Cint(), and ORDER BY...but I'm not sure how to make it work in this VBA or if I'm even in the right place to do that. Note: There are only numbers...no text...in the text field. I'm only concerned with the SIZE because the CLASS doesn't go into double-digits.
Thanks in advance. :-)

[topher23]

Whenever the number in your text field is less than 32,000 or so, you can do something like

Me![cboSIZE].RowSource = "SELECT [SIZE] FROM tblCOMMODITY WHERE [CLASS] = '" & Me![cboCLASS] & "' ORDER BY CInt([SIZE]);"

However, if there is actual text in the field, this will cause trouble. Since you pointed out '10L' as a possible value, I'll show you a way to deal with that by using some string manipulation. WARNING: I haven't tested this so it might have errors.

Me![cboSIZE].RowSource = "SELECT [SIZE], FROM tblCOMMODITY WHERE [CLASS] = '" & Me![cboCLASS] & "' ORDER BY CInt(IIf(IsNumeric(Right([SIZE],1)),[SIZE],Left([SIZE],Len([SIZE])-1));"

Just in case that was a bit convoluted to follow, the IIf statement checks to see if the last character of the [SIZE] string is a number. If it is, it passes the [SIZE] field whole to the CInt function. If it isn't, it takes the [SIZE] field and pulls everything from the Left side of the field to the length of the string minus one (effectively dropping off the last character) and passes that to the CInt function.

Of course, if you have a size of '10XL' you're totally screwed. ;)

[DanicaDear]

LOL. Well I don't have XL but I'm glad you noted that L size. I had forgotten about that, and you're right, that would have screwed me up!

When using your code, I get this error message:

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

I noted that there are 6 "(" and only 5 ")" I have tried putting them where I thought they should go with no luck. I really thought it should go at the very end but I can tell you that gives the same error message. Any more thoughts?

Thanks for your help!