INSERT INTO creates a pop up messagebox?

[TheKillerMonkey]

Hello,



I'm trying to add a new user into a table. The code essentially works except that for my user I'm getting a msgbox with a caption of what I've put in as the new user.

SQL = "Insert into Users (Username,SecurityLevel_FK) "
SQL = SQL & "Values (" & Forms!SystManHome!NSfSystManHome.Form!tbNewUser & ","
SQL = SQL & DLookup("[ID]", "tblSecurityLevels", "[Level]='" & Forms!SystManHome!NSfSystManHome.Form!cbSecLevels & "'") & ")"


DoCmd.RunSQL SQL

Did 2nd try with tester and User in the boxes



Thanks!
-Matt

[ranman256]

1 you don't need DLOOKUP for a combo box. The value is already in the combo.
2. you don't put dlookup in sql
3. text box is a string, so the sql must be surrounded by string quotes.
... & "Values ('" & Forms!SystManHome!NSfSystManHome.Form!tbNewUser & "',"

[ssanfu]

Is the field "Username" a text type field or numeric? If it is a text type field (you are storing text), there needs to be delimiters in the SQL string.

SQL = "Insert into Users (Username,SecurityLevel_FK) "
SQL = SQL & "Values ('" & Forms!SystManHome!NSfSystManHome.Form!tbNewUser & "',"   ' <--single quotes in this line
SQL = SQL & DLookup("[ID]", "tblSecurityLevels", "[Level]='" & Forms!SystManHome!NSfSystManHome.Form!cbSecLevels & "'") & ")"

Expanded, it looks like:
SQL = SQL & "Values ( ' " & Forms!SystManHome!NSfSystManHome.Form!tbNewUser & " ' ,"



Also, it might be easier to create two variables to get the values and use the variables in the SQL.

Dim UN as String
Dim SL_FK as Long


UN = Forms!SystManHome!NSfSystManHome.Form!tbNewUser
SL_FK = [SIZE=2]DLookup("[ID]", "tblSecurityLevels", "[Level]='" & Forms!SystManHome!NSfSystManHome.Form!cbSecLevels & "'")

SQL = "Insert into Users (Username,SecurityLevel_FK) "
SQL = SQL & "Values ('" & UN & "', " & SL_FK & ")"
'  Debug.Print SQL
Currentdb.Execute SQL, dbfailonerror

This makes it easier to check the values when single stepping through the code.


BTW, "SQL" is a reserved word in Access and shouldn't be used as an object name. Maybe use "strSQL" or "sSQL".



Edit: ranman256 is correct. You should be able to get the security level froim the combo box.
I missed that......

[TheKillerMonkey]

Awesome, adding the single quotes helped. Thanks for the feedback!