Working with form events (Access 2000)

I have a form/subform.

After the user enters some data on the main form I want the program to make sure all of the required fields have been completed before they move to the subform or off of the main form altogether.   I don't want to use the table-level required field property because I want to customize the message.

My question is- where is the best place to put the code that checks to see if the fields have entries?  I get confused by all of the form event choices (UNLOAD, CLOSE, DEACTIVATE, LOST FOCUS, etc..).  Would one of them be the best place or is there some other way I should do this?   I don't want them to be able to leave the form until they fill in certain fields.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

As these fields are essentially required for each reecord in the database, I suggest you use the BeforeInsert event & BeforeUpdate event.

snyperjAuthor Commented:
You mean either one or the other or somehow in combination with each other?
The BeforeInsert event is triggered before a new record is inserted into the table.
In this instance you may want default values for the fields.

The BeforeUpdate event is triggered before a record is updated on the table.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Leigh PurvisDatabase DeveloperCommented:
Before Insert fires when you add a new record - not at the attempt to commit it.
So when it fires - *all* of your controls will yet to be entered properly (I'd imagine) :-)

BeforeUpdate should be adaquate to your purposes.

If this is for new records though - if you're wanting to guarantee control - it's difficult to beat an unbound form.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Leigh PurvisDatabase DeveloperCommented:
I should probably explain a little more - by "when you add a new record" I don't mean when you move to the new record at the end of your recordset, but when you dirty that record - and it gets added (but not committed remember - it's still undoable).
And just to add to joseph's last comment.

The reason you want to check in the BeforeUpdate event as well as the BeforeInsert event is to prevent the users from emptying required fields when they are editing an existing record. They can perhaps change the data, but not clear it out compeletly.
Leigh PurvisDatabase DeveloperCommented:
I see no value in checking in the BeforeInsert event at all.
(Unless you've got default values everywhere - and want to chack them :-S )
With or without default values, if you do not check in the BeforeInsert event then new records can be saved that are missing mandatory data.
Leigh PurvisDatabase DeveloperCommented:
What do you check for in the Before Insert event?
The BeforeUpdate event certainly - but what's to check at the point of Insertion?

"After the user enters some data on the main form I want the program to make sure all of the required fields have been completed"

So after the users have entered data for a brand new record, the requirement is to check that all the required data is present befroe allowing them to enter data in the subform. The data on the main form will be saved to the table, and because this is a *new* record the BeforeInsert event will fire. The BeforeUpdate event will *not* fire for a new record as nothing is being updated.

<pause to check facts and then wipe egg off face>

But silly me I made the incorrect assumption that BeforeInsert fired just befroe the records is saved. In fact (as you no doubt already know, LPurvis) it fires after the first character is entered on a new record - so it's no good for this purpose. A quick check of the on-line help suggests that for new records you need to do this validation in the AfterInsert event, which does fire off after a new records has been created.
Leigh PurvisDatabase DeveloperCommented:
Yeah - it was the main jist of my first post in this thread... and explained a little further in my subsequent one
i.e. "when you dirty that record "

BeforeUpdate is all you need.  New record or not - it fires regardless.
Handy wee thing.
I confess that I did not see your original posting before adding mine.

I'll also retract my comment about needing to check the data in the AfterInsert event, becuase the BeforeUpdate event will not fire for a new record.

The BeforeUpdate event *does* fire for new records, at least in Access XP (aka 2002). I just tested it. :-)

I *think* I'm right in saying that this is a change since Access 97 where I'm fairly sure you needed to use AfterInsert. But not having a copy of Access 97 hanging around any more I cannot check it.

So, looks like BeforeUpdate has the goods!!!
Leigh PurvisDatabase DeveloperCommented:
It was the same back in 97 days :-)
John Mc HaleForensic Computer Examiner, Analyst/Programmer & Database ArchitectCommented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.