Save changes - problem (1 Viewer)

[ReginaF]

Hello,

Recently I found a bug in my database, and I cannot seem to figure the solution out, so I would like ask for your help in this matter.

So in my database I have a form with a subform. When I click the save button I want the program to ask me if I want to save the changes I made, and in case I click NO, I want the records to remain the same. It worked really well until I noticed, that if I modify a record in the Main form and after that I do something in the subform, then if I click save, the program won’t ask for confirmation. Also “tied” to the save button I also have a sort of “validation rule” where I check if certain fields changed value, because in case I save the changes the program should make alterations to another field in light of the changes made in the record.

Now it would be really important that the program asks for confirmation, do you have any idea how can I resolve this problem?

Here is my existing code:

Private Sub saveBtn_Click()
'Validation rule
If Me.Izolálás_eredménye = "sikeres" And Nz(Me.Törzs_név, "") <> "" Then
If Nz(Me.Szubtípus.OldValue) <> Nz(Me.Szubtípus) Then
If MsgBox("A szubtipus modsitasaval modositani kell a törzsnevet is. Mented a szubtipus modosítását?", vbYesNo) = vbYes Then
Me.Törzs_név = Me.Előzetes_eredmény & "/Hungary/" & Me.Sorszám & "/" & Me.Izolálás_éve & "(" & Me.Szubtípus & ")"
Else
Me.Szubtípus = Me.Szubtípus.OldValue
End If
End If
End If

Dim MBszam As String
MBszam = Me!MBszám

'modositott rekord mentese
If Me.Dirty = True Then
If MsgBox(" Mented a változtatásokat?", vbYesNo) = vbYes Then
Me.Dirty = False 'with this we force access to save changes
Else
Me.Undo
End If
End If

DoCmd.Close acForm, "frm Main Edit"
DoCmd.OpenForm "frm Main Open", OpenArgs:=MBszam
End Sub


Thanks in advance,

Regina

 

[HiTechCoach]

Access by design does the following with Bound forms

1) When you move from the main/parent form to a subform control, the Parent form's record is automatically saved by Access.

2) In a subform, records are automatically saved by Access when you change records in a subform control.

You can ask to conform changes for a single record.

You can not conform multiple records at the same time, like with a form with subform control(s)

Based on how Access works with bound forms:

Your confirmation and validation code should be in the Form's Before Update event.

The Before Update event can be canceled to prevent updates with:

Cancel = True

 

[CJ_London]

As Boyd says, a form bound to a table or query will automatically update when you leave it, close it (or close access) or go to another record - moving to a subform means you have left the main form so an update (or insert) occurs - often necessary if the form/subform represents a parent/child relationship such as invoice header/invoice rows otherwise a relationship cannot be created so relationship integrity would not be maintained.

Not sure if the 'before update' solution is sufficient for your needs, but there are three ways that I know of to 'coordinate' not saving the main form until after the subform has been completed, but all involve a lot more code.

The first is to use an unbound form, perhaps with an array, collection or dictionary to store multiple rows and then have code to save all the records (main form first, subform second) when a button is clicked or similar.

The second is to use disconnected recordsets - again requires code. For continuous forms (such as your subform) using the latter is generally easier.

The 3rd way is using transactions - similar to using disconnected recordsets but using DAO rather than ADO. It also comes with some potential issues around locking.

Which is right for you depends on what your form is actually doing - if primarily editing I would suggest using transaction, if creating new records, probably disconnected recordsets.