Me.Dirty Don't Work

[John S]

I have a form / subform (datasheet boumd to a temporary table), with a save
button, and I need a way of determining if anything has been changed in the
form (to warn users about to exit to save their changes). I hoped the
following code would work in the form unload event of the main form, but it
doesnt:


If Me.Dirty Then
anInt = MsgBox ("Va tu sauvez vos changements", VbYesNo)
End If

I get a 2455 error.

I tried shifting the focus to the main form (a Google search suggested that
focus could be a problem). I tried putting the me!dirty clause in subform
events. Nada.

Is there any way to make use of me.dirty in this kind of form/subform
(datasheet) arrangement? Otherwise, is there any way of determining if there
has been a change in the form and subform (other than some kind of esoteric
recordset matching of the original and current data)?

John S
Aylmer, PQ

 

[PC Datasheet]

John,

Try removing the parantheses in your MsgBox statement. If you still get an
error, post back with specifically what the error says.

 

[Dirk Goldgar]

I have a form / subform (datasheet boumd to a temporary table), with
a save button, and I need a way of determining if anything has been
changed in the form (to warn users about to exit to save their
changes). I hoped the following code would work in the form unload
event of the main form, but it doesnt:


If Me.Dirty Then
anInt = MsgBox ("Va tu sauvez vos changements", VbYesNo)
End If

I get a 2455 error.

I tried shifting the focus to the main form (a Google search
suggested that focus could be a problem). I tried putting the
me!dirty clause in subform events. Nada.

Is there any way to make use of me.dirty in this kind of form/subform
(datasheet) arrangement? Otherwise, is there any way of determining
if there has been a change in the form and subform (other than some
kind of esoteric recordset matching of the original and current data)?

John S
Aylmer, PQ

Is the main form unbound? An unbound form has no Dirty property,
because it can never be "dirty" in the sense of having unsaved data.
It's not clear to me whether there's anything on the main form that must
be saved, or whether it's just the subform data that must be saved
(presumably by copying it from the temp table to a "live" one).

If you'll explain in more detail about how you have these forms set up
and how they're supposed to work, we could probably figure out how to
check for "dirtiness".

 

[John S]

You are right: the main form is unbound, and the sub form is bound to a
temporary table. This is all in order to use a save button for both, and to
conduct validation on items in the data sheet (and the main form).

I'm sure you are right about "me.dirty" (excuse my ignorance): a serious
option to verify if changes have occurred is to retain the original input in
record sets and to compare the controls against the original recordsets. But
this seems a very esoteric option (and is probably "over the top"). Is there
no way of polling the controls for change?

Ah, "save button madness" continues.

Thanks for the advice.

John S.
Aylmer, PQ

 

[John S]

You're right of course: the main form is unbound, and the subform bound to
a temporary table and thus (as you point out) "me.dirty" don't work.

My problem is that I need a save button to do validation on the form and
subform, but I also need a way to signal the user to save changes when they
forget.

I could try something a tad esoteric: keep a copy of the original record
sets, and compare control values with this original copy on exit, etc. You
think there would be some way of polling the controls to identify change, or
something.

Savebutton madness continues. Thanks again for straightening me out.

John S
Aylmer, PQ

 

[Dan Artuso]

Hi John,
The AfterUpdate and Change events still fire for an unbound control.
Use a module level 'IsDirty' boolean variable.
Set it to True when the user changes any value and back to False when they've
clicked the Save button.

 

[Rick Brandt]

You're right of course: the main form is unbound, and the subform bound to
a temporary table and thus (as you point out) "me.dirty" don't work.

My problem is that I need a save button to do validation on the form and
subform, but I also need a way to signal the user to save changes when they
forget.

I could try something a tad esoteric: keep a copy of the original record
sets, and compare control values with this original copy on exit, etc. You
think there would be some way of polling the controls to identify change, or
something.

Savebutton madness continues. Thanks again for straightening me out.

John, the problems you are encountering are the primary reason I argue against
the explicit save model. It is almost always more trouble than it's worth. I
do see some merit in what you're trying to do as it regards changes to existing
records. A new record where the user changes their mind can simply be deleted,
but if they make extensive changes to an existing record and then change their
minds there is no easy way to get the record back to its original state. Here
is what I have done in a few of these instances:

A complete copy of the table structures is created and an additional field for
"Revision" is added. Initially all existing records are locked when viewed in
the form. The user who wants to modify the record must first press an [Edit]
button. Doing so copies the record and all sub-records into the second set of
tables, the form is unlocked, and the [Revision] field for the record is
incremented. Now the user is free to muck about with the record secure in the
knowledge that the previous version has been archived. If they [Abort] the
changes, the current record is deleted and the archived record is brought back
in from the second set of tables.

This works with all forms being bound and no "Are you sure?" prompts bombarding
the user.

 

[John S]

