Undo subform from main form

[Darryl Kerkeslager]

I don't do much with subforms - in fact I've deliberately avoided them - but
....

I have a tab control that will contain several subforms, each bound to a
separate table, and each table/subform will relate to the same person - but
the subform data will not be 'linked' to the main form.

I want to have one Cancel button for all the undo operations on all the
subforms, so the individul subform's Public Sub, Form_Cancel will be called
depending on the subform to be cancelled:

Select Case Me.tabSupervision.Value ' main form's tab control
Case PROGRAMS
Me.ProgramSub.Form.Form_Cancel
Case DRUG_TESTS
Me.DrugSub.Form.Form_Cancel

etc...

Okay .. now, since the subform loses focus when the Cancel button is
selected on the main form, I'm having trouble easily undoing the data
changes. In the subform's Form_Cancel sub, Me.Undo doesn't work. I thought
setting the value = OldValue would work, but ... well, it doesn't.

So what will?
Darryl Kerkeslager

[PC Datasheet]

Undo only works if the current record has not yet been saved. When you go
from a subform to another subform or to the main form, the record on that
subform gets saved and consequently you can no longer undo it. The same
principle applies to the old value. Once a record gets saved, the old value
of all the controls is lost and the "old value" becomes the value that got
saved or the current value shown on the screen.

To answer your question, all you can do is delete the record shown on the
screen:
Select Case Me.tabSupervision.Value ' main form's tab control
Case PROGRAMS
Me!ProgramSub.SetFocus
Me!ProgramSub!NameOfSomeFieldOnProgramSub
DoCmd.RunCommand acDeleteRecord
Case DRUG_TESTS
Me.DrugSub.SetFocus
Me!DrugSub!NameOfSomeFieldOnDrugSub
DoCmd.RunCommand acDeleteRecord

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com


"Darryl Kerkeslager" <Ke*********@comcast.net> wrote in message
news:f-********************@comcast.com...

I don't do much with subforms - in fact I've deliberately avoided them - but ...

I have a tab control that will contain several subforms, each bound to a
separate table, and each table/subform will relate to the same person - but the subform data will not be 'linked' to the main form.

I want to have one Cancel button for all the undo operations on all the
subforms, so the individul subform's Public Sub, Form_Cancel will be called depending on the subform to be cancelled:

Select Case Me.tabSupervision.Value ' main form's tab control
Case PROGRAMS
Me.ProgramSub.Form.Form_Cancel
Case DRUG_TESTS
Me.DrugSub.Form.Form_Cancel

etc...

Okay .. now, since the subform loses focus when the Cancel button is
selected on the main form, I'm having trouble easily undoing the data
changes. In the subform's Form_Cancel sub, Me.Undo doesn't work. I thought setting the value = OldValue would work, but ... well, it doesn't.

So what will?
Darryl Kerkeslager

[Darryl Kerkeslager]

Doesn't this newsgroup ever sleep? ;)

I was hoping there was an easier way that I was missing. My FE stores
records for the current person being edited in cloned tables of the BE, with
just the current person's records. If I delete the current record, I have
to restore it somehow - from stored variables, reloading from the BE (uck!),
text file, or a temp table. Or ... perhaps if, when the record is dirtied,
I copied the current record to a new record with "impossible" ID of 0, just
store the ID in a varaible, then change the ID number of record 0 to the
current ID if it needs to be deleted on Undo ... hmmm ..

Thanks, Steve.
Darryl Kerkeslager

"PC Datasheet" <no****@nospam.spam> wrote:

Undo only works if the current record has not yet been saved. When you go
from a subform to another subform or to the main form, the record on that
subform gets saved and consequently you can no longer undo it. The same
principle applies to the old value. Once a record gets saved, the old value of all the controls is lost and the "old value" becomes the value that got
saved or the current value shown on the screen.

To answer your question, all you can do is delete the record shown on the
screen:
Select Case Me.tabSupervision.Value ' main form's tab control
Case PROGRAMS
Me!ProgramSub.SetFocus
Me!ProgramSub!NameOfSomeFieldOnProgramSub
DoCmd.RunCommand acDeleteRecord
Case DRUG_TESTS
Me.DrugSub.SetFocus
Me!DrugSub!NameOfSomeFieldOnDrugSub
DoCmd.RunCommand acDeleteRecord

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com


"Darryl Kerkeslager" <Ke*********@comcast.net> wrote in message
news:f-********************@comcast.com...

I don't do much with subforms - in fact I've deliberately avoided them -

but

...

