I have a main form Containers with a subform ContainerSubstances. The latter
has a combo box showing the substances in the Substances table. If the
desired substance is not in the combo, the user may bring up the Substances
form in Add and Dialog mode. The user can press Save or Cancel from the
latter form, and both work as designed.

If Save is pressed, the combo box on the original subform is requeried to
include the new value, and then the new value is selected by the code, and
the Substances form closes, and the user returns to the Containers form, with
focus set in the subform. All of this works.

The Containers form has its own set of Save/Cancel buttons. If its Cancel
button is pressed, any changes made on the Main form are undone, but not the
subform, including the change to the combo box.

Private Sub cmdCancel_Click()
On Error GoTo Err_Handler

If Me.Dirty = True Then
Me.sbfContainerSubstances.Form.Undo
Me.Undo
End If

DoCmd.Close acForm, Me.Name

Err_Exit:
Exit Sub

Err_Handler:
Select Case Err.Number
Case Else
MsgBox Err.Number & vbCrLf & Err.Description
Resume Err_Exit
End Select

End Sub

Does anyone have a suggestion? Do I need to copy the state of the subform
to an array temporarily, and reset it?

Thank you.

Best regards,
Sprinks

The problem is that once you move the cursor out of the subform to click the
save button on the main form, that makes access trigger the subform to save
the record.
Usually this is what we all want in our apps.

Same type of thing happens when you move the cursor out of the main form
into the subform, access saves the main form.

I have very occasionally put an undo button on the subform.
You can instead write code to delete all the related subform records for
that main form record (I think I have only ever done this once).
This code would run when you click the undo button on the main form.
Then you would requery the subform.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

"Sprinks" <(E-Mail Removed)> wrote in message
news:97FBDC32-378D-4EEA-A79D-(E-Mail Removed)...
>I have a main form Containers with a subform ContainerSubstances. The
>latter
> has a combo box showing the substances in the Substances table. If the
> desired substance is not in the combo, the user may bring up the
> Substances
> form in Add and Dialog mode. The user can press Save or Cancel from the
> latter form, and both work as designed.
>
> If Save is pressed, the combo box on the original subform is requeried to
> include the new value, and then the new value is selected by the code, and
> the Substances form closes, and the user returns to the Containers form,
> with
> focus set in the subform. All of this works.
>
> The Containers form has its own set of Save/Cancel buttons. If its Cancel
> button is pressed, any changes made on the Main form are undone, but not
> the
> subform, including the change to the combo box.
>
> Private Sub cmdCancel_Click()
> On Error GoTo Err_Handler
>
> If Me.Dirty = True Then
> Me.sbfContainerSubstances.Form.Undo
> Me.Undo
> End If
>
> DoCmd.Close acForm, Me.Name
>
> Err_Exit:
> Exit Sub
>
> Err_Handler:
> Select Case Err.Number
> Case Else
> MsgBox Err.Number & vbCrLf & Err.Description
> Resume Err_Exit
> End Select
>
> End Sub
>
> Does anyone have a suggestion? Do I need to copy the state of the subform
> to an array temporarily, and reset it?
>
> Thank you.
>
> Best regards,
> Sprinks