How to verify data before closing record?

[Sandro997]

Yes, I know there must be a guide out there somewhere that answers this question quickly, but then again most of them answer a question slightly different than the one I want.

Basically, I have a form, which is just a recordset of a few thousand entries. When ever I leave the current record, I want to perform a check of validity on some of my fields. This is not the same as wanting to perform the check "After Update", that's too frequent. Also not the same as "On Close" or "On Unload", since those apply to the form, not the record.

Where do I need to put this code?

[NeoPa]

You need to put it in the Form_BeforeUpdate event.
Not in any events for the controls but only for the form itself.
If validation fails then just set Cancel=True and the update won't happen.

[Sandro997]

I thought of that one. The problem I have with that is that this form is tabbed. The field I want to verify is not on the main tab. In fact, in some cases there is a subform contained in the tab which contains the field I want to verify(heh, figure that one out). Anyway, when the user creates a new record, the field I wish to verify is empty, but when the user tries to get at the tab where the special field is, the form automatically attempts to perform an Update. Since the special field is not yet populated, the verification will fail, and the user will get an error message. It's true that the user can enter the information immediately after, but this is messy and not really acceptable and I would prefer it only to verify on the final update, before the record is closed.....Make any sense?

[NeoPa]

It makes sense (sort of - I'm surprised that an update is performed on the main form dataset).
This is not an insurmountable problem though. You can surely do a test to determine if this update is caused by just selecting the form. If it is then bypass the verification code.

[MSeda]

I've had similar problems using tab controls and really the easiest way around records updating when moving between subforms is to use unbound forms and then insert all data when the user clicks an enter button via an SQL statement that pulls all of the fields from the form. And of course you could run any number of validations prior to that.

[NeoPa]

I've had similar problems using tab controls and really the easiest way around records updating when moving between subforms is to use unbound forms and then insert all data when the user clicks an enter button via an SQL statement that pulls all of the fields from the form. And of course you could run any number of validations prior to that.

That's a good solution but becomes more impractical the more complicated the subforms are.
With this in mind, it's probably worth getting to grips with a method which works in all circumstance.
It's a shame that MS don't provide an option to disable that behaviour though :(.

[Sandro997]

In the end I solved it by putting all of the required fields in the default tab of my form. In this way I don't have to worry about BeforeUpdate being called automatically. The validation still fails all the time, but at least the error is intuitive to the user(at least I hope it is).