Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2012
    Posts
    5

    Unanswered: Hoping for someone more knowledgeable than I

    Good Afternoon,

    Let me start off by saying I'm a novice at using MS Access for anything more than combining 2 unrelated data sources.

    I know Oracle SQL and Java but VBA syntax is killing me.

    That being said, I'm building a DB for work and one of the forms I'm building cascading filters in the header that populate the record to be modified in the form below.

    I got everything to work using 1 Combo Box, but the when adding the cascade filters I keep getting a Syntax Error

    "(missing operator) in query expression 'Model Master!Device Model Number'"

    Below is the script, Combo 51 lets me choose an OEM, Combo should let me choose from the models on Model Master with OEM_CD = Combo51.value

    Listbox 47 lets me pick an issue will will drive the form population. (Also I need to make sure the form populates based on the choices from Combo49 and Listbox47)

    Can someone tell me what the operator error is?

    Private Sub Combo51_AfterUpdate()
    On Error Resume Next
    Combo49.RowSource = "Select Model Master!Device Model Number " & _
    "FROM Model Master " & _
    "WHERE Model Master!OEM_CD = '" & Combo51.Value & "' " & _
    "ORDER BY Model Master!Device Model Number;"
    End Sub

    Private Sub Combo49_AfterUpdate()

    On Error Resume Next
    List47.RowSource = "Select [Guidance Tracker]![Issue] " & _
    "FROM [Guidance Tracker] " & _
    "WHERE [Guidance Tracker]![Device Model Number] = '" & Combo49.Value & "' " & _
    "ORDER BY [Guidance Tracker]![Issue];"
    End Sub

    Private Sub List47_AfterUpdate()

    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ID] = " & Str(Nz(Me![List47], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

    End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you have been foolish enough to use spaces in your column / table names then they need to be enclosed in square brackets
    eg:-
    Code:
    [My Table Name].[My Column Name]
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2012
    Posts
    5
    I've changed the Table/Column names to remove the spaces and added the [] brackets, now I don't get any error however, my combo box doesn't return any hits.

    I modified the query to:

    Private Sub Combo51_AfterUpdate()
    On Error Resume Next
    Combo49.RowSource = "Select Distinct [Model_Master].[Device_Model] " & _
    "FROM [Model_Master] RIGHT JOIN [Guidance_Tracker] ON [Guidance_Tracker].[Device_Model] = [Model_Master].[Device_Model]" & _
    "WHERE [Model_Master].[OEM_CD] = '" & Combo51.Value & "' " & _
    "ORDER BY [Model_Master]![Device_Model];"
    End Sub

    So now when I check the rowsource for Combo49 it shows me the query with the where statement being updated based on the combo box selection; and in the datasheet view I see the expected results. Those results are just not populating within the Drop down list itself.

    Any Ideas?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is the query valid
    is it sane
    is it actually doing whart you think its doing
    either put a watch/breakpoint on the after update for combo51
    or display the SQL in a message box

    persoanlly I prefer to assign SQL to a variable and then do whatever with that variable afterwards. yes its not neccesarily 'efficient' code but unless you are targetting very modest memory computers then it wont be a problems

    Code:
    strSQL =  "Select Distinct [Model_Master].[Device_Model] " & _
    "FROM [Model_Master] RIGHT JOIN [Guidance_Tracker] ON [Guidance_Tracker].[Device_Model] = [Model_Master].[Device_Model]" & _
    "WHERE [Model_Master].[OEM_CD] = '" & Combo51.Value & "' " & _
    "ORDER BY [Model_Master]![Device_Model];"
    msgbox "My SQL is:" & strSQL
    Combo49.RowSource =strSQL
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2012
    Posts
    5
    Forgive my ignorance that snippet of coding you provided, should that go into the Row source for the tha combo Box that is not displaying or in the VBA editor for the "after update" for the previous combo box?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its vba code, so it should go in the afterupdate event in combo51. after all its a modification of your existing code for combo51 afterupdate

    another thing to check is to make certain the code is actually firing. one of the nasty tricks that can happen in Access is you type in some perfectly legitiamte code in an event in the VBA code window however the code is not picked up at runtime. so make certain that the form 'knows' about the code. check the properties for the relevant code and make certain thart the after update event says use code or similar.

    ideally set a watch/breakpoint on the function call. do a google on debugging in access, it has a truly fantastic IDE and debugger but in the mean time open the code window
    click the vertical bar to the left of the code you want to debug (a red circle should appear). then whent he code executes the debugger/ide will halt the code at that point. used f8 to step through the code line by line. examine any variables by puting the mouse cursor over the relevant bit
    you can also examine or set values for variables/controls and so on in the immedaite window


    to examine something in the immediate window
    ?MyVariable
    ?combo51.value
    to set
    MyVariable = 72

    jsut looking at your code now Im not sure if combo51.value is valid... thats why I strongly recommend you make certain this SQL statement is doing what you think its doing
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Dec 2012
    Posts
    5
    FYI...

    I had trouble with the code you provided ( I honestly don't understand it)

    But... when I added the Prmary key from the tabe [Guidance_Tracker].[ID] the combo box populated.... just displaying dups.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by taylowa View Post
    FYI...

    I had trouble with the code you provided ( I honestly don't understand it)

    But... when I added the Prmary key from the tabe [Guidance_Tracker].[ID] the combo box populated.... just displaying dups.
    The only difference was that the sql was assigned to a variable before being assigned to a the controls row source. Functionally it is identical to your code. However it shows your sql inasmuch message box so you can easily examine ignite to majesty certain the sql you are sending to the sql engine is doing what you think it is.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Dec 2012
    Posts
    5
    Thanks for your help anyway.

    I've managed to get the form to do exactly what I wanted, I had to redo the list boxes (Think something was off in their properties) but here is the final code I got working as an FYI...

    Private Sub Combo51_AfterUpdate()
    On Error Resume Next
    Combo49.RowSource = "Select distinct [Model_Master].[Device_Model] " & _
    "FROM [Model_Master] RIGHT JOIN [Guidance_Tracker] ON [Guidance_Tracker].[Device_Model] = [Model_Master].[Device_Model]" & _
    "WHERE [Model_Master].[OEM_CD] = '" & Combo51.Value & "' " & _
    "ORDER BY [Model_Master]![Device_Model];"
    End Sub

    Private Sub Combo49_AfterUpdate()
    On Error Resume Next
    List47.RowSource = "Select [Guidance_Tracker].[ID], [Guidance_Tracker].[Issue] " & _
    "FROM [Guidance_Tracker] " & _
    "WHERE [Guidance_Tracker].[Device_Model] = '" & Combo49.Value & "' " & _
    "ORDER BY [Guidance_Tracker].[ID], [Guidance_Tracker].[Issue];"
    End Sub

    Private Sub List47_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Guidance_Tracker].[ID] = " & Str(Nz(Me![List47], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End Sub

Posting Permissions

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