Excellent. It shows that I'm not really well versed in VBA controls and
events (and has me thinking about them). Thanks for the tip.

John S
Aylmer, PQ

 

[Dan Artuso]

You're welcome.
By the way, we're almost neighbors (unless there's another Alymer,Quebec), I'm just over in Wakefield.
Of course I work in Ottawa.

 

[John S]

Hi Rick,

You may be right: I confess it may well be "savebutton madness". Your
approach certainly has merit, but it really is a kind of transaction
processing (which is what the big boys - DB2, Oracle, etc- do). I was
reading about the availability of full transaction processing with ADo in
VB 6, and (in my case) perhaps its the logical way to go instead of gerry
rigging MS Access.

And a datasheet is great, but a little bit of a black box . I know from VB
and VBA that control level validation is not bullet proof - you need full
form validation ( all controls being tested together prior to permitting a
save).

Am I gerry rigging MS Access too much to get this level of control,
especially when using a subform/datasheet. Could well be.

I do appreciate the power (of features) and ease of development of MS
Access, especially when compared to Java / MySQL (which I also work with).
Could be these latter two have turned me into a control freak.

Thanks for the comments.

John S.
Aylmer, PQ

You're right of course: the main form is unbound, and the subform bound to
a temporary table and thus (as you point out) "me.dirty" don't work.

My problem is that I need a save button to do validation on the form and
subform, but I also need a way to signal the user to save changes when they
forget.

I could try something a tad esoteric: keep a copy of the original record
sets, and compare control values with this original copy on exit, etc. You
think there would be some way of polling the controls to identify change, or
something.

Savebutton madness continues. Thanks again for straightening me out.

John, the problems you are encountering are the primary reason I argue against
the explicit save model. It is almost always more trouble than it's worth. I
do see some merit in what you're trying to do as it regards changes to existing
records. A new record where the user changes their mind can simply be deleted,
but if they make extensive changes to an existing record and then change their
minds there is no easy way to get the record back to its original state. Here
is what I have done in a few of these instances:

A complete copy of the table structures is created and an additional field for
"Revision" is added. Initially all existing records are locked when viewed in
the form. The user who wants to modify the record must first press an [Edit]
button. Doing so copies the record and all sub-records into the second set of
tables, the form is unlocked, and the [Revision] field for the record is
incremented. Now the user is free to muck about with the record secure in the
knowledge that the previous version has been archived. If they [Abort] the
changes, the current record is deleted and the archived record is brought back
in from the second set of tables.

This works with all forms being bound and no "Are you sure?" prompts bombarding
the user.

 

[Dirk Goldgar]

You're right of course: the main form is unbound, and the subform
bound to a temporary table and thus (as you point out) "me.dirty"
don't work.

My problem is that I need a save button to do validation on the form
and subform, but I also need a way to signal the user to save changes
when they forget.

I could try something a tad esoteric: keep a copy of the original
record sets, and compare control values with this original copy on
exit, etc. You think there would be some way of polling the controls
to identify change, or something.

Savebutton madness continues. Thanks again for straightening me out.

I was going to make the same suggestion that Dan Artuso made, or
something very similar. I'd suggest using a Public variable in the main
form's module, or else a hidden checkbox control. You could initialize
it to False, and then set it not only in the AfterUpdate event of each
control on the main form, but also in the Form_AfterUpdate and
Form_AfterDelConfirm events of the subform. That is, every time you
add, change, or delete a record on the subform, set the flag
variable/control on the subform's parent form. You could make your work
with the controls on the main form simpler by using a common function to
set the "form-dirty" flag, called directly from the AfterUpdate property
of each control, instead of creating an event procedure for each control
..
The only drawback here is that, if the user changes an unbound control's
value without actually changing it (i.e., changes it to the same value
it already had), then you'll still flag this as a change to the form.
If you want to get fancy, you can record (in a module-level variable)
the current value of the ActiveControl in the Enter event of each
control. Then the function you call in the control's AfterUpdate event
can check to see if the control's value has actually changed. But
remember you can't test for Null = anything -- you have to work around
that.

 

[John S]

Ya, for a couple of reasons, that may be easier than "me.dirty": use the
event to change the flag. I'm still poking around for the datasheet though:
might get as messy as having to maintain a copy of the original data, and
compare it on exit.

Thanks for the advice.

John S
Aylmer, PQ (not far from Wakefield!)

 

[Fred Boer]

Ah ha! So I can put another pin on my map of "Who lives Where?"... And also
add another Canadian to the list!

Cheers!
Fred Boer
Guelph, Ontario

 

[JohnWL]

How about using VBA code in each control's After Update event to compare the
ctrl.OldValue against the current ctrl.Value (or pass these on to a common
function) to determine if the user actually changed the data. Then set the
parent form's Dirty property to True if data changed.

Or, alternatively, use the sub-form's Dirty event to set the parent form's
Dirty property with similar code to reverse it on the sub-form's Undo event.
This would be a problem if there is more than one sub-form, however.

