Run time error '3075': Syntax error (missing operator) in query expression

M

manusri

Registered User.
Local time
Yesterday, 18:49
Joined
Dec 31, 2012
Messages
29
Hi all ,
I am struggling with a set of VBA coding that a previous person on the project was using. I am fixing their mess and i cannot for nuts figure out why the error comes up. So let me describe what i am trying here.

There is a parent table with basic information about each vendor. I have linked the parent table with a bunch of other tables including the one that stores contacts for each vendor. There is a main form that has the contacts in a subform. The subform has a list box that has to list the name and role of each person for that vendor.

This is my code
Code: 
' Adding new vendor
Private Sub AddCont_Click()
    Me.author.Value = ""
    Me.AuthorRole.Value = ""
    Me.RoleOth.Value = ""
    Me.Condition.Value = ""
    Me.othrcondition.Value = ""
    Me.CmbTest.Value = ""
    Me.email.Value = ""
    Me.phone.Value = ""
    Me.Fax.Value = ""
    Me.fix.Value = ""
    Me.chkCurrent.Value = "False"
    Me.txtAction.Value = "NewRecord"
End Sub
'Save command button on Subform
Private Sub Command5_Click()

Dim db As Database
Set db = CurrentDb

Dim rs As DAO.Recordset
Dim SQLStr As String
Dim SQLstr2 As String

    On Error GoTo HandleError

    If Me.txtAction.Value = "NewRecord" Then
    
        Set rs = CurrentDb.OpenRecordset("Contacts")
        
            With rs
            .AddNew
                ![siteid] = Me.Parent!NewSiteID.Value
                ![Name] = Me.author.Value
                ![Role] = Me.AuthorRole.Value
                ![ORole] = Me.RoleOth.Value
                ![Speciality] = Me.Condition.Value
                ![SpecialityOther] = Me.othrcondition.Value
                ![Test] = Me.CmbTest.Value
                ![email] = Me.email.Value
                ![phone] = Me.phone.Value
                ![Fax] = Me.Fax.Value
                ![Suffix] = Me.fix.Value
                ![IsCurrent] = Me.chkCurrent.Value
            .Update
            End With
        rs.Close
        Set rs = Nothing
    
    MsgBox ("Record is saved")
    Me.txtAction.Value = ""
    
    Call RefreshLstAuthors
    'refresh list box

    
    ElseIf Me.txtAction.Value = "UpdateRecord" Then
        SQLStr = "UPDATE Contacts SET Name = " & "'" & Me.author.Value & "'" & ", Role = " & "'" & Me.LstAuthors.Value & "'" & _
           ", ORole = " & "'" & RoleOth & "'" & ", Speciality = " & "'" & Me.Condition.Value & "'" & _
           ", SpecialityOther = " & "'" & Me.othrcondition.Value & "'" & ", Test = " & "'" & Me.CmbTest.Value & "'" & _
           ", Email = " & "'" & Me.email.Value & "'" & ", Phone = " & "'" & Me.phone.Value & "'" & ", Fax = " & "'" & Me.Fax.Value & "'" & _
           ", Suffix = " & "'" & Me.fix.Value & "'" & ", IsCurrent = " & Me.chkCurrent.Value & _
           " WHERE ContactID = " & Me.LstAuthors.Value & " AND SiteID = " & Me.Parent!NewSiteID.Value
       
       
       db.Execute SQLStr
       
    Call RefreshLstAuthors

    MsgBox ("Record is updated")
    Else
    End If
    db.Close
   
ExitHere:
    Exit Sub
    
HandleError:
    MsgBox Err.Description
    Resume ExitHere
End Sub

'On load - subform
Private Sub Form_Load()
    Me.author.Value = ""
    Me.AuthorRole.Value = ""
    Me.RoleOth.Value = ""
    Me.Condition.Value = ""
    Me.othrcondition.Value = ""
    Me.CmbTest.Value = ""
    Me.email.Value = ""
    Me.phone.Value = ""
    Me.Fax.Value = ""
    Me.fix.Value = ""
    Me.chkCurrent.Value = ""
    Me.txtAction.Value = ""
