CODEWITHSUNDEEP
sqlvbams-access
BeardedSith
BeardedSith

Reputation: 129

Executing Append Query not doing anything

Executing Append Query not doing anything

I'm having an odd problem that just reared its ugly head for some reason. I have a form that is used to add/edit/delete records in tblWorkOrder. When the record is saved, a check is made to see if the companion record in tblServiceRecord exists, and if not (like if it were the first time the record in tblWorkOrder is being saved/input) it will execute a query (qryCreateSR).

Several weeks ago it worked just fine. I had no problems with it, but then I updated tblServiceRecord to add several new columns and now it's not working at all. However, the SQL for the query doesn't delineate any of these new columns, let alone any specific information from tblWorkOrder. So I'm not entirely sure how this bug came up.

Here is the SQL:

INSERT INTO tblServiceRecord ( WorkOrderID )
SELECT Forms![frmWorkOrders].Form![txtID];

And here is the code behind the command button:

Private Sub cmdSave_Click()
    If DCount("*", "[tblServiceRecord]", "[WorkOrderID] = " & [Forms]![frmWorkOrders].[Form]![txtID]) > 0 Then
        Else
            DoCmd.SetWarnings False
            DoCmd.OpenQuery "qryCreateSR"
    End If

    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.GoToRecord , "", acNewRec
    Me.lstWorkOrders.Requery
    Me.lstWorkOrders.Value = ""
    Me.txtComments.Value = ""

cmdSave_Click_Exit:
    Exit Sub

cmdSave_Click_Err:
    MsgBox Error$
    Resume cmdSave_Click_Exit
End Sub

After removing the warnings suppression I get a key violation issue.

No clue what is causing the key violations. I checked my tables, and the two tables in question are tblWorkOrder and tblServiceRecord. Both have no records in them, I compacted the database, the linked fields (in tblServiceRecord, there is a reference to tblWorkOrder with the field WorkOrderID) are set to the same data type (number), and the child-tables are set to Indexed (No).

Just in case anyone wants to look at the database itself, here is a link:
https://drive.google.com/open?id=1_T-G9fyYQYjH3-YBe4PXhbBDTKNmY3ce

Upvotes: 0

Views: 156

Answers (1)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112279

Try saving the current record in the form (by setting Me.Dirty = False), before inserting the record to the other table. Since you try to insert into a child table with a relation to a parent table, you must have a corresponding (saved) record in the parent table. When you create a new entry in the form, it first doesn't exist in the table until it is saved for the first time.

Private Sub cmdSave_Click()
    Me.Dirty = False ' Or DoCmd.RunCommand acCmdSaveRecord    <==== Save here
    If DCount("*", "[tblServiceRecord]", "[WorkOrderID] = " & [Forms]![frmWorkOrders].[Form]![txtID]) = 0 Then
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "qryCreateSR"
    End If

    'Removed: DoCmd.RunCommand acCmdSaveRecord   <==== instead of here
    DoCmd.GoToRecord , "", acNewRec
    Me.lstWorkOrders.Requery
    Me.lstWorkOrders.Value = ""
    Me.txtComments.Value = ""

cmdSave_Click_Exit:
    DoCmd.SetWarnings True
    Exit Sub

cmdSave_Click_Err: // This code will never run, since a "On Error Goto cmdSave_Click_Err" is missing
    MsgBox Error$
    Resume cmdSave_Click_Exit
End Sub

Upvotes: 2

Related Questions