Can't get the cascading combo boxes to work

0
votes

I am trying to make cascading combo boxes but I can't seem to get it to work. For example, if I select a specific computer in the first combo box, then the second combo box should only show the HDD that is compatible with that computer. I also provided a link to the database that I have created. Can anyone help me out with this?

I have 2 tables with the fields:

  • tblComputer(Computer)
  • tblHDD(HDD, Computer)

cboxComputer Row Source: SELECT tblComputer.Computer FROM tblComputer;

cboxHDD Row Source: SELECT tblHDD.HDD, tblHDD.Computer FROM tblHDD; 

Private Sub cboxComputer_AfterUpdate()
    Me.cboxHDD.RowSource = "SELECT HDD " & _
                           "FROM tblHDD " & _
                           "WHERE Computer = " & Nz(Me.cboxComputer) & _
                           "ORDER BY HDD"
End Sub

https://drive.google.com/file/d/0Bye-M8FI1tRURmQ0MEFzRjBCdWM/view?usp=sharing

1
vbams-accesscomboboxfilteringcascadingdropdown
what does not work? is the cboxHDD not loading? is there error message? – BobNoobGuy
Sorry for not including what was wrong. The second combo box does not work. When I select the second combo box, it says "syntax error (missing operator) in query expression 'Computer = SonyORDER BY HDD'." – Ron T

1 Answers

2
votes

The Computer field in the database is a string datatype. Try putting apostrophes around the name like this:

Private Sub cboxComputer_AfterUpdate()
    Me.cboxHDD.RowSource = "SELECT HDD " & _
                       "FROM tblHDD " & _
                       "WHERE Computer = '" & Nz(Me.cboxComputer) & "' " & _
                       "ORDER BY HDD"
End Sub