End Sub

'On Open - Subform
Private Sub Form_Open(Cancel As Integer)
    Me.author.Value = ""
    Me.AuthorRole.Value = ""
    Me.RoleOth.Value = ""
    Me.Condition.Value = ""
    Me.othrcondition.Value = ""
    Me.CmbTest.Value = ""
    Me.email.Value = ""
    Me.phone.Value = ""
    Me.Fax.Value = ""
    Me.fix.Value = ""
    Me.chkCurrent.Value = ""
    Me.txtAction.Value = ""
    
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim rcount As DAO.Recordset
    Dim rs As DAO.Recordset
    
    Dim SQLCount As String
    Dim SQLStr As String
    Dim Records As String
    
    Dim Count As Integer
    Dim i As Integer
    Dim rsDate As String
    Dim rsAuthor As String
    Dim rsNote As String
    Dim strReturn As String
    
     
    SQLCount = "SELECT Count(*) FROM Contacts WHERE Contacts.SiteID = " & Me.Parent!NewSiteID.Value
    Set rcount = db.OpenRecordset(SQLCount, dbOpenDynaset)
                
    rcount.MoveFirst
    
    Count = rcount.Fields(0).Value
    rcount.Close
    Set rcount = Nothing
    
    ' recordset
     
    SQLStr = "SELECT Contacts.ContactID, Contacts.Name, Contacts.Role, Contacts.ORole" & _
                    ", Contacts.Speciality, Contacts.SpecialityOther ,Contacts.Email, Contacts.Phone, Contacts.Fax, Contacts.Test " & _
                    ", Contacts.IsCurrent, Contacts.Suffix " & _
            " FROM Contacts WHERE Contacts.SiteID = " & Me.Parent!NewSiteID.Value
        
    Set rs = db.OpenRecordset(SQLStr, dbOpenDynaset)
     
    Me.LstAuthors.RowSourceType = "Value List"


    If Count = 0 Then
        Me.LstAuthors.RowSource = ""
    Else
        
        rs.MoveFirst
        Do Until rs.EOF
        
            For i = 0 To Count - 1
                With Me.LstAuthors
                                  
                    .RowSourceType = "Value List"
                    .ColumnCount = 4
                  
                    .AddItem Item:=rs![ContactID] & ";" & rs![Name] & ";" & rs![Role] & ";" & rs![ORole], Index:=i
                    
                End With
        rs.MoveNext
        Next i
        Loop
    End If

    
    rs.Close
    Set rs = Nothing
   db.Close

End Sub

'Refreshing List box
Private Sub RefreshLstAuthors()

    Me.LstAuthors.RowSource = ""
    Me.author.Value = ""
    Me.AuthorRole.Value = ""
    Me.RoleOth.Value = ""
    Me.Condition.Value = ""
    Me.othrcondition.Value = ""
    Me.CmbTest.Value = ""
    Me.email.Value = ""
    Me.phone.Value = ""
    Me.Fax.Value = ""
    Me.fix.Value = ""
    Me.chkCurrent.Value = ""
    Me.txtAction.Value = ""
        
    Dim db As DAO.Database
    Dim rcount As DAO.Recordset
    Dim rs As DAO.Recordset
  
    Dim SQLStr As String
    Dim SQLCount As String
    
    Dim Records As String
    Dim Count As Integer
    Dim i As Integer
    
    SQLCount = "SELECT Count(*) FROM Contacts WHERE Contacts.SiteID = " & Me.Parent!NewSiteID.Value
        
    Set db = CurrentDb
    Set rcount = db.OpenRecordset(SQLCount, dbOpenDynaset)
                
    rcount.MoveFirst
    
    Count = rcount.Fields(0).Value
    rcount.Close
    Set rcount = Nothing

     
    SQLStr = "SELECT Contacts.ContactID, Contacts.Name, Contacts.Role, Contacts.ORole" & _
                    ", Contacts.Speciality, Contacts.SpecialityOther ,Contacts.Email, Contacts.Phone, Contacts.Fax, Contacts.Test " & _
                    ", Contacts.IsCurrent, Contacts.Suffix " & _
                    "FROM Contacts WHERE SiteID = " & Me.Parent!NewSiteID.Value
    
    Set rs = db.OpenRecordset(SQLStr, dbOpenDynaset)
         
    Me.LstAuthors.RowSourceType = "Value List"


    If Count < 0 Then

    Else
        
        rs.MoveFirst
        Do Until rs.EOF
        
            For i = 0 To Count - 1
                With Me.LstAuthors
                                  
                    .RowSourceType = "Value List"
                    .ColumnCount = 4
                    
                   .AddItem Item:=rs![ContactID] & ";" & rs![Name] & ";" & rs![Role] & ";" & rs![ORole]
                                        
                End With
        rs.MoveNext
        Next i
        Loop
    End If

    
    rs.Close
    Set rs = Nothing
    db.Close
    
