Uninserted Record Disappears

[Dan McClelland]

SQL Server 2000 back end and Access XP adp front end.

Objective: User enters a new record and misses a required
field. Application warns user and returns them to the
form to fix their mistake and allow them to continue.

Simple explanation of problem: If I skip any required
fields (bound form), upon attempting to save record, I get
a variety of messages. Then, upon returning to the form,
my newly entered data is gone, was not inserted, and I'm
staring at a blank form and must re-enter. My newly
entered, non-inserted data disappears.

More details, if needed: If tried code in the form's
BeforeUpdate event to stop it from attempting to insert
the record if a required field is blank. I've got code in
the form's OnError event, same reason. I've got code in
the form's OnUnload event to prevent it from closing.
Regardless of what combination of events I trap and how I
handle them, my newly entered data disappears.

Form has been based on a sproc and an SQL statement (I've
tried both). Sproc and SQL statement join a few tables,
but I'm updating only ONE table on the many side, and my
Unique Table property is set.

 

[Cheryl Fischer]

Dan,

Have you tried using the Before Insert event? That's the one to use with a
New Record validation. Before Update would be the event to use if you are
validating edits to an existing record.

hth,

 

[Dan McClelland]

The BeforeInsert event fires upon entering the very first
character in any field of a new record. The event fires,
alerting me that I haven't filled in my required fields.
This just didn't seem like the proper event, since it
fires before I have a chance to fill out ANY of the
required fields.

 

[Kevin]

I handle the validation problem by using a form which not
bound. When my user presses the Save button, then I
validate all data and give the user one message that is
built with all the appropriate information depending on
what is missing. This way I give the user on message. This
way the user is flagged up fron. I also change the label
text color for any field which is not correct. By using an
unbound form, I have complete control over when the data
is inserted, data validation, etc.

Making this type of change will require a fair amount of
work to put into place, but in the end, I am confident
that everything is correct when the data is inserted.

Hope this helps!

Kevin