Assign Record Number once submitted

[matthewnsarah07]

I currently have an autonumber field [RequestNumber] to give a unique identifier to each record in my holiday database - if the number is somewhat random that doesn't matter.

The only problem I get is that one someone starts filling out a sheet the number is assigned as they type, if they don't click submit (which emails the form) it is still stored in whatever state it was left.

Question - is it possible to suppress the autonumber until the submit button has been clicked or if not possible what vba code can i use say in on close to delete a record that has not been submitted??

Thanks for your help

 

[namliam]

You can generate your own primary key (search the forum for samples)

But autonumbers are well.... automatic... nothing you can do to change it.

 

[Guus2005]

Question - is it possible to suppress the autonumber until the submit button has been clicked or if not possible what vba code can i use say in on close to delete a record that has not been submitted??

Thanks for your help

Yes, you can!

Use unbound forms and perform an insert query into the table(s) when you press the Save button.

Share & Enjoy!

 

[namliam]

Didnt think about unbound forms... good one Guus...
I hardly ever use them, thats why probably... still once you start inserting from your unbound form...

 

[Guus2005]

Most of the forms i create are unbound. It gives me more control on what's happening. If something happens, it is because i programmed it that way.

@namliam, you don't seem like a macro person to me so do you program every event?

 

[namliam]

I DO NOT USE MACRO'S EVER!!! They stink!

Edit:
Well.. maybe here or there in exceptions AND an ocational startup macro like Autoexec or something I want to do from the command prompt...
/Edit

I program events where applicable yes... or command buttons... etc...

Autonumber fields are to be MEANINGLESS IMHO, that after all is part of the definition of Primary keys. I dont care about the sequence as long as they are unique.

 

[gemma-the-husky]

or you could stick with the bound form, but then you need to either

a) set mandatory fields as "required" in the underl;ying table, so that Access will scrap any incomplete record or

b) use the FORM's before update method to validate the key input fields, before accepting the record.