I have a tab control that will contain several subforms, each bound to a
separate table, and each table/subform will relate to the same person -

but

the subform data will not be 'linked' to the main form.

I want to have one Cancel button for all the undo operations on all the
subforms, so the individul subform's Public Sub, Form_Cancel will be

called

depending on the subform to be cancelled:

Select Case Me.tabSupervision.Value ' main form's tab control
Case PROGRAMS
Me.ProgramSub.Form.Form_Cancel
Case DRUG_TESTS
Me.DrugSub.Form.Form_Cancel

etc...

Okay .. now, since the subform loses focus when the Cancel button is
selected on the main form, I'm having trouble easily undoing the data
changes. In the subform's Form_Cancel sub, Me.Undo doesn't work. I

thought

setting the value = OldValue would work, but ... well, it doesn't.

So what will?
Darryl Kerkeslager

[Bas Cost Budde]

Darryl Kerkeslager wrote:

Doesn't this newsgroup ever sleep? ;)
Here you have a splendid advantage of world-wide netting! :-)
I was hoping there was an easier way that I was missing. My FE stores
records for the current person being edited in cloned tables of the BE, with
just the current person's records. If I delete the current record, I have
to restore it somehow - from stored variables, reloading from the BE (uck!),
text file, or a temp table. Or ... perhaps if, when the record is dirtied,
I copied the current record to a new record with "impossible" ID of 0, just
store the ID in a varaible, then change the ID number of record 0 to the
current ID if it needs to be deleted on Undo ... hmmm ..

Undo is not simple! At least not to the designer. I have been trying to
implement a limited form of Undo to one of my projects, but every method
that changes information must register its change in a way to a central
undo object, otherwise it won't work.

You could consider storing the record you offer for edit to a buffer
first. Yes, as you say, copy with the impossible ID. I think that is
closest.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[rkc]

Darryl Kerkeslager wrote:

Doesn't this newsgroup ever sleep? ;)

I was hoping there was an easier way that I was missing. My FE stores
records for the current person being edited in cloned tables of the BE, with
just the current person's records. If I delete the current record, I have
to restore it somehow - from stored variables, reloading from the BE (uck!),
text file, or a temp table. Or ... perhaps if, when the record is dirtied,
I copied the current record to a new record with "impossible" ID of 0, just
store the ID in a varaible, then change the ID number of record 0 to the
current ID if it needs to be deleted on Undo ... hmmm ..

Things can of course be more complicated, but the following code sets
up a pretty simple basic undo system for a single form.

<Form module>
Private oldValues As VBA.Collection

Private Sub Form_Load()
Call loadOldValues
End Sub

Private Sub Form_Current()
Call loadOldValues
End Sub

Private Sub loadOldValues()
Dim ctl As Access.Control
Set oldValues = New VBA.Collection

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
oldValues.Add ctl.Value & "", ctl.Name
End If
Next
End Sub

Public Sub restore()
Dim ctl As Access.Control

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
ctl.Value = oldValues.Item(ctl.Name)
End If
Next

Set ctl = Nothing
End Sub

</Form module>

A call to the sub form's restore() from a Main form command button
could look something like the following taking advantage of the fact
that the sub form control is returned by Screen.PreviousControl if a
control on a sub form has the focus. The error handling is used as a
way to avoid a Select Case statement.

Private Sub Undo_Click()
Dim n As String
On Error GoTo exitHere
n = Screen.PreviousControl.Name

Call Me(n).Form.restore

exitHere:

End Sub

[Darryl Kerkeslager]

Thanks, Bas.

I just read one of your other posts that mentioned English-language usage,
and then I re-read my own post here, where I've used some sort of
stream-of-consciousness run-on sentence that is an exteremely poor
representative of proper English. It occurs to me that such usage is rather
rude to non-native English speakers, who have consented to using English,
and now have to contend with my poor grammar.

I will also keep this in mind, should I try to brush up on my very limited
Spanish skills, that a newsgroup is probably not the best place to learn
proper syntax.
Darryl Kerkeslager
"Bas Cost Budde" <b.*********@heuvelqop.nl> wrote:

Darryl Kerkeslager wrote:

Doesn't this newsgroup ever sleep? ;)

Here you have a splendid advantage of world-wide netting! :-)

I was hoping there was an easier way that I was missing. My FE stores
records for the current person being edited in cloned tables of the BE, with just the current person's records. If I delete the current record, I have to restore it somehow - from stored variables, reloading from the BE (uck!), text file, or a temp table. Or ... perhaps if, when the record is dirtied, I copied the current record to a new record with "impossible" ID of 0, just store the ID in a varaible, then change the ID number of record 0 to the
current ID if it needs to be deleted on Undo ... hmmm ..

