Auto populate a combobox

Question

Hi Here i am again. I have 3 comboboxes on my form ie cmbPart_number, cmbPart_name and cmbEOnumber.

All i want to find out is how the cmbPart_name and cmbEO_number boxes are autopopulated when the user makes a selection from the cmbPart_number. What i have tried so far is adding part_name and EO_number in the row source SQL query and in the control source of cmbpart_name i have tried cmbpart_name(value)=cmbpart_number.column(1)

Any help with this is truly appreciated

Answer 1

You should be able to do this by setting the RowSource in the AfterUpdate event

Private Sub cmbPart_number_AfterUpdate()

   cmbPart_name.RowSource = "...WHERE [PartNumber] = '" & _
                                                   cmbPart_number.value & "'; "
   cmbPart_name.Requery

End Sub

Here's a walk through on different ways to implement cascading combo boxes in Access

Answer 2

There is a similar question here: http://stackoverflow.com/questions/927256/is-there-a-simple-way-of-populating-dropdown-in-this-access-database-schema

Answer 3

I knew there had to be a simple way of doing this without digging deep into VB. My first combobox ie cmbPart_number, i have modified as follows - Row Source has all 3 fields in the SQL query ie SELECT part_number, part_name, EO_number from parts ORDER BY...; I have modified the column count to 2 as Access always counts the first column in a table as number 0. So its Column 0,1 & 2 for all 3 fields. My column widths are 1";0"

Next I modified cmbPart_name's control source to read =cmbpart_number.column(1). Its row source has a SELECT statement SELECT part_number, part_name FROM parts, ORDER BY... Column Count is 1 and Column Width is 0";1"

Finally for cmbEO_number, control source is =cmbpart_number.column(2). Row Source select statement is SELECT part_number, EO_number FROM parts ORDER BY...Column count is 1 and column width is 0";1"

This now works fine and any entry selected in the cmbPart_number box autopopulates the other 2. Thank you all for your help.