By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,154 Members | 2,313 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,154 IT Pros & Developers. It's quick & easy.

Insert Into query using VBA

P: 36
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
Share this Question
Share on Google+
15 Replies


nico5038
Expert 2.5K+
P: 3,072
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

P: 36
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 :(
Dec 3 '06 #3

P: 36
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 " ' "
Dec 3 '06 #4

NeoPa
Expert Mod 15k+
P: 31,616
Unfortunately the code generated syntax error :(
Try this slightly fixed version :
Expand|Select|Wrap|Line Numbers
  1. 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).
Dec 3 '06 #5

P: 36
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
Dec 3 '06 #6

NeoPa
Expert Mod 15k+
P: 31,616
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.
Dec 3 '06 #7

P: 36
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
Expert Mod 15k+
P: 31,616
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?
Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings(False)
  2. and after all finished
  3. DoCmd.SetWarnings(True)
Can I take it then that Nico's code worked fine for you?
Dec 3 '06 #9

P: 36
Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings(False)
  2. and after all finished
  3. DoCmd.SetWarnings(True)
Can I take it then that Nico's code worked fine for you?

unfortunately, it didn't
Dec 4 '06 #10

NeoPa
Expert Mod 15k+
P: 31,616
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?
Dec 4 '06 #11

nico5038
Expert 2.5K+
P: 3,072
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

P: 36
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
Dec 16 '06 #13

NeoPa
Expert Mod 15k+
P: 31,616
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?
Dec 17 '06 #14

PEB
Expert 100+
P: 1,418
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
Dec 17 '06 #15

PEB
Expert 100+
P: 1,418
PEB
Ah it is finished here! OK!
Dec 17 '06 #16

Post your reply

Sign in to post your reply or Sign up for a free account.