End Sub

' List box on click
Private Sub LstAuthors_Click()

Dim SQLStr As String
Dim db2 As Database
Dim rst As DAO.Recordset
Set db2 = CurrentDb
    
    If IsNull(Me.LstAuthors.Value) = False Then
    SQLStr = "SELECT Name, Role, ORole, Speciality, SpecialityOther, Email, Phone, Fax, Test, IsCurrent, Suffix FROM Contacts WHERE ContactID = " & Me.LstAuthors.Value
    Set rst = db2.OpenRecordset(SQLStr, dbOpenDynaset)
    

        rst.MoveFirst
        Me.author.Value = rst.Fields(0).Value
        Me.AuthorRole.Value = rst.Fields(1).Value
        Me.RoleOth.Value = rst.Fields(2).Value

    
    rst.Close
    db.Close
    Me.txtAction.Value = "UpdateRecord"
    Else
    End If
        
End Sub

Now my error "Run time error '3075': Syntax error (missing operator) in query expression 'ContactID = Baker Tom'. pops up and the line that is highlighted is in the List box on click Sub as shown below.(underlined in code)

Code: 
' List box on click
Private Sub LstAuthors_Click()

Dim SQLStr As String
Dim db2 As Database
Dim rst As DAO.Recordset
Set db2 = CurrentDb
    
    If IsNull(Me.LstAuthors.Value) = False Then
    SQLStr = "SELECT Name, Role, ORole, Speciality, SpecialityOther, Email, Phone, Fax, Test, IsCurrent, Suffix FROM Contacts WHERE ContactID = " & Me.LstAuthors.Value
   [U] Set rst = db2.OpenRecordset(SQLStr, dbOpenDynaset)[/U]
    

        rst.MoveFirst
        Me.author.Value = rst.Fields(0).Value
        Me.AuthorRole.Value = rst.Fields(1).Value
        Me.RoleOth.Value = rst.Fields(2).Value

    
    rst.Close
    db.Close
    Me.txtAction.Value = "UpdateRecord"
    Else
    End If
        
End Sub

Can anyone please help me. I am tired of staring at the code and not knowing what to do. You can see how the subform in the attachment.
 

Attachments

  • SiteDetailscontacts.png
    SiteDetailscontacts.png
    42.8 KB · Views: 635
The field name ContactID sounds like a Number type.. If this is the case then I think you know what the error is.. You cannot equate Number to a String..

If it is Text, then you need to surround the variable with single quotes..
Code: 
SQLStr = "SELECT Name, Role, ORole, Speciality, SpecialityOther, Email, Phone, Fax, " & _
         "Test, IsCurrent, Suffix FROM Contacts WHERE ContactID = [COLOR=Red][B]'[/B][/COLOR]" & Me.LstAuthors.Value & "[COLOR=Red][B]'[/B][/COLOR]"
On the other hand..

  1. Why the choice of UNBOUND forms?
  2. You need a good naming convention.
 
Hi Paul,

The choice of unbound forms is definitely ghastly but, i am not able to make huge changes to this format now as it has been approved by ppl above :mad:. Also as i mentioned earlier i am not the one who designed it. It took me a week to figure out what is what. All i was trying to do was to fix some of the issues that are showing up.

