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

Filter a combobox based on another combobox

100+
P: 178
Table set up that feed the comboboxes:

SAPFinishedPartNo
OldPartNo
SAPCompNo
OldCompNo

Combobox Name on form:

1st combobox:cboSAPNNo
2nd Combobox: cboComp

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboSAPNNoID_AfterUpdate()
  2.  
  3. With Me![cboComp]
  4.     If IsNull(Me!cboSAPNNo) Then
  5.       .RowSource = ""
  6.     Else
  7.       .RowSource = "SELECT [SAPCompNo] " & _
  8.                    "FROM tblPartNoList " & _
  9.                    "WHERE [SAPFinishedPartNo]=" & Me!SAPCompNo
  10.     End If
  11.     Call .Requery
  12.   End With
  13. End Sub
I believe I have something in the wrong order or jumbled...This is my first time trying this. I tried to follow an article on this forum, but apparently mucked it up.

I need to select a number from the 1st combobox and have it filter the 2nd combobox that match the 1st box.

I have all 4 (SAPFinishedPartNo; OldPartNo; SAPCompNo; oldCompNo) in an embedded query feeding the comboboxes.

On the 1st combobox I have SAPFinishedPartNo; OldPartNo visible to the user with SAPFinishedPartNo as the bound column.

On the 2nd combobox I have SAPCompNo; oldCompNo visible with SAPCompNo as the bound column.

Thanks for the help and hopefully I was fairly clear on what I was looking for....
2 Weeks Ago #1

✓ answered by twinnyfo

Line 3 above has an asterisk before the quotes, unless you are just pointing out where it breaks.

Also, you should have a space after the closing square brace in line 1:

Expand|Select|Wrap|Line Numbers
  1. .RowSource = _
  2.     "SELECT [SAPCompNo] " & _
  3.     "FROM tblPartNoList " & _
  4.     "WHERE [SAPFinishedPartNo]='" & Me.cboSAPNNo & "'"
