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...
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...
Code:
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
Last edited: