Detecting record exit without update

  • Thread starter Thread starter Mark Chapman
  • Start date Start date
M

Mark Chapman

We have a form with unbound text boxes which only change
the underlying data upon the press of the Post Changes
button.

We want to remind the user that if he leaves the record
without pressing the button his changes will be ignored.

I'm having trouble with this since the form's
BeforeUpdate event does not trigger since no BOUND field
is changing -- only unbound text boxes.

I'd really appreciate any help or suggestions.

Thanks,
Mark
 
To what does the user go when he "leaves" the record? What is happening on
this case?

One thought would be to create a global variable (to know that something was
changed) in the form's module's Declarations section:

Private blnValueChanged As Boolean

Then you could put code on each textbox's OnChange event, similar to this:

Private Sub txtBoxName_Change()
blnValueChanged = True
End Sub

Then add this line to the code in the command button's Click event (which is
where I'm assuming you're running the code to save the changes):
blnValueChanged = False

Then, once we know what occurs when the user "leaves" the record, you could
test for the value of blnValueChanged to see if the user has saved the
changes. If the value is True, the changes have not been saved.
 
I may be dense, but I still don't see when we would check
the value of the global variable. If the user uses any
navigation method to go to another record, what would
trigger the checking of the global variable.

Currently, I increment a variable so I know how many
fields have been changed. The problem is there's nowhere
I'm aware of to perform the check on the variable.

Please help clear this up for me! I'd be saved if the
BeforeUpdate event triggered. The problem is that all
these text boxes are unbound and therefore have no
connection to the underlying query.

I've thought of binding the text boxes to dummy fields in
the table (and therefore the query). Perhaps then the
BeforeUpdate would trigger. What do you think?

Thanks again,
Mark
 
Another option (assuming the form is bound to a recordset and some of the
controls are bound).

When you enter/change data in an unbound control, set any one of the bound
controls equal to itself. As far as Access is concerned that is a change and
will dirty the form even though no human perceivable change has occurred on the
bound control.
 
That is a much easier solution than what I am envisioning...thanks, John!

--
Ken Snell
<MS ACCESS MVP>

John Spencer (MVP) said:
Another option (assuming the form is bound to a recordset and some of the
controls are bound).

When you enter/change data in an unbound control, set any one of the bound
controls equal to itself. As far as Access is concerned that is a change and
will dirty the form even though no human perceivable change has occurred
Click to expand...
My reply specifically did not provide any info about "when" to check for the
value of the global variable because you hadn't specified what was meant by
"leaving the record". This could mean closing the form, going to a new
record, going to another form, etc.

Your comment below indicates that the need is to know when the user has
moved to a different record.

Your concept of using a counter variable (which I assume is incremented each
time that the user makes a change to the record) should also work in place
of the global boolean variable that I've suggested. In both cases, the
variables need to be reset back to their default values when the command
button is clicked to "save" the new info.

Let me ask a few more questions about your form's setup and then we should
be able to identify the solution.

Where on the form are the unbound textboxes located?
How are you "transferring" the values from the textboxes into the form's
recordsource query?
Do you "clear" the textboxes once you've transferred their values?
Does the user move to the next record, or can the user go to the previous,
first, last, or any other record?

