Prevent blank records (1 Viewer)

pd06498

Registered User.
Local time
Today, 22:40
Joined
Dec 30, 2003
Messages
80
Hi

In my database, I have a couple of forms to enter data.

If I open one of these forms then close it again without entering anything, no new record is saved. However, if I enter data into the first field, tab to the next, then decide not to enter the date and delete all fields and close the form, I end up with blank records.

I understand how this occurs, but how can I stop it occurring? I thought of regularly running a delete query to remove all blank records, however cannot link the delete query to a command button. I am sure there is a simple way to link the delete query to the 'On Close' property of the appropriate form, but cannot work the code out for that.

Is this the right way to go, or should I be looking at stopping the null entry before it happens.

Any advice appreciated.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:40
Joined
Feb 19, 2002
Messages
42,970
A simple solution is to define those fields which are required as required in the table definition. That way Jet won't let you save the record unless at least those fields are filled in.

If you want to solve the problem without relying on required fields, your code needs to go in the BeforeUpdate of the FORM. NO OTHER event is suitable. In the BeforeUpdate event, you can cancel the record update if an error is found. The same code in the close event for example would fail but not give any errors. It would fail because the record would already have been saved by Jet. The Form's BeforeUpdate event is the last event exectuted prior to the actual save action and it CANNOT be bypassed regardles of what the user does short of a power off. That is what makes it the event of choice for this type of processing.

Code:
If IsNull(SomeField) or IsNull(SomeOtherField) Then
    msgbox "Record will not be saved.  Please enter required data", vbOKOnly
    Cancel = True
End If
 

pd06498

Registered User.
Local time
Today, 22:40
Joined
Dec 30, 2003
Messages
80
Thanks Pat, that worked. And thanks to other responders also.
 

Users who are viewing this thread

Top Bottom