Cascading Combo box doesn't update 2nd box options

1
votes

I am using MS Access 2010 to store records about audits that take place at our hospital. I am trying to create a form where it will filter the audits according to the drop down options selected.

I want the Specialty combo boxes to cascade from Directorate, so if I select "Family and Public Health" Directorate, only the Specialties that are within that Directorate appear in the combo box below.

My ComboDirectorate Row Source is SELECT [tblDirectorate].[Directorate Key], [tblDirectorate].[Directorate] FROM tblDirectorate;

My ComboSpecialty Row Source is SELECT tblSpecialty.[Specialty Key], tblSpecialty.Specialty FROM tblSpecialty;

I then have After Update code for ComboDirectorate:

Private Sub ComboDirectorate_AfterUpdate()
Me.ComboSpecialty.RowSource = _
  & "SELECT [tblSpecialty].[Specialty Key], [tblSpecialty].[Specialty]" _
  & "FROM tblSpecialty WHERE [tblSpecialty].[Directorate] = " _
  & Me.ComboDirectorate
Me.ComboSpecialty.Requery
End Sub

This doesn't seem to have any affect on the specialties listed.

I have tried putting in ' ' as per Can't get the cascading combo boxes to work

WHERE Directorate = '" & Nz(Me.ComboDirectorate) & "' " & _

But it still doesn't list the specialties according to the directorate selected.

2
comboboxms-access-2010

2 Answers

0
votes

I have known combobox to become corrupted and I had to create new combobox. However, first check that BoundColumn and ColumnCount properties for each combobox are set appropriately.

Make sure the value of ComboDirectorate is the same value saved into tblSpecialty - I assume numeric PK/FK.

I would set ComboSpecialty RowSource to:
SELECT [Specialty Key], Specialty FROM tblSpecialty WHERE Directorate = [ComboDirectorate];

Then I would put code in ComboSpecialty GotFocus event.

Sub ComboSpecialty_GotFocus()
Me.ComboSpecialty.Requery
End Sub

Be aware that cascading combobox with lookup alias will not work nice in Continuous or Datasheet form.

Advise no spaces or punctuation/special characters (underscore only exception) in naming convention.

0
votes

Using June7's answer I coded the following:

Private Sub ComboSpecialty_GotFocus()
  If IsNull(Me.ComboDirectorate) Then
     Me.ComboSpecialty.RowSource = "SELECT [Specialty Key], Specialty FROM tblSpecialty"
 ElseIf Not IsNull(Me.ComboDirectorate) Then
     Me.ComboSpecialty.RowSource = "SELECT [Specialty Key], Specialty FROM tblSpecialty WHERE Directorate = [ComboDirectorate]"
 End If

Me.ComboSpecialty.Requery
End Sub

If the user doesn't select a value from the first combo box (ComboDirectorate) then all values are displayed in the second combo box (ComboSpecialty). If a value is selected in the first box then only related values are displayed in the second combo box.