I am thinking that we could use the form's OnCurrent property to "look" at
the form (either by seeing if there are values in the textbox or by checking
the value of the global boolean or counter variable), and if they are not at
the "default" state, move the form back to the previous record. Of course,
this will get tricky because the form's code would have to know NOT to run
the verification steps when you move the form back to the previous record.
(This assumes that the user moves forward in the form's recordset.) If the
user can go to any record, then we'd need to store the "bookmark" of the
current record so that we can go back to it easily.

Another alternative for you to consider is to disable all navigation methods
so that the user must click your buttons to move around; that will give you
complete control over ensuring that the user has saved the changes before
going somewhere else.
Click to expand...
[/QUOTE]
 
When I use File - Get External Data - Import Table
one of the questions asked in the wizard is whether the first row
contains field names.
I believe this is a textbox I can set on the second or third screen of
the wizard.

HTH
- Turtle
 
I'll attempt to answer your well-thought out questions.

This is a forecasting application wherein the salesmen
can edit the forecasts for future months. A forecast
record is a combination of year/site/customer/part as a
primary key.

However, the values they type in for various months are
nothing but values in unbound text boxes in the detail
section of the form until they press our command button
labeled Post Changes. Then we have code that changes the
actual fields in the table to match the new values typed
in. Validity checks are done at this time as well.

After the user presses Post Changes, he's still at the
same record. He can then move to another record using
Access's navigation buttons, close the form, or make more
changes to the monthly forecasts. It's highly unlikely he
would go to another form without first closing this one.
Most of the users of this form will have only this form
on their menu.

The sales director decided that the salesmen should be
notified if they leave a record (by going to another
record or closing the form or whatever) without pressing
Post Changes. That way they won't think the changes they
made are permanent when they're not.

I thought my solution was great. I use an array variable
with a subscript for each month. I set the appropriate
variable equal to 1 in the AfterUpdate of each text box
(if January, ChangeCount(1) = 1). Then the message to the
user can sum the totals and get how many fields were
changed. We'd give that message to the user as soon as he
attempts in any way to leave that record he was changing.
If the sum of the variables is zero, no changes were made
and we just perform the naviation requested by the user.

After the message displays OR after Post Changes is
pressed, we reset the variables for each month to zero. I
think it also would be set to zero in the OnCurrent
event. Obviously when you first go to a record, no
changes have yet been made.

Everything is fine except when I placed this code in the
form's BeforeUpdate event it never fired. That was
because changes in unbound text fields don't force an
update.

My latest idea was to set up dummy fields in the table
(and the query used by the form) and bind the text fields
to them. That way I think the BeforeUpdate would trigger
and everything would be fine.

Would that work? If not, we may have to try your
suggestion of controlling the user's movement with our
own navigation buttons. It's unlikely the user would
close the form and expect his values to be permanent. But
I guess we could even control that if necessary.

I just prefer using the BeforeUpdate event since that
covers all our bases.

Thanks for your patience with this problem. We're near
the end of the job and this is the next to the last
problem we need to solve.

Please let me know if I can give you any more information
that would help you solve my problem.

Thanks,
Mark
 
I'll try this in the morning. I'll post a message here
indicating my success.

If it works, I'm forever indebted to you! What a simple,
elegant solution it would be.
 
Actually I just tried it and it didn't work.

Didn't I read that changing a text box programmatically
does NOT cause a trigger of the BeforeUpdate event?

Doesn't the user have to actually type something into it?

Thanks for the try, but I guess we're back to the drawing
board.
 
Mark Chapman said:
Actually I just tried it and it didn't work.
Click to expand...

It should work. Post the code you used. Are you sure it was a *bound*
control you set to itself?
Didn't I read that changing a text box programmatically
does NOT cause a trigger of the BeforeUpdate event?
Click to expand...

It doesn't trigger the *control's* events, but it does dirty the form and
force the *form's* BeforeUpdate event to fire when the record is about to be
saved.
Doesn't the user have to actually type something into it?
No.

Thanks for the try, but I guess we're back to the drawing
board.
Click to expand...

Don't give up too soon. This approach should work.
 
I
-----Original Message-----


It should work. Post the code you used. Are you sure it was a *bound*
control you set to itself?


It doesn't trigger the *control's* events, but it does dirty the form and
force the *form's* BeforeUpdate event to fire when the record is about to be
saved.
it?

Don't give up too soon. This approach should work.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup, not by e-mail)



.
Click to expand...
 
I tried 3 different ways:

1) txtCurrFcstAvgPrice = txtCurrFcstAvgPrice
where txtCurrFcstAvgPrice is bound to a field in the
query (a field selected straight from tblForecast)

2) txtCurrFcstAvgPrice = txtCurrFcstAvgPrice + 0

