Prevent partial records

[BruceM]

I have a situation that has proved vexing in several databases. In one
instance, a record is created and a number automatically assigned via code,
then a reason for creating the record is selected, then the summary and the
description are added. Since there could be several reasons for creating
the record, the Reason data are stored in a related table, entered via a
subform. Entering the subform means Access attempts to save the record. I
use a Boolean (form-level variable) to determine whether the Before Update
code runs.

This is the sequence: The user starts a new record by selecting the
department. This generates the record number, and send the user to the
Reason subform. Since it is a new record the Boolean (blnNoVal), which is
False at that point, is set to True in the form's Before Update event, and
the rest of the Before Update code is skipped. If not a new record blnNoVal
remains False, which allows the form's Before Update code to run fully.

Upon exiting the Reason subform blnNoVal is set to False (if at least one
Reason has been selected). The user then enters the Summary and the
Description. This dirties the record, so the form's Before Update code
runs, and all is well.

However, since the form is not dirty after exiting the Reason subform, the
user can navigate to another record, close the database, etc. This means in
some cases a record is created and a number assigned, but that is as far as
it goes. I would like to get rid of the garbage record, but I can't use
Undo because the record has already been saved. Maybe I could delete the
record via DELETE SQL when the user attempts to navigate away from it or
close the database, but I'm not sure where to trap that attempt. I have
User Level Security in the FE and BE of a split database, so I would need to
allow Delete permissions for all users. As long as I keep users away from
the tables and queries it should be OK, but I would rather not do it this
way.

If I could have one Access wish I would have to consider seriously if it
would be not to save the record upon entering a subform. The only reason
this is a problem is that the record is saved before I am ready. It has
been a problem in several databases.

Maybe the solution is to enter the Reason information in unbound controls,
perform the validation, and use code to write from the unbound controls to
the related table if the rest of the validation succeeds.

How do others solve this difficulty?

 

[Jeanette Cunningham]

Hi BruceM,
Why not just save the parent records normally. If the user doesn't choose
any reasons on the child form, just let it happen.
This is an easy way and you don't need the before update code in the main
form.

When you are querying this table for forms and reports, you can use criteria
to skip the parent records that don't have any associated child records.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Marshall Barton]

I have a situation that has proved vexing in several databases. In one
instance, a record is created and a number automatically assigned via code,
then a reason for creating the record is selected, then the summary and the
description are added. Since there could be several reasons for creating
the record, the Reason data are stored in a related table, entered via a
subform. Entering the subform means Access attempts to save the record. I
use a Boolean (form-level variable) to determine whether the Before Update
code runs.

This is the sequence: The user starts a new record by selecting the
department. This generates the record number, and send the user to the
Reason subform. Since it is a new record the Boolean (blnNoVal), which is
False at that point, is set to True in the form's Before Update event, and
the rest of the Before Update code is skipped. If not a new record blnNoVal
remains False, which allows the form's Before Update code to run fully.

Upon exiting the Reason subform blnNoVal is set to False (if at least one
Reason has been selected). The user then enters the Summary and the
Description. This dirties the record, so the form's Before Update code
runs, and all is well.

However, since the form is not dirty after exiting the Reason subform, the
user can navigate to another record, close the database, etc. This means in
some cases a record is created and a number assigned, but that is as far as
it goes. I would like to get rid of the garbage record, but I can't use
Undo because the record has already been saved. Maybe I could delete the
record via DELETE SQL when the user attempts to navigate away from it or
close the database, but I'm not sure where to trap that attempt. I have
User Level Security in the FE and BE of a split database, so I would need to
allow Delete permissions for all users. As long as I keep users away from
the tables and queries it should be OK, but I would rather not do it this
way.

If I could have one Access wish I would have to consider seriously if it
would be not to save the record upon entering a subform. The only reason
this is a problem is that the record is saved before I am ready. It has
been a problem in several databases.

Maybe the solution is to enter the Reason information in unbound controls,
perform the validation, and use code to write from the unbound controls to
the related table if the rest of the validation succeeds.

How do others solve this difficulty?

Either the unbound control or, more often, make the subform
invisible until the minimal required main form data has been
entered.

 

[BruceM]

Thanks for the reply. Having no Reason child records is not an option.
There must be at least one Reason; often there are several. The Before
Update code is to ensure all necessary fields have been filled in. It would
be great if users just did that, but they don't.
I find in this situation, as I do so often, that building the database is
easier than being sure people use it as intended.

 

[BruceM]

