What is wrong with this insert statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to add a SSN into a table called Provider. The SSN is a string
and is on the current form as me.ssn.
But it is not inserting anything into the Provider table


dim strSQL as string
strSQL = "INSERT INTO Provider (SSN) " & _
"VALUES (" & Me.SSN & ");"
 
Hi,

pokdbz schreibselte:
I am trying to add a SSN into a table called Provider. The SSN is a
string and is on the current form as me.ssn.
But it is not inserting anything into the Provider table


dim strSQL as string
strSQL = "INSERT INTO Provider (SSN) " & _
"VALUES (" & Me.SSN & ");"
Click to expand...

strSQL = "INSERT INTO Provider (SSN) " & _
"VALUES ('" & Me!SSN & "');"

If SSN is a text-field use ...'<Value>'...

Acki
 
pokdbz said:
I am trying to add a SSN into a table called Provider. The SSN is a
string and is on the current form as me.ssn.
But it is not inserting anything into the Provider table


dim strSQL as string
strSQL = "INSERT INTO Provider (SSN) " & _
"VALUES (" & Me.SSN & ");"
Click to expand...

You need quotes around the value...

dim strSQL as string
strSQL = "INSERT INTO Provider (SSN) " & _
"VALUES ('" & Me.SSN & "');"
 
strSQL = "INSERT INTO Provider (SSN) " & _
"VALUES ('" & Me.SSN & "');"

This still didn't insert it into the table. I used the debugger and strSQL
had a value in it and so did Me.SSN, but it didn't insert it into the table.
So I have to do something with Provider (SSN)?
Any ideas?
 
strSQL = "INSERT INTO Provider (SSN) " & _
"VALUES ('" & Me.SSN & "');"

This still didn't insert it into the table. I used the debugger and strSQL
had a value in it and so did Me.SSN, but it didn't insert it into the table.
So I have to do something with Provider (SSN)?
Click to expand...

well, you *do* have to do something with strSQL; are you in fact
executing it as a query, either with

DoCmd.RunSQL strSQL

or (better, since it traps errors)

Dim qd As DAO.Querydef
Dim db As DAO.Database
Set db = CurrentDb
<your definition code>
Set qd = db.CreateQueryDef("", strSQL)
qd.Execute dbFailOnError

John W. Vinson[MVP]
 
yep that was the problem forgot to run it. Is there a way to make it so the
box doesn't pop up saying to append this record?
 
yep that was the problem forgot to run it. Is there a way to make it so the
box doesn't pop up saying to append this record?
Click to expand...

LOL... been there, done that.

"Why doesn't my new car run?"
"Well, first you have to turn the ignition..."

The Execute method of the qeurydef object doesn't pop up anything.

John W. Vinson[MVP]
 
It does pop up something. It says do you want to append this record and it
has a yes or no option.

If I wanted to add something else to the insert how would I do this? I
wanted to add something to a different field called SetA in the same table.
 
Ok, I used the qd.Execute and that worked fine. Thanks

I tried to search through the help on how to append something else to the
same record, but all that I found was how to do appends through the wizard.

I need to append a 1 to the same record in the field SetA.
So I need to append the SSN and SetA = 1.
 
I got it thanks for the help

John Vinson said:
<shrug> Then you're not using the qd.Execute code that I posted. If
you use it, you won't get a message.

If you want to use RunSQL, then put a line

DoCmd.RunCommand acCmdSetWarnings False

before the RunSQL line; be sure to put

DoCmd.RunCommand acCmdSetWarnings True

afterward or you'ld just turn off ALL warning messages for the rest of
the Access session.


Look up "Append" in the Help for Queries. You can append up to an
absurd 255 fields in a single query.

John W. Vinson[MVP]
Click to expand...
 
Back
Top