Entry Validation

[jonneyjudo]

Hello Access Experts,

I want to include a validation on a form. I have an auto number field called ID and a field called Nature Of Incident. I only want to have the auto number generated if the Nature of Incident is NotNull.

Can anyone help?

Thank You

John

 

[pr2-eugin]

Hello jonneyjudo, Welcome to AWF..

Auto Numbers are generated as soon as the Form's New record gets dirty. And they (normally) are primary keys in which case they cannot be Null.. Could you give another shot at explaining what is that you have done and what you wish to achieve?

 

[jonneyjudo]

Hi Paul and TYVM for your quick reply. I have inherited a database that has several fields. The ID field is the primary key. Another field is called Nature of Incident. I have seen that some users are opening a new form, starting to enter information into it, then decide not to. They delete the information already input into the form, then close out of access. Problem is, because a field got 'dirty' an auto number was generated and therefore an entry was created. I only want an auto number to be generated if all fields in my form are populated.

Thanks,

John

 

[pr2-eugin]

As said before AutoNumber are generated right after the Form is dirtied.. To delete all changes, use the Form Before Update.. You need to set a Flag determining if the record is safe to close.. Probably through buttons, then use the flag to Undo all changes.. What you are asking is for the AutoNumber not even to be generated until Save is performed, which is not possible.

 

[Chrafangau]

I have found a workaround for this that works, but it's....messy. Still, if no one can find better, this works.

Rather than editing/creating new entries directly with the form, I have used an append query, run with a button on the form, to take the values from fields in the form and insert them into the table. By checking that the value of 'Nature of Incident' (or 'txtData' in my demo) is not null as part of the On_Click event for the button, we can do this validation before ever dirtying the table, and therefore no auto number is created.

I have attached a database that demonstrates this. Hopefully more experienced members than me can take this idea and make it usable.

 

[spikepl]

@Chrafangau

It's like dumping the baby with the bathwater. The strength of Access is the bound forms, where you do not need to do anything to insert/update data. To craeet you own mechanism for saving data is supefluous work, unless there is some good reason for it.

If an autnumber is created and then discarded, so what? I suspect autonumber has some business meaning in OP's case, which it shouldn't.

@jonneyjudo

If you are concerned about the value of autonumber then explain why. Autonumbers are not designed for human consumption.