(how to?) Confirm insert succeeded, then do more... (1 Viewer)

[madEG]

Hello,

I don't know how to search for what I am looking for. I keep ending up at the 'watercooler' looking at jokes and religious comments

SITUATION: I have a form that accepts text info for insert into a table's record. Further, there is a multi select listbox that upon completion of the aforementioned record then adds subsequent records to a bridge table, using the 'newly inserted' record as one of the parents to these child records.

Thus far I'm fine re-getting the newly inserted record's identity value (sql server 2012 backend, office 2016, generally unbound VBA driven app) for use in the child record's fkeys... and the process works. Insert parent, get parent's ID, reuse for child records... No worries.

But when/If the initial insert fails, my code then carries on attempting to insert the child records which can get ugly.

QUESTION: How do I "trap" for some sort of confirmation/lack-of-error that the parent record successfully inserted before allowing the code to carry on inserting child records?

Something like:

If insert succeeds then do more work else msgbox("ugh") exit end if...

Other than running the same sort of check I do to get the identity to confirm the initial record has inserted... is there some sort of "look - no error... so carry on..."

I hope this makes sense. Thanks!


EDIT: I guess the code would help...

Private Sub btnAddNewRecord_Click()

  If Format(Trim(txtGoalObjectiveName)) <> "" Then
        Dim strSQL As String
        
        strSQL = "INSERT INTO dbo_GoalsObjectives (CommDeptIDf, StratGoalIDf, GoalObjectiveName, GoalObjectiveDesc, GoalObjectiveDesiredOutcome) VALUES (" & _
        
[listboxCommDepts].[Value] & _
        ", " & _
        
[listboxStrategicGoals].[Value] & _
        ", '" & _
        Replace(Format(Trim(txtGoalObjectiveName)), "'", "''") & _
        "', '" & _
        Replace(Trim(Nz(txtGoalObjectiveDesc, "")), "'", "''") & _
        "', '" & _
        Replace(Trim(Nz(txtGoalObjectiveDesiredOutcome, "")), "'", "''") & _
        "');"
        
        'MsgBox ("strSQL: " & strSQL)
        
        'DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        'DoCmd.SetWarnings True
    
        ' store values for use in bridge table inserts
        
                Dim CommDeptIDfLV As Integer   ' part of unique key
                Dim StratGoalIDfLV As Integer  ' part of unique key
                Dim GoalObjectiveNameLV As String  ' part of unique key
                
                Dim rst As DAO.Recordset
                Dim NewIDs As Integer ' new ID of objectives insert
                Dim NewIDsSQL As String ' new ID of objectives insert
                
                CommDeptIDfLV = 
[listboxCommDepts].[Value]
                StratGoalIDfLV = 
[listboxStrategicGoals].[Value]
                GoalObjectiveNameLV = Replace(Format(Trim(txtGoalObjectiveName)), "'", "''")
                                
        ' re-get the ID of the inserted objective for use in bridge table inserts - use unique name key to obtain newly inserted objective record
                NewIDsSQL = "select GoalObjectiveID from dbo_GoalsObjectives where CommDeptIDf = " & CommDeptIDfLV & " AND StratGoalIDf = " & StratGoalIDfLV & " AND GoalObjectiveName = '" & GoalObjectiveNameLV & "';"
                Set rst = CurrentDb.OpenRecordset(NewIDsSQL, dbOpenSnapshot) ' open readonly copy of the table
                NewIDs = rst!GoalObjectiveID  ' store the column returned to the variable for use in bridge table insert
                rst.Close  ' clean up
                Set rst = Nothing

        ' after insert of main objective record, and capture of the pkeyID using candidate key/unique name key, add in the associated Areas of Emphasis to the Objectives-AoE bridge table...
                Dim strSQL2 As String

                If listboxAreaOfEmphasis.ItemsSelected.Count <> 0 Then ' if any items selected, then do inserts...
                    'MsgBox ("Inside Bridge Inserts")
                    For Each Item In listboxAreaOfEmphasis.ItemsSelected
                        strSQL2 = "INSERT INTO dbo_ObjectivesAreaOfEmphasis (GoalObjectiveIDf, AreaOfEmphasisIDf) VALUES (" & _
                        NewIDs & _
                        ", " & _
                        
[listboxAreaOfEmphasis].ItemData(Item) & _
                        ");"
                        
                        'MsgBox ("strSQL2 being INSERTED:     " & strSQL2)
                        'DoCmd.SetWarnings False
                        DoCmd.RunSQL strSQL2
                        'DoCmd.SetWarnings True
                    Next Item
                End If
        ' exit bridge inserts - clean up form and exit...
    
        ' probably need to unselect all the AoEs - may be able to just requery
        NewIDs = ""
        listboxAreaOfEmphasis.Requery
                
        txtGoalObjectiveName = ""
        txtGoalObjectiveDesc = ""
        txtGoalObjectiveDesiredOutcome = ""
        
        listboxGoalsObjectives.Requery
    Else
        MsgBox ("Enter Goal/Objectives info and press ADD.")
    End If

 

[pbaldy]

If you switch to the Execute method, you can test RecordsAffected. You can also switch to a recordset based method of adding the parent record which will let you get the ID more efficiently.

 

[madEG]

pbaldy,

Ok - I'm getting there...

For the parent/initial insert I replaced:

DoCmd.RunSQL strSQL

with:

On Error GoTo Err_Execute
CurrentDb.Execute strSQL, dbFailOnError

and added:

Err_Execute:
     
     ' Notify user of any errors that result from executing the query.
     If DBEngine.Errors.Count > 0 Then
        MsgBox ("insert failed")
        ' get out of here if the initial record fails...
     End If

...but the 'insert failed' msgbox always fires, even though the insert appears to happening just fine.

I guess I don't fully understand yet. Would you kindly give me another push?

 

[pbaldy]

Well, I didn't suggest testing for errors. More like

Dim db As DAO.Database

Set db = CurrentDb()
db.Execute strSQL, dbFailOnError
If db.RecordsAffected = 0 Then

 

[madEG]

Oh, I see what you mean.

So I did this:

        Set db = CurrentDb()
        db.Execute strSQL
        If db.RecordsAffected = 0 Then
            MsgBox ("Insert of Objective Failed.")
            Exit Sub
        Else
       '...carry on inserting children records...

And I think I'm all set. Thank you so much!

 

[pbaldy]

Happy to help!