ContactID is indeed a number infact an "Autonumber". I get what you are saying but, what i am trying to do is make the list box populate with the name and role of each person. I am kind of stuck now and dont know how to do that.
 
So what is LstAuthors? TextBox or ComboBox? If it is a ComboBox, then what is the RowSource? You can modify that to give you a proper result..
 
LstAuthors is a List box that is unbound. I changed the rowsource to

SELECT Contacts.ContactID, Contacts.Name, Contacts.Role, Contacts.ORole
FROM Contacts
ORDER BY Contacts.Name;

But now i get a new error

Run Time error '3061': Too few parameters.Expected 1. and this goes back to the same OpenRecordset statement as earlier.
 
So what is the number of columns, column width, bound column?
 
This is what i did. I realized the rowsource has already been set. It is a 'Value List' iterated a million times in the code. So i changed the Row source type to 'Value List ' and watched the magic happen. the number of column were 4 with widths 0";1";2";2".Yes 2 columns are bound.

It works fine now. there was nothing wrong with the code i guess it was the Subform rowsource.:D

I have a feeling there are going to be other probelms. shall keep you posted :o

MAnu
 
New Issue....

So when i update a record on the subform, and hit the save command button (command 5), it updates my list box at the same time, all my fields(excluding the listbox) on the subform go blank. Finally the message box "the record was updated" shows up then, when i click anywhere on the Listbox (LstAuthors) I get a warning

The data has been changed.

Another user edited this record and saved the changes before you attempted to save your changes. Re-edit the record.

I have never come across such an warning. can you help me.

Here is my code so far
Code: 
Option Compare Database

Private Sub AddCont_Click()
    Me.author.Value = ""
    Me.AuthorRole.Value = ""
    Me.RoleOth.Value = ""
    Me.Condition.Value = ""
    Me.othrcondition.Value = ""
    Me.CmbTest.Value = ""
    Me.email.Value = ""
    Me.phone.Value = ""
    Me.Fax.Value = ""
    Me.fix.Value = ""
    Me.stats.Value = ""
    Me.txtAction.Value = "NewRecord"
End Sub

Private Sub Command5_Click()

Dim db As Database
Set db = CurrentDb

Dim rs As DAO.Recordset
Dim SQLStr As String
Dim SQLstr2 As String



    If Me.txtAction.Value = "NewRecord" Then
    
        Set rs = CurrentDb.OpenRecordset("Contacts")
        
            With rs
            .AddNew
                ![siteid] = Me.Parent!NewSiteID.Value
                ![Name] = Me.author.Value
                ![Role] = Me.AuthorRole.Value
                ![ORole] = Me.RoleOth.Value
                ![Speciality] = Me.Condition.Value
                ![SpecialityOther] = Me.othrcondition.Value
                ![Test] = Me.CmbTest.Value
                ![email] = Me.email.Value
                ![phone] = Me.phone.Value
                ![Fax] = Me.Fax.Value
                ![Suffix] = Me.fix.Value
                ![Status] = Me.stats.Value
            .Update
            End With
        rs.Close
        Set rs = Nothing
    
    MsgBox ("Record is saved")
    Me.txtAction.Value = ""

  
    'refresh list box
   Call RefreshLstAuthors
    
    ElseIf Me.txtAction.Value = "UpdateRecord" Then
        SQLStr = "UPDATE Contacts SET Name = " & "'" & Me.author.Value & "'" & ", Role = " & "'" & Me.AuthorRole.Value & "'" & _
           ", ORole = " & "'" & RoleOth & "'" & ", Speciality = " & "'" & Me.Condition.Value & "'" & _
           ", SpecialityOther = " & "'" & Me.othrcondition.Value & "'" & ", Test = " & "'" & Me.CmbTest.Value & "'" & _
           ", Email = " & "'" & Me.email.Value & "'" & ", Phone = " & "'" & Me.phone.Value & "'" & ", Fax = " & "'" & Me.Fax.Value & "'" & _
           ", Suffix = " & "'" & Me.fix.Value & "'" & ", Status = " & "'" & Me.stats.Value & "'" & _
           " WHERE ContactID = " & Me.LstAuthors.Value & " AND SiteID = " & Me.Parent!NewSiteID.Value
       
       
       db.Execute SQLStr
       
         'refresh list box
    Call RefreshLstAuthors
    

    MsgBox ("Record is updated")
    Else
    End If
    db.Close

