Major Problem with Database!!

[laurat]

A problem has arose with a database that is in use by many people. There is a form where users go to enter data about a record, they than press a button once all data is entered that takes them to another form where they get the IRR Num (this is what equipment is tagged with in shop, it is the primary key, it is an autonumber field).

The problem that arose is if the user only enters info in a few fields and than decides to close the database, it gives messages about required fields and that data won't be saved. The users closes the form anyways. The problem is that autonumber is skipped and does not show in the table. Now numbers are not in sequence, records are missing.

Is there a way to solve this?

 

[Fuga]

Does the db require data in other fields than the PK? If so, that might be the problem.

Fuga.

 

[laurat]

There are several other required fields on the form.

 

[Cosmos75]

Autonumber and Required Field(s)

Do you autonumbers need to be in sequence for any particular reason? Autonumbers are generally used to just identify a unique record and not for any other reason (e.g. you wouldn't want to use an autonumber to identify the order in which records are entered).

If there are required fields, then the user needs to enter ALL required fields before a record can be created, hence why they are called required fields.

 

[laurat]

The problem is once users enter data in the first field the record is created in the table. Therefore the number is assigned even if they close the form and enter no more data. I need to know if there is a way for the number to be assigned only after all requiered fields are entered.

 

[Cosmos75]

I think that once you start to enter information, the autonumber is generated. Not quite sure how that works for an incomplete record with some of the required fields left empty, though.

In your other post, you say,

The problem I am having is users enter in one field (which causes the autonumber to be assigned) and than close the form without filling in the remaining data. Than they come back in with all the info and fill it in. This causes a number to be skipped over and not show in the table because it was assigned when the user entered only one field.

Aren't they just adding to the incomplete record then?? Or are they making a NEW record that is complete?

Why does it matter so much if an autonumber is skipped? What do you use it for? See my earlier question....

 

[RichMorrison]

The solution I use for this type of problem is:

1) Make a form for adding records where ALL the fields are unbound.

2) Add command buttons to Save or Cancel a new record.

3) When the user clicks Save, validate the fields as needed. If not valid give an error message and exit the sub. If valid, insert a record into the table. Use DAO code or make a SQL string to insert via DoCmd.RunSQL

4) When the user clicks Cancel, display a confirmation message and don't insert a record.

RichM