Good morning, all:

I'm not sure this is exactly the right forum. Please, feel free to move/transfer it to a more appropriate forum.

I'm trying to program a button on a form, to run a SQL statement, that will insert records into a table.

And, I'm wracking my brains: It appears to compose the SQL statement just fine. (I present an Msgbox immediately before the docmd.runSQL line.) But, when I run it, I get neither the new record, nor an error.

A couple points to consider:

1) I have double-checked that the number of values I'm trying to employ does match the number (and data-type) of fields in the table.

2) This code is (of course) part of a larger routine. At the beginning of this routine, I delete any existing table "TmpFollowupSamples", and I recreate the table. Is there any kind of "save" function that I need to perform on a newly-created table, before populating it.

Here's my code (below). Any input would be appreciated.

Cliff

==============================
START CODE
==============================
' This first component contains the columns FarmID, AnimalID, CompiledQtrs
strSQL = "INSERT INTO TmpFollowUpSamples " & _
"VALUES (" & _
rs1.Fields("FarmID") & ", " & _
rs1.Fields("PersonResponsible") & ", " & _
"'" & rs1.Fields("AnimalID") & "', " & _
"'" & rs1.Fields("CompiledQtrs") & "', " & _
"'', '', '', '', '', '', '');"

MsgBox strSQL
DoCmd.RunSQL strSQL
DoCmd.OpenTable "TmpFollowUpSamples"
==============================
END CODE
==============================

Hi
Would it help if you also placed the table field names in your sql Insert command..

eg:
The syntax for inserting data into a table one row at a time is as follows:

INSERT INTO "table_name" ("column1", "column2", ...)
VALUES ("value1", "value2", ...)

Hi, Larry. Thx for the suggestion.

I actually did (in my trouble-shooting) go ahead and insert the field names. (Is that an industry-standard recommendation?)

Doing so didn't have any impact. The result was the same: no new record, and no errors.

Thx.

Cliff

Hi
Are we also able to see how your populating the recordset that feeds the sql statement...!!!

UPDATE - UPDATE - UPDATE

I think I'm onto something. I began massaging the code, one field at a time.

I encountered a do-nothing problem (where the SQL INSERT INTO code didn't seem to do anything -- either execute, or error-out)... when I got to Field #2.

Turns out, in my test case, Field #2 was null.

So... what do I need to know about this?

When I'm using this INSERT INTO command... and there are 11 fields... can my INSERT INTO statement contain null values?

If not, how do I manage those?

Thx.

Cliff

Hi
Try using the Nz() function to emulate a value if null..

eg:
Nz(rs1.Fields("PersonResponsible"),"")

Excellent, Larry! That did it.

I'd used the Nz function before... just straight in the VBA code. But, I didn't think of using it within my SQL code.

Thank you so much. The issue is closed.

Have a nice weekend.

Cliff