Unanswered: Updating Multiple records in a sub form
I have the following forms:
(1) JobSeekerDetails which has a table record source of cJSDetails1
(2) usysbJobSeekerMODetails which has a table record source of cJSDetails2JSMODetails
(3) usysbJobSeekerPlacement which has a query record source of usysbcJSDetails3PlacementQ (the base table is cJSDetails3Placement)
The JobSeeker Details form is the parent form.
The usysbJobSeekerMODetails is the child form.
The usysbJobSeekerPlacement is the child form (of usysbJobSeekerMODetails)
usysbJobSeekerMODetails has two fields: [MO_ID] and [Active/InactiveMO].
usysbJobSeekerPlacement has two fields: [MO_IDHold] and [Active/Inactive]
Thanks to Sinndho (from this forum) I have some code which from the usysbJobSeekerMODetails form opens and filters the subform (usysbJobSeekerPlacement). So please take the OpenChildForm and FilterChildForm and CloseChildForm subroutines as correct.
I want to create an on change event which when the [Active/InactiveMO] field (on the usysbJobSeekerMODetails form) is changed to "Inactive" then all records in the cJSDetails3Placement table (or filtered records on the usysbJobSeekerPlacement form) which have the same MO_ID are changed to "Inactive".
As you can see from the following code some of which has been commented out, I have been trying all sorts of things to make this happen. Can anyone help me with this?
Private Sub Active_InactiveMO_Change()
If Me.[Active/InactiveMO].Value = "Inactive" Then
'Open form usysbJobSeekerPlacement
'Update Jobseeker Placement table [cJSDetails3Placement]
Set [Active/Inactive] = "Inactive"
WHERE Forms![usysbJobSeekerPlacement]![MO_ID] = Forms![JobSeekerDetails]![usysbJobSeekerMODetails].Form![MO_ID]
Here is a function that will do the job. I've created it Public in a module, so it's callable from everywhere in the application but you can place it wherever you want, making it private if necessary. I've also added tests so that the function only executes if the form [usysbJobSeekerPlacement] is open and filtered. You can remove these tests if you consider them being superfluous.
Public Function Update_cJSDetails3Placement()
Dim frm As Form
Dim strSQL As String
' Check if form is loaded.
If SysCmd(acSysCmdGetObjectState, acForm, "usysbJobSeekerPlacement") <> 0 Then
Set frm = Forms!usysbJobSeekerPlacement
' Check if form is filtered.
If frm.Filter <> "" And frm.FilterOn = True Then
' Create SQL statement and execute it.
strSQL = "UPDATE cJSDetails3Placement SET [Active/Inactive] ='Inactive' WHERE " & frm.Filter
' Clean up
Set frm = Nothing
If you remove every test, you can do it in one line:
CurrentDb.Execute "UPDATE cJSDetails3Placement SET [Active/Inactive] ='Inactive' WHERE " & Forms!usysbJobSeekerPlacement.Filter