Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 17 votes, 5.00 average. Display Modes
Old 10-11-2013, 10:29 AM   #1
manusri
Newly Registered User
 
Join Date: Dec 2012
Posts: 29
Thanks: 11
Thanked 0 Times in 0 Posts
manusri is on a distinguished road
Run time error '3075': Syntax error (missing operator) in query expression

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
    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
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.
Attached Images
File Type: png SiteDetailscontacts.png (42.8 KB, 314 views)

manusri is offline   Reply With Quote
Old 10-11-2013, 10:34 AM   #2
pr2-eugin
Super Moderator
 
pr2-eugin's Avatar
 
Join Date: Nov 2011
Location: Bournemouth, UK
Posts: 8,498
Thanks: 68
Thanked 2,063 Times in 2,012 Posts
pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about
Re: Run time error '3075': Syntax error (missing operator) in query expression

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 = '" & Me.LstAuthors.Value & "'"
On the other hand..
  1. Why the choice of UNBOUND forms?
  2. You need a good naming convention.
__________________
Regards,
Paul Eugin

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
!
!
Windows 7 Professional, MS Access 2010
---------------------------------------------------------------------------------------------------------------------
If the above post has helped you, please click the scales
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
or click the 'Thumbs up'. Cheers.

---------------------------------------------------------------------------------------------------------------------



pr2-eugin is offline   Reply With Quote
The Following User Says Thank You to pr2-eugin For This Useful Post:
manusri (10-11-2013)
Old 10-11-2013, 10:45 AM   #3
manusri
Newly Registered User
 
Join Date: Dec 2012
Posts: 29
Thanks: 11
Thanked 0 Times in 0 Posts
manusri is on a distinguished road
Re: Run time error '3075': Syntax error (missing operator) in query expression

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 . 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.

manusri is offline   Reply With Quote
Old 10-11-2013, 10:48 AM   #4
pr2-eugin
Super Moderator
 
pr2-eugin's Avatar
 
Join Date: Nov 2011
Location: Bournemouth, UK
Posts: 8,498
Thanks: 68
Thanked 2,063 Times in 2,012 Posts
pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about
Re: Run time error '3075': Syntax error (missing operator) in query expression

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..
__________________
Regards,
Paul Eugin

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
!
!
Windows 7 Professional, MS Access 2010
---------------------------------------------------------------------------------------------------------------------
If the above post has helped you, please click the scales
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
or click the 'Thumbs up'. Cheers.

---------------------------------------------------------------------------------------------------------------------



pr2-eugin is offline   Reply With Quote
The Following User Says Thank You to pr2-eugin For This Useful Post:
manusri (10-11-2013)
Old 10-11-2013, 10:59 AM   #5
manusri
Newly Registered User
 
Join Date: Dec 2012
Posts: 29
Thanks: 11
Thanked 0 Times in 0 Posts
manusri is on a distinguished road
Re: Run time error '3075': Syntax error (missing operator) in query expression

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.
manusri is offline   Reply With Quote
Old 10-11-2013, 11:14 AM   #6
pr2-eugin
Super Moderator
 
pr2-eugin's Avatar
 
Join Date: Nov 2011
Location: Bournemouth, UK
Posts: 8,498
Thanks: 68
Thanked 2,063 Times in 2,012 Posts
pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about
Re: Run time error '3075': Syntax error (missing operator) in query expression

So what is the number of columns, column width, bound column?
__________________
Regards,
Paul Eugin

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
!
!
Windows 7 Professional, MS Access 2010
---------------------------------------------------------------------------------------------------------------------
If the above post has helped you, please click the scales
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
or click the 'Thumbs up'. Cheers.

---------------------------------------------------------------------------------------------------------------------



pr2-eugin is offline   Reply With Quote
The Following User Says Thank You to pr2-eugin For This Useful Post:
manusri (10-11-2013)
Old 10-11-2013, 12:17 PM   #7
manusri
Newly Registered User
 
Join Date: Dec 2012
Posts: 29
Thanks: 11
Thanked 0 Times in 0 Posts
manusri is on a distinguished road
Re: Run time error '3075': Syntax error (missing operator) in query expression

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.

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

MAnu

manusri is offline   Reply With Quote
Old 10-11-2013, 02:16 PM   #8
manusri
Newly Registered User
 
Join Date: Dec 2012
Posts: 29
Thanks: 11
Thanked 0 Times in 0 Posts
manusri is on a distinguished road
Re: Run time error '3075': Syntax error (missing operator) in query expression

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
manusri is offline   Reply With Quote
Old 10-11-2013, 02:17 PM   #9
manusri
Newly Registered User
 
Join Date: Dec 2012
Posts: 29
Thanks: 11
Thanked 0 Times in 0 Posts
manusri is on a distinguished road
Re: Run time error '3075': Syntax error (missing operator) in query expression

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
manusri is offline   Reply With Quote
Old 02-16-2017, 05:45 PM   #10
RonLavi
Newly Registered User
 
Join Date: Feb 2017
Location: California, USA
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
RonLavi is on a distinguished road
Re: Run time error '3075': Syntax error (missing operator) in query expression

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"

RonLavi is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Syntax error (missing operator) in query expression Jambuck General 3 12-06-2012 02:42 AM
Question strSQL = Run Time Error 3075 Syntax Error Missing Operator in qry Expr maxsun08 General 4 07-27-2012 06:39 AM
Syntax error (missing operator) in query expression alanij Forms 2 04-23-2009 01:19 AM
Syntax error: missing operator in query expression cmarucco Queries 9 12-05-2008 11:18 AM
Run time error 3075 - syntax error (missing operator) in query expression puskardas Modules & VBA 8 06-30-2008 07:35 PM




All times are GMT -8. The time now is 09:50 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World