Access 2007 basic Cascading Combo box problems

[infoman206]

I have hit my head against the wall for too long on this, and redone the code based on different samples and each time get new problems so hopefully somebody can assist.

I have a form with a combo box for department (combo133) and a combo box for signing authority (combo139). The source table for both department (field department) and signing authority (field "name_Last_First") is called authority. The output of the form is being saved to a table WO with fields called "Originating Department" and "signing Authority"

I've added the following to my originating department after update:

Private Sub Combo133_AfterUpdate()
On Error Resume Next
combo139.rowsource = "Select distinct Name_Last_first " & _
"From Authority " & _
Where Department ='" & me.combo133 & "' " & _
"Order by Name_Last_First"
End Sub

each time I'm getting VBA errors "Compile error: Expected: end of statement" highlighting "department"

Another error I have seen is on the main form once the initial combo value is selected I receive "Syntax error (missing operator) in query expression 'department=Security'.

Any pointers would be greatly appreciated.

Thanks.

[zmbd]

You've stumbled upon one of my pet peeves by building the criteria string directly to the command/property - and it's not your fault because that's how a majority of examples show how to create these things.

Instead I suggest that you build the string first and then use the string in the command. Why you might ask, because you can then check how the string is actually resolving; thus, making troubleshooting the code so much easier as most of the time the issue is with something missing or not resolving properly/as expected within your string.

So to use your code:

Private Sub Combo133_AfterUpdate() 
   DIM strSQL as string
'
   On Error Resume Next 
'
   strSQL = "SELECT DISTINCT Name_Last_first " & _ 
      "FROM Authority " & _ 
      "WHERE Department = '" & me.combo133 & "' " & _ 
      "ORDER BY Name_Last_First;" 
 '>>(Made two additional changes here:
      'Added a double quote before the WHERE clause
      'Added the closing semicolon
'
'now you can insert a debug print here for troubleshooting
' - press <ctrl><g> to open the immediate window
' - you can now cut and paste this information for review!
'
debug.print "Your criteria = " & strSQL
'
'now use the string in your code:
   combo139.rowsource = strSQL
End Sub

If you will make these little changes and if you are still haveing issues, then post back the resolved string we can help you tweak the code.

[NeoPa]

Z has already found and fixed the problem that stopped your code working (The missing double-quote (")). He has also given very good advice as to work generally with SQL and that you should formulate the string first (See How to Debug SQL String).

The semi-colon (;) is not absolutely necessary in Jet SQL (The SQL used in Access) so will not throw up any errors. Nevertheless it wouldn't hurt to use it generally as it can make your code more portable in the long-term.

[infoman206]

Thank you VERY much for the assistance - it is often the small things with code that can cause us to run around in a circle for too long. Have a great week.