Help on INSERT SQL Statement

[DanteEllis]

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 "Administra tor" 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.Execu te strSQL

___

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

[Breezwell]

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 "Administra tor" 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.Execu te 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_passwo rd, txt_user_type_t ext

3. Add a button to your form.

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

Private Sub SubmitButton_Cl ick()

Dim strSQL As String
strSQL = "INSERT INTO t_logon(user_na me, user_password, user_type_text) "
strSQL = strSQL & "VALUES (txtuser_name,t xt_user_passwor d,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.

[DanteEllis]

Change Error Message

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_Cli ck()

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_na me,user_passwor d,user_type_tex t)"
strSQL = "strSQL & "VALUES (txtLogonName,t xtNew,txtLogonT ype"

DoCmd.RunSQL strSQL

Msgbox "The new user has been added", vbOKonly, "Confirmati on"

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

[Breezwell]

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_na me, user_password, user_type_text) "
strSQL = strSQL & "VALUES (txtLogonName, txtNew, txtLogonType)"

[DanteEllis]

clarification

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?

[Breezwell]

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.

[DanteEllis]

Okay perfect, that worked. Thanks a lot for your help!