End Sub

Private Sub Form_Load()
    Me.author.Value = ""
    Me.AuthorRole.Value = ""
    Me.RoleOth.Value = ""
    Me.Condition.Value = ""
    Me.othrcondition.Value = ""
    Me.CmbTest.Value = ""
    Me.email.Value = ""
    Me.phone.Value = ""
    Me.Fax.Value = ""
    Me.fix.Value = ""
    Me.stats.Value = ""
    Me.txtAction.Value = ""
End Sub

Private Sub Form_Open(Cancel As Integer)
    Me.author.Value = ""
    Me.AuthorRole.Value = ""
    Me.RoleOth.Value = ""
    Me.Condition.Value = ""
    Me.othrcondition.Value = ""
    Me.CmbTest.Value = ""
    Me.email.Value = ""
    Me.phone.Value = ""
    Me.Fax.Value = ""
    Me.fix.Value = ""
    Me.stats.Value = ""
    Me.txtAction.Value = ""
    
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim rcount As DAO.Recordset
    Dim rs As DAO.Recordset
    
    Dim SQLCount As String
    Dim SQLStr As String
    Dim Records As String
    
    Dim Count As Integer
    Dim i As Integer
    Dim rsDate As String
    Dim rsAuthor As String
    Dim rsNote As String
    Dim strReturn As String
    
     
    SQLCount = "SELECT Count(*) FROM Contacts WHERE Contacts.SiteID = " & Me.Parent!NewSiteID.Value
    Set rcount = db.OpenRecordset(SQLCount, dbOpenDynaset)
                
    rcount.MoveFirst
    
    Count = rcount.Fields(0).Value
    rcount.Close
    Set rcount = Nothing
    
    ' recordset
     
    SQLStr = "SELECT Contacts.ContactID, Contacts.Name, Contacts.Role, Contacts.ORole" & _
                    ", Contacts.Speciality, Contacts.SpecialityOther ,Contacts.Email, Contacts.Phone, Contacts.Fax, Contacts.Test " & _
                    ", Contacts.Status, Contacts.Suffix " & _
            " FROM Contacts WHERE Contacts.SiteID = " & Me.Parent!NewSiteID.Value
        
    Set rs = db.OpenRecordset(SQLStr, dbOpenDynaset)
     
    Me.LstAuthors.RowSourceType = "Value List"


    If Count < 1 Then
        Me.LstAuthors.RowSource = ""
    Else
        
        rs.MoveFirst
        Do Until rs.EOF
        
            For i = 0 To Count - 1
                With Me.LstAuthors
                                  
                    .RowSourceType = "Value List"
                    .ColumnCount = 4

                    .AddItem Item:=rs![ContactID] & ";" & rs![Name] & ";" & rs![Role] & ";" & rs![ORole], Index:=i
                    
                End With
        rs.MoveNext
        Next i
        Loop
    End If

    
    rs.Close
    Set rs = Nothing
   db.Close

End Sub


