How form event code works on datasheets

[ncsthbell]

I hope I can explain this to make sense..... I have a form (datasheet) and the record source is TABLEA. TABLEA has 3 columns defined as required keys, COL1, COL2, COL3. When user is in row in datasheet doing data entry, I have put some validation code on the 'exit event' of each column to do some validation. The problem that I am having is that the user can enter data in COL1 or any COL and use the mouse to click into a different column so they actually did not execute the 'on exit' event since they did not go into the column. When they do this, they get MFD error because they skipped over a column that is defined as a primary key on the table. So, I thought I would try also putting the same validation on the Form event "Before Update", however, if they key COL1, then decide they don't want to enter it and hit the escape key the "Before Update" form event executes and runs the validations for each column and it seems to get 'stuck' and pops up my warning messages from the validation over and over.
I wish I could tell users they must tab through each field... but they do not want to do it that way, they want to skip around on the record. Any suggestions on how to handle this????

[janders468]

It sounds from your description that they are adding new records. Typically I wouldn't let users make this change directly on the table, it would probably be best to have a data entry form (for example with three textboxes, one for each column). Don't bind the textboxes to the underlying data and provide a button which uses these values to trigger a query appending these values to the table. This way you can loop through all of the text boxes and validate the data for type, nulls, etc.

[Fiddler2]

I hope I can explain this to make sense..... I have a form (datasheet) and the record source is TABLEA. TABLEA has 3 columns defined as required keys, COL1, COL2, COL3. When user is in row in datasheet doing data entry, I have put some validation code on the 'exit event' of each column to do some validation. The problem that I am having is that the user can enter data in COL1 or any COL and use the mouse to click into a different column so they actually did not execute the 'on exit' event since they did not go into the column. When they do this, they get MFD error because they skipped over a column that is defined as a primary key on the table. So, I thought I would try also putting the same validation on the Form event "Before Update", however, if they key COL1, then decide they don't want to enter it and hit the escape key the "Before Update" form event executes and runs the validations for each column and it seems to get 'stuck' and pops up my warning messages from the validation over and over.
I wish I could tell users they must tab through each field... but they do not want to do it that way, they want to skip around on the record. Any suggestions on how to handle this????

Try using the "After Insert" or "On Dirty" event for the form, as opposed to the field event.

[missinglinq]

"I wouldn't let users make this change directly on the table, it would probably be best to have a data entry form"

janders468, please read threads carefully before responding! The OP clearly stated that

"I have a form (datasheet) and the record source is TABLEA"

No mention is made of the users entering data directly in the table!

Doing validation to assure that fields actually have data in them (as opposed to validation to assure that the data entered is of the proper type) should always be done in the form's BeforeUpdate event, for the very reason mentioned; doing the validation in a field event doesn't work if the user doesn't at least tab/click into the field. What you need to do is to post your validatrion code from the BeforeUpdate event that was causing the problem.

Linq ;0)>

[janders468]

I suppose I should be more clear, in a situation like this I find it to be more robust to add the data through a query containing the values you wish to add instead of operating on the table itself via the data bound form, however linq, you are right, while this solves the problem it is somewhat tangential to the question.

[ncsthbell]

Thanks for all the replies. Unfortunately, I inherited an application already built in which the 'previous' owner create the forms in datasheet view and created them with tables as the record source so there are no 'add or delete record' buttons that I could use to put validation code on. All the validation has to be done on the field or form events. I would not have designed this way myself, however, I am stuck with trying to fix the 'bugs'. I will try some of the suggestions and let you know how it goes.
Again, Thanks!