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!

[topher23]

Whoops! You're right, that was, in fact, a mistake. I tested the SQL in Query Designer with a quick mockup table this time, so this should work.

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)));"
 

[DanicaDear]

topher23,
You are great! This worked perfectly on the first try. Thanks again!!!!!!!!

Thanks to all who helped with this post. :-) I am very grateful.

[NeoPa]

Too late to help much here, but I thought I'd add a few links to peruse, that may help to clarify things for you Danica.

Quotes (') and Double-Quotes (") - Where and When to use them.
Literal DateTimes and Their Delimiters (#).
ANSI Standards in String Comparisons.

Have fun :)

[DanicaDear]

This string kind of got out of hand, as most of my posts...(one question leads to another) but I'm having a problem with the original question, now that I'm in testing mode. I have 3 text boxes on a form. 2 are dropdowns--the second depends on the first--and the selection of those two automatically fills the third. However, because I set these up as "unbound" (as recommended in the post) they aren't taking the data back to my table. Can anyone offer me any guidance?

Thanks in advance!

[NeoPa]

Hi Danica.

Is this a related question, in which case I need to review the thread, or is it a new one, in which case I won't need to do that, but I'll probably need to split the question into it's own thread.

Let me know ;)

[DanicaDear]

It is definetely related. Post #1 tells you what I want to do. Post #3 was someone's example of how to do it. All the other posts are the fine details.

I want you to spend as little time as possible so I guess the ultimate question to ask is "Can you take data from an unbound combobox and put it into a table? "
If you then ask "Why is it unbound if you want it in a table?" the answer to that is because that's where the guidance led me, in order to have values in my second combobox dependont on the value chosen in the first combobox. Hope that helps!

[NeoPa]

That's normally done (if I understand you aright) by setting the .DefaultValue property of one of the bound controls from within the AfterUpdate event procedure of the unbound filtering control.

Let me read up and see if that makes sense here.

[DanicaDear]

Here's my code in my current DB, which is posted above but probably in chunks. This may be easier to digest:

Option Compare Database
 
Private Sub cboClassfrmNewGloves_AfterUpdate()
If Not IsNull(Me![cboClassfrmNewGloves]) Then
  Me![cboSizefrmNewGloves].RowSource = "SELECT [SIZE] FROM tblCOMMODITY WHERE [CLASS] = '" & Me![cboClassfrmNewGloves] & "'"
End If
End Sub
 
Private Sub cboSizefrmNewGloves_AfterUpdate()
If Not IsNull(Me![cboClassfrmNewGloves]) And Not IsNull(Me![cboSizefrmNewGloves]) Then
  Me![txtCommodityNum] = DLookup("[COMMODITY_NUM]", "tblCOMMODITY", "[CLASS] = '" & Me![cboClassfrmNewGloves] & _
                       "' And [SIZE] = '" & Me![cboSizefrmNewGloves] & "'")
End If
End Sub
 
 

It looks like it's lacking anything to send the data back to the table. I think you're right on.

[NeoPa]

I hope to post the resolution to this tomorrow. There were several issues, requiring various changes, but the solution is pretty sweet and matches the full requirement (explained vocally so fewer chances for confusion).

[NeoPa]

The requirement is to filter on two values only - Class and Size. The form manages records from a table that deals with purchases of the gloves. Each glove is identified by a Component Number, which is unique to each Class/Size combination. The table may contain multiple (or none) records that pertain to the same Component Number, or Class/Size combination.

The design of the form requires unbound filter controls (ComboBoxes) in the header for specifying the filtering, but it's worth noting that some Size values are only valid within a particular Class. Thus, the filtering is also cascaded (When a Class is selected, not only is the form filtered, but the Size ComboBox is also filtered, to reflect valid items for that Class).

A point worth considering is that in filtered mode, entering of a new record should automatically set the values for the fields filtered to match those in the filter controls. To add further complication, the Component Number should only be set when both [Class] and [Size] are filtered. All good fun.

I don't have the database to hand just now, so I'll post details of the solution later when I'm at home.

[NeoPa]

In case anyone's interested, I felt this was a subject well worthy of a full article with all details covered.

Cascaded Form Filtering.

[DanicaDear]

Thanks NeoPa. I had researched a bit on how to do this before I posted my question. I had found similar questions but no great solutions. Your article will be appreciated by many in the future too! Thanks again!

[NeoPa]

Always a pleasure Danica dear :)