Autonumber & Record Updates

[JohnnyM]

Hello -

I'm looking for detailed information (event structures/orders/variables) surrounding autonumber and record update events.

Specifically, I have a form for adding new records to a table. Some of the fields are linked to other tables (displayed in combo-boxes). If a user tries to enter data in one of those fields that isn't in the underlying table, I give them the option to add it. This works by opening another data entry form for the appropriate table, taking the value entered by the user and passing it to the new form and setting that as the default value of the appropriate field (not the actual value). This is deliberate, so that if the user decides not to add the record and immediately escapes the form, the autonumber is not wasted. Setting the default value does not cause a new autonumber to be generated.

Let's assume that there are only two fields in the underlying table: an autonumber primary key and a description field. So, the data entry form is open and the default value of the description field is set to the value the user tried to enter in the previous form, and that field has the focus. The autonumber field has not incremented - it says [Autonumber].

Now, the problem occurs when I tab or enter out of the description field - the form doesn't "take" the default value. Focus just moves to the autonumber field. Hit enter again and it moves back to the description field.

What needs to happen to trigger the autonumber to increment? I would expect that as soon as I left the description field, Access would generate the autonumber - but it doesn't happen.

Anyone know the events underlying the autonumber field - and perhaps how they mesh with when a record is actually "created" and "added" to the table? Are there any usable variables associated with autonumber? Where's it stored, etc.?

Thanks,
John

 

[KeithG]

The autonumber should be generated whenenter data into the description field.

 

[GaryPanic]

if you look at my post renumbering there is coding on t his - and you would trigger this after the update of description field -

only asked for help on this today - so it should be their under my tag...
g

 

[JohnnyM]

Thanks to Keith & Gary, but...

Keith - the default value is sitting in the description field, I hit "enter" and nothing happens. Focus moves to autonumber field, which does not increment.
Gary - "...after the update of description field..."

Yes - but the key is - what event or function "updates" or "enters" the default value into the description field? Pressing "enter" or "tab" doesn't do it, so "on exit" or "on lost focus" events don't trigger it.

Typing text in the description field does it - but that kind of defeats the purpose of passing in the value in the first place.

Perhaps another way to phrase the question is: "How can I get Access to assign the default value to the actual value of the control/field?"

 

[John_W]

I suspect it's something to do with you not having actually created a new record for the default value to be applied to. But really I just wanted to say you shouldn't be so bothered about 'wasting' autonumbers. If you're relying on them to be sequential or something, that's not good and you'll run into all sorts of difficulties. Don't try and use autonumbers as meaningful data - they're just good for uniquely identifying records.

 

[JohnnyM]

Thank you John.

You're right - the concept of "wasting" an autonumber is a bit obsessive. But say, for example, the autonumber is an Invoice Number and your Accounting dept doesn't want any gaps in invoice numbers? There has to be a way to handle it.

I read about using the Default Value instead of the Value in a book. I've sent an email to the author in hopes of getting some relief. If he replies, I'll post his comments.

Thanks again.

 

[John_W]

say, for example, the autonumber is an Invoice Number and your Accounting dept doesn't want any gaps in invoice numbers? There has to be a way to handle it.

I wouldn't use autonumbers for invoice numbers, or for anything meaningful. You can code something to assign sequential numbers to invoices if that's what you need.

 

[Fear Naught]

If you want to use a sequential number for accounting purposes then the autonumber is not the one to use. There are plenty of threads on this forum that cover sequential numbers that will help you.

Autonumber in Access is similar (sorry you Gurus) to a GUID in SQL Server. It is used purely as a PK in order to provide a true relationship to other tables and should never be used for meaningful data as posted above by John_W.

 

[GaryPanic]

on invocie the coding i put in is what it is used for
and in over 6000 clicks only had 4 duplicates - 2 people click the button same time .
....