AutoNumber problem is driving me CRAZY!!! 1

[INeedAccessHelp]

Hi

I'm attempting to open a form for data entry and display an ID number which is a primary key AutoNumber. Instead of showing the next available number, the field on the form keeps displaying "(AutoNumber)". I want the field on the form to display the next available number in the table. I know this will create a new record, but that is exactly what I want it to do. Please help!!! It will be greatly appreciated. Thanks.

--Rob

 

[THoey]

Access doesn't apply the autonumber until the record is saved. I guess the only way to do this would be to save the record right away and then update it with the rest of the data. Of course, if you have any not null constraints, this won't work either.

How about a separate table with autonumber and a Used (boolean) column. Change your table to have a number instead of the autonumber. When the form opens, populate the ID from the new table and mark that ID as used. The user enters the data and then save to your main table.

Neither of these sound like good answers. Maybe someone else has a better way. Terry M. Hoey

 

[tempclerk]

There are quite a few threads about alternatives to autonumber on the forums. I'd try changing the field to Long Integer, and on your data entry form set the default value to =DMax("[keynumber]","Tablename&quot+1.

 

[Rhonin]

Due to the UNFORTUNATE number of problems access can give you using sutonumber fields, DMax+1 is your best bet.
You can hav it populate as soon as you open the form if you so desire.

So many versions. So many bugs!

 

[haj1503]

Hi rob,

I do something like this:

Change your table field AutoNumber to Number and increment.

after that in your form do like this:
On Current Event:

If Me.NewRecord Then
On Error Resume Next 'It should never occur, just to be sure...
Me!yournumber.DefaultValue = Nz(DMax("[yournumber]", "Your table name&quot, 0) + 1
End If
End sub

Hope can help you.

haj1503.

 

[MichaelRed]

On the orthagonal (Again!) - just 'get over it'. Autonumber should really only be the internal record identifier, not a user manipulated value. You should not even have an AutoNumber field displayed on a form. Users certainly should not be expected/required to 'remember' such a value or be required to use it to access records, so why clutter the forms/reports with it? the only real reason to use autonmuber would be as a key to related tables. You should be able to find/access all records in a (primary) table through keys (indexes) derived from the data (fields) in the record, and the data in the related tables through some (froegin) key (Index) - which may be an autonumber field from the primary table. MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[MichaelRed]

ON the ther hand, if you do not mind 'wasting' the storage, just add a memo field to your table. To develop the "Pointer" to the memo field for the table, Ms. Access needs to create the record. In that creation, 'she' also create the autonumber, so it is available when you 'bring up' the form for the new record.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[ZaZa]

Thanks for sharing that last point Michael, I just learn't something today.....................

ZaZa