CODEWITHSUNDEEP
vbams-access
Desert Spider
Desert Spider

Reputation: 778

Populate Field based on another Combo Box

I have a form (MS-Access 2003) that has three combo boxes. 1st (Category), 2nd (CatDetail), 3rd (NbrValue). I have no problem populating 2nd with an after update event from 1st. My problem comes in when I try to run a similar update event for the 2nd to update the 3rd. Here is my code.

Table 1 (Category)

CategoryID    Category Name
1             Vacation
2             DH

1st combo box (Category) SQL

SELECT Category.CategoryID, Category.[Category Name]
FROM Category
ORDER BY Category.[Category Name];

After Update VBA for 1st combo box (Category)

Option Compare Database
Private Sub Category_AfterUpdate()
Me.CatDetail = Null
Me.CatDetail.Requery
Me.CatDetail = Me.CatDetail.ItemData(0)
End Sub

Table 2 (CatDetail)

CatDetail ID    CatDetail         Category
1               Vac Day Used      Vacation
2               Partial Vac Day   Vacation
3               DH Gain           DH
4               DH Used           DH

2nd Combo Box SQL (CatDetail)

SELECT CatDetail.[CatDetail ID], CatDetail.[CatDetail Name], CatDetail.CategoryID
FROM CatDetail
WHERE (((CatDetail.CategoryID)=[Forms]![Combo Form]![Category]));

After Update VBA for 2nd Combo box (CatDetail)

Private Sub CatDetail_AfterUpdate()
Me.NbrValue = Null
Me.NbrValue.Requery
Me.NbrValue = Me.NbrValue.ItemData(0)
End Sub

This portion works great. I am lost with the next set to get the 3rd combo box to update.

Table 3 (Value)

ValueID     Value    CatDetail Name
1           -1       Vac Day Used
2           -0.5     Partial Vac Day
3           1        DH Gain
4           -1       DH Used

3rd combo box (NbrValue) SQL

SELECT Value.ValueID, Value.Value, Value.[CatDetail Name]
FROM [Value]
WHERE (((Value.[CatDetail Name])=[Forms]![Combo Form]![CatDetail]));

To summarize, I can select my 1st combo box value. After that selection the 2nd combo box updates with all corresponding values. The trouble is with the after update event on teh 2nd combo box as my 3rd combo box comes back Blank.

Upvotes: 2

Views: 5689

Answers (1)

Kassabba
Kassabba

Reputation: 342

I do this all the time. In the AfterUpdate() set the rowsource of the dependent combobox.

Sub Combo1_AfterUpdate()

 comboBox2.RowSource = "SELECT .... WHERE FIELD = " & Combo1.Value

 comboBox3.RowSource = ""  ' Reset the third combo until after the 2nd is set.

End Sub
Sub Combo2_AfterUpdate()

 comboBox3.RowSource = "SELECT .... WHERE FIELD = " & Combo2.Value

End Sub

This will work if the query's are actually returning something.

Upvotes: 1

Related Questions