Emulating Autonumber

[striker222]

We have an access 2000 database at work used to log purchase orders . The primary key is a numeric field(LONG) named "PO_Number" which really is a sequential number. We manually enter the next number everytime we create a new record.

What I am trying to do is have Access 2000 automatically increment the value of this field for each new record without having to type it in. To make it short, I want to have it act as an Autonumber data type without making the first record start at 0 (our PO #'s started at 100500).

I came up with the idea of writing an expression for the Default Value for this field, but can't get it to retrieve the Max(PO_Number) and add 1 to it.

I can easily do this by making a form in VB, or ASP for that matter, but management want's us to use the datasheet view in Access. Thanks for the help, hope this wasn't too confusing.

 

[FACETS]

Create a temporary table with just one field, a Number field; set its FieldSize property to Long Integer and give it the same name as the AutoNumber field in the table whose value you want to change.
In Datasheet view, enter a value in the Number field of the temporary table that is 1 less than the starting value you want for the AutoNumber field. For example, if you want the AutoNumber field to start at 100, enter 99 in the Number field.
Create and run an append query to append the temporary table to the table whose AutoNumber value you want to change.


If your original table has a primary key, you must temporarily remove the primary key before running the append query. Also, if your original table contains fields that have the Required property set to Yes, the Indexed property set to Yes (No Duplicates), or field and/or record ValidationRule property settings that prevent Null entries in fields, you must temporarily disable these settings.

Delete the temporary table.


Delete the record added by the append query.


If you had to disable property settings, return them to their original settings.
When you enter a record in the remaining table, Microsoft Access uses an AutoNumber field value 1 greater than the value you entered in the temporary table.

 

[MichaelRed]

see faq700-184




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[mbuell]

If you're going to do the "open a separate table" routine, I think the code method, rather than the append query is cleaner. But I've created similar functionality in the past using dmax and assigning the value to a hidden control. The control might be on a hidden form, or on the current form.

However, if you have a big multi-user app, I think you should try to get a code method going, like the one MichaelRed posted. None of the stuff I've designed has had enough users to have a problem with 2 users in one field (all my stuff is for 5 or 6 users). But I have come up with several different ways to manage similar situations. Sometimes the syntax of a reference to a control can be particular.

Mark
<O>
_|_

 

[mbuell]

Also, check this FAQ
&quot;What can I use besides AutoNumber&quot;
faq181-1023
for a simple function technique. If you haven't got several users simultaneously and constantly entering data in the same records something like this should work fine.

Mark
<O>
_|_