Complex Table "Lookup" Lists

[pbaldy]

I don't see anything offhand, presuming both fields are text. The way to debug it is to add this line:

Debug.Print strSource2

right before setting the rowsource. That will print the finished SQL to the VBA Immediate window. If you don't spot the problem there, copy/paste the SQL into a new query in SQL view and try to run it. If that still doesn't reveal the problem, can you post the db?

[JennyHunt]

I had to strip some info, but here is the database.
Any help is greatly appreciated.

[Rawb]

I'm guessing the pbaldster should be able to help you, but if you don't mind another pair of eyes, can you upload it as an Access 2000 .MDB file?

If you're willing to do that, I can give it a look too and see if I can help.

[pbaldy]

Should have noticed this. In this code you've reset the Unit combo, but then try to use it's value, so the SQL is

...WHERE Unit = '' AND PropertyCode = 'BBP'...

which obviously won't return anything.

[Rawb]

Ooo, good point.

Try the following and see if it works

Private Sub cboPropCode_AfterUpdate()
  Dim strSource As String
  Dim strSource2 As String

  strSource = "SELECT DISTINCT Unit " & _
              "FROM qry_Contacts " & _
              "WHERE PropertyCode = '" & Me.cboPropCode & "' ORDER BY Unit"

  strSource2 = "SELECT DISTINCT Tenant " & _
               "FROM qry_Contacts " & _
               "WHERE Unit = '" & Me.cboUnit & "' AND PropertyCode = '" & Me.cboPropCode & "' ORDER BY Tenant"

  Me.cboUnit.RowSource = strSource
  Me.cboUnit = vbNullString

  Me.cboTenant.RowSource = strSource2
  Me.cboTenant = vbNullString
End Sub