Should I be using autonumber?

[louis504842]

I'm creating a database to track deficiencies to move from using paper forms to inputting directly into the database. I would prefer that the serial number for each form to be entered automatically vice having to look up the next number each time. I'm considering using the autonumber function to do this, but is there a way to reclaim the number it assigns if the record isn't finished? This is my first project and I'm teaching myself as I go. Any help would be appreciated.

 

[John Big Booty]

Welcome to the forum.

The general advise would be to use the Autonumber to only create the Primary Key for your records. You can then use the DMax() function to automagically construct your Serial numbers as required.

The following Code in the Form's On Current Event Should get you started;

    If Me.YourCounterField = 0 Or IsNull(Me.YourCounterField) Then
        Me.YourCounterField = Nz(DMax("YourCounterField", "YourTableName"), [B][COLOR="Red"]X[/COLOR][/B]) + 1
    End If

Note; Replace X with your seed number (the number you wish your series to start at).

 

[louis504842]

I think that makes sense. But I'm not sure how to implement it. I'm using Access 2010, and when I right-click on the field and open the Build Event tab, I only see Macro, Expression, and Code Builders. Also, I'm using 2 tables to separate the basic form information from the comments and have them linked by a relationship via the serial numbers. Will I need to apply this to field on both tables or will it carry over?

 

[John Big Booty]

You should be able to Code Builder to insert and modify the code.

You should simply store the Serial Number in your master table, and link the two tables using the AutoNumber, Primary Key.

 

[mahenkj2]

@JBB

Thanks for this useful information.

I wanted to use this, but if I use current event then as soon as I go to new record, I find serial number already inserted and thus a new PK generated. In my dB, I have used before update event to log date and user name, so they all are logged.

Till now, I was using directly PK but in that case, if I press ESc, I can abort that record to store.

Now I have used your proposed method in before update event and that seems to work fine. I just want to get advise if this shall be robust in a multiuser environment.

many thanks for your support.

 

[John Big Booty]

Sorry I'm not sure how this solution would behave in a multi-user environment

 

[mahenkj2]

I meant to say that when we use auto number, they are generated by access and skipped if one user is working and escape the record in between. So, there is rare chance of duplicacy. Shall the same thing be true for the number generated by this method.

Also, as I used this in before update event, is it same effective as advised for current event.

 

[mahenkj2]

@ Pat

Thanks for your comments. I have read several of your comments and hope I properly understood why auto numbers are generated and why there are gap generated. I am in fact happy and comfortable with using auto numbers and my all initial databases kept using autonumbers as record identifier untill coming across this post.

I found this post useful to avoid some instances where auto-numbers start appearing from very high numbers. I am not sure but I think sometimes when I imported data from excel in some of access tables and then saw access generating some error files and from thereon next auto-numbers start from very high value. In that case, if I keep using auto-number as an identifier then it is a problem for users.

As I explained in my earlier post-5, If I use this method in before update then even if user just navigates through the form and thus generates a new record if reaches to new record page, is that OK. I fear that in that case, my table would be having many such blank records.

At last, is it advisable to use autonumber directly for record identifier for users point of view.

 

[mahenkj2]

Thanks Pat,

I will give a try.