SQL statment Insert Into error

  • Thread starter Thread starter Glen
  • Start date Start date
G

Glen

Can anybody tell what is wrong with the way this is set up. I am not
real familiar with SQL and it kicks my tail a lot. Any help is greatly
appreciated.

strSQL = "INSERT INTO tblSS_HEADER " & _
"FORMS!frmUser_Data.SSelect_ID, " & _
"FORMS!frmUser_Data.txtShipClass, " & _
"FORMS!frmUser_Data.txtShipHull, " & _
"FORMS!frmUser_Data.VesselName, " & _
"FORMS!frmUser_Data.Avl_Type.UIC, " & _
"FORMS!frmUser_Data.Avl_Type, " & _
"FORMS!frmUser_Data.Avl_Type, " & _
"FORMS!frmUser_Data.StartDate, " & _
"FORMS!frmUser_Data.EndDate"
DoCmd.RunSQL (strSQL)

I have split the string for ease of reading in this post. I am trying
to take information from a form and insert it into a table instead of
grabbing all of this information from multiple tables and placing it
into tblSS_Header based on primary keys. Thanks again if you can help.

Glen
 
Dear Glen:

First a suggestion. Put a breakpoint on the DoCmd line of your code and
display the value of strSQL in the Immediate Pane. Then you can deal with
the SQL it produces.

Your query now is:

INSERT INTO tblSS_HEADER FORMS!frmUser_Data.SSelect_ID,
FORMS!frmUser_Data.txtShipClass, FORMS!frmUser_Data.txtShipHull,
FORMS!frmUser_Data.VesselName, FORMS!frmUser_Data.Avl_Type.UIC,
FORMS!frmUser_Data.Avl_Type, FORMS!frmUser_Data.Avl_Type,
FORMS!frmUser_Data.StartDate, FORMS!frmUser_Data.EndDate

It is missing some elements. Here's something that may work:

INSERT INTO tblSS_HEADER ( ColumnName1, ColumnName2, . . . )
SELECT TOP 1 FORMS!frmUser_Data.SSelect_ID, FORMS!frmUser_Data.txtShipClass,
FORMS!frmUser_Data.txtShipHull, FORMS!frmUser_Data.VesselName,
FORMS!frmUser_Data.Avl_Type.UIC, FORMS!frmUser_Data.Avl_Type,
FORMS!frmUser_Data.Avl_Type, FORMS!frmUser_Data.StartDate,
FORMS!frmUser_Data.EndDate
FROM tblSS_HEADER

This requires that there be at least one row in tblSS_HEADER. If there may
not be, you can substitute the name of any other table on the last line,
choosing one that will always have at least one row. You could even create
a "dummy" table with one column and one row just for this.

May I recommend you create a new query to test this. First, just put this
in:

SELECT TOP 1 FORMS!frmUser_Data.SSelect_ID, FORMS!frmUser_Data.txtShipClass,
FORMS!frmUser_Data.txtShipHull, FORMS!frmUser_Data.VesselName,
FORMS!frmUser_Data.Avl_Type.UIC, FORMS!frmUser_Data.Avl_Type,
FORMS!frmUser_Data.Avl_Type, FORMS!frmUser_Data.StartDate,
FORMS!frmUser_Data.EndDate
FROM tblSS_HEADER

Then add the first line and test again. The form frmUser_Data must be open
when you test this.

Watch out for any columns that are "required" in tblSS_HEADER, and any
columns that are a unique index (including PK). A test of these values in
the controls of frmUser_Date would be most wise. Otherwise, the query will
fail and the user receive cryptic messages. If you test them in the
program, you could give a more rational error message and put the focus on
the control that requires revision.

Tom Ellison
 
Thanks for your response Tom. Been out of the office for a while but I
will try your suggestions today. It is painfully obvious that I am no
SQL guru. I haven't seen the SELECT TOP 1 command before. I'm
interested in checking out how it works. Thanks again.
 
Back
Top