Re: How to code an Insert query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" wrote:

DetRich wrote:
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?


--
HTH,
Bob Barrows



.

Relevant Pages

  • Re: How to code an Insert query
    ... it prompts me for the same info as the ... Won't this insert double quotes around the value being ... What he is doing is creating a string that will be passed to the ... delimiters in the string you create. ...
    (microsoft.public.access.queries)
  • Re: Py-dea: Streamline string literals now!
    ... literal delimiters. ... single or double quotes into a string literal. ... HOWEVER, he did find the perfect char, and that char is the pipe! ...
    (comp.lang.python)
  • Re: DSUM for Dummies. Like Me! Some one please help
    ... without the "Passenger = True". ... Getting the quotes right can be really tricky. ... That will work if you build that same string in your domain function. ... delimiters for text fields. ...
    (microsoft.public.access.formscoding)
  • Re: DSUM for Dummies. Like Me! Some one please help
    ... Getting the quotes right can be really tricky. ... a query in the query grid which gets the results that you want; ... That will work if you build that same string in your domain function. ... delimiters for text fields. ...
    (microsoft.public.access.formscoding)
  • Re: Update existing values incrementally w/UPDATE SQL
    ... I think it would be safe to say, use the Dim statement any time you are ... Dim basically tells vba that you want to initiate a new variable. ... As far as quotes go, this was one of the trickiest ... Consider vba's interpretation of a string: ...
    (microsoft.public.access.modulesdaovba)