Undo is not simple! At least not to the designer. I have been trying to
implement a limited form of Undo to one of my projects, but every method
that changes information must register its change in a way to a central
undo object, otherwise it won't work.

You could consider storing the record you offer for edit to a buffer
first. Yes, as you say, copy with the impossible ID. I think that is
closest.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Bas Cost Budde]

Darryl Kerkeslager wrote:

Thanks, Bas.

I just read one of your other posts that mentioned English-language usage,
and then I re-read my own post here, where I've used some sort of
stream-of-consciousness run-on sentence that is an exteremely poor
representative of proper English. It occurs to me that such usage is rather
rude to non-native English speakers, who have consented to using English,
and now have to contend with my poor grammar.

I will also keep this in mind, should I try to brush up on my very limited
Spanish skills, that a newsgroup is probably not the best place to learn
proper syntax.

Not for human language :-)

Or, it is indeed: to get a wider view of how your language can be used.
At least you have the opportunity (I suppose your native language is
English), I seldom do.

For non-native speakers, it is easier to produce basic sentences with
limited vocabulary, that stand a higher chance to be understood by the
majority of the non-native-english community.
In air traffic, a severely limited and strongly formalized subset of
English is in use. Even the pronounciation has been formalized. The risk
is too high.
I can think of very few reasons people would suffer or perish from
programming failure; yet for the same reason, adaptation is a good thing
to have in your toolbag. I try my best English when I have the feeling
I'm talking to someone who outdoes me; but I try my worst, so to say,
with others.

Sometimes I read aloud others' posts just to understand them. English is
not easy to read because there is so little structural difference for
functional difference. Other languages have endings that are hard to
learn to use, but easy on the reader IFF you get what they are for.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Darryl Kerkeslager]

Thanks. I had not considered using a Collection. Very clean solution. I
thought this ought to be less complicated than what I was toying with, but I
couldn't find the key.
Darryl Kerekslager
"rkc" <rk*@rochester.yabba.dabba.do.rr.bomb> wrote

Darryl Kerkeslager wrote:

Doesn't this newsgroup ever sleep? ;)

I was hoping there was an easier way that I was missing. My FE stores
records for the current person being edited in cloned tables of the BE, with just the current person's records. If I delete the current record, I have to restore it somehow - from stored variables, reloading from the BE (uck!), text file, or a temp table. Or ... perhaps if, when the record is dirtied, I copied the current record to a new record with "impossible" ID of 0, just store the ID in a varaible, then change the ID number of record 0 to the
current ID if it needs to be deleted on Undo ... hmmm ..

Things can of course be more complicated, but the following code sets
up a pretty simple basic undo system for a single form.

<Form module>
Private oldValues As VBA.Collection

Private Sub Form_Load()
Call loadOldValues
End Sub

Private Sub Form_Current()
Call loadOldValues
End Sub

Private Sub loadOldValues()
Dim ctl As Access.Control
Set oldValues = New VBA.Collection

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
oldValues.Add ctl.Value & "", ctl.Name
End If
Next
End Sub

Public Sub restore()
Dim ctl As Access.Control

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
ctl.Value = oldValues.Item(ctl.Name)
End If
Next

Set ctl = Nothing
End Sub

</Form module>

A call to the sub form's restore() from a Main form command button
could look something like the following taking advantage of the fact
that the sub form control is returned by Screen.PreviousControl if a
control on a sub form has the focus. The error handling is used as a
way to avoid a Select Case statement.

Private Sub Undo_Click()
Dim n As String
On Error GoTo exitHere
n = Screen.PreviousControl.Name

Call Me(n).Form.restore

exitHere:

End Sub

[PC Datasheet]

Be Careful!

That procedure only undoes textboxes. If you have other controls on your
form such as comboboxes, they DO NOT get undone and when you leave the
subform a record gets saved with the values in all the controls that are not
textboxes.

Steve
PC Datasheet

"Darryl Kerkeslager" <Ke*********@comcast.net> wrote in message
news:y7********************@comcast.com...

Thanks. I had not considered using a Collection. Very clean solution. I
thought this ought to be less complicated than what I was toying with, but I couldn't find the key.
Darryl Kerekslager
"rkc" <rk*@rochester.yabba.dabba.do.rr.bomb> wrote

Darryl Kerkeslager wrote:

