VB INSERT INTO

[Lisa]

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

 

[mcescher]

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.

 

[Lisa]

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

 

[Douglas J. Steele]

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\#") & _

 

[Douglas J. Steele]

Oops

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

and

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

should have been

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

and

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

 

[Lisa]

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]

Immediately before your line

conDatabase.Execute strSQL

add the line

Debug.Print strSQL

When your code executes (and fails), go to the Immediate Window (Ctrl-G) and
look at the SQL statement that's printed there. Copy it into your response
if you don't see anything wrong with it.

 

[Lisa]

Doug,

I didn't get anything when I hit Ctrl / G... but I didn't get another error
message which is

Run Time Error 91

Object variable or with block variable not set.

Hope this makes sense.

Lisa

 

[Douglas J. Steele]

I can't see any way that that error message is related to the one line of
code I suggested putting in.

Before trying again, you might want to try compacting the database.