okay, i think i'm beginning to get the picture. i'll guess that:
your main form F_SuiviModMission is bound to tblMissions (or bound to a
query that includes fields from tblMissions)
your subform F_FicheContrat sous-formulaire is bound to tblContrats
within main form F_SuiviModMission, in subform F_FicheContrat
sous-formulaire, you create new records in tblContrats by entering an
IDInterimaires value from tblInterimaires. in other words, you assigning a
temporary worker to a specific mission belonging to a specific client.
now, i'll assume that there is a field in tblInterimaires that shows the
current status of the temporary worker - either "Candidat" or "Interimaire".
(i'm guessing that more or less means "available for assignment" or
"currently assigned to a job".)
i'll also assume there are fields in tblContrats that hold the date a
particular job was assigned, and another date for when it was closed. or
perhaps a "Closed" checkbox. at any rate, *some value* in each record that
indicates whether the worker's assignment is current, or concluded.
given all the above assumptions, i would forget changing the status of the
worker's record from within main form F_Interimaires. instead, change the
status of the worker to "Interimaire" when the new record is added in
subform F_FicheContrat sous-formulaire, within main form F_SuiviModMission.
and then change the worker's status again when the contract is concluded.
you could do that with an update query, run on the subform's AfterUpdate
event, and referring to the value in field IDInterimaire to update the
correct record in tblInterimaires. something along the lines of
If Me!ClosedContract = False Then
CurrentDb.Execute "UPDATE tblInterimaires SET " _
& "WorkerStatus = 'Interimaires' WHERE IDInterimaires = " _
& Me!IDInterimaire, dbFailOnError
ElseIf Me!ClosedContract = True Then
CurrentDb.Execute "UPDATE tblInterimaires SET " _
& "WorkerStatus = 'Candidat' WHERE IDInterimaires = " _
& Me!IDInterimaire, dbFailOnError
End If
i made up the name WorkerStatus, of course, but the above should give you an
understanding of what i'm getting at. you can modify it to work in your
situation. you have to consider some possible situations here, such as:
once a contract record is created, can the IDInterimaire value be changed
(can you substitute Mary for Lisa, in an existing record)? if yes, you need
to write code to handle changing Lisa's status back to "Candidat", and
changing Mary's status to "Interimaire". once a contract record is created,
can it be deleted? or marked as "void"? in either case, you'd again have to
update the worker's status back to "Candidat".
hopefully my guesses are close enough to give you some ideas of how to
accomplish your task.
Frankie said:
Here is my table/relationships setup :
tblInterimaires (Temp Workers)
IDInterimaires (PK)
FirstName
LastName
Departement
Acticity
etc.
tblClients (Prospects/Customers)
IDClients (PK)
FirstName
LastName
etc.
tblMissions
IDMissions (PK)
IDClients (foreign key to tblClients)
Departement
Activity
etc.
tblContrats
IDContrat (PK)
IDMissions (foreign key to tblMissions)
IDInterimaire (foreign key to tblInterimaires)
tblCommercial (Sales Force)
IDCommercial (PK)
FirstName
LastName
etc.
tblSuiviCommercial (Prospects follow up)
IDSuivi (PK)
IDCommercial (foreign key to tblCommercial)
IDClients (foreign key to tblClients)
etc;
tblCRH (Contracts Hours )
IDCRH (PK)
IDContrat (foreign key to tblContrat)
tbl Interimaires [one-to-many] tblContrat
tblClients [one-to-many] tblMissions
tblMissions [one-to-many] tblContrat
tblCommerciaux [one-to-many] tblSuiviCommercial
tblClients [one-to-many] tblsuiviCommercial
tblContrat [one-to-many] tblCRH