Just a couple of thoughts. Do they make good sense or are there additional
problems with them?

John Loewen

 

[JohnWL]

Thanks for the comments, Dirk. I was aware that an unbound control doesn't
have and OldValue. But I just assumed that any form, bound or not, has a
Dirty property that can be manually manipulated. I can still see the Dirty
event listed in the form's properties even tho the form is not bound to any
data source. I haven't tried testing it tho.

I'm still searching, myself, for the pros and cons of various techniques to
save records with only validated data rather than letting Access just save a
record silently whenever the user moves to another record or closes the
form. I've tried developing my own mechanisms which got unwieldy in a large
complex form. So I'm watching these kinds of posts to get more tips.

John Loewen

 

[Dirk Goldgar]

Thanks for the comments, Dirk. I was aware that an unbound control
doesn't have and OldValue. But I just assumed that any form, bound or
not, has a Dirty property that can be manually manipulated. I can
still see the Dirty event listed in the form's properties even tho
the form is not bound to any data source. I haven't tried testing it
tho.

I oversimplified. The property is there, but if you refer to it you get
run-time error 2455, "You entered an expression that has an invalid
reference to the property Dirty." Technically speaking, unbound
controls have an OldValue property, too -- it's just that the property
is always equal to the Value propery.

I'm still searching, myself, for the pros and cons of various
techniques to save records with only validated data rather than
letting Access just save a record silently whenever the user moves to
another record or closes the form. I've tried developing my own
mechanisms which got unwieldy in a large complex form. So I'm
watching these kinds of posts to get more tips.

It's a popular subject. One of the tricks that tickles my fancy, though
I've never had occasion to use it in real life, is to use a bound form
(so that Access provides the recordset and handles record navigation)
but put only unbound controls on the form. Then you use code in the
Current event to load up all these controls from the actual fields
(which are readily available as run-time properties of the form). The
user can do whatever he wants to the values in those controls, but
nothing can get saved to the form's recordset until your code validates
the control values and explicitly assigns the values back to the fields.

The real problem comes up when subforms are involved, and you want to
validate and save or roll back all changes that were made on the
subform -- that is, multi-record changes -- as in the original question
here. There's been a lot written on the subject over the years, with
various techniques proposed. In fact, there was at least one other
thread on the subject in the last few days. I've never written up a
survery of all that's been proposed, with pros and cons; maybe you'd
like to? I'm sure a Google Groups search will turn up lots of ideas.

 

[Ken Snell]

It's a popular subject. One of the tricks that tickles my fancy, though
I've never had occasion to use it in real life, is to use a bound form
(so that Access provides the recordset and handles record navigation)
but put only unbound controls on the form. Then you use code in the
Current event to load up all these controls from the actual fields
(which are readily available as run-time properties of the form). The
user can do whatever he wants to the values in those controls, but
nothing can get saved to the form's recordset until your code validates
the control values and explicitly assigns the values back to the fields.

Dirk, I'm using this technique in a database that is in a client's shop. I
found it to be an easy way to let users cancel entries without having to
delete records from the recordset. To the user, it's seamless.

 

[John S]

Hi Dirk,

Neat idea, and Ill put the Google review on my list (which is getting longer
by the minute). A number of persons have mentioned that trying to get away
from bound controls and forms is defeating the purpose of MS Access, and I
think they have a point. Someone suggested the continuous form (for the sub
form), and I'm playing with it, and with validations options. They aren't
entirely straight forward either.

And I'm running out of time: I have to update street names, numbers and
postal codes of some 15,000 pet owners (municipal fusion and street address
changes) in time for the annual renewals, and before doing that I have to
ensure that all the street names meet the municipal standard. BTW, I'm
using Java (and JDBC) to parse, compare recordsets and update the ms
access database (perhaps out of ignorance). Does Access / VB have any
equivilant to REGEX?

Anyway, thanks again for the idea: if I have time, I wouldn't mind
experimenting with your idea of unbound controls and the change method.

John S
Aylmer, Quebec (Canada)

 

[Dirk Goldgar]

And I'm running out of time: I have to update street names, numbers
and postal codes of some 15,000 pet owners (municipal fusion and
street address changes) in time for the annual renewals, and before
doing that I have to ensure that all the street names meet the
municipal standard. BTW, I'm using Java (and JDBC) to parse,
compare recordsets and update the ms access database (perhaps out of
ignorance). Does Access / VB have any equivilant to REGEX?

Assuming it's installed on your system -- I think it's installed with
Windows Scripting -- you can set a reference to Microsoft VBScript
Regular Expressions 5.5, and work with the RegExp object. I have to say
I don't know much about it, though, so again you'd have to do a little
research. Here's one place to start:


http://msdn.microsoft.com/library/default.asp?URL=/library/en-us/dnclinic/html/scripting051099.asp