Update table column line by line based on yes/no values from combo box

1
votes

I am very new to Access and VBA but what i'm trying to do is simple:

I have a table which i want to show in a form, i have put said table in a subform box. I want to have a column in which i want to update the status based on options from the combo box (yes/ no/ maybe).

So as shown in the picture: i want to select a line in the subform and update the column status by selecting an option in the combo box.

enter image description here

1
vbams-accesscombobox
Need a field in table to receive data. Either set that field as a combobox (I never do this in table) or use a form object instead of table in subform and build a combobox on form. – June7

1 Answers

0
votes

You can modify the selected record in the subform in the AfterUpdate event of the combo box.

Private Sub cboStatus_AfterUpdate()
    subForm1.Form!Status = cboStatus
End Sub

This assumes that your combo box control is named cboStatus, your subform is named subForm1, and the field you want to update is named Status. It also assumes you aren't using numerical ID's as foreign keys for your values.

You will only be able to update one row at a time.

Beyond the scope of your question....

There are numerous other ways to develop an interface to edit the row. One way is to modify the field Lookup properties in the table. You can change the Display Control to a Combo Box, set the Row Source type to Value List, and then set the Row Source to a list of possible values separated by semicolon.

enter image description here

Now whenever you open that table in a datasheet view (like the example on your subform) a dropdown will appear in that column. A user can edit directly in that view without requiring a separate combo box control.

enter image description here