By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,736 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

How to verify data before closing record?

P: 33
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?
Dec 7 '06 #1
Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,660
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.
Dec 7 '06 #2

P: 33
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?
Dec 8 '06 #3

NeoPa
Expert Mod 15k+
P: 31,660
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.
Dec 8 '06 #4

MSeda
Expert 100+
P: 159
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.
Dec 8 '06 #5

NeoPa
Expert Mod 15k+
P: 31,660
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 :(.
Dec 8 '06 #6

P: 33
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).
Dec 11 '06 #7

Post your reply

Sign in to post your reply or Sign up for a free account.