Insert Query only inserting the first value?

I looked around quite a bit figuring someone else must have had this issue, but I didn’t see it.

It’s driving me nuts, that something so simple isn’t working and I can’t figure out why, and so I’ve spent 3 hours now on something that should have taken a couple minutes…

So here’s the set up. I have a query that I need to add 2 fields to. A checkbox and a submission date. Now I can’t add the fields to the underlying tables because they get deleted and recreated by a series of queries and manual updates (inherited system just trying to get by).

So I came up with the idea to write the current query’s output to a table (via append query) then use that table as a data source for a form which opens in datasheet view. Then the user can add the date and check the box in the checkbox field. Then I use the checkbox’s afterupdate event to insert those 2 fields into another table (or delete them if it’s unchecked) with the following:

Private Sub Submitted_AfterUpdate()

Dim strSQL, strSQL2 As String

strSQL = "INSERT into tbl_SaveSubInfo (SO, Submitted, SubmissionDate) SELECT ord_no, Submitted, SubmissionDate FROM tbl_ReadyToPay WHERE ord_no ='" & Me.ord_no & "'"
strSQL2 = "DELETE * from tbl_savesubinfo where SO ='" & Me.ord_no & "'"
DoCmd.Save

If Submitted = -1 Then
    DoCmd.RunSQL (strSQL)
Else
    DoCmd.RunSQL (strSQL2)
End If

End Sub

(I added the docmd.save as an attempt to fix this but it didn’t work). Then I added this query to the append query that creates the table based on the original query I need to add the fields to and add the Submit (Checkbox) field and the SubmissionDate field to the append figuring it will write it back in when it rewrites the table.

It mostly works… except that the insert query mostly only writes the ord_no to the SO field and the other 2 fields (submit, submission date) are not inserted and I cannot figure out why despite looking through tons of websites and tutorials to see if my syntax is wrong and not being able to find anything. Now here’s the weird part. SOMETIMES it will insert all 3 fields, but I have not been able to figure out the trigger that makes it happens. I thought perhaps it was because I was in the record and so the data didn’t save till I left it (hence the docmd.save) but that didn’t fix it.

When the insert query does insert all 3 fields, the rest of it works like a charm… but I can’t make it do it every time…

I’d greatly appreciate some thoughts!

Answer

The option DoCmd.Save saves the object you are on, the form. To force the data to save you have a couple options:

DoCmd.RunCommand acCmdSaveRecord

If Me.Dirty = True Then
    Me.Dirty = False
End If`

Advertisement

Answer