Cascading combo box code error

[Sue R via AccessMonster.com]

I have read through here and used Microsoft's site to write the code for
cascading combo boxes but am receiving an error on the first combo box
selection "The expression After Update you enterd as the event property
setting produced the following error:Expected:identifier.

It's a simple db - Three tables
tblMain (all staff data)
tblDepartment (ID, Department)
tblUnit (ID, Unit, Department, DepartmentID)
One main form that I have 2 combo boxes on. The first combo box
(cbxDepartment) should drop down the possible Departments and the second
combo box (cbxUnit) should choose from possible units based on the Department
chosen in the first.

Using the instructions and code from the MS site, this is what I have in the
first Combo box (cbxDepartment):
Row Source Type - Table/Query
Row Source tblDepartment
After Update/Event Procedure:

Option Compare Database

Private Sub
End Sub

Private Sub cbxDepartment_AfterUpdate()
Me.tblUnit.RowSource = "SELECT Unit FROM" & _
" tblUnit WHERE DepartmentID = " & Me.tblDepartment & _
" ORDER BY Unit"
Me.tblUnit = Me.tblUnit.ItemData(0)
End Sub

Private Sub Unit_AfterUpdate()

End Sub

Private Sub cbxDepartment_AfterUpdate()

End Sub

And on the second combo box (cbxUnit)
Row Source Type - Table/Query

Any suggestions would be greatly appreciated.

Thanks
Sue

 

[Guest]

I think you just have a typo here. Change:

Me.tblUnit.RowSource = "SELECT Unit FROM" & _ To
Me.cbxUnit.RowSource = "SELECT Unit FROM" & _

 

[Sue R via AccessMonster.com]

That didn't seem to make a difference. I did get the first combo box to work
with no error message by trying something in another thread (Some say to use
the first combo for the coding, others the second)??? - I completely took out
the After update event procedure on the first combo box (cbxDepartment) and
in the second combo box (cbxUnit) entered this which fixed the error on the
first combo box so it works perfectly but the second combo box is empty as
before.

Private Sub cbxDepartment_AfterUpdate()
Dim sSQL As String

'This function sets the RowSource of cbxUnit, based on the
'value selected in cbxDepartment.
sSQL = "SELECT Department, Unit " _
& " FROM tblUnit WHERE Department = " & Me.cbxDepartment _
& " ORDER BY Unit"

Me.cbxUnit.RowSource = sSQL
'The combo should requery on it's own, but if it doesn't,
'uncomment the next line.
'Me.cboCombo2.Requery 'Requery the combo
End Sub


Private Sub Unit_AfterUpdate()

End Sub

Private Sub cbxDepartment_AfterUpdate()

End Sub

Thanks still....Sue

I think you just have a typo here. Change:

Me.tblUnit.RowSource = "SELECT Unit FROM" & _ To
Me.cbxUnit.RowSource = "SELECT Unit FROM" & _

I have read through here and used Microsoft's site to write the code for
cascading combo boxes but am receiving an error on the first combo box

[quoted text clipped - 43 lines]

Thanks
Sue

 

[Sue R via AccessMonster.com]

I got it somewhat working using a different method- it's now requesting me to
Enter a Parameter Value when I select the second combo box. If I retype the
name from combo one, everything shows up correctly in combo 2....but that's a
whole new thread

Thanks anyway...Sue

That didn't seem to make a difference. I did get the first combo box to work
with no error message by trying something in another thread (Some say to use
the first combo for the coding, others the second)??? - I completely took out
the After update event procedure on the first combo box (cbxDepartment) and
in the second combo box (cbxUnit) entered this which fixed the error on the
first combo box so it works perfectly but the second combo box is empty as
before.

Private Sub cbxDepartment_AfterUpdate()
Dim sSQL As String

'This function sets the RowSource of cbxUnit, based on the
'value selected in cbxDepartment.
sSQL = "SELECT Department, Unit " _
& " FROM tblUnit WHERE Department = " & Me.cbxDepartment _
& " ORDER BY Unit"

Me.cbxUnit.RowSource = sSQL
'The combo should requery on it's own, but if it doesn't,
'uncomment the next line.
'Me.cboCombo2.Requery 'Requery the combo
End Sub

Private Sub Unit_AfterUpdate()

End Sub

Private Sub cbxDepartment_AfterUpdate()

End Sub

Thanks still....Sue

I think you just have a typo here. Change:

Me.tblUnit.RowSource = "SELECT Unit FROM" & _ [quoted text clipped - 6 lines]
Thanks
Sue