Re: VB INSERT INTO

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

I went and fixed all the ones that were numbers and ran it again and still
getting the syntax error i was getting before and where it is breaking is on
the the line below.

conDatabase.Execute strSQL

Thanks

Lisa

"Douglas J. Steele" wrote:

Your query assumes all the fields in the table are text (you're putting
single quotes around all of the values in the Value list). Is that true?
Numeric value shouldn't have quotes around them, so if, for example,
EvaluatorID is a numeric field, change

& ", '" & txt_EvalID & "'" _

to

& ", " & txt_EvalID _

Similarly, dates need to have delimiters of #, not quotes (and the format of
the date is important: queries in Access don't respect the user's Short Date
format as set through Regional Settings), so if QualityDate is a date field,
change

& "'" & txt_QualityDate & "'" _

to

& Format(txt_QualityDate, "\#yyyy\-mm\-dd\#") & _

That assumes that txt_QualityDate only contains a date. If it contains both
a date and time, use

& Format(txt_QualityDate, "\#yyyy\-mm\-dd hh\:nn\:ss\#") & _

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Lisa" <Lisa@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:428C8228-CED7-414A-A311-BCC25107A621@xxxxxxxxxxxxxxxx
Ok so I took the apostrophe out and now I am getting a runtime error
-2147217900 (980040e14) Syntax error in INSERT INTO statement.

Thanks for any help you can give me.

Lisa

"mcescher" wrote:

Couple of things to look at. Your Execute statement is commented
out. It won't run with the apostrophe in the beginning of the line.
I'm assuming the commented lines at the bottom are from the original
code and can be ignored.

Second, right before your Execute statement, enter "DoCmd.Print
strSQL" without the quotes. This will print the resulting SQL
statement that your code created. Paste this into a query, and see if
it runs. If not, post that back here, and we'll take a look

Does your code generate any errors? If so, where does it break. What
error message?

Hope this helps,
Chris M.

On Mar 13, 7:14 am, Lisa <L...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
I have created an unbound form that I wanted to create a command button
to
insert the data into the table (I am doing it this way because I need
to do a
DLookup for a particular field on the form that i need to then populate
into
the table and the only way that I can think of to get this to populate
is to
use a command button). I am having a problem getting my code to work.
Im
not an avid VB user, but I had a form that had similar code and I
copied it
and changed it based on my new data table but it doesn't seem to be
working.
Hope someone can help.

Private Sub Command418_Click()

Dim strSQL As String
Dim conDatabase As ADODB.Connection

strSQL = "INSERT INTO TBL_Billing Eval " _
& "(QualityDate, EvalPurpose, EvaluatorID, Campus,
SiteLead,
FLM, Employee, FunctionGrp, PolNo, ANI, Duration, TransType, ScorePlan,
TransDate, EvalCompl, CorrectAction, Q1, Q1TrendComm, Q1Comment)
Values(" _
& "'" & txt_QualityDate & "'" _
& ", '" & cb_EvalPurp & "'" _
& ", '" & txt_EvalID & "'" _
& ", '" & txt_Campus & "'" _
& ", '" & txt_Lead & "'" _
& ", '" & Txt_FLM & "'" _
& ", '" & txt_EmplID & "'" _
& ", '" & cb_functGrp & "'" _
& ", '" & txt_PolNo & "'" _
& ", '" & txt_ANI & "'" _
& ", '" & txt_duration & "'" _
& ", '" & cb_transtype & "'" _
& ", '" & cb_ScorePlan & "'" _
& ", '" & txt_transdate & "'" _
& ", '" & Frm_EvalCompl & "'" _
& ", '" & frm_CorrectAction & "'" _
& ", '" & Frm_Q1 & "'" _
& ", '" & cb_Q1Trending & "'" _
& ", '" & txt_Q1Comment & "'" _
& ")"
MsgBox "You are adding something else."
Set conDatabase = CurrentProject.Connection

' conDatabase.Execute strSQL

conDatabase.Close
Set conDatabase = Nothing
' MsgBox "Evaluation " & tbWR_ & " has been added."
'
' Now reset the controls on the form to default values
' lb_Sections.Requery
' tbTarget_Date = ""
' tbDate_Logged = Date
' tbWR_ = Null
' cbSegment.Value = Null
' tbSuggestion = Null
' tbSubmitter = Null
' cblocation.Value = Null
' cbWR_Type.Value = Null
' cbAnalyst.Value = Null
' cbLetterSent.Value = 0
' cbchartsdirectory.Value = 0
' FileAttachment.Value = "<enter file name>"

End Sub





.

Relevant Pages

  • Re: VB INSERT INTO
    ... Debug.Print strSQL ... Doug Steele, Microsoft Access MVP ... Numeric value shouldn't have quotes around them, so if, for example, ... Set conDatabase = CurrentProject.Connection ...
    (microsoft.public.access.modulesdaovba)
  • Re: VB INSERT INTO
    ... Debug.Print strSQL ... When your code executes, go to the Immediate Window and ... Numeric value shouldn't have quotes around them, so if, for example, ... -2147217900 Syntax error in INSERT INTO statement. ...
    (microsoft.public.access.modulesdaovba)
  • Re: VB INSERT INTO
    ... Your query assumes all the fields in the table are text (you're putting ... Numeric value shouldn't have quotes around them, so if, for example, ... Dim strSQL As String ... Set conDatabase = CurrentProject.Connection ...
    (microsoft.public.access.modulesdaovba)
  • Using DDL to Create Constraint - Syntax Error
    ... get a syntax error on the third DDL SQL statement. ... Dim strSQL As String ... dbsTemp.Execute strSQL, dbFailOnError ...
    (microsoft.public.access.queries)
  • Re: My Brain Hurts - Help
    ... If you must fill in all the fields that would be returned by the select statement, this would account for me getting an error, but why it appears as "Syntax error in INSERT INTO statement." ... strSQL = "Select * FROM Clients;" da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter ... I have tried changing the strSQL text so that it is more complex and it makes no difference. ...
    (microsoft.public.dotnet.languages.vb)