Autonumber the same as existing key

[Dumarest]

Hello all:

Thank you in advance for your help.

I have a table with an existing key field "Number" into which a number has been manually keyed. I'd like to now create a field called "New_Number" which is an Autonumber field.

Now the problem. I'd like the New_Number field to contain the old Number values for existing records and then continue to add sequentially. If I just add the Autonumber field the values do not match as the physical and logical sequence of the table does not match.

Can some one suggest how I can do this??

Thanks

Dumarest

[wvmitchell]

Here's an idea:

1. Create a new table with the same structure.
2. Change the "Number" field in the new table to autonumber.
3. Append all the records from the old to the new table.
4. Rename your tables so the new one uses the original name.
5. Compact the database

Existing records will have the original number, and any new records added should then start with the next higher autonumber. Don't forget that any relationships will need to be re-set.

This should work but haven't tried it myself.

[missinglinq]

First off, if your field is actually named Number you need to change that; Number is a reserved word, and using it as an object name will cause you problems sooner or later!

Secondly, Autonumbers shouldn't be used in this manner. As John Vinson, an MVP once said, Autonumbers are not fit for human consumption! Many things cause breaks in the sequence of Autonumbers.

You simply need an auto-incrementing number. You can do this extending your current numbers.

Private Sub Form_Current()
If Me.NewRecord Then
  If RecordsetClone.RecordCount = 0 Then
   YourNumber = "1"
'Me.Dirty = False
  Else
   YourNumber = DMax("val([YourNumber])", "YourTable") + 1
Me.Dirty = False
End If
End If
End Sub

If this is a system where you have multiple users working at the same time, you run into the possibility of two or more users starting a record at the same time and hence two or more records could have the same number. To cut the chances of this down to almost zero, I've added the line Me.Dirty = False after the new number is assigned. If this system is only used by one user at a time, you can remove these lines!

Welcome to TheScripts!

Linq ;0)>

[wvmitchell]

I agree that reserved words should never be used for field names, but every new version of Access adds new reserved words that can break your application. Therefore, you need to adopt a best practice as far as naming your fields to be as specific as possible, i.e. instead of "Number" you might use "Order_Number" which avoids conflict with future Access versions, plus has the benefit of being self-documenting.

However, I think that autonumbers have their place, you just need to understand their limitations. In a financial app that might be audited, you should not use autonumbers because as Linq said you can have gaps - nothing riles an auditor more than gaps in a number sequence :-) In that case, I would suggest using a function to create a new record with the next sequential number, and then open the form filtered for that number. The function might look like this (this is old code, I wouldn't write it exactly this way today but it worked):

Function NextToolID(Customer)
    ' add the next sequential tool number
    Dim db As Database, rst As Recordset, tmp As Long
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblHyTechToolLog")
    rst.MoveLast
    tmp = 1 + rst!fldHyTechToolCodeID
    rst.AddNew
    rst!fldHyTechToolCodeID = tmp
    rst!fldCustomerID = Customer
    rst.Update
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    NextToolID = tmp
End Function
 

That's one way to handle unique numbering in a multiuser scenario.

However, if you have a batch process then autonumbers are OK. For example, let's say you have a membership database & each year you run an append query to create an invoice for every active member. Using an autonumber for the invoice number saves you some work, and you can safely show that number to the user.