469,318 Members | 1,926 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,318 developers. It's quick & easy.

Insert Into query using VBA

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
15 98768
nico5038
3,080 Expert 2GB
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
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
omozali
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
32,173 Expert Mod 16PB
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
omozali
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
32,173 Expert Mod 16PB
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
omozali
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
32,173 Expert Mod 16PB
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
omozali
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
32,173 Expert Mod 16PB
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
3,080 Expert 2GB
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
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
32,173 Expert Mod 16PB
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
1,418 Expert 1GB
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
1,418 Expert 1GB
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.

Similar topics

reply views Thread by Hrvoje Vrbanc | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.