Subform Data Not Saving

[mewins]

I have a main client form with several subforms, one of which appears to be
having problems. The main and subforms are linked by ClientID. I'm getting
complaints that my one subform (CaseNotes) isn't saving data; people will
type in a case note and navigate away from the subform or the client, but
when they come back the next day their case note is completely missing. I
don't know if this is being caused by too many people in the database, people
looking at the same record at the same time, or something else. I've read a
bit about me.dirty, but I'm not sure where exactly to stick that coding.
Thanks for any ideas on fixing this problem.

 

[Dirk Goldgar]

I have a main client form with several subforms, one of which appears to be
having problems. The main and subforms are linked by ClientID. I'm
getting
complaints that my one subform (CaseNotes) isn't saving data; people will
type in a case note and navigate away from the subform or the client, but
when they come back the next day their case note is completely missing. I
don't know if this is being caused by too many people in the database,
people
looking at the same record at the same time, or something else. I've read
a
bit about me.dirty, but I'm not sure where exactly to stick that coding.
Thanks for any ideas on fixing this problem.

If people aren't getting an error message when they navigate away from the
record, the problem probably doesn't have anything to do with people working
on the same record at the same time. And it's unlikely that it's caused by
too many people in the database, as I would expect Access to simply keep
people from opening the database if the maximum number of simultaneous
connections was exceeded.

The first thing to do is check to see if the "missing" records are actually
being stored in the table, but not displayed on the form. It could be that
the linking fields aren't set up correctly, or the subform has gotten itself
set to Data Entry mode, or something else that is keeping the previously
entered records from being displayed. So check the table itself to make
sure that the records aren't stored there, maybe with some key fields Null.

If the records really aren't being saved in the table, can you determine if
there's some reason that they might have been unsavable due to failing som
validation rule? If so, there are some ways to close the form or database
that might silently lose the data. That would only happen if you were doing
the closing in code, and would not happen if the user just navigated to
another record on the subform or main form.

It's possible that there is some database corruption behind this, but it's
too early to say. Is this a split, front-end/back-end application, with
each user having his own copy of the front-end? Or are you sharing a
monlithic database file over the network?

 

[mewins]

I do regularly check the table to see if there are entries that are missing
the ClientID (which would account for why it's not showing up in the
subform), but that doesn't seem to be the main issue. I will find some
records, but people are saying there are many more that they have entered
that aren't showing up. I'm not sure what, if any, validation rules that are
on the form or that I should be looking for. The subform has several fields,
many of which must be completed before the user is allowed to navigate away;
could that be causing issues? Staff know to fill out the required fields and
I haven't heard of any problems with that, but who knows. I don't have any
closing code that I know of either, or at least code that does anything other
than close the main form. The database is split with a centralized back end
and 2 different front ends, and everything is on our shared network. I don't
know how to make individualized front ends for all users, but that seems like
it would be useful.

 

[Dirk Goldgar]

I do regularly check the table to see if there are entries that are missing
the ClientID (which would account for why it's not showing up in the
subform), but that doesn't seem to be the main issue. I will find some
records, but people are saying there are many more that they have entered
that aren't showing up. I'm not sure what, if any, validation rules that
are
on the form or that I should be looking for. The subform has several
fields,
many of which must be completed before the user is allowed to navigate
away;
could that be causing issues? Staff know to fill out the required fields
and
I haven't heard of any problems with that, but who knows. I don't have
any
closing code that I know of either, or at least code that does anything
other
than close the main form. The database is split with a centralized back
end
and 2 different front ends, and everything is on our shared network. I
don't
know how to make individualized front ends for all users, but that seems
like
it would be useful.

If your users are sharing the same front-end, then your odds of corruption
are much higher. So it could be a corruption problem. But the first thing
to do is try to reproduce the failure. If it appears that some records are
being saved and some are not, then it would help to determine the exact
circumstances under which a record is not being saved.

If you have code that closes the main form, and that code is triggered by a
command button or some other control that is *on* the main form, not the
subform, then you don't have to worry about any silent failure to save the
subform record due to validation errors when that code is executed. That's
because the subform record will already have been saved before the command
button on the main form gets the focus.

Until you can determine steps to reproduce the problem, it's going to be
hard to know where to look for the cause and solution.

 

[mewins]

Thanks. I'll start keeping track to tease out if it's particular computers
or client records. So far I haven't detected a pattern, but maybe there is
one. And yes, the main form closes using a command button that I made with
the wizard; I didn't add/alter any code to that. Asides from that, how do I
protect against corruption in the front end? Thanks.

 

[Dirk Goldgar]

Thanks. I'll start keeping track to tease out if it's particular
computers
or client records. So far I haven't detected a pattern, but maybe there
is
one.

It seems to me that it's most likely to be a specific sequence of user
actions, which maybe only happens sometimes. But it could be that certain
users do it this way, while others don't, so you may see an association with
a given user or computer.

Asides from that, how do I
protect against corruption in the front end?

The main way is to give each user their own copy of the front-end, installed
on their own PC. Otherwise, Access is continually retrieving and storing
design information across the network, which exposes it to corruption.

 

[mewins]

Ok, thanks so much for your help. Do you know what the easiest way to have
individualized front ends for all of the users (there are about 25 total and
I do make cosmetic/structural changes about once a month or so). Is it best
to just copy/paste the front ends to the hard drive or is there a code I can
use to make a new front end for each user when they go to open the database?

 

[Dirk Goldgar]

Ok, thanks so much for your help. Do you know what the easiest way to
have
individualized front ends for all of the users (there are about 25 total
and
I do make cosmetic/structural changes about once a month or so). Is it
best
to just copy/paste the front ends to the hard drive or is there a code I
can
use to make a new front end for each user when they go to open the
database?

Usually, what you'd do is have a master copy of the front-end stored on the
network share, and have it automatically be copied to the user's PC, either
every time they start the application or only when there's a version change.
You can write a simple script or .bat file to do the former, while the
latter is a bit more complicated. Lots of people user Tony Toews' "Auto FE
Updater" for this:

http://www.granite.ab.ca/access/autofe.htm

 

[John W. Vinson]

And yes, the main form closes using a command button that I made with
the wizard; I didn't add/alter any code to that.

Just be aware that if you have code that closes the form, it's quite possible
that it does so silently discarding the edited data. It might be prudent to
add a line in this command button's code to explicitly save the record prior
to closing:

If Me.Dirty Then Me.Dirty = False

will do so.

 

[mewins]

Thanks, I will look into setting this up and see if that helps. Thank you!