Doesn't this newsgroup ever sleep? ;)

I was hoping there was an easier way that I was missing. My FE stores
records for the current person being edited in cloned tables of the BE,

with

just the current person's records. If I delete the current record, I have to restore it somehow - from stored variables, reloading from the BE (uck!), text file, or a temp table. Or ... perhaps if, when the record is dirtied, I copied the current record to a new record with "impossible" ID of 0, just store the ID in a varaible, then change the ID number of record 0 to

the current ID if it needs to be deleted on Undo ... hmmm ..

Things can of course be more complicated, but the following code sets
up a pretty simple basic undo system for a single form.

<Form module>
Private oldValues As VBA.Collection

Private Sub Form_Load()
Call loadOldValues
End Sub

Private Sub Form_Current()
Call loadOldValues
End Sub

Private Sub loadOldValues()
Dim ctl As Access.Control
Set oldValues = New VBA.Collection

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
oldValues.Add ctl.Value & "", ctl.Name
End If
Next
End Sub

Public Sub restore()
Dim ctl As Access.Control

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
ctl.Value = oldValues.Item(ctl.Name)
End If
Next

Set ctl = Nothing
End Sub

</Form module>

A call to the sub form's restore() from a Main form command button
could look something like the following taking advantage of the fact
that the sub form control is returned by Screen.PreviousControl if a
control on a sub form has the focus. The error handling is used as a
way to avoid a Select Case statement.

Private Sub Undo_Click()
Dim n As String
On Error GoTo exitHere
n = Screen.PreviousControl.Name

Call Me(n).Form.restore

exitHere:

End Sub

[Darryl Kerkeslager]

I assume that you mean that I just need to check combos using the
appropriate Access constant, or is there a "gotcha" particular to combos?

Darryl Kerkeslager

"PC Datasheet" <no****@nospam.spam> wrote:

Be Careful!

That procedure only undoes textboxes. If you have other controls on your
form such as comboboxes, they DO NOT get undone and when you leave the
subform a record gets saved with the values in all the controls that are not textboxes.

Steve
PC Datasheet

"Darryl Kerkeslager" <Ke*********@comcast.net> wrote in message
news:y7********************@comcast.com...

Thanks. I had not considered using a Collection. Very clean solution. I

thought this ought to be less complicated than what I was toying with, but

I

couldn't find the key.
Darryl Kerekslager
"rkc" <rk*@rochester.yabba.dabba.do.rr.bomb> wrote

Darryl Kerkeslager wrote:
> Doesn't this newsgroup ever sleep? ;)
>
> I was hoping there was an easier way that I was missing. My FE stores > records for the current person being edited in cloned tables of the

BE,

with

> just the current person's records. If I delete the current record,

I have

> to restore it somehow - from stored variables, reloading from the BE

(uck!),

> text file, or a temp table. Or ... perhaps if, when the record is

dirtied,

> I copied the current record to a new record with "impossible" ID of

0, just

> store the ID in a varaible, then change the ID number of record 0 to

the > current ID if it needs to be deleted on Undo ... hmmm ..

Things can of course be more complicated, but the following code sets
up a pretty simple basic undo system for a single form.

<Form module>
Private oldValues As VBA.Collection

Private Sub Form_Load()
Call loadOldValues
End Sub

Private Sub Form_Current()
Call loadOldValues
End Sub

Private Sub loadOldValues()
Dim ctl As Access.Control
Set oldValues = New VBA.Collection

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
oldValues.Add ctl.Value & "", ctl.Name
End If
Next
End Sub

Public Sub restore()
Dim ctl As Access.Control

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
ctl.Value = oldValues.Item(ctl.Name)
End If
Next

Set ctl = Nothing
End Sub

</Form module>

A call to the sub form's restore() from a Main form command button
could look something like the following taking advantage of the fact
that the sub form control is returned by Screen.PreviousControl if a
control on a sub form has the focus. The error handling is used as a
way to avoid a Select Case statement.

Private Sub Undo_Click()
Dim n As String
On Error GoTo exitHere
n = Screen.PreviousControl.Name

Call Me(n).Form.restore

exitHere:

End Sub

[rkc]

Darryl Kerkeslager wrote:

Thanks. I had not considered using a Collection. Very clean solution. I
thought this ought to be less complicated than what I was toying with, but I
couldn't find the key.

As PC Datasheet pointed out the code I posted only handles
textbox controls. I figured that was obvious and didn't
bother to clutter things up with Or's or a Select Case.
Anyhow, since then I've thought about it a bit and would
probably handle things by checking to see if the control
had a data source. Something like:

