INSERT SQL giving me problems... 1

[AJLoehr]

what's wrong with this syntax?

INSERT INTO USERtbl (EIDP,RANK,FIRSTNAME,LASTNAME,OFFICE,PHONE,EMAIL,LEVEL,RIGHTS) Values ('john.doe','Mr.','John','Doe',19,'318-555-5555','john.doe@gmail.com','Company','Admin')

All fields are 'Text' except for Office which is a 'Number'.

I thought maybe one of the fieldnames I'm using might be reserved, but I searched it in Google and they weren't on any reserved words lists.

Appreciate any help on this!


AJ

 

[dhookom]

Do you get an error message? How are you calling the query?

Duane
Hook'D on Access
MS Access MVP

 

[AJLoehr]

yeah the error is Run-time error 3134: Syntax error in INSERT INTO statement.

I'm simply trying to submit field data from an unbound form to a table. I do a verification msgbox and when the user clicks Yes, I use this INSERT INTO statement, but it's not working.

 

[dhookom]

Can you share your code? Are there any relationships?

Duane
Hook'D on Access
MS Access MVP

 

[AJLoehr]

I thought it best to just show the results of the code because it looked too messy when I put it in a post. But then I forgot you could copy paste somewhere it would read better.

    If ADDchk = vbYes Then
        'add user
        ADDsql = "INSERT INTO USERtbl (EIDP,RANK,FIRSTNAME,LASTNAME,OFFICE,PHONE,EMAIL,LEVEL,RIGHTS) Values ('"
        ADDsql = ADDsql & Forms!MAINfrm!MAINsub!EIDP.Value & "','"
        ADDsql = ADDsql & Forms!MAINfrm!MAINsub!RANK.Value & "','"
        ADDsql = ADDsql & Replace(Forms!MAINfrm!MAINsub!FIRSTNAME.Value, "'", "''") & "','"
        ADDsql = ADDsql & Replace(Forms!MAINfrm!MAINsub!LASTNAME.Value, "'", "''") & "',"
        ADDsql = ADDsql & Forms!MAINfrm!MAINsub!OFFICE.Value & ",'"
        ADDsql = ADDsql & Forms!MAINfrm!MAINsub!PHONE.Value & "','"
        ADDsql = ADDsql & Replace(Forms!MAINfrm!MAINsub!EMAIL.Value, "'", "''") & "','"
        ADDsql = ADDsql & Forms!MAINfrm!MAINsub!LEVEL.Value & "','"
        ADDsql = ADDsql & Forms!MAINfrm!MAINsub!RIGHTS.Value & "')"
        'Debug.Print ADDsql
        DoCmd.SetWarnings False
        DoCmd.RunSQL (ADDsql)
        DoCmd.Close acForm, "MAINfrm"
        DoCmd.OpenForm "MAINfrm"
    Else
        'do not add user
        Exit Sub
    End If

 

[dhookom]

When in doubt, surround each field name with []s. According to Allen Browne's Problem names and reserved words in Access LEVEL is a reserved word.

Duane
Hook'D on Access
MS Access MVP

 

[AJLoehr]

I figured as much, but I looked up a reserved words list and didn't find it. Thanks for pointing out the brackets, I tested by putting [LEVEL] in brackets, but I think I'm going to change the fieldname just to be safe. Thanks again!