Filter a combobox based on another combobox

[DJRhino1175]

Table set up that feed the comboboxes:

SAPFinishedPartNo
OldPartNo
SAPCompNo
OldCompNo

Combobox Name on form:

1st combobox:cboSAPNNo
2nd Combobox: cboComp

Private Sub cboSAPNNoID_AfterUpdate()
 
With Me![cboComp]
    If IsNull(Me!cboSAPNNo) Then
      .RowSource = ""
    Else
      .RowSource = "SELECT [SAPCompNo] " & _
                   "FROM tblPartNoList " & _
                   "WHERE [SAPFinishedPartNo]=" & Me!SAPCompNo
    End If
    Call .Requery
  End With
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....

[NeoPa]

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 :

.RowSource = "SELECT [SAPCompNo] " _
           & "FROM [tblPartNoList] " _
           & "WHERE ([SAPFinishedPartNo]='" & Me.cboSAPNNoID & "')"

See how that works in place of lines #7 through #9.

[DJRhino1175]

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

Private Sub cboSAPNNo_AfterUpdate()
 
With Me![cboComp]
    If IsNull(Me!cboSAPNNo) Then
      .RowSource = ""
    Else
      .RowSource = "SELECT [SAPCompNo] " & _
                   "FROM tblPartNoList " & _
                   "WHERE [SAPFinishedPartNo]=" & Me!cboSAPNNo
    End If
    Call .Requery
  End With
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"

[DJRhino1175]

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:

Private Sub cboSAPNNo_AfterUpdate()
 
With Me![cboComp]
 
    If IsNull(Me!cboSAPNNo) Then
 
      .RowSource = ""
 
    Else
 
      .RowSource = "SELECT [SAPCompNo]" & _
                    "FROM tblPartNoList " & _
                    "Where [SAPFinishedPartNo]=""[cboSAPNNo]"" "
    End If
 
    Call .Requery
 
  End With
 
End Sub

[NeoPa]

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

[DJRhino1175]

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.

Private Sub cboSAPNNo_AfterUpdate()
 
With Me![cboComp]
 
    If IsNull(Me!cboSAPNNo) Then
 
      .RowSource = ""
 
    Else
 
      .RowSource = "SELECT [SAPCompNo]" & _
                    "FROM tblPartNoList " & _
                    "Where [SAPFinishedPartNo]=""[cboSAPNNo]"" "
    End If
 
    Call .Requery
 
  End With
 
End Sub

[DJRhino1175]

NeoPa,


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

.RowSource = "SELECT [SAPCompNo]" & _
                    "FROM tblPartNoList " & _
                    * "WHERE ([SAPFinishedPartNo]='" & Me.cboSAPNNo & "')"

[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:

.RowSource = _
    "SELECT [SAPCompNo] " & _
    "FROM tblPartNoList " & _
    "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!

[DJRhino1175]

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:

"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.

[twinnyfo]

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.

[DJRhino1175]

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

[twinnyfo]

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

[DJRhino1175]

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.

[NeoPa]

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.

[DJRhino1175]

NeoPa,

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

[twinnyfo]

Your SQL is only asking for one column.

[DJRhino1175]

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.

[twinnyfo]

Glad we could hepp!

Merry Christmas and Happy New Year to you as well!