Private Sub RefreshLstAuthors()

    Me.LstAuthors.RowSource = ""
    Me.author.Value = ""
    Me.AuthorRole.Value = ""
    Me.RoleOth.Value = ""
    Me.Condition.Value = ""
    Me.othrcondition.Value = ""
    Me.CmbTest.Value = ""
    Me.email.Value = ""
    Me.phone.Value = ""
    Me.Fax.Value = ""
    Me.fix.Value = ""
    Me.stats.Value = ""
    Me.txtAction.Value = ""
        
    Dim db As DAO.Database
    Dim rcount As DAO.Recordset
    Dim rs As DAO.Recordset
  
    Dim SQLStr As String
    Dim SQLCount As String
    
    Dim Records As String
    Dim Count As Integer
    Dim i As Integer
    
    SQLCount = "SELECT Count(*) FROM Contacts WHERE Contacts.SiteID = " & Me.Parent!NewSiteID.Value
        
    Set db = CurrentDb
    Set rcount = db.OpenRecordset(SQLCount, dbOpenDynaset)
                
    rcount.MoveFirst
    
    Count = rcount.Fields(0).Value
    rcount.Close
    Set rcount = Nothing

     
    SQLStr = "SELECT Contacts.ContactID, Contacts.Name, Contacts.Role, Contacts.ORole" & _
                    ", Contacts.Speciality, Contacts.SpecialityOther ,Contacts.Email, Contacts.Phone, Contacts.Fax, Contacts.Test " & _
                    ", Contacts.Status, Contacts.Suffix " & _
                    "FROM Contacts WHERE SiteID = " & Me.Parent!NewSiteID.Value
    
    Set rs = db.OpenRecordset(SQLStr, dbOpenDynaset)
         
    Me.LstAuthors.RowSourceType = "Value List"


    If Count < 1 Then
        Me.LstAuthors.RowSource = ""
    Else
        
        rs.MoveFirst
        Do Until rs.EOF
        
            For i = 0 To Count - 1
                With Me.LstAuthors
                                  
                    .RowSourceType = "Value List"
                    .ColumnCount = 4

                    .AddItem Item:=rs![ContactID] & ";" & rs![Name] & ";" & rs![Role] & ";" & rs![ORole], Index:=i
                                        
                End With
        rs.MoveNext
        Next i
        Loop
    End If

    
    rs.Close
    Set rs = Nothing
    db.Close
    
End Sub
Private Sub LstAuthors_Click()

Dim SQLStr As String
Dim db2 As Database
Dim rst As DAO.Recordset
Set db2 = CurrentDb
    
    If IsNull(Me.LstAuthors.Value) = False Then
    SQLStr = "SELECT Name, Role, ORole ,Speciality, SpecialityOther, Test, Email, Phone, Fax, Suffix, Status FROM Contacts WHERE ContactID = " & Me.LstAuthors.Value

    Set rst = db2.OpenRecordset(SQLStr, dbOpenDynaset)
    

        rst.MoveFirst
        Me.author.Value = rst.Fields(0).Value
        Me.AuthorRole.Value = rst.Fields(1).Value
        Me.RoleOth.Value = rst.Fields(2).Value
        Me.Condition.Value = rst.Fields(3).Value
        Me.othrcondition.Value = rst.Fields(4).Value
        Me.CmbTest.Value = rst.Fields(5).Value
        Me.email.Value = rst.Fields(6).Value
        Me.phone.Value = rst.Fields(7).Value
        Me.Fax.Value = rst.Fields(8).Value
        Me.fix.Value = rst.Fields(9).Value
        Me.stats.Value = rst.Fields(10).Value

    rst.Close
    db2.Close
    Me.txtAction.Value = "UpdateRecord"

    End If
        
End Sub
 
The weird part of the issue above is, the update is saved on the table and the fields below the subform but, not on the list box:(
 
If you are executing your code from a FORM (event/ sub form), go to properties and click on the form body (not any field).
Click on Data in properties and make sure the Record Source is NOT a Query.
The Query locks the table through its scripts and you might get "This Recordset is not updatable"
 
You must log in or register to reply here.

Similar threads

S
Syntax error (missing operator) in query expression
Replies
7
Views
2,696
SachAccess
S
M
Solved Syntax error in query expression
Replies
3
Views
807
cheekybuddha
cheekybuddha
J
Syntax Error (Missing Opeator) in Query Expression
Replies
7
Views
1,407
gemma-the-husky
gemma-the-husky
M
Run-time error '3075': Syntax error (missing operator) in Access 2013 but not in 2010
Replies
9
Views
1,966
theDBguy
theDBguy
K
Solved Run-Time Error 3131 Syntax error in FROM clause
Replies
6
Views
4,087
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom