Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002
    Posts
    157

    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()

    'Forms![JobSeekerDetails]![usysbJobSeekerMODetails].Form![Active/InactiveMO]
    If Me.[Active/InactiveMO].Value = "Inactive" Then
    'Open form usysbJobSeekerPlacement
    OpenChildForm
    FilterChildForm
    Forms![usysbJobSeekerPlacement]![Active/Inactive].SetFocus
    'Update Jobseeker Placement table [cJSDetails3Placement]
    'Update cJSDetails3Placement.[Active/Inactive]
    Update Forms![usysbJobSeekerPlacement]![Active/Inactive]
    Set [Active/Inactive] = "Inactive"
    WHERE Forms![usysbJobSeekerPlacement]![MO_ID] = Forms![JobSeekerDetails]![usysbJobSeekerMODetails].Form![MO_ID]
    CloseChildForm

    End If
    End sub


    Thanks in advance for any assistance here.

    Regards

    Karen

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Karen,

    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.
    Code:
    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
                CurrentDb.Execute strSQL
            End If
            
            ' Clean up
            '
            Set frm = Nothing
        End If
        
    End Function
    If you remove every test, you can do it in one line:
    Code:
       CurrentDb.Execute "UPDATE cJSDetails3Placement SET [Active/Inactive] ='Inactive' WHERE " & Forms!usysbJobSeekerPlacement.Filter
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •