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

Help on INSERT SQL Statement

P: 4
Hi,

I am using Microsoft Office 2003, but save my Databases in Access 2000 format.

I have a table for user verification. I have a login form which works well. I am trying to make a form to add users, so it doesn't have to be done via the table directly.

I have the following in an unbound form:

User Name (unbound text box)
User Password (unbound text box)
Confirm Password (unbound text box)
User type (bound to a table for looking up)
User Type combo box is bound to column 1 (which is userID - an autonumber), and it then has the text from the lookup field "Administrator" or "User"

I have the following error when I run the code below:

strSQL = "INSERT INTO t_logon (user_name,user_password,user_type_text)"
strSQL = strSQL & "VALUES('" & Me.txtLogonName & "',"
strSQL = strSQL & Me.txtConfirm & "',"
strSQL = strSQL & Me.txtLogonType & ");"

CurrentDB.Execute strSQL

___

I get the error of Run-time error '3075':
Syntax error in string in query expression '123',
Nov 17 '08 #1
Share this Question
Share on Google+
6 Replies


Breezwell
P: 33
Hi,

I am using Microsoft Office 2003, but save my Databases in Access 2000 format.

I have a table for user verification. I have a login form which works well. I am trying to make a form to add users, so it doesn't have to be done via the table directly.

I have the following in an unbound form:

User Name (unbound text box)
User Password (unbound text box)
Confirm Password (unbound text box)
User type (bound to a table for looking up)
User Type combo box is bound to column 1 (which is userID - an autonumber), and it then has the text from the lookup field "Administrator" or "User"

I have the following error when I run the code below:

strSQL = "INSERT INTO t_logon (user_name,user_password,user_type_text)"
strSQL = strSQL & "VALUES('" & Me.txtLogonName & "',"
strSQL = strSQL & Me.txtConfirm & "',"
strSQL = strSQL & Me.txtLogonType & ");"

CurrentDB.Execute strSQL

___

I get the error of Run-time error '3075':
Syntax error in string in query expression '123',

DanteEllis,

It appears you probably don't need the Me keyword in your textbox reference.

The easiest way to do this would be the following:

1. Create a form.

2. Create textbox controls on your form for each piece of data you need to insert into your database. So, you could create textbox controls and name them txtuser_name, txt_user_password, txt_user_type_text

3. Add a button to your form.

4. In the buttons OnClick event handler, add the following code:

Private Sub SubmitButton_Click()

Dim strSQL As String
strSQL = "INSERT INTO t_logon(user_name, user_password, user_type_text)"
strSQL = strSQL & "VALUES (txtuser_name,txt_user_password,txt_user_type_text )"

DoCmd.RunSQL strSQL
End Sub


After you fill your textboxes with the information you need and click the button, Access should prompt you telling you your going to append data to the database. Hope this helps.
Nov 18 '08 #2

P: 4
Breezewell,

Thanks.

I tried taking the "Me." out of each and I still get the Syntax error in INSERT INTO statement. I realize I didn't give the rest of the code. Here it goes (with the changes you said I should make.

I'm still getting the error.

Private Sub cmdSaveExit_Click()

Dim strSQL As String

If Msgbox("Are you sure you want to add this new user?", vbYesNo, "Warning") = vbYes Then

strSQL = "INSERT INTO t_logon(user_name,user_password,user_type_text)"
strSQL = "strSQL & "VALUES (txtLogonName,txtNew,txtLogonType"

DoCmd.RunSQL strSQL

Msgbox "The new user has been added", vbOKonly, "Confirmation"

Docmd.Close acForm, "f_add_user"
DoCmd.OpenForm "f_switchboard", acNormal

Else

Msgbox "New User Addition Aborted", vbOKOnly, "Error"

Exit Sub

End If

End Sub

___

START EDIT: I'm now getting the run-time error "3134": Syntax error in INSERT INTO statement, the error number is a little different from my original post above. / END EDIT
Nov 18 '08 #3

Breezwell
P: 33
Your SQL string is not formed properly. Your string variable canno't be in the quotes and your parenthesis are incomplete.

Paste this into your code for your SQL string:

strSQL = "INSERT INTO t_logon(user_name, user_password, user_type_text)"
strSQL = strSQL & "VALUES (txtLogonName, txtNew, txtLogonType)"
Nov 18 '08 #4

P: 4
Thanks Breezwell,

That worked and it was added to the table. Thanks!

Now, when it executed, the table field "user_type_text", the text added was the number 6, as opposed to a the text Administrator.

txtLogonType is the unbound combo box on my form. It looks up data in another table (called "lookups), the number 6 is the ID that ids it as the number 6.

Do you know why that would be?
Nov 18 '08 #5

Breezwell
P: 33
It would appear your Bound Column for the combo box is set to column 1, the ID which I assume is your primary key.

The confusing part is that the Row Source of your control determines what is seen in your combo box. The Bound Column of your control determines what is actually passed to your query when you hit the submit button. Basically, what you see is not always what you get with controls.

Change the Bound Column in the combo box's Properties to whatever column of data you need to reference in your table. So, if you have a table like this:

ID Name
** ******
1 A
2 B
3 C


and you need to pass the value of A to your query, make your Bound Column 2 in your controls properties.

Hope this helps.
Nov 18 '08 #6

P: 4
Okay perfect, that worked. Thanks a lot for your help!
Nov 18 '08 #7

Post your reply

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