Cascading Drop Down Box Not Working

[Bridiewms]

I am trying to set up a cascading drop down box. The default value for the first drop box is 0 which means that no form has been selected. If no form has been selected I want all items of PPE to be listed from the PPE table. If any form number above 0 have been issued this relates to only selected item and I want only these to be listed.

For example
0 = All items from PPE table
1 = Hard Hat & Gloves issued
2 = Gloves & Dustmasks issued
3 = Gloves & Dustmasks & Earplugs issued

I have a query that lists all the form numbers and the PPE issued with these form numbers.

The code I currently have is as follows. No errors come up but the second drop down box is blank after update of the FormNo dropbox.



Private Sub FormNo_AfterUpdate()

If [FormNo] > 0 Then

Me.PPE_Issued.RowSource = "SELECT ID, Item_Name " & _
"FROM QryPPEIssuedDropDown " & _
"WHERE FormNo = '" & PPEIssueID & "' " & _
"ORDER BY Item_Name"
ElseIf [FormNo] = 0 Then

Me.PPE_Issued.RowSource = "SELECT ID, Item_Name " & _
" FROM PPE " & _
" ORDER BY [Item_Name]"

End If

End Sub

 

[pr2-eugin]

You need to Requery the Second ComboBox AfterUpdating the First ComboBox..

Private Sub FormNo_AfterUpdate()
    If [FormNo] > 0 Then
        Me.PPE_Issued.RowSource = "SELECT ID, Item_Name " & _
                                  "FROM QryPPEIssuedDropDown " & _
                                  "WHERE FormNo = '" & PPEIssueID & "' " & _
                                  "ORDER BY Item_Name"
    Else
        Me.PPE_Issued.RowSource = "SELECT ID, Item_Name FROM PPE " & _
                                  " ORDER BY [Item_Name]"    
    End If
    [COLOR=Red][B]Me.PPE_Issued.Requery[/B][/COLOR]
End Sub

 

[Bridiewms]

tried adding the additional piece of coding but still not returning anything. Any other ideas?

 

[Bridiewms]

Never mind. managed to sort it.

This seemed to work...

Me.FormNo.Requery

If [FormNo] > 0 Then

Me.PPE_Issued.RowSource = "SELECT ID, Item_Name, PPEIssueID FROM QryPPEIssuedDropDown " & _
"WHERE PPEIssueID = FormNo.Value " & _
"ORDER BY Item_Name"
ElseIf [FormNo] = 0 Then

Me.PPE_Issued.RowSource = "SELECT ID, Item_Name FROM PPE " & _
" ORDER BY Item_Name"

End If

Me.PPE_Issued.Requery