3) curTempAvgPrice = txtCurrFcstAvgPrice
txtCurrFcstAvgPrice = 999999.99
txtCurrFcstAvgPrice = curTempAvgPrice
This stores the AvgPrice in a variable, then changes
the text box bound to the AvgPrice and then changes it
back to the original amount

BeforeUpdate for the form does not trigger at all.
I know you guys have forgotten more than I'll ever know,
but read this excerpt from the BeforeUpdate Help in
Access 2002:
Notes

Changing data in a control by using Visual Basic or a
macro containing the SetValue action doesn't trigger
these events for the control. However, if you then move
to another record or save the record, the form's
BeforeUpdate and AfterUpdate events do occur.

Doesn't that mean what we're trying won't work?

Thanks as always . . . we'll solve this one yet!
 
I even tried the following:

I bound the August text box to a dummy field I created in
the table. Now when I changed the August value in the
text box it changed the value in the table (via the
query). Even though I later verified that the table value
changed, the form's BeforeUpdate event never triggered.

I have no idea why unless the complexity of the query is
causing problems. It has about 97 fields including 5 of
which are filter combo boxes on the form. That causes
there to be 32 criteria rows as well. We are filtering
the following fields: Year, Site, Customer, Part,
Forecast Set.

Nevertheless, the dummy field did get changed in the
table and yet never triggered the form's BeforeUpdate
event. What is going on?
 
Something strange going on. I just tested this in 2000 and it worked exactly as
I expected and I know it works in Access 97. I don't have later versions
available here.

Quoting from your earlier message:

Changing data in a control by using Visual Basic or a
macro containing the SetValue action doesn't trigger
these events for the control. However, if you then move
to another record or save the record, the form's
BeforeUpdate and AfterUpdate events do occur.
End Quote

According to that and to what I have observed, the control's events don't
execute, but the form's events still do.
 
Did you use a query for underlying data or a table?

Could my complex query be the source of the problem? For
the record, I'm using Access 2000. Access 2002 will be
used down the road for this application.

It bothers me that the underlying data changed, but there
was no trigger of the BeforeUpdate event. I used the
debugger and in the AfterUpdate method of the text box
the code executed fine. Then I paged down to the next
record and no code executed. Strange.
 
Well, thanks to everyone we've solved all the problems.
The Write Conflict dialog box was caused by changing the
AvgPrice (to trigger the BeforeUpdate) through a bound
text box on the form followed by updating the forecast
record by directly accessing the table with a recordset.

Now the record is updated through bound text boxes and
all works as it should.

I'll never know why we changed the Recordset Type to
Inconsistent Updates because everything is updateable now
without that.

Again my sincere thanks to everyone who helped including:
Ken Snell
John Spencer
Dirk Goldgar

I certainly try to solve my problems first, but it's nice
knowing that there are competent and interested
individuals available for times I need more assistance.

Thanks again,
Mark
 
I have experimented in a test form and have confirmed that using code to set
a control equal to its current value does "dirty" the form and does cause
the form's BeforeUpdate event to occur when you move to another record. So
John's idea does work in my test environment.

With respect to the ACCESS Help, what it says is that setting a control's
value through code does not cause any of the control's events to occur, but
it does cause the form's BeforeUpdate and AfterUpdate events to occur when
you do record navigation. Thus, Help is consistent with what John and Dirk
have said.

I believe that John's idea is the best way for you to go. But I'm puzzled by
the inability of your setup to "see" the form's BeforeUpdate event when you
do this.

If I'm understanding correctly, the txtCurrFcstAvgPrice control's name is
different from the name of the field to which it's bound, correct? Just for
fun, try using Me.txtCurrFcstAvgPrice.Value in place of just
txtCurrFcstAvgPrice in your code. See if that works for you.

Also, put a msgbox step in your code to show you the value of
txtCurrFcstAvgPrice control after you set it equal to any other value, just
to be sure that your code is "getting" to the correct control.

Also, how are you determining that the form's BeforeUpdate event is not
occurring? Do you have code in that event? Post that procedure as well so we
can see what it is doing.
 
Back
Top