Insert Into query using VBA

omozali

Can someone please tell me why INSERT INTO generates syntax error

Private Sub Command7_Click()
Dim strUSER As String
strUSER = Nz(DLookup("[user]", "table2", "[user] ='" & username.Value & "'"), "nouser")
If strUSER = "nouser" Then
INSERT INTO table2;
VALUES (username.Value,password.Value);
MsgBox "User added"
Else
MsgBox "user already exist"
End If

Dec 3 '06 #1

nico5038

Try to execute the query like:

If strUSER = "nouser" Then
currentdb.execute ("INSERT INTO table2 (username, password) VALUES ('" & me.Username & "','" & me.txtPassword & "') ;
MsgBox "User added"
else

VBA isn't capable of handling SQL syntax. The query will need to be constructed and then executed e.g. with docmd.runSQL or the above currentdb.execute.
The INSERT statement needs some study I see, firt try them with the grapical query editor and then move the SQLtext for stringing to your code.

Nic;o)

Dec 3 '06 #2

omozali

Unfortunately the code generated syntax error :(

Dec 3 '06 #3

omozali

I tried this

CurrentDb.Execute ("INSERT INTO table2 ([user], [pass]) VALUES (username.value,password.value)")

generates: too few parameters, expect 2

I also tried
CurrentDb.Execute ("INSERT INTO table2 ([user], [pass]) VALUES ('username.value','password.value')")

Things get added to the table but instead the value of username I actualy get the text username.value in the table!!!

3rd attempt

CurrentDb.Execute ("INSERT INTO table2 ([user], [pass]) VALUES ("'" & username.value & "'","'" & password.value & "'")")

would generate error at the first " ' "

Dec 3 '06 #4

NeoPa

Try this slightly fixed version :
It's exactly the same except Nico left off the last double-quote (easily done).

Dec 3 '06 #5

omozali

an update for those interested , this code works

Private Sub Command7_Click()
Dim strUSER As String
Dim UN As String
Dim PW As String
UN = username.Value
PW = password.Value
strUSER = Nz(DLookup("[user]", "table2", "[user] ='" & username.Value & "'"), "nouser")
If strUSER = "nouser" Then
Rem CurrentDb.Execute ("INSERT INTO table2 ([user], [pass])" & _
"VALUES (UN,PW)")
DoCmd.RunSQL ("INSERT INTO table2 ([user], [pass]) VALUES (username.value,password.value)")

No idea why the previous one didn't


ACCESS VB is very tricky language to learn and it make no sense to me why microsoft made so many variations for commands that would only confuse us

Dec 3 '06 #6

NeoPa

Let us know if Nico's (fixed) version works first.
If not then we can revisit it, but I suspect all will be fine.

Dec 3 '06 #7

omozali

one small thing when the code run a small window would appear saying
you are about to a append 1 row and asks for confirmation

any way to remove this annoying window?

Dec 3 '06 #8

NeoPa

Can I take it then that Nico's code worked fine for you?

Dec 3 '06 #9

omozali

unfortunately, it didn't

Dec 3 '06 #10

NeoPa

In that case - are you happy that you have a working answer?
Or are you looking for more help sorting that out?

Dec 3 '06 #11

nico5038

Please start with creating the INSERT query in the query editor till it's working.
Than start with transforming that SQL into the string needed.

Nic;o)

Dec 4 '06 #12

omozali

Sorry for the late responce. my situation is ok now thanks

Dec 16 '06 #13

NeoPa

Do you still have the warning come up (We can help to remove the warning)?
Or are you happy with everything?

Dec 17 '06 #14

PEB

Can't use SQL just like this in VBA! Use docmd.runsql YourSQL

Dec 17 '06 #15

PEB

Ah it is finished here! OK!

Dec 17 '06 #16