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

[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)

[omozali]

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)

Unfortunately the code generated syntax error :(

[omozali]

Unfortunately the code generated syntax error :(

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 " ' "

[NeoPa]

Unfortunately the code generated syntax error :(

Try this slightly fixed version :

currentdb.execute ("INSERT INTO table2 (username, password) VALUES ('" & me.Username & "','" & me.txtPassword & "') ;"

It's exactly the same except Nico left off the last double-quote (easily done).

[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 " ' "

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

[NeoPa]

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 " ' "

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

[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?

[NeoPa]

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?

DoCmd.SetWarnings(False)
and after all finished
DoCmd.SetWarnings(True)

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

[omozali]

DoCmd.SetWarnings(False)
and after all finished
DoCmd.SetWarnings(True)

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

unfortunately, it didn't

[NeoPa]

unfortunately, it didn't

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

[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)

[omozali]

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

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

[NeoPa]

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

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

[PEB]

Can someone please tell me why INSERT INTO generates syntax error

INSERT INTO table2;
VALUES (username.Value,password.Value);
MsgBox "User added"
End If

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

[PEB]

Ah it is finished here! OK!

[NeoPa]

I thought I'd add a link to an article (How to Debug SQL String) that may help the many people who are still viewing this thread many years later.

As Nico indicated many years ago, if you approach the problem step-by-step in the appropriate order then it is so much easier to find and fix problems.