insert syntax help required

[vandy]

Hi all,

I have a login form were a sysadmin can enter new values into the table.

I need the values entered into the table through the form.

i have 3 txtfeilds on my form
txtuserid - accepts username - text
txtpwd - accepts userpwd - text
txtlevel - accepts level - numeric

I have an insert query which inserts these values into the authorized table

strQuery = "INSERT INTO Authorized (UserId, Pwd, Level) " & " VALUES ('"
& Me.txtuserid & "', '" & Me.txtpwd & "', '" & Me.txtlevel & ");"

I am getting a syntax error on my insert statement
what is wrong here

 

[Carl Rapson]

Since Level is numeric, you don't need single quotes around the value:

strQuery = "INSERT INTO Authorized (UserId, Pwd, Level) " & " VALUES ('"
& Me.txtuserid & "', '" & Me.txtpwd & "', " & Me.txtlevel & ");"

Carl Rapson

 

[Bob Barrows [MVP]]

Hi all,

I have a login form were a sysadmin can enter new values into the
table.

I need the values entered into the table through the form.

i have 3 txtfeilds on my form
txtuserid - accepts username - text
txtpwd - accepts userpwd - text
txtlevel - accepts level - numeric

I have an insert query which inserts these values into the authorized
table

strQuery = "INSERT INTO Authorized (UserId, Pwd, Level) " & "
VALUES ('" & Me.txtuserid & "', '" & Me.txtpwd & "', '" & Me.txtlevel
& ");"

I am getting a syntax error on my insert statement
what is wrong here

I'd like to show you how to parameterize this statement so you don't
have to worry about delimiters, but I'm not sure if you are using DAO or
ADO to execute it.

 

[vandy]

Hi Bob I am using a DAO and quite frankly i do not know why!

This is my code and i do appreciate you helping me on this.


Private Sub btnNew_Click()

Dim dbs As DAO.Database
Dim wrk As DAO.Workspace
Dim strQuery As String, strMessage As String

On Error GoTo Err_Handler

Set wrk = DAO.DBEngine.Workspaces(0)
Set dbs = CurrentDb


If IsNull(Me.txtuserid) Then
MsgBox ("You must enter username")
Me.txtuserid.SetFocus
Exit Sub

Else

If IsNull(Me.txtpwd) Then
MsgBox ("You must enter a Password")
Me.txtpwd.SetFocus
Exit Sub
Else

If IsNull(Me.txtlevel) Then
MsgBox ("You must enter 1 - Full Access OR 2 - Read only access")
Me.txtlevel.SetFocus
Exit Sub
Else

End If
End If
End If


If Not IsNull(Me.txtuserid) And Not IsNull(Me.txtpwd) And Not
IsNull(Me.txtlevel) Then
wrk.BeginTrans
strQuery = "INSERT INTO Authorized (UserId, Pwd, Level) " & " VALUES ('"
& Me.txtuserid & "', '" & Me.txtpwd & "', '" & Me.txtlevel & ");"

wrk.CommitTrans

Else
Exit_Here:
End If
Exit Sub

Err_Handler:
strMessage = Error & " (" & Err.Number & ")" & vbNewLine & vbNewLine & _
"(Error in " & strQuery & ")" & _
vbNewLine & vbNewLine & "Transaction rolled back and no tables
updated."

MsgBox strMessage, vbExclamation, "Error"

wrk.Rollback
Resume Exit_Here

MsgBox ("New user added")
DoCmd.RunCommand acCmdDataEntry
End Sub

 

[vandy]

Hi Bob,

I forgot to add that i am getting a
Syntax error in INSERT INTO statement ( 3134 )

My user defined error is being displyed.

Transacation rolled back and no records inserted.

vandy

 

[Bob Barrows [MVP]]

Hi Bob I am using a DAO and quite frankly i do not know why!

This is my code and i do appreciate you helping me on this.

wrk.BeginTrans
strQuery = "INSERT INTO Authorized (UserId, Pwd, Level) " & "
VALUES ('" & Me.txtuserid & "', '" & Me.txtpwd & "', '" & Me.txtlevel
& ");"

But you never execute the statement!??

Here is how I would do it:
strQuery = "INSERT INTO Authorized (UserId, Pwd, Level) " & _
"VALUES([p1],[p2],[p3])"

dim qdf as querydef
set qdf=dbs.CreateQuerydef("",strQuery)
qdf(0) = Me.txtuserid
qdf(1) = Me.txtpwd
qdf(2) = Me.txtlevel
qdf.execute dbFailOnError