Error 3134 on a query that worked before.

[ClearlyAnIdiot]

I apologise in advanced, as there will be some VBA in this, but I figured it would fit better in the query section.
I ran a very similar code with near-identical SQL, with only the fields and tables changed, and it had worked before. However, after changing the fields and tables, I repeatedly get error 3134 for "syntax". The code was

Private Sub UpdateI_Click()
Dim PerName As String, strsubb As String, Popup As Role, Strsub As String

PerName = InputBox("What is their name?")
strsubb = "INSERT INTO Company-personID (CompanyNo, Name) VALUES ('" & Me.CompanyNo & "', '" & PerName & "');"
DoCmd.RunSQL strsubb
Set Popup = New Role
Popup.Show
Select Case Popup.Tag
Case 1
Strsub = "UPDATE Company-personID SET [RolesPerformed] = 'Director' WHERE [Name] = '" & PerName & "'"
Case 2
Strsub = "UPDATE Company-personID SET [RolesPerformed] = 'AlternateDirector' WHERE [Name] = '" & PerName & "'"
Case 3
Strsub = "UPDATE Company-personID SET [RolesPerformed] = 'ReserveDirector' WHERE [Name] = '" & PerName & "'"
Case 4
Strsub = "UPDATE Company-personID SET [RolesPerformed] = 'Shareholder' WHERE [Name] = '" & PerName & "'"
End Select
    DoCmd.RunSQL Strsub
Set Popup = Nothing
End Sub

You can ignore the userform and inputbox if they're not important in this, but I figured they might just be.
When I run "Strsub" and "strsubb" by themselves, they both come up with errors, so it's not one or the other.
There are two tables in this. A table of companies, which has its form open, and a child table of companies and their people.
I wanted strsubb to put in a record where a person is added, their name based on the inputbox, and their associated company to be marked as the one that is in the field "companyno".
Strsub is meant to set the same person's roles to be whatever was clicked on the userform. If it's important, the roles are in a lookup wizard with multiple choices.

 

[pr2-eugin]

Which line are you facing this error?

Private Sub UpdateI_Click()
    Dim PerName As String, strsubb As String, Popup As Role, Strsub As String
    PerName = InputBox("What is their name?")
    strsubb = "INSERT INTO Company-personID (CompanyNo, Name) VALUES ('" & Me.CompanyNo & "', '" & PerName & "');"
    DoCmd.RunSQL strsubb
    Set Popup = New Role
    Popup.Show
    Select Case Popup.Tag
        Case 1
            Strsub = "UPDATE Company-personID SET [RolesPerformed] = 'Director' WHERE [Name] = '" & PerName & "'"
        Case 2
            Strsub = "UPDATE Company-personID SET [RolesPerformed] = 'AlternateDirector' WHERE [Name] = '" & PerName & "'"
        Case 3
            Strsub = "UPDATE Company-personID SET [RolesPerformed] = 'ReserveDirector' WHERE [Name] = '" & PerName & "'"
        Case 4
            Strsub = "UPDATE Company-personID SET [RolesPerformed] = 'Shareholder' WHERE [Name] = '" & PerName & "'"
    End Select
    
    DoCmd.RunSQL Strsub
    Set Popup = Nothing
End Sub

 

[ClearlyAnIdiot]

It keeps coming up on the "docmd.runsql" lines, and the error box keeps telling me it's a syntax error with INSERT INTO and UPDATe, depending on which one I'm testing.

 

[pr2-eugin]

What is the Type of Company No.. What is the Input you are giving in the InputBox?

 

[ClearlyAnIdiot]

Company number is a long integer, and the inputbox accepts a string. It's used to input the name of a person, which is meant to match the person's name in the "company-personid" table. I tried something very similar before, with a table called "individuals", and instead of setting the role as so-and-so, it had set the company number in a field that indicates roles to me.companyno. I had a problem with it, as it couldn't store multiple companies, so I'm now using this thrid table. Although I'm only changing up the table name and field names, it comes up with this problem.

 

[pr2-eugin]

If CompanyNo is Long then do not enclose them in Single Quotes..

"INSERT INTO Company-personID (CompanyNo, Name) VALUES ([COLOR=Red][B]" & Me.CompanyNo & "[/B][/COLOR], '" & PerName & "');"

Or, how about this code.??

Private Sub UpdateI_Click()
    Dim PerName As String, strsubb As String, Popup As Role, Strsub As String
    Set Popup = New Role
    Popup.Show
    Select Case Popup.Tag
        Case 1
            Strsub = "Director"
        Case 2
            Strsub = "AlternateDirector"
        Case 3
            Strsub = "ReserveDirector"
        Case 4
            Strsub = "Shareholder"
    End Select
    
    PerName = InputBox("What is their name?")
    strsubb = "INSERT INTO Company-personID (CompanyNo, [Name], RolesPerformed) VALUES (" & Me.CompanyNo & ", '" & PerName & "', '" & Strsub & "');"
    DoCmd.RunSQL strsubb
    Set Popup = Nothing
End Sub

 

[ClearlyAnIdiot]

Somehow, I'm still getting the same error with that code, for syntax problems in the "INSERT INTO" statement. I'm trying out the subforms now, by the way.

 

[pr2-eugin]

If you hover over the Variables what are the values in them? Could you take a Screen shot of the Error as you are missing some important information..

 

[ClearlyAnIdiot]

Errrrm, you know that other post you're helping me with, about dlookups? And how CJ recommended using subforms? It turns out that was the absolute solution to all of these problems. 5 hours of work in codebuilder, all outdone by a simple macro :banghead:

 

[pr2-eugin]

Sometimes the solution is right under our nose and we miss it.. Glad you have it sorted..