So I working on an Access form and ive noticed that when i dont fill out all the fields and close the form, the fields that i did fill out populate into my table as a sort of "incomplete" record which messes up my numbering increment system that i have going. Is there any way of discarding all data entered on form close without making certain fields required in the table properties? I dont want to do this because it gives a message when trying to close and i want to avoid that if possible

CodePudding user response：

The issue of autonumbering incrementing, and that of not wanting to save record with missing fields or values are TWO HUGE different issues.

Try going to a new record, type in a few things, hit esc 2 times (or go to ribbon and use un-do and now exit form.

The record will now be blank and if you exit, then no record is created (however, the auto number will have incremented) So auto number incrementing and saving of records are two VERY DIFFERENT and separate things.

When you start typing into the blank new record on the form - the record becomes "dirty", and a auto number is incremented and assigned to that record.

if you don't want the record to be saved with missing things, then in the forms before update event, you can check for missing fields, or even bad or wrong values, and if you set cancel = true, the record will NOT be saved, nor created.

So, you don't need to set required in the table design. There are often ALL kinds of things you might want to check for. Say you might require first name or last name (one or the other - such things can't be done at table level. So you can have as complex record verification as you want. And as noted, in most cases, you can and would use the forms before update event.

However, preventing a blank record, or a record being saved with missing information? Sure, not problem - a common requirement.

The above has ZERO, but absolute ZERO to do with the autonumber issue.

Access will issue and set the autonumber as SOON as the first key is typed into that form. As noted, you can use undo, and upon exit, NO REOCRD will be created nor saved.

However, the autonumber created will be tossed out - and skipped. The reason for this is for multi-user operation. If two users move to a new blank record, then when one starts typing, the autonumber is assigned. (even if you don't save the record). when the 2nd user starts typing, they also get a autonumber. Now, if both users hit undo (or esc key), then both can exit, and you note that 2 autonumbers will have been skipped. As noted, access works this way so two users can't be assigned the same PK value when adding records.

As a result, autonumbers can NEVER be deemed to not skip. And of course what about deleting records - again, you have gaps.

Autonumbers cannot be used for say invoice numbers, or some kind of external value. Autonumbers can NEVER be assigned ANY more meaning then that of just some "random" like PK value. You can't use those numbers for external business use if such numbers are to say only be sequential without gaps.

If you need a business incrementing number, such as invoice, or job number or whatever? The you need to create your own column, and manage that incrementing in code. (such as assigning that number in the before update event - but as I noted, yo can also CANCEL that before update event for any old reason - including missing fields or even if this is a odd day of the week (any criteria that floats your boat can be used here, and thus when they exit you can prompt them and tell them that the record is incomplete. Or in those special cases, you can even let them exit without a save, but some kind of message probably is better.

So, if you don't want some incomplete record to be saved? Then put whatever you wish into the forms before update event - and set cancel = true (that event has a cancel option built EXACTLY for this purpose). However, that goal of saving, or preventing a record save has VERY LITTLE to do with the issue of not having gaps in the autonumber - that you can't control, and that you cannot prevent. (prevent gaps).

If you need some external business numbering system, you can't use autonumbers. In fact users should never even see autonumbers, and they are for you the developer to build relations between tables.

As such, these internal house keeping auto numbers cannot be used for external business process(s) that require some kind of sequential number without gaps, since that is not their purpose, and worse yet, you can't control or avoid gaps in such numbers anyway.

You can undo, but autonumbers increments means numbers will and are boing to be skipped as a normal operation of the database system.

Since that number is automatic and under control of the database system, and NOT you the developer, the you can only accept how it works and use it - but you can't change the behavour of that auto number. This applies to all and any database systems. In fact later versions of sql server will skip forward by 10,000 when you reboot.

If you don't want to save a record, then put in your code to prevent as such.

If you need a separate issue of some incrementing number for business use? Then add a column to the database and design the increment system to work whatever way you want - but you can't control the built in system, and its not designed with the goal you have here in mind anyway.