Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2013
    Posts
    2

    Unanswered: Cascading Combo boxes revisited

    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 source table and destination table fields are text - no ID fields in use at this time - just in the process of cleaning up an old filemaker pro system...

    The output of the form is being saved to a table WorkOrder_Tbl with fields called "Originating Department" and "signing Authority"

    I've reviewed countless samples online - 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"

    I've also tried:

    On Error Resume Next
    Me.Combo141.RowSource = "Select distinct Name_Last_first " & _
    "From Authority " & _
    "Where authority.Department = " & Nz(Me!Combo133) & _
    "Order by Name_Last_First

    With no success.

    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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    assign the value of what will be the rowsource to a variable first
    ..it makes debugging easier, bear in mind its one thing to have VBA code that compiles, its another to have valid SQL compile within VBA code
    Code:
    dim strSQL as string
    strSQL = "Select distinct Name_Last_first From Authority"
    strSQL = strSQL & " Where Department ='" & me.combo133 & "' " & _
    strSQL = strSQL & "Order by Name_Last_First"
    Combo141.RowSource = strsql
    then you can examine what you have actually sent to the sql engine
    either put a breakpoint on the code (do a google on MS access debugging if you don't know about breakpoints [and you should]) or display it in a msgbox eg:-
    msgbox "My sql is:-" & vbcrlf & strSQL

    if the code you are posting is the code you attemtped to run AND the datatype of deprtment IS string text, then:

    version 1 won't compile because you are missing a leading " in the where clause, eg:-
    "From Authority " & _
    "Where Department ='" & me.combo133 & "' " & _

    version 2 will compile, but won't work as the value of the combo box isn't delimited (you must delimit values in SQL so the SQL engine knows where the value starts and stops. eg:-

    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 source table and destination table fields are text - no ID fields in use at this time - just in the process of cleaning up an old filemaker pro system...

    The output of the form is being saved to a table WorkOrder_Tbl with fields called "Originating Department" and "signing Authority"

    I've reviewed countless samples online - 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"

    I've also tried:

    Me.Combo141.RowSource = "Select distinct Name_Last_first " & _
    "From Authority " & _
    "Where authority.Department = '" & Nz(Me!Combo133) & "'" & _

    on error resume next is a useless piece of code UNLESS you have a specific need for it. effectively it says if there is a problem keep the code running, rather than trap for an error and wrok out what to do with that errror.

    get to grips with debugging, it will save you masses of time when developing in VBA
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2013
    Posts
    2

    Thumbs up Thank you!

    Thank you for the quick response and pointers - the system now works as required and I can actually look forward to some sleep. Have a great week!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •