ComboBox Based On Another ComboBox

[Cobra17]

Hey, New to this forum, but I've used access for years.



I've created code to fill a combobox based off field values from multiple tables (4 currently), that all have a few identical fields, with no duplicates.

Function ItemCombo()Dim NewValList              As StringDim myArray                 As VariantDim rs                      As DAO.RecordsetDim strstring               As StringDim lngcount                As LongDim x                       As String
NewValList = ""
' Tables to loop through    For i = 1 To 4        If i = 1 Then            x = "tbl_Fittings"        ElseIf i = 2 Then            x = "tbl_Gaskets"        ElseIf i = 3 Then            x = "tbl_MiscPipingComps"        ElseIf i = 4 Then            x = "tbl_Pipe"        End If        ' Sort and get items for drop down list        Set rs = CurrentDb.OpenRecordset("SELECT * " & "FROM " & x & " ORDER BY Item", dbOpenDynaset)        strstring = rs.Fields("Item").Value        rs.MoveNext        lngcount = 0        Do While Not rs.EOF            If strstring = rs.Fields("Item").Value Then                lngcount = lngcount + 1            Else                lngcount = 1                strstring = rs.Fields("Item").Value            End If            If lngcount = 1 Then                NewValList = NewValList + Chr(34) + strstring & Chr(34) + ";"            End If        rs.MoveNext        Loop    Next i    myArray = Split(NewValList, ";")    Call SortArray(myArray)    End Function

Function SortArray(myArray As Variant)Dim NewValList              As StringDim x                       As LongDim y                       As LongDim TempTxt1                As StringDim TempTxt2                As String

'Alphabetize Names in Array List  For x = LBound(myArray) To UBound(myArray)    For y = x To UBound(myArray)      If UCase(myArray(y)) < UCase(myArray(x)) Then        TempTxt1 = myArray(x)        TempTxt2 = myArray(y)        myArray(x) = TempTxt2        myArray(y) = TempTxt1      End If     Next y  Next x  NewValList = Right(Join(myArray, ";"), Len(Join(myArray, ";")) - 1)  Forms!frm_Piping!cboTypes.RowSource = NewValListEnd Function

My issue now is my second combobox needs to be filled with a 'Size' field in the correct table. I can get the correct table by an 'On error resume next' and the following code.

myItem = Forms!frm_Piping!cboTypes
myTable = DLookup("'tbl_Fittings'", "tbl_Fittings", "[Item]= '" & myItem & "'")myTable = DLookup("'tbl_Gaskets'", "tbl_Gaskets", "[Item]= '" & myItem & "'")myTable = DLookup("'tbl_MiscPipingComps'", "tbl_MiscPipingComps", "[Item]= '" & myItem & "'")myTable = DLookup("'tbl_Pipe'", "tbl_Pipe", "[Item]= '" & myItem & "'")
Set rs = CurrentDb.OpenRecordset("SELECT * " & "FROM " & myTable & " ORDER BY Item", dbOpenDynaset)

But I need to get the 'Size' field values based on the 'Type' field matching 'myItem' and I'm having no luck getting it to grab the no duplicate sizes for only the selected 'Item'

P.S.
Not sure how to keep the code together so it actually is readable.

[pbaldy]

Welcome aboard. Yeah, that's hard to read. You might try using the code tags instead of HTML tags (the # icon instead of the <> icon).

A couple of thoughts based on what I can see. Most of us would use Table/Query instead of Value List, and use a query or SQL for the row source. Not saying what you're doing is wrong, but it seems like a lot more work.

The concept of basing one combo on another is often called "cascading combos". Here's one link among many:

http://www.baldyweb.com/CascadingCombo.htm

Generally if you have multiple tables with the same or similar fields, it's a design mistake. Could they all go in one table with a field to denote what you now have as tables?

[Gicu]

You can use a union query to get your items from the four tables (the second field is to see the source of the record):

SELECT [Item], "tbl_Fittings" As TableName FROM tbl_Fittings
UNION
SELECT [Item], "​tbl_Gaskets" As TableName FROM tbl_Gaskets
UNION
......

Now change the row source type of the first combo from Value List to Table\Query and select the new union query. You need to set the number of columns to 2 so you can access the source table name.
In the second combo for size you need to add code in the Enter or Got Focus event to dynamically change its row source based on the value chosen in the first combo:

Dim sTable as string,sItem as string
sItem =Me.cboTypes.Column(0)
sTable = Me.cboTypes.COlumn(1)
Me.cboSize.RowSource="Select Distinct [Size] FROM [" & sTable "] Where [Item] = '" & sItem & "';" 'assumes Item is a text data type

Cheers,

[Cobra17]

pbaldy: that's how I normally do it, I was just messing around in VBA. I ended up doing it as one table.

Gicu: I did try your method too... I managed to get it to work, but for simplicity I did it as one table.

Thanks guys.

[pbaldy]

Glad you got it working.