Search code examples
ms-accessbuttonreplacesubform

Empty field for each related record in subform

Regarding a MS Access database.

  1. Master Form (Clients) contains fields Case and OriginalCase

  2. Master Form button #1 (Deactivate) copies value from Case to OriginalCase and clears Case value

  3. Master Form button #2 (Command297) copies value from OriginalCase back to Case (like restore backup)

  4. Master Form contains Subform (ClientDBHistory) showing multiple records per Client in Master Form (For each client in Master, it shows all related records from Subform)

  5. Subform has linked OriginalCase fields

I have tried using similar coding for Button #1 and it replaces only 1 value of the subform's Casenum and not all related ones. Button #2 didn't restore the value of subform's Casenum.

This was as follows:

Forms!Clients!ClientDBHistory.Form!OriginalCase = Forms!Clients!ClientDBHistory.Form!Casenum
Forms!Clients!ClientDBHistory.Form!Casenum = Null

Below is my code

Private Sub Command297_Click()
If IsNull(Me.[OriginalCase]) Then
MsgBox ("Cannot use this button if the Original Case field is empty. It will result in errors.")
ElseIf Not IsNull(Me.[OriginalCase]) Then
Me.[Casenum] = Me.[OriginalCase]
End If
End Sub

Private Sub Deactivate_Click()
If IsNull(Me.[Casenum]) Then
MsgBox ("Cannot use this button if the Casenum field is empty. It will result in errors.")
ElseIf Not IsNull(Me.[Casenum]) Then
Me.[OriginalCase] = Me.[Casenum]
Me.[Casenum] = Null
End If
End Sub

I want Buttons #1 and #2 to perform the same actions to all related records in the Subform. (Not new buttons).

I.e.

Master has Casenum 123456 Subform shows all related entries from 123456 on different dates

Button #1 copies Master's Casenum to OriginalCase and clears out Casenum. The same is then supposed to happen for all related entries 123456 in Subform, from Subform's Casenum to OriginalCase and clear out Subform's Casenum. Button #2 is to restore OriginalCase of both Master and Subform to the records (without clearing OriginalCase)

Solution

  • You have to look at this problem from the point of view of tables and not forms. You do not provide anything about your table structure so I can only provide generic guidance. What you need for your buttons is something like this:

    DoCmd.RunSQL "UPDATE [MyTable] SET [Values I want change] WHERE [limit changes to the records displayed in the form]"
Rem Repeat if you need to UPDATE more than one table and your table relationships are not set to update
Me.Requery

    Edit: This is a (IMO) simplified version of the code the OP used to solve the problem:

    DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblClient SET Casenum=OriginalCase WHERE OriginalCase=' & Me.OriginalCase & "'"
DoCmd.RunSQL "UPDATE tblPayments SET Casenum=OriginalCase WHERE OriginalCase=' & Me.OriginalCase & "'"
Me.Requery
DoCmd.SetWarnings True

    The simplification is based on running in the main form's context and that OriginalCase is a field that links form and sub-form and is therefore equal in both forms. I assume OriginalCase is a text field, otherwise the code can be simplified even further.

    As an added note, it is possible to permanently disable the warnings for running action queries instead of having to disable and enable them all the time.