Forcing Form Updates

[TrekBiker]

Form ‘For Booking Out’ uses order details that need to be inserted into three separate tables for a new Cask.

A Cask ID is entered then ‘Update Own Cask Tables’ puts fields into the appropriate tables, eg Casks Racked/Sold needs Date Sold and Order Detail ID from the first form.
If wanting to undo the changes immediately, clicking ‘Undo All Changes’ displays the changes properly. But closing this form and clicking ‘Show Update Results’ shows the changes have been ignored; ie the table fields still have the updates.

If instead I close the second form after it’s received the updates, then click ‘Show Update Results’ in the first form the results are correct, with the updates undone.

The second form has Save instructions as below but they're not covering both methods.

Private Sub cmdReverseChanges_Click()

    If Me.Dirty = True Then
        Me.Dirty = False
    End If

    If MsgBox("Are you sure you want to undo the updates?", vbYesNo) = vbNo Then
        Exit Sub
    End If
    
    DoCmd.SetWarnings False
    
   'Update queries for the three tables
    DoCmd.OpenQuery "qryResetCaskPopulation"
    DoCmd.OpenQuery "qryResetCasksRackedSold"
    DoCmd.OpenQuery "qryResetOrderDetails"

    DoCmd.SetWarnings True
    
   'Ensure updates are displayed in subforms
    Me.sfmUpdatedCaskRackedSold.Form.Requery
    Me.sfmUpdatedCaskPopulation.Form.Requery
    Me.sfmUpdatedOrderDetails.Form.Requery
    
    DoCmd.Save
    
   'Ensure changes are reflected in first form
    Forms!frmBookOut.Requery
    
End Sub

 

[dhookom]

I rarely store the same data in two different tables other than primary/foreign key values. I assume you understand normalization and your tables.

I don’t believe your code regarding “Dirty” on a main form has any effect on the subforms.

I would set a break point in the code to step through the lines while checking the values in the tables.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

Thanks Duane, and your comments on the tables are absolutely correct. Unfortunately they were created before I became involved and are full of bad practices. I'd like to rebuild the tables but there's a whole separate system for barcode readers feeding into the table fields as they are, and I daren't undertake risk disturbing them. Other horrors include using lookups in some table fields, like displaying a product's name in the ID field, adding complications elsewhere.

Back to the question, I'd had the second 'Updates for Own Products' being opened by the first form. I tried adding the Dirty trick to each subform like this.

        If Me!sfmUpdatedCaskPopulation.Form.Dirty = True Then
            Me!sfmUpdatedCaskPopulation.Form.Dirty = False
        End If

The form showed that the updates appeared to have been done but in fact looking at the tables they hadn't.

I've now stopped opening the Updates form immediately at the end of the 'Update Own Casks Tables' procedure. Instead, if I use the 'Show Update Results' and click the 'Undo All Changes' button it works properly. This isn't ideal but it's a solution.