Me.Dirty - Where to put this logic?

[ar013871 via AccessMonster.com]

Okay,

Here is the setup of this monster database that we're using. We have an
Access frontend with SQL server backend. We have about 14 tables that all
link to one main table via main table's PK. To present this data, we have
forms that have 14 sub-forms on them (long story, but in order to get what we
needed, this was how we got it to work). We have the SQL server residing on
one server and then six laptops connect to that server via wireless router
(again, due to the mobility of the laptops, it has to be wireless). So at
one time, there are six users entering data, but never working on the same
person at the same time. Lately we've been experiencing, what seems to be a
common error, the write conflict error where the only options are Save, Copy
to Clipboard or Drop Changes. With me so far?

Okay, so it is my understanding that as a user tabs through the form and
fills out the appropriate text boxes, check boxes, and option buttons, the
record does not save until the form is closed, correct? This is when the
record is considered "dirty," it's there but not written to SQL. In doing
some research, it looks like I need to utilize the following logic:

If Me.Dirty = True Then
Me.Dirty = False
End If

My question is where to do I put this? Do I put in in the OnExit event of
the subform? Does it go in each AfterUpdate event for each control on the
form (this would be ridiculous (there's thousands))? Does it go in the
AfterUpdate event of the main form? With this setup, I can set many places
to put it, I just need to make sure it's in the right spot so the error goes
away. Please let me know.

Thanks in advance!

 

[Guest]

Hi,

Basically, you are saying that you need to have force a save... Have you
considered simply having a "Save" button, that has the following code in the
on click event:

docmd.runcommand accmdsaverecord

By definition, Access saves the record when the record changes, so putting
it in the after update event for the form won't help.

If your form is so astoundingly large that the users are taking too long to
reach the save point, you could consider having the same code run somewhere
else along the way.

Hope this helps.

Damian.

 

[Dirk Goldgar]

In

Okay,

Here is the setup of this monster database that we're using. We have
an Access frontend with SQL server backend. We have about 14 tables
that all link to one main table via main table's PK. To present this
data, we have forms that have 14 sub-forms on them (long story, but
in order to get what we needed, this was how we got it to work). We
have the SQL server residing on one server and then six laptops
connect to that server via wireless router (again, due to the
mobility of the laptops, it has to be wireless). So at one time,
there are six users entering data, but never working on the same
person at the same time. Lately we've been experiencing, what seems
to be a common error, the write conflict error where the only options
are Save, Copy to Clipboard or Drop Changes. With me so far?

Okay, so it is my understanding that as a user tabs through the form
and fills out the appropriate text boxes, check boxes, and option
buttons, the record does not save until the form is closed, correct?
This is when the record is considered "dirty," it's there but not
written to SQL. In doing some research, it looks like I need to
utilize the following logic:

If Me.Dirty = True Then
Me.Dirty = False
End If

My question is where to do I put this? Do I put in in the OnExit
event of the subform? Does it go in each AfterUpdate event for each
control on the form (this would be ridiculous (there's thousands))?
Does it go in the AfterUpdate event of the main form? With this
setup, I can set many places to put it, I just need to make sure it's
in the right spot so the error goes away. Please let me know.

When the focus changes from the main form to a subform, the main form's
record will be saved automatically if the form is dirty. When the focus
changes from a subform to the main form, the subform's record will be
saved automatically, if the subform is dirty. So there's no point in
trying to save records as you switch between main form and subform.

Is it taking the users a long time to fill out the form or subform, so
that the record remains dirty for a long time? You say that the users
are never "working on the same person at the same time." Given that you
have a SQL Server back-end, I'm not sure why you're getting write
conflicts, unless "working on a person" involves updating records that
may be in common between two "persons". That is, unless your problem
isn't really one of true write conflicts, but rather a configuration or
data problem that is causing Access to *think* there's a write conflict
when there really isn't. Do you get these conflict messages all the
time, or only occasionally? Do they really seem to correspond to
situations where users are updating the same records?

 

[ar013871 via AccessMonster.com]

It wouldn't surprise me if it is taking users a long time to fill out the
form. Not because the form is too long or complicated, it is because they
are doing health screenings of kids while they're entering the data. When we
first started to get the error, it was sporratic. Then when we shut down the
six computer and just worked on the application that is on the server we even
got the error. Since each kids goes to a different station, the record
dealing with that kid is only open at one time. I don't think there would be
any commonality between the users and the records they have pulled up.

I've tried including the timestamp that has been suggested in a lot of forums
and this change doesn't seem to resolve the write conflict error either. I
do not have any fields that are bit size. Is there anything else that I am
missing? I do have memo fields, but I really do not want to have to break
those down to seperate tables or change them to text fields. We get a lot of
school nurses who can be wordy and the memo field helps.

We've received the error when opening up a form (i've incorporated a search
feature that returns results in a list box - user can double click on the
record or select the open form button to open it up) and we've seen the error
when a user was trying to enter data. I'm stumped!

I've even seen forums where they say this is a known bug for Access and to
deal with it, however, I would really like to exhaust all outlets before
concluding that. Would the wireless connection play a part? Would it help
to be wired? I feel like I'm grasping for straws.

Okay,

[quoted text clipped - 28 lines]

setup, I can set many places to put it, I just need to make sure it's
in the right spot so the error goes away. Please let me know.

When the focus changes from the main form to a subform, the main form's
record will be saved automatically if the form is dirty. When the focus
changes from a subform to the main form, the subform's record will be
saved automatically, if the subform is dirty. So there's no point in
trying to save records as you switch between main form and subform.

Is it taking the users a long time to fill out the form or subform, so
that the record remains dirty for a long time? You say that the users
are never "working on the same person at the same time." Given that you
have a SQL Server back-end, I'm not sure why you're getting write
conflicts, unless "working on a person" involves updating records that
may be in common between two "persons". That is, unless your problem
isn't really one of true write conflicts, but rather a configuration or
data problem that is causing Access to *think* there's a write conflict
when there really isn't. Do you get these conflict messages all the
time, or only occasionally? Do they really seem to correspond to
situations where users are updating the same records?

 

[ar013871 via AccessMonster.com]

Would splitting the database help? I've never done this before, is the
process easy?

It wouldn't surprise me if it is taking users a long time to fill out the
form. Not because the form is too long or complicated, it is because they
are doing health screenings of kids while they're entering the data. When we
first started to get the error, it was sporratic. Then when we shut down the
six computer and just worked on the application that is on the server we even
got the error. Since each kids goes to a different station, the record
dealing with that kid is only open at one time. I don't think there would be
any commonality between the users and the records they have pulled up.

I've tried including the timestamp that has been suggested in a lot of forums
and this change doesn't seem to resolve the write conflict error either. I
do not have any fields that are bit size. Is there anything else that I am
missing? I do have memo fields, but I really do not want to have to break
those down to seperate tables or change them to text fields. We get a lot of
school nurses who can be wordy and the memo field helps.

We've received the error when opening up a form (i've incorporated a search
feature that returns results in a list box - user can double click on the
record or select the open form button to open it up) and we've seen the error
when a user was trying to enter data. I'm stumped!

I've even seen forums where they say this is a known bug for Access and to
deal with it, however, I would really like to exhaust all outlets before
concluding that. Would the wireless connection play a part? Would it help
to be wired? I feel like I'm grasping for straws.
[quoted text clipped - 19 lines]

time, or only occasionally? Do they really seem to correspond to
situations where users are updating the same records?

 

[ar013871 via AccessMonster.com]

I guess my first question in regards to splitting a database, is does this
work if you have a SQL BE?

Would splitting the database help? I've never done this before, is the
process easy?

It wouldn't surprise me if it is taking users a long time to fill out the
form. Not because the form is too long or complicated, it is because they

[quoted text clipped - 27 lines]

 

[Dirk Goldgar]

In

Would splitting the database help? I've never done this before, is
the process easy?

If you have a SQL Server back-end, you're already split. Now, if every
user is logging into the same Access front-end, which resides on a
network share, you aren't taking full advantage of the
front-end/back-end arrangement. Is that what you're doing? Normally,
each user would have her own copy of the front-end, installed on her
local PC.

It's possible, of course, that you aren't *fully* split. Does the
Access front-end contain local tables with shared data? It's not
unusual to have user-specific information in the front-end, since each
user would have a separate copy of the front-end. But if there's
non-user-specific data in the front-end, you would need to migrate that
to the back-end before giving each user her own copy of the front-end.

It occurs to me that, if you have all your users using the same copy of
the front-end, that may have something to do with the write-conflict
problem you're experiencing. But it's just conjecture at this point.

 

[Dirk Goldgar]

In

We've received the error when opening up a form (i've incorporated a
search feature that returns results in a list box - user can double
click on the record or select the open form button to open it up) and
we've seen the error when a user was trying to enter data. I'm
stumped!

Is it that you are opening popup forms to enter detail information while
editing the basic information on the main form? That could cause the
problem, as you'd be in conflict with yourself.

I've even seen forums where they say this is a known bug for Access
and to deal with it

I don't know of any such "known bug" except with regard to needing
timestamp fields, not trying to save Null bit fields, etc. Those are
known issues, with specific cures.

outlets before concluding that. Would the wireless connection play a
part? Would it help to be wired?

I don't think so. It could be that your application design is causing
the problem, but it's hard to say without pinning down one or more
reproducible scenarios for triggering the conflict.