How to code an Insert query

[DetRich]

Hello,

My app has a table with usernames. On a form, the user needs to pick an
user from this table. If the user is not in the table, there is a command
button to add a new user. This process simply uses an InputBox to prompt the
user for the name, adds it to the textbox on the form. Then, it needs to add
that new user to the Username table. I would like to code this.

The Insert statement will look like this:
Insert into tblUsers
(Username)
Values (inputusername); This is the value from the InputBox.

I don't know how to execute the SQL at this point. Is there anything else I
need to say?

Thanks in advance,

Rich

 

[Duane Hookom]

I don't care for inputbox since you don't have much control. However:

Dim strUserName as String
Dim strSQL as String
strUserName = InputBox("Enter User Name") & ""
If Len(strUserName) > 1 Then
strSQL = "INSERT INTO tblUsers (Username) Values (""" & strUserName &
""")"
CurrentDb.Execute strSQL, dbFailOnError
'or use the following rather than the execute.
'If Warnings are on (they should be) the user will get confirmation msgs
'DoCmd.RunSQL strSQL
End If

 

[DetRich]

Thanks for the reply Duane.

Two follow-up questions:
When the DoCmd.RunSQL executes, it prompts me for the same info as the
InputBox. Why does it do this, and how do I eliminate it?

2nd: In your INSERT statement, you have 3 double quotes in the Values part.
Won't this insert double quotes around the value being inserted?

This is what I have and along with the actual Username, it prompts for, and
attempts to insert first and last names:

Private Sub cmdNewSME_Click()

Dim strSME, strFirstName, strLastName As String
Dim strQuery As String

strQuery = "Insert into tblUsers (CDSID, FName, LName) Values (strSME,
strFirstName, strLastName)"
SME = UCase(InputBox("Please enter the CDSID of the SME"))
FName = UCase(InputBox("Please enter the First Name of the SME"))
LName = UCase(InputBox("Please enter the Last Name of the SME"))
AppSME = SME
DoCmd.RunSQL Query, False

End Sub

 

[Bob Barrows]

Thanks for the reply Duane.

Two follow-up questions:
When the DoCmd.RunSQL executes, it prompts me for the same info as the
InputBox. Why does it do this, and how do I eliminate it?

See below.

2nd: In your INSERT statement, you have 3 double quotes in the
Values part. Won't this insert double quotes around the value being
inserted?

Yes. What he is doing is creating a string that will be passed to the
query engine to be executed. This string needs to be the same string you
would see in the SQL View of the query builder if you used it to build a
similar query using hardcoded values. So that means, if the query engine
expects string delimiters (quotes) then you have to supply those
delimiters in the string you create. The trouble is, when assigning
literal text to a variable in VBA, you have to delimit that text with
quotes:

s = "this is my text"

The problem is, the query engine is also expecting delimiters for
character data:

insert into table(textcol1) values("text to be inserted")

If you try:

s="insert into table(textcol1) values("text to be inserted")"

The VBA parser will interpret the quote before the word "text" as the
closing delimiter for your string and will error out because of the
characters following that quote. To avoid this, the literal quote
characters must be "escaped", by doubling them. When the VBA parser
encounters two adjacent quotes, it interprets them as a single literal
quote. To put that into practice:

s="insert into table(textcol1) values(""text to be inserted"")"
msgbox s

This is what I have and along with the actual Username, it prompts
for, and attempts to insert first and last names:

Private Sub cmdNewSME_Click()

Dim strSME, strFirstName, strLastName As String
Dim strQuery As String

strQuery = "Insert into tblUsers (CDSID, FName, LName) Values (strSME,
strFirstName, strLastName)"

You have just created a string containing the words strSME, etc. See for
yourself:

Msgbox strQuery

The query engine will not be able to run that string without prompting
for values for the parameters you gave it (any unknown object names are
treated as if they are parameters).

SME = UCase(InputBox("Please enter the CDSID of the SME"))
FName = UCase(InputBox("Please enter the First Name of the SME"))
LName = UCase(InputBox("Please enter the Last Name of the SME"))
AppSME = SME

This does nothing to change the content of strquery. Again, see for
yourself:
Msgbox strQuery

What you have to do is assign those values to the variables first.

SME = UCase(InputBox("Please enter the CDSID of the SME"))
FName = UCase(InputBox("Please enter the First Name of the SME"))
LName = UCase(InputBox("Please enter the Last Name of the SME"))
AppSME = SME

Then, concatenate the values of those variables into strQuery, properly
delimiting the character data. I am not sure what datatype CDSID is. I
will assume it is numeric, in which case the concatenation should look
like this:

strQuery = "Insert into tblUsers (CDSID, FName, LName) Values (" & _
strSME & ", """ & strFirstName & """, """ & strLastName & """)"
Msgbox strQuery 'alternatively write it to the debug window

Personally, I prefer to use parameters so I don't have to worry about
delimiters. In your case, I'm curious why you are so committed to the
use of InputBox ... are you planning to use those variables later on in
your procedure? Why not keep your code as written and let Access prompt
for those values?

 

[Duane Hookom]

I don't think you reviewed my code. You can't set the value of strQuery until
after you have the user input values. You don't have any of the quotes etc
that my suggestion included.

Please read my initial reply again and try apply the logic. If you have
questions, post back but at least give it a good try.

 

[DetRich]

Thanks for the reply Bob.

I think I understand the need for the double-quotes.
But, I am not entering text, per se. The actual text is now stored in the
variables so how does this affect the parser looking for a delimiter?

 

[Bob Barrows]

Thanks for the reply Bob.

I think I understand the need for the double-quotes.
But, I am not entering text, per se.

Why do you say that? What do you think the InputBox method is returning?

The actual text is now stored
in the variables so how does this affect the parser looking for a
delimiter?

Run the code I supplied and pay attention to the results of the msgbox
statements.

 

[DetRich]

Thanks again Duane,

What you were explaining just did not click. I got it now and it's working
as expected.