multiple cascading combo boxes

[Guest]

i am a novice to access and im looking to learn how to create multiple
cascading combo boxes. for example, i have 3 tables...a region table, a
department table, and a requestor table...with a combo box for each on a
form. my goal is to have a user select a region in the region combo box,
which would then limit the choices in the department combo. once the user
selects a department, the choices in the requestor combo would be limited.

i am able to get this to work for the region and department, but have no
idea how to incorporate coding for the third. here is the coding that i am
using (Combo44 is for region, Combo46 is for department). how should the
coding look if i am to incorporate the third combo box for requestor?

Option Compare Database

Private Sub Combo44_AfterUpdate()
Combo46 = Null
Combo46.Requery
Combo46 = Me.Combo46.ItemData(0)
End Sub

Private Sub Form_Current()
Combo46.Requery
End Sub

Private Sub Form_Load()
If IsNull(Combo44) Then
Combo44 = Me.Combo44.ItemData(0)
Call Combo44_AfterUpdate
End If
End Sub

 

[Marshall Barton]

i am a novice to access and im looking to learn how to create multiple
cascading combo boxes. for example, i have 3 tables...a region table, a
department table, and a requestor table...with a combo box for each on a
form. my goal is to have a user select a region in the region combo box,
which would then limit the choices in the department combo. once the user
selects a department, the choices in the requestor combo would be limited.

i am able to get this to work for the region and department, but have no
idea how to incorporate coding for the third. here is the coding that i am
using (Combo44 is for region, Combo46 is for department). how should the
coding look if i am to incorporate the third combo box for requestor?

Option Compare Database

Private Sub Combo44_AfterUpdate()
Combo46 = Null
Combo46.Requery
Combo46 = Me.Combo46.ItemData(0)
End Sub

Private Sub Form_Current()
Combo46.Requery
End Sub

Private Sub Form_Load()
If IsNull(Combo44) Then
Combo44 = Me.Combo44.ItemData(0)
Call Combo44_AfterUpdate
End If
End Sub

Using more meaningful names, I think something like this air
code may be appropriate:

Private Sub cboRegion_AfterUpdate()
cboDepartment.Requery
cboDepartment.Enabled = True
cboDepartment = cboDepartment.ItemData(0)
cboRequestor = Null
cboRequestor.Enabled = False
End Sub

Private Sub cboDepartment_AfterUpdate()
cboRequestor.Requery
cboRequestor = cboRequestor.ItemData(0)
cboRequestor.Enabled = True
End Sub

Private Sub Form_Current()
If IsNull(cboRegion) Then
cboDepartment.Enabled = False
cboRequestor.Enabled = False
Else
cboDepartment.Requery
cboDepartment.Enabled = True
If IsNull(cboDepartment) Then
cboRequestor.Enabled = False
Else
cboRequestor.Requery
cboRequestor.Enabled = True
End If
End If
End Sub

Private Sub Form_Load()
'nothing - Current event will be fired
End Sub