Results 1 to 10 of 10
  1. #1
    cpullen91 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    9

    Syntax error (missing operator) in query expression 'ModelID ='

    Hello,

    I am currently building my first data base and getting the above error.

    this is my code:

    Code:
    Option Compare Database
    Option Explicit
    Private Sub cboModelID_AfterUpdate()
    
    
      ' Set the Size combo box to be limited by the selected Model
      Me.cboSizeID.RowSource = "SELECT tblSize.SizeID, tblsize.sizeName FROM tblsize " & _
         " WHERE ModelID = " & Nz(Me.cboSizeID) & _
         " ORDER BY SizeName"
      Me.cboSizeID = Null
      
       EnableControls
        
    End Sub
    Private Sub cboSizeID_AfterUpdate()
    
    
      ' Set the Suffix combo box to be limited by the selected Size
      Me.cboSuffixID.RowSource = "SELECT tblSuffix.SuffixID, tblSuffix.SuffixName FROM tblSuffix " & _
         " WHERE SizeID = " & Nz(Me.cboSuffixID) & _
         " ORDER BY SuffixName"
      Me.cboSuffixID = Null
      
       EnableControls
        
    End Sub
    
    
    Private Sub EnableControls()
    
    
      ' Clear the combo boxes
      If IsNull(Me.cboModelID) Then
         Me.cboSizeID = Null
      End If
    
    
      If IsNull(Me.cboSizeID) Then
        Me.cboSuffixID = Null
      End If
        
    
    
     ' Enable or disable combo boxes based on whether the combo box preceeding it has a value.
      Me.cboSizeID.Enabled = (Not IsNull(Me.cboModelID))
      Me.cboSuffixID.Enabled = (Not IsNull(Me.cboSizeID))
    
    
    End Sub
    
    
    
    
    Private Sub Form_Load()
      ' When the form loads, enable/disable the combo boxes. Combo boxes are only enabled if the preceeding combo box has a value.
       EnableControls
      End Sub
    What does this error mean and what do i need to do to fix it?



    Many thanks in advance

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    What data type is the ModelID field? Numeric or Text?

    Here is a little trick. Create your SQL code in a string like this:
    Code:
      Dim mySQL as String
      mySQL = "SELECT tblSize.SizeID, tblsize.sizeName FROM tblsize " & _
         " WHERE ModelID = " & Nz(Me.cboSizeID) & _
         " ORDER BY SizeName"
    Then before applying to your record source, you can use a MsgBox to see what it returns, i.e.
    Code:
      MsgBox mySQL
      Me.cboSizeID.RowSource = mySQL
    Then you can visually inspect it to see if it looks correct.

    If you cannot see anything obvious, try creating a query manually using the exact same SQL code (open up the Query Builder in SQL View, and type the SQL code in), and see if it works.

    Normally, what I do is go the other way. First create a working example of my query in Query Builder, view the code in SQL View, and recreate that exact same code in VBA. And I use the MsgBox trick to verify it is returning what I think it should be returning.

  3. #3
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You're setting model ID to the size combo instead of the model combo.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    cpullen91 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    9
    This just populated the second combo box with all options, not the specific ones that are set for what was selected in the first.

  5. #5
    cpullen91 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    9
    Quote Originally Posted by JoeM View Post
    What data type is the ModelID field? Numeric or Text?

    Here is a little trick. Create your SQL code in a string like this:
    Code:
      Dim mySQL as String
      mySQL = "SELECT tblSize.SizeID, tblsize.sizeName FROM tblsize " & _
         " WHERE ModelID = " & Nz(Me.cboSizeID) & _
         " ORDER BY SizeName"
    Then before applying to your record source, you can use a MsgBox to see what it returns, i.e.
    Code:
      MsgBox mySQL
      Me.cboSizeID.RowSource = mySQL
    Then you can visually inspect it to see if it looks correct.

    If you cannot see anything obvious, try creating a query manually using the exact same SQL code (open up the Query Builder in SQL View, and type the SQL code in), and see if it works.

    Normally, what I do is go the other way. First create a working example of my query in Query Builder, view the code in SQL View, and recreate that exact same code in VBA. And I use the MsgBox trick to verify it is returning what I think it should be returning.
    This just populated the second box with all of its available content. it didnt filter down through the first box. and ModelID is a text field

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You miss my point. You're comparing apples and oranges:

    " WHERE ModelID = " & Nz(Me.cboSizeID) & _

    Plus you need delimiters if it's a text field.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    cpullen91 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    9
    Quote Originally Posted by pbaldy View Post
    You miss my point. You're comparing apples and oranges:

    " WHERE ModelID = " & Nz(Me.cboSizeID) & _

    Plus you need delimiters if it's a text field.
    Does that not Tell cboSizeID where to look when the first combo box gets populated?

  8. #8
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    If you haven't already done do, you need to fix the error pointed out by pbaldy :

    Code:
      ' Set the Size combo box to be limited by the selected Model
      Me.cboSizeID.RowSource = "SELECT tblSize.SizeID, tblsize.sizeName FROM tblsize " & _
         " WHERE ModelID = '" & Nz(Me.cboModelID) & "'" & _
         " ORDER BY SizeName"
      Me.cboSizeID = Null
    Note as well the addition of the quote marks, since ModelID is a text field.

    The way you had it before, cboSizeID was blank or Null, leading to the syntax error in the where clause.

    (Sorry Paul - your post wasn't there when I typed mine!)

  9. #9
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'll get out of the way.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    cpullen91 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    9
    Quote Originally Posted by John_G View Post
    If you haven't already done do, you need to fix the error pointed out by pbaldy :

    Code:
      ' Set the Size combo box to be limited by the selected Model
      Me.cboSizeID.RowSource = "SELECT tblSize.SizeID, tblsize.sizeName FROM tblsize " & _
         " WHERE ModelID = '" & Nz(Me.cboModelID) & "'" & _
         " ORDER BY SizeName"
      Me.cboSizeID = Null
    Note as well the addition of the quote marks, since ModelID is a text field.

    The way you had it before, cboSizeID was blank or Null, leading to the syntax error in the where clause.

    (Sorry Paul - your post wasn't there when I typed mine!)
    I now completley understand and it now works. Thank you all so much!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 03-16-2016, 12:24 PM
  2. Replies: 6
    Last Post: 03-27-2014, 09:43 AM
  3. Replies: 2
    Last Post: 02-17-2014, 10:27 AM
  4. Replies: 9
    Last Post: 01-22-2013, 04:23 PM
  5. Replies: 1
    Last Post: 10-07-2009, 07:36 AM

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
  •  
Other Forums: Microsoft Office Forums