Pooling data from multiple fields within a record into one pulldown on another form

Question

I have an MS access database. Mysql backend.

Looking to have a pull-down type field's values only be that of 3 or 4 other field's data form a certain record. The matching criteria would be Me.Lot_Number = data from fields 1,2,3 from table2 with matching Lot_Number

For example.

table2 Lot_Number: 50 Field1: Blue Field2: R22 Field3: Brown-16

(on another form and different table) Pulldown field choices would be: - Blue - R22 - Brown-16

Thanks,

Lybren Lybren · Accepted Answer · 2016-11-30T18:12:07

Just worked this solution up, best way would be to use VBA code to open recordset, find the pertinent record, and add those record's fields to the value list of your pulldown(most commonly referred as combobox).

My approach would be to add an afterupdate event to the control that has the lotnumber, so after it is updated, the rowsource of your pulldown updates as well to your desired options. So

Create an Afterupdate event procedure on the control that has Lot_Number.

Put the following code in the event.

Me.CombboxNameHere.RowSource = "SELECT Field1 FROM TableNameHere WHERE Lot_Number=" & Me.TextBoxLot_Number.Value _
                            & " UNION SELECT Field2 FROM TableNameHere WHERE Lot_Number=" & Me.TextBoxLot_Number.Value _
                            & " UNION SELECT Field3 FROM TableNameHere WHERE Lot_Number=" & Me.TextBoxLot_Number.Value & ";"

Pooling data from multiple fields within a record into one pulldown on another form

1 Answers