By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,687 Members | 2,055 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,687 IT Pros & Developers. It's quick & easy.

Combo box selection generates variables in another combo box

100+
P: 256
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!
Feb 15 '10 #1

✓ answered by ADezii

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

Share this Question
Share on Google+
27 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
You add an AfterUpdate event handler to the first combobox,
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmb_Class_AfterUpdate()
  2.   cmb_Size.Requery
  3. End Sub
You then make the 2nd combobox query include the first combobox as a selection criteria, which would look something like this:
Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 15 '10 #2

ADezii
Expert 5K+
P: 8,610
Hello Danica. I've created a Simple Demo that should point you in the right direction, just Download it.
Attached Files
File Type: zip DanicaDear.zip (21.7 KB, 188 views)
Feb 15 '10 #3

100+
P: 256
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. ;-)
Feb 16 '10 #4

ADezii
Expert 5K+
P: 8,610
The pleasure is all ours, DanicaDear.
Feb 16 '10 #5

100+
P: 256
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?
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboClass_AfterUpdate()
  2. If Not IsNull(Me![cboCLASS]) Then
  3.   Me![cboSIZE].RowSource = "SELECT [SIZE] FROM tblCOMMODITY 
  4. WHERE [CLASS] = " & Me![cboCLASS]
  5. End If
  6. End Sub
  7.  
  8. Private Sub cboSize_AfterUpdate()
  9. If Not IsNull(Me![cboCLASS]) And Not IsNull(Me![cboSIZE]) Then
  10.   Me![txtCommodityNum] = DLookup("[COMMODITY_NUM]", "tblCOMMODITY",
  11.  "[CLASS] = " & Me![cboCLASS] & " And [SIZE] = " & Me![cboSIZE])
  12. End If
  13. End Sub
Thanks!!! Danica
Feb 26 '10 #6

TheSmileyCoder
Expert Mod 100+
P: 2,321
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:
Expand|Select|Wrap|Line Numbers
  1. "[CLASS] = " & Me![cboCLASS] & ""
is to add ' at the beginning and end of string literal.
Example:
Expand|Select|Wrap|Line Numbers
  1. "[CLASS] = '" & Me![cboCLASS] & "'"
Feb 26 '10 #7

100+
P: 256
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:
Expand|Select|Wrap|Line Numbers
  1.   Me![txtCommodityNum] = DLookup("[COMMODITY_NUM]", "tblCOMMODITY", 
  2.  "[CLASS] = " & Me![cboCLASS] & " And [SIZE] = " & Me![cboSIZE]) 
  3.  
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.
Feb 26 '10 #8

ADezii
Expert 5K+
P: 8,610
Expand|Select|Wrap|Line Numbers
  1. Me![txtCommodityNum] = DLookup("[COMMODITY_NUM]", "tblCOMMODITY", "[CLASS] = '" & Me![cboCLASS] & _
  2.                        "' And [SIZE] = '" & Me![cboSIZE] & "'")
Feb 26 '10 #9

100+
P: 256
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!!!!
Feb 26 '10 #10

ADezii
Expert 5K+
P: 8,610
You are quite welcome. I guess TheSmileyOne and myself are not invited to your party! (LOL)
Feb 26 '10 #11

TheSmileyCoder
Expert Mod 100+
P: 2,321
/me goes to the basement to grab a cold one.....ahhhh

A few examples of literals
Number Literals:
Expand|Select|Wrap|Line Numbers
  1. 2
  2. 3.1 (Depending on regional settings :P)
  3. 4.12414
  4. 31414
  5. 2352525
  6. 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 #.
Feb 27 '10 #12

100+
P: 256
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)
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboClass_AfterUpdate()
  2. If Not IsNull(Me![cboCLASS]) Then
  3.   Me![cboSIZE].RowSource = "SELECT [SIZE] FROM tblCOMMODITY WHERE [CLASS] = '" & Me![cboCLASS] & "'"
  4. End If
  5. End Sub
  6.  
  7. Private Sub cboSize_AfterUpdate()
  8. If Not IsNull(Me![cboCLASS]) And Not IsNull(Me![cboSIZE]) Then
  9.   Me![txtCommodityNum] = DLookup("[COMMODITY_NUM]", "tblCOMMODITY", "[CLASS] = '" & Me![cboCLASS] & _
  10.                        "' And [SIZE] = '" & Me![cboSIZE] & "'")
  11. End If
  12. 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. :-)
Apr 5 '10 #13

topher23
Expert 100+
P: 234
Whenever the number in your text field is less than 32,000 or so, you can do something like
Expand|Select|Wrap|Line Numbers
  1. Me![cboSIZE].RowSource = "SELECT [SIZE] FROM tblCOMMODITY WHERE [CLASS] = '" & Me![cboCLASS] & "' ORDER BY CInt([SIZE]);"
  2.  
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.
Expand|Select|Wrap|Line Numbers
  1. 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));"
  2.  
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. ;)
Apr 5 '10 #14

100+
P: 256
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:
Expand|Select|Wrap|Line Numbers
  1. 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!
Apr 5 '10 #15

topher23
Expert 100+
P: 234
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.

Expand|Select|Wrap|Line Numbers
  1. 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)));"
  2.  
Apr 5 '10 #16

100+
P: 256
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.
Apr 12 '10 #17

NeoPa
Expert Mod 15k+
P: 31,277
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 :)
Apr 13 '10 #18

100+
P: 256
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!
Aug 16 '10 #19

NeoPa
Expert Mod 15k+
P: 31,277
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 ;)
Aug 16 '10 #20

100+
P: 256
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!
Aug 16 '10 #21

NeoPa
Expert Mod 15k+
P: 31,277
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.
Aug 16 '10 #22

100+
P: 256
Here's my code in my current DB, which is posted above but probably in chunks. This may be easier to digest:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub cboClassfrmNewGloves_AfterUpdate()
  4. If Not IsNull(Me![cboClassfrmNewGloves]) Then
  5.   Me![cboSizefrmNewGloves].RowSource = "SELECT [SIZE] FROM tblCOMMODITY WHERE [CLASS] = '" & Me![cboClassfrmNewGloves] & "'"
  6. End If
  7. End Sub
  8.  
  9. Private Sub cboSizefrmNewGloves_AfterUpdate()
  10. If Not IsNull(Me![cboClassfrmNewGloves]) And Not IsNull(Me![cboSizefrmNewGloves]) Then
  11.   Me![txtCommodityNum] = DLookup("[COMMODITY_NUM]", "tblCOMMODITY", "[CLASS] = '" & Me![cboClassfrmNewGloves] & _
  12.                        "' And [SIZE] = '" & Me![cboSizefrmNewGloves] & "'")
  13. End If
  14. End Sub
  15.  
  16.  
It looks like it's lacking anything to send the data back to the table. I think you're right on.
Aug 16 '10 #23

NeoPa
Expert Mod 15k+
P: 31,277
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).
Aug 17 '10 #24

NeoPa
Expert Mod 15k+
P: 31,277
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.
Aug 17 '10 #25

NeoPa
Expert Mod 15k+
P: 31,277
In case anyone's interested, I felt this was a subject well worthy of a full article with all details covered.

Cascaded Form Filtering.
Aug 17 '10 #26

100+
P: 256
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!
Aug 23 '10 #27

NeoPa
Expert Mod 15k+
P: 31,277
Always a pleasure Danica dear :)
Aug 23 '10 #28

Post your reply

Sign in to post your reply or Sign up for a free account.