Problems with grabbing data

[Hothmire]

Good afternoon all,

I am having some problems transferring data from my MS Form to an MS report. I am writing Code for a button Command to grab the information in the text boxs or combo boxes of a form and put the data into its respective place in the form. I use the following code to do so:

[Private Sub cmdProduceReport_Click()

MsgBox "Producing Initial Issue Report"

Dim strquery As String

strquery = "INSERT INTO [rptInitialIssue] " & _
            "( [txtEmailSubject])" & _
            "VALUES(" & _
            "" & Me.EmailSubject_Date & "," & _
            ")"

DoCmd.RunSQL strquery

MsgBox "Report has been Produced. Opening Report for export."

End Sub]

When I attempt this I receive the following error code.

Runtime-error '3705'

Syntax error (missing operator) in query expression. Can someone help me with this. Thank you

[zmbd]

Fairly simple insert query:

INSERT INTO tbl_System 
   ( system_pk, system_fk_location, 
      system_fk_family, system_fk_station,
       system_description )
SELECT tbl_System_BCKUP.system_pk,
   tbl_System_BCKUP.system_fk_location, 
   tbl_System_BCKUP.system_fk_family, 
   tbl_System_BCKUP.system_fk_station, 
   tbl_System_BCKUP.system_description
FROM tbl_System_BCKUP
WHERE (((tbl_System_BCKUP.system_pk)>1));

Take a look at how your SQL is resolving:
Insert STOP at Line 12,
<CTRL-G>
?strquery

Post this back.

[Rabbit]

You need to put quotes around the value you're trying to insert. Also you have an extraneous comma towards the end.

[NeoPa]

Please check out Before Posting (VBA or SQL) Code. If you have a SQL issue then we need to see your SQL rather than the code that creates the string that is sent to the SQL engine (Jet).

The code that does formulate your SQL has various literal strings concatenated together. That is pointless and confusing (more for you than us I suggest), so I'd tidy that up first if I were you. It also seems you are using a date field to insert into the SQL as a literal, yet the name of the field you are adding implies it is normal text ([txtEmailSubject]), and the format of the SQL is such that neither quotes (' - for string literals) nor hashes (# - for date literals) has been used.

As Rabbit says, you also append an extraneous comma after the value which will cause grief, probably because you got confused building up a string from a lot of smaller ones.