CODEWITHSUNDEEP
ms-access
Jossy
Jossy

Reputation: 1021

How to update a record based on a combo box determined by a combo box?

I have tbl_1:

+-------+---------+
| fld_A |  fld_B  |
+-------+---------+
|     1 |         |
|     2 |         |
|     3 | str_3   |
|     4 | str_4   |
|     5 | str_5   |
+-------+---------+

And tbl_2:

+-------+---------+
| fld_C |  fld_D  |
+-------+---------+
|     1 | str_1_X |
|     1 | str_1_Y |
|     2 | str_2_X |
|     2 | str_2_Y |
|     3 | str_3_X |
|     3 | str_3_Y |
+-------+---------+

I want to update tbl_1 with data from tbl_2 using a form (frm_1) which has two combo boxes on it: cbo_1 and cbo_2.

The Control Source for cbo_1 is fld_A and the Row Source is as follows:

SELECT tbl_1.fld_A
FROM tbl_1
WHERE (((tbl_1.fld_B) Is Null));

The Row Source is working fine and displays both 1 and 2. The Bound column is 1.

The Control Source for cbo_2 is fld_B and the Row Source is as follows:

SELECT tbl_2.fld_D, tbl_2.fld_C
FROM tbl_2
WHERE (((tbl_2.fld_C)=[Forms]![frm_1].[cbo_1]));

The Row Source is working fine, e.g. displays str_1_X and str_1_Y when 1 is selected in cbo_1. The Bound column is 1.

I also have some VBA to ensure cbo_2 updates on a change in cbo_1:

Private Sub cbo_1_Change()

cbo_2.Value = Null
cbo_2.Requery

End Sub

This also works fine.

My issue is that when I select a record from cbo_1 and then a record from cbo_2 then fld_B doesn't update.

Upvotes: 1

Views: 64

Answers (1)

June7
June7

Reputation: 21379

Options:

  1. bind form to tbl_1, cbo_1 is UNBOUND (no ControlSource) and used as criteria with code in AfterUpdate event to either apply filter or move to record, cbo_2 has ControlSource set to fld_B to save selected value

  2. form and both comboboxes are UNBOUND, combobox selections used in an UPDATE action SQL
    CurrentDb.Execute "UPDATE tbl_1 SET fld_B='" & Me.cbo_2 & "' WHERE fld_A=" & Me.cbo_1

Upvotes: 1

Related Questions