You could use the After Insert event to create the required foreign records
with VBA code, something like (the property SQLServer is used to determine
if we are working against a SQL-Server as the backend; this is also why the
option dbSeeChanges is used) :
Private Sub Form_AfterInsert()
' Création des fiches associées au premier conteneur:
OffreService_CreationFiches Me!NoOffreService
Me.Refresh ' Réactualise également les deux sous-formes.
' Ajuste les modes Insert et Deletion pour les deux sous-formes.
Call FrameQED_Ajustements
End Sub
Function OffreService_CreationFiches(ByVal NoOffreService As Long) As
Boolean
OffreService_CreationFiches = False ' En cas d'exit prématurée.
On Error GoTo label_erreur
If (NoOffreService = 0) Then
MsgBox "Erreur interne: OffreService_CreationFiches(), #1."
Exit Function
End If
Dim db As DAO.Database
Dim wk As DAO.Workspace
Dim tr As Boolean
Dim rs As DAO.Recordset
Set wk = DBEngine.Workspaces(0)
Set db = wk.Databases(0)
wk.BeginTrans
tr = True
Dim IdConteneur As Long
' Création de la fiche Conteneur suivie de celles des autres fiches
associées.
Set rs = db.OpenRecordset("Conteneurs", dbOpenDynaset, dbAppendOnly Or
dbSeeChanges)
rs.AddNew
rs!No = 1
rs!NoOffreService = NoOffreService
rs!DateService = DLookup("DateArriveePrevue", "OffresService",
"[NoOffreService]=" & NoOffreService)
#If SQLServer = 1 Then
rs.Update
rs.Bookmark = rs.LastModified
IdConteneur = rs!IdConteneur
#Else
IdConteneur = rs!IdConteneur
rs.Update
#End If
rs.Close
' Création de l'Avis de déplacement (1 par Conteneur).
Set rs = db.OpenRecordset("AvisDeplacement", dbOpenDynaset, dbAppendOnly
Or dbSeeChanges)
rs.AddNew
rs!IdConteneur = IdConteneur
Dim IdEmploye
IdEmploye = DLookup("IdEmploye", "Employes", "DefautAD=True")
If (Not IsNull(IdEmploye)) Then
rs!IdEmploye_Resp = IdEmploye
End If
IdEmploye = DLookup("IdEmploye", "Employes", "DefautOS=True")
If (Not IsNull(IdEmploye)) Then
rs!IdEmploye_Sign = IdEmploye
End If
rs.Update
rs.Close
' Création de l'Accusé de réception (1 par Conteneur).
Set rs = db.OpenRecordset("AccusesReception", dbOpenDynaset,
dbAppendOnly Or dbSeeChanges)
rs.AddNew
rs!IdConteneur = IdConteneur
rs!NoCentre = 0 ' Centre non-défini par défaut.
rs.Update
rs.Close
' Création du Rapport de fumigation (1 par Conteneur).
Set rs = db.OpenRecordset("RapportsFumigation", dbOpenDynaset,
dbAppendOnly Or dbSeeChanges)
rs.AddNew
rs!IdConteneur = IdConteneur
IdEmploye = DLookup("IdEmploye", "Employes", "DefautRappF=True")
If (Not IsNull(IdEmploye)) Then
rs!IdEmploye = IdEmploye
End If
rs.Update
rs.Close
' Création de l'Avis de relâchement (1 par Conteneur).
Set rs = db.OpenRecordset("AvisRelachement", dbOpenDynaset, dbAppendOnly
Or dbSeeChanges)
rs.AddNew
rs!IdConteneur = IdConteneur
rs.Update
rs.Close
tr = False
wk.CommitTrans
OffreService_CreationFiches = True
label_exit:
Exit Function
label_erreur:
MsgBox Err.Number & " - " & Err.description
If (tr) Then wk.Rollback
MsgBox "La première fiche « Conteneur » associée à cette nouvelle offre
de service n'a pu être créée.", vbCritical
Resume label_exit
End Function
In this code, the creation of records by VBA code have been enclosed in a
transaction. However, to be truly OK, the creation of the initial record
(OffreService) should also be part of the transaction but this is impossible
to do with a bound form under Access. If you want to, you could correct
this by forcing the user to click on a button for the creation of a new set
of records (and deactivate the possibility of insertion on the form) and
enclose all these in a single transaction.