On Error Resume Next
For Each ctl In f.Controls
If Len(ctl.ControlSource & vbNullString) > 0 Then
ctl.Value = oldValues.Item(ctl.Name)
End If
Next

[Bas Cost Budde]

Darryl Kerkeslager wrote:

I assume that you mean that I just need to check combos using the
appropriate Access constant, or is there a "gotcha" particular to combos?

I suggest to not test for controltype=acWhatever, but for a filled
Controlsource. That way you save every bound control.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Bas Cost Budde]

rkc wrote:

As PC Datasheet pointed out the code I posted only handles
textbox controls. I figured that was obvious and didn't
bother to clutter things up with Or's or a Select Case.
Anyhow, since then I've thought about it a bit and would
probably handle things by checking to see if the control
had a data source. Something like:

Umm, that sort of thing.

Slight disadvantage of forking threads/not reading in order of arrival :-)

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Darryl Kerkeslager]

As it turns out, I'll need to use the first method, since I have some
unbound textboxes that are filled from the hidden columns of a bound
combobox (no point in going to the effort of releading the textboxes), but I
do like the simplicity of checking the control source. It's one of those
obvoius solutions that I might overlook - I think my first thought is
usually to cover all the bases (textbox+combobox+checkbox), rather than find
the common denominator (has control source).
Darryl Kerkeslager
"rkc" <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in message
news:Ls*****************@twister.nyroc.rr.com...

Darryl Kerkeslager wrote:

Thanks. I had not considered using a Collection. Very clean solution. I thought this ought to be less complicated than what I was toying with, but I couldn't find the key.

As PC Datasheet pointed out the code I posted only handles
textbox controls. I figured that was obvious and didn't
bother to clutter things up with Or's or a Select Case.
Anyhow, since then I've thought about it a bit and would
probably handle things by checking to see if the control
had a data source. Something like:

On Error Resume Next
For Each ctl In f.Controls
If Len(ctl.ControlSource & vbNullString) > 0 Then
ctl.Value = oldValues.Item(ctl.Name)
End If
Next

[Darryl Kerkeslager]

I have some unbound data to save also, but the controlsource is certainly a
more elegant solution.
Darryl Kerkeslager
"Bas Cost Budde" <b.*********@heuvelqop.nl> wrote in message
news:cq**********@news2.solcon.nl...

Darryl Kerkeslager wrote:

I assume that you mean that I just need to check combos using the
appropriate Access constant, or is there a "gotcha" particular to

combos?
I suggest to not test for controltype=acWhatever, but for a filled
Controlsource. That way you save every bound control.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Darryl Kerkeslager]

> Slight disadvantage of forking threads/not reading in order of arrival :-)
I often read in reverse order, and have done the same thing. __it happens.
Darryl Kerkeslager

[Bas Cost Budde]

Darryl Kerkeslager wrote:

I have some unbound data to save also, but the controlsource is certainly a
more elegant solution.

Hey? Where do you save unbound data?

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Darryl Kerkeslager]

I have a combobox of agencies. Only the name displays, but the hidden
columns contain the address and phone number. In the After Update for the
combo, I fill some unbound textboxes with the address and phone info. If I
do an undo, I just want to save all the address and phone info in the
Collection, and repopulate those textboxes from the Collection, not again by
Me.txtAddress = Me.cboAgency.column(2)
Darryl Kerkeslager
"Bas Cost Budde" <b.*********@heuvelqop.nl> wrote in message
news:cq**********@news2.solcon.nl...

Darryl Kerkeslager wrote:

I have some unbound data to save also, but the controlsource is certainly a more elegant solution.

Hey? Where do you save unbound data?

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[rkc]

Darryl Kerkeslager wrote:

As it turns out, I'll need to use the first method, since I have some
unbound textboxes that are filled from the hidden columns of a bound
combobox (no point in going to the effort of releading the textboxes), but I
do like the simplicity of checking the control source. It's one of those
obvoius solutions that I might overlook - I think my first thought is
usually to cover all the bases (textbox+combobox+checkbox), rather than find
the common denominator (has control source).

This is why I began my original reply with things could get
more complicated. It would be easy enough to write a class
that handled all bound controls and even monitored the forms
events so that the client code was reduced to simply creating
the "undo object" and delegating a restore() method to that
object's restore() method. But, then there are cases like yours
where that isn't enough and you'd need to add to the class or
re-create similar code to handle the additional requirements.