Thanks for the reply. I think it may be best with the unbound controls in
this case. For existing records I can stay with the subform, in case there
is a need to add another Reason child record. Too often people start a
record without adding all required information, then there is no good way to
enforce the requirement after the record has been saved by going to the
subform control.
On the other hand, there are just three required fields, so perhaps I could
test each field at the control's After Update event (or Exit event?). If
the other two fields are filled in, unhide the subform control and set the
focus there. Test the subform for Records upon leaving the subform. If
there are none, don't let the user out. Hmm. That could work, too. I
think I'll try that first. The potential problem with unbound controls is
that I would need to provide several in case there are several Reasons, but
the users may think they need to fill in all of them. On the other hand, if
I provide just one it may be awkward to go back to fill in additional
Reasons (if necessary) after the main record has been saved. Maybe I don't
give the users enough credit, but some people only use the database once or
twice a year, and I can't realistically expect people to remember the
details if a year has passed since they were trained.

 

[Marshall Barton]

Thanks for the reply. I think it may be best with the unbound controls in
this case. For existing records I can stay with the subform, in case there
is a need to add another Reason child record. Too often people start a
record without adding all required information, then there is no good way to
enforce the requirement after the record has been saved by going to the
subform control.
On the other hand, there are just three required fields, so perhaps I could
test each field at the control's After Update event (or Exit event?). If
the other two fields are filled in, unhide the subform control and set the
focus there. Test the subform for Records upon leaving the subform. If
there are none, don't let the user out. Hmm. That could work, too. I
think I'll try that first. The potential problem with unbound controls is
that I would need to provide several in case there are several Reasons, but
the users may think they need to fill in all of them. On the other hand, if
I provide just one it may be awkward to go back to fill in additional
Reasons (if necessary) after the main record has been saved. Maybe I don't
give the users enough credit, but some people only use the database once or
twice a year, and I can't realistically expect people to remember the
details if a year has passed since they were trained.

Your reasoning appears to lead to making the subform
invisible.

Another thought that may help users would be to add a label
control (same size as the subform control?) behind the
subform control (Format menu - Send to Back) with a comment
about filling in the other fields. If it was colored to
look like a disabled control, they will be lead to doing the
right entries first. When the other three control's
AfterUpdate (not Exit) event make the subform visible the
label would be hidden behind the subform.

You can find the color codes for the disabled look in VBA
Help - "System Colors".

 

[BruceM]

Your reasoning appears to lead to making the subform
invisible.

Another thought that may help users would be to add a label
control (same size as the subform control?) behind the
subform control (Format menu - Send to Back) with a comment
about filling in the other fields. If it was colored to
look like a disabled control, they will be lead to doing the
right entries first. When the other three control's
AfterUpdate (not Exit) event make the subform visible the
label would be hidden behind the subform.

You can find the color codes for the disabled look in VBA
Help - "System Colors".

I'm working on the invisible subform angle. The label idea may be helpful
especially in that the database has been in operation for a while, and it
may help to have a reminder that the sequence has changed.

I use the system colors quite a bit. I found a listing somewhere of the
color codes
(-2147483633, etc.), and created a form for my own reference that shows how
the color appears in the current profile and the RGB combination in case I
want it to be the same on all computers. I copy the form to a database
during development. I didn't know (or maybe I forgot) about the color
constants. Thanks for pointing it out.

I don't know why I even wondered about using the Exit event to test. I only
want to do the test if the value has changed, so of course it would be After
Update. Again, thanks for the reminder.

 

[Marshall Barton]

I'm working on the invisible subform angle. The label idea may be helpful
especially in that the database has been in operation for a while, and it
may help to have a reminder that the sequence has changed.

I use the system colors quite a bit. I found a listing somewhere of the
color codes
(-2147483633, etc.), and created a form for my own reference that shows how
the color appears in the current profile and the RGB combination in case I
want it to be the same on all computers. I copy the form to a database
during development. I didn't know (or maybe I forgot) about the color
constants. Thanks for pointing it out.

I don't know why I even wondered about using the Exit event to test. I only
want to do the test if the value has changed, so of course it would be After
Update. Again, thanks for the reminder.

You're welcome.

Nice work re using the System Colors. IMO, they should be
used just about everywhere so each user's theme or custom
color settings are reflected in the Access application.

You may want to consider taking another step with your
system colors form along the lines of what I did with mine.
I created a public function to open the form from a custom
tool bar button and apply the selected color code to the
fore/back/border color properties of all the selected
controls in the form I am developing.

Good luck with the rest of your project.

 

[BruceM]

I am trying to add automation to repeated tasks. For instance, I use an
adaptation of the navigation button subform that Stephen Lebans (I think)
has made available. It hadn't occurred to me to use the Color form to
format the entire form all at once, but it is something to consider. I
guess that's one place the system color constants can come into play. The
problem is getting the time to do it in the first place.

Thanks again for your help. I think I have it under control now.