Beyond Excel: VBA and Database Manipulation

Today’s post is about a routine I call Check_For_Normal_Entry_Errors. In my opinion, the toughest part of coding is checking for entry errors because just knowing what good values look like isn’t good enough. You also need to anticipate anything a user might do even though it may make no sense for anyone to do whatever that might be. If you’ve been coding end user entry applications for any length of time, you know exactly what I mean. I’m not going to tell you that I’ve solved that riddle, but I do offer some basic validation routines that fit many common entry problems. I also offer the source code so you can fill in the gaps I have left.

Check_For_Normal_Entry_Errors is meant to be a function that is never modified for a specific entry problem. It is for ‘general’ entry problems and for that reason is placed in modTableUpdate. Check_For_Normal_Entry_Errors works in concert with Check_Entry which is a routine meant to be modified for a particular worksheet’s specific validation needs. That routine may have to be modified often; but, not everytime. In the example we are building, we won’t need to modify it. Check_For_Normal_Entry_Errors handles everything we need.

Check_For_Normal_Entry_Errors receives a cell and finds which validation rule should be applied to it from the Fields Definition table discussed in several prior posts. The rules that can be specified in the Fields Definition table that Check_For_Normal_Entry_Errors can apply to the cell are to make sure:

Date fields contain valid dates (NOTE: This does not check to make sure dates are future or past, workdays or not, etc.)

Flag fields contain “Y” or “N” (Yes or No)

Numeric fields contain numbers only

Code fields are limited to values in an Excel Code Table

Type Code Fields are limited to values that match a “Type” value in the entry and a “Code” value in an Excel Type Table

Text fields are limited to a designated number of characters

Custom Edits/Validations are handled by the worksheet’s Check_Entry routine

There are a few support routines here that I have not yet covered. They are small routines that mark cells in error or as having passed our validation rules. Those will be the subject of our next post.

One last note, this routine will never, and should never try to correct anything. ALL this routine does is determine if an entry is valid or not. Now that may seem odd because I spent so much time on frmSelect which is all about helping users correct bad entries. I included frmSelect because it is called by routines Check_For_Normal_Entry_Errors uses, but Check_For_Normal_Entry_Errors tells those routines to NOT use frmSelect for that purpose. Those routines serve multiple masters and when they serve Set_Entry_Defaults, frmSelect will come into play. Just keep in mind that there are times when you want to let the user know their entry is wrong and help them fix it, and times when ALL you want to know is if the entry is ready for posting. Those functions have a lot of overlap, but they MUST be independent of each other.

As always, I believe the code’s documentation explains how the routines works so I’ll not duplicate what’s in the code. Here is the code.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

Processing your reply...

There was an error processing your information. Please try again later.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

Processing your reply...

About This Blog

A blog for developers eager to go beyond paper reports into interactive analytical tools and database applications using tools their customers already own and love – Excel and ODBC.