No need for any parentheses (that's an MS Accessism, which is totally unnecessary with only one criteria in the WHERE clause).

Hope this hepps!

Share this Question
Share on Google+
17 Replies


NeoPa
Expert Mod 15k+
P: 31,561
Hi DJ.

You've done a decent job explaining but I suspect you've been a little sloppy with names. Your code indicates a ComboBox of cboSAPNNOID but your explanation calls it cboSAPNNO. In conversation such precision is rarely important. In computer terms it's critical. I don't expect that's directly related to this not working but there's more missing that makes it hard to steer you accurately.

I know. It's very hard for you to know what is necessary to include when you're in the position before you understand it well enough to do it without help anyway. Catch 22!

Reading between the lines I would guess what you're actually trying to do is a form of cascaded filtering such that you want cboCompID to contain a list of [SAPCompNo]s where the [SAPFinishedPartNo] equals the value already selected in cboSAPNNoID if there is one.

It helps to know whether cboSAPNNoID is a string or numeric value. We don't know that so I'll just comment that the quotes (') should be removed from the following suggested code unless the value is a string :
Expand|Select|Wrap|Line Numbers
  1. .RowSource = "SELECT [SAPCompNo] " _
  2.            & "FROM [tblPartNoList] " _
  3.            & "WHERE ([SAPFinishedPartNo]='" & Me.cboSAPNNoID & "')"
See how that works in place of lines #7 through #9.
2 Weeks Ago #2

100+
P: 178
NeoPa,

Sorry I thought I had updated and removed all of the "ID"'s from this post....

This is the coda as it stands now
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboSAPNNo_AfterUpdate()
  2.  
  3. With Me![cboComp]
  4.     If IsNull(Me!cboSAPNNo) Then
  5.       .RowSource = ""
  6.     Else
  7.       .RowSource = "SELECT [SAPCompNo] " & _
  8.                    "FROM tblPartNoList " & _
  9.                    "WHERE [SAPFinishedPartNo]=" & Me!cboSAPNNo
  10.     End If
  11.     Call .Requery
  12.   End With
  13. End Sub
I get a data type mismatch in criteria expression.

I looked up in the table and all are short text (SAP numbers are all numeric, but we also have alphanumeric in there to, so I made them all short text)

So now when I enter a SAP number in the combobox "cboSAPNNo", I get the above error when opening the dropdown of the combobox "cboComp"
2 Weeks Ago #3

100+
P: 178
I did a little more work on it and got rid of the data mismatch, but now nothing shows up in "cboComp" when I pick a number/Text from "cboSAPNNo" So We/I must be getting closer and it's just not seeing the selection from "cboSAPNNo"

Here is where it stands now:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboSAPNNo_AfterUpdate()
  2.  
  3. With Me![cboComp]
  4.  
  5.     If IsNull(Me!cboSAPNNo) Then
  6.  
  7.       .RowSource = ""
  8.  
  9.     Else
  10.  
  11.       .RowSource = "SELECT [SAPCompNo]" & _
  12.                     "FROM tblPartNoList " & _
  13.                     "Where [SAPFinishedPartNo]=""[cboSAPNNo]"" "
  14.     End If
  15.  
  16.     Call .Requery
  17.  
  18.   End With
  19.  
  20. End Sub
2 Weeks Ago #4

NeoPa
Expert Mod 15k+
P: 31,561
Hi DJ.

I suspect if you go back to my post and use that as a template it will work perfectly without any problems. Of course you'll need to pay close attention to how you do that this time ;-)
2 Weeks Ago #5

100+
P: 178
NeoPa,

I copied exactly how you have it and it comes out blank. It should list 10 items.Here is the code exactly as its entered.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboSAPNNo_AfterUpdate()
  2.  
  3. With Me![cboComp]
  4.  
  5.     If IsNull(Me!cboSAPNNo) Then
  6.  
  7.       .RowSource = ""
  8.  
  9.     Else
  10.  
  11.       .RowSource = "SELECT [SAPCompNo]" & _
  12.                     "FROM tblPartNoList " & _
  13.                     "Where [SAPFinishedPartNo]=""[cboSAPNNo]"" "
  14.     End If
  15.  
  16.     Call .Requery
  17.  
  18.   End With
  19.  
  20. End Sub
1 Week Ago #6

100+
P: 178
NeoPa,


Just noticed I entered it wrong from what you put, but now I get a syntax error when compiling.

Expand|Select|Wrap|Line Numbers
  1. .RowSource = "SELECT [SAPCompNo]" & _
  2.                     "FROM tblPartNoList " & _
  3.                     * "WHERE ([SAPFinishedPartNo]='" & Me.cboSAPNNo & "')"
1 Week Ago #7

twinnyfo
Expert Mod 2.5K+
P: 3,313
Line 3 above has an asterisk before the quotes, unless you are just pointing out where it breaks.

Also, you should have a space after the closing square brace in line 1:

Expand|Select|Wrap|Line Numbers
  1. .RowSource = _
  2.     "SELECT [SAPCompNo] " & _
  3.     "FROM tblPartNoList " & _
  4.     "WHERE [SAPFinishedPartNo]='" & Me.cboSAPNNo & "'"
No need for any parentheses (that's an MS Accessism, which is totally unnecessary with only one criteria in the WHERE clause).

Hope this hepps!
1 Week Ago #8

100+
P: 178
Twinnyfo,

That did it. My only issue is that I have 2 visible columns for the cboSAPNo, but after the filter it only shows the bound column. I was figuring the second column would show up. Do I need to add this into the .recordsource? I did try changing it to:

Expand|Select|Wrap|Line Numbers
  1. "SELECT [SAPCompNo], [OldCompNo] "
But this did not work, just came out blank again.

I know this wasn't part of the original question but I "assumed" it would automatically show.

Thanks for the fix on the last part.
1 Week Ago #9

twinnyfo
Expert Mod 2.5K+
P: 3,313
I'll allow as a follow-on question within this thread, as it is an easy resolution (should be) and is directly related to your particular issue.

If you have a Combo Box with two columns, typically, the first column is the bound column and is numerical (and index) and the second column shows the text referred to in the index. For your ColumnWidth property, you set it to 0"; 1". This means that the first column is "hidden", and the second is one inch wide (displayed).

Hope this hepps.
1 Week Ago #10

100+
P: 178
Twinnyfo,

I have it set up like this already. If I comment out the filter in VBA it shows exactly the way it's supposed to. I have it set as (1.25";2"), Column count 2, Column Heads=Yes, Bound column 1. This is why I had to ask the follow up question, I figured it would show...
1 Week Ago #11

twinnyfo
Expert Mod 2.5K+
P: 3,313
I'm not sure what you mean by "comment out the filter".

Also, typically, your bound column is not shown, because it is just a numerical value. But, based upon the example in Post #6, you only have one column being returned from the query, so, I'm not exactly sure what you are chasing after....
1 Week Ago #12

100+
P: 178
When you put ' in front of a line of code..."Comment out"

I need:

SAPCompNo - Bound
OldCompNo - unbound but visible in dropdown

I uploaded 2 pictures to show you what I need when filtered and what it is doing currently filtered.
Attached Images
File Type: jpg Pic of both Columns need.jpg (61.4 KB, 4 views)
File Type: jpg Pic when filtered.jpg (59.1 KB, 5 views)
1 Week Ago #13

NeoPa
Expert Mod 15k+
P: 31,561
Hi DJ.

You've been busy in my absence.

I try to get my suggestions right but I do struggle to see properly so sometimes I include a * when it should be an & :-( Nevertheless, you should find that the original suggestion, if copied accurately, gives you the result you require.

That if copied accurately part is so very important. Copy/Paste is your friend.

Thanks, as always, to Twinny for stepping in and illustrating again what you needed.

I would add that I always use parentheses in my WHERE clauses. Not that it's absolutely necessary; simply that it helps to clarify what's what - particularly when trying to follow VBA code that is used to create the SQL. It's more of a style thing than necessary syntax obviously.
1 Week Ago #14

100+
P: 178
NeoPa,

We got the filter working perfectly, but for some reason when it is filter the second column isn't present.
1 Week Ago #15

twinnyfo
Expert Mod 2.5K+
P: 3,313
Your SQL is only asking for one column.
1 Week Ago #16

100+
P: 178
I got it to work, I added , [OldCompNo], this time it seem to work, not sure what I did differently...but hey it worked, that's all that matters. Now I can go on vacation for the rest of the month.

Thanks for yours and Neopa's help and merry christmas and Happy new years to you both.
6 Days Ago #17

twinnyfo
Expert Mod 2.5K+
P: 3,313
Glad we could hepp!

Merry Christmas and Happy New Year to you as well!
2 Days Ago #18

Post your reply

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