Union selection in combobox, autofill based on selection

[gaker10]

Sounds simple, and I've done something like this before, but it doesn't seem to work now.



Need to populate several text fields based on a combobox selection. I need to union some info and would like to put autofilled info in as columns just so I can reference a column to autofill.

Combobox field is a QtyA. The table in question has a QtyA, B, and C fields. I want all three as selection for the combobox. Each Qty has an associated Unit Price, Date, and Vendor. I want unit price, date, and vendor as columns so I can set my fields to populate based on referencing these columns.

For example, Part 1122 has a QtyA of 100 at $0.50 unit price from VendorA that was retreived on 8/15/2014. It also has a QtyB of 500 at $0.45 from the same vendor on the same date. QtyC is 1000 at $0.30 from the same vendor on the same date. I want my combobox to display all three Qtys, as well as have unit price, date, and vendor as displayed columns that are associated with the appropriate qty. I will use these columns to autofill text fields, which I already know how to code. Just having trouble with the Row Source on the combobox.

My database has too much info to scratch out and post on this thread. I'll do screenshots if needed though.

[gaker10]

FYI, I got it to display the unioned items, but not the other columns.

[gaker10]

So, I realized that I was trying to change the row source from the properties sheet instead of the code -_- Derp on my part.

Also, I think I got it to work Here is da code:

cboQtyA.RowSource = "SELECT PDatabase.QtyA, " & _
                " PDatabase.UnitPriceA " & _
                "FROM PDatabase " & _
                "WHERE PDatabase.PartNumber = '" & Me.txtComponent & "' " & _
                "ORDER BY PDatabase.QtyA " & _
                "UNION SELECT PDatabase.QtyB, " & _
                " PDatabase.UnitPriceB " & _
                "FROM PDatabase " & _
                "WHERE PDatabase.PartNumber = '" & Me.txtComponent & "' " & _
                "UNION SELECT PDatabase.QtyC, " & _
                " PDatabase.UnitPriceC " & _
                "FROM PDatabase " & _
                "WHERE PDatabase.PartNumber = '" & Me.txtComponent & "' "

I will be expanding on this to include the things mentioned in my original problem. Posted this for anyone else who needs this sort of function.