Insert statement - I give up...

[hmanvel]

I have constructed an access database which generates a word document, which is a price quotation, including many specs that automatically populate the document from two subforms. This is working great, very quick and it works. I am now struggling to insert a few of the fields into a recorder table, so that we have a record of all the quotes generated, just basic info. My problem is with the syntax of using INSERT with ME!. Using the same context as was used to populate the word document doesnt seem to work, and I wonder if anyone could offer advice. Following is the code which generates the document:

----------------------------------
Dim appWord As Word.Application
Dim doc As Word.Document

On Error Resume Next
Set appWord = GetObject(, "Word.application")
If Err = 429 Then
Set appWord = New Word.Application
Err = 0
End If

With appWord
Set doc = .Documents(DOC_NAME)
If Err = 0 Then
If MsgBox("Do you want to save the current document " _
& "before updating the data?", vbYesNo) = vbYes Then
.Dialogs(wdDialogFileSaveAs).Show
End If
doc.Close False
End If


Set doc = .Documents.Open("C:\My Documents\Equipment\ChinaGate\NAMSI Quote.doc", , True)

With doc
.FormFields("frmPrice").Result = Me!Master1.Form.Price
.FormFields("frmModel").Result = Me!Master1.Form.Model
.FormFields("frmAbc").Result = Me!Master1.Form.Abc
.FormFields("frmScrDia").Result = Me!Master1.Form.ScrDia
.FormFields("frmScrLd").Result = Me!Master1.Form.ScrLd
.FormFields("frmInjPress").Result = Me!Master1.Form.InjPress
.FormFields("frmShotVol").Result = Me!Master1.Form.ShotVol
.FormFields("frmShotWt").Result = Me!Master1.Form.ShotWt
.FormFields("frmInjRt").Result = Me!Master1.Form.InjRt
.FormFields("frmPlasCap").Result = Me!Master1.Form.PlasCap
.FormFields("frmScrRot").Result = Me!Master1.Form.ScrRot
.FormFields("frmHtrZones").Result = Me!Master1.Form.HtrZones
.FormFields("frmTons").Result = Me!Master1.Form.Tons
.FormFields("frmDaylight").Result = Me!Master1.Form.Daylight
.FormFields("frmStroke").Result = Me!Master1.Form.Stroke
.FormFields("frmMinMold").Result = Me!Master1.Form.MinMold
.FormFields("frmMaxMold").Result = Me!Master1.Form.MaxMold
.FormFields("frmTbrWidth").Result = Me!Master1.Form.TbrWidth
.FormFields("frmTbrHeight").Result = Me!Master1.Form.TbrHeight
.FormFields("frmPltWidth").Result = Me!Master1.Form.PltWidth
.FormFields("frmPltHeight").Result = Me!Master1.Form.PltHeight
.FormFields("frmEjStr").Result = Me!Master1.Form.EjStr
.FormFields("frmHp").Result = Me!Master1.Form.Hp
.FormFields("frmKw").Result = Me!Master1.Form.Kw
.FormFields("frmVolts").Result = Me!Master1.Form.Volts
.FormFields("frmAmps").Result = Me!Master1.Form.Amps
.FormFields("frmOilCap").Result = Me!Master1.Form.OilCap
.FormFields("frmLength").Result = Me!Master1.Form.Length
.FormFields("frmWidth").Result = Me!Master1.Form.Width
.FormFields("frmHeight").Result = Me!Master1.Form.Height
.FormFields("frmWeight").Result = Me!Master1.Form.Weight
.FormFields("frmAddress").Result = Me![Customers Quote Subform].Form.Address
.FormFields("frmFirstName").Result = Me![Customers Quote Subform].Form.ContactFirstName
.FormFields("frmLastName").Result = Me![Customers Quote Subform].Form.ContactLastName
.FormFields("frmCo").Result = Me![Customers Quote Subform].Form.CompanyName
.FormFields("frmCity").Result = Me![Customers Quote Subform].Form.CITY
.FormFields("frmState").Result = Me![Customers Quote Subform].Form.StateOrProvince
.FormFields("frmZip").Result = Me![Customers Quote Subform].Form.PostalCode
.FormFields("frmPhone").Result = Me![Customers Quote Subform].Form.Phone
.FormFields("frmEmail").Result = Me![Customers Quote Subform].Form.ContactEmail

.Visible = True
.Activate
End With

Set doc = Nothing
Set appWord = Nothing


Exit Sub
-----------------------------------------
When I experiment with just placing one field into one field of the table, I have used dozens of variations of the following:
------------------------------------------
SQLText = "INSERT INTO QuoteRecorder([Quote Number])" "VALUES (Me!Master1.Form.Model);"
'
DoCmd.RunSQL SQLText
------------------------------------------
It doesnt seem to be a type mismatch, both fields are text. I get syntax errors, or alternately if I change things I get a parameter box.

I put this code at the beginning of the above code, at the end, etc. Nothing works. I am an amateur so please don't flame too loudly. Any advice would be greatly appreciated!

 

[tehNellie]

There's an obvious syntax problem here:

SQLText = "INSERT INTO QuoteRecorder([Quote Number])" "VALUES (Me!Master1.Form.Model);"

Where your text is non variable, wrap it in quotes and use the "&" to append variable information to your text. When it comes to needing quotes around variable supplied values I tend to use & chr(34) & (The ASCII code for ") rather than the myriad of double quotes you need otherwise as I find it makes it far easier to read:

SQLText = "INSERT INTO QuoteRecorder([Quote Number]) VALUES (" & chr(34) & Me!Master1.Form.Model & chr(34) & ");"

In general, when it comes to getting errors on SQL, stick a debug.print SQLtext after you assign the variable, then you can make sure that you're getting correctly formatted SQL into your string. (i'm no export on forms so your Me!master1.form etc might need checking).

 

[hmanvel]

tehNellie, Thanks a million. That works!!