Automatically increment without Autonumber (Access 2000)

Topic

New Reply

Subject: Automatically increment without Autonumber

I have imported a bunch of records into Access and each record has a unqiue ID number which I am using as the table key. I have made a form to add/edit the records and I want the ID number to increment each time I add a new record. I can’t use an Autonumber because I need to preserve the existing ID numbers. Can someone tell me how to automatically increment the ID number without using an Autonumber field?

Reply | Quote

Replies

Hi Irene,
Here’s some code that should do the job. You will have to substitute your table name and your control name. Open your form, go to Design View and then View Code. You should see a window with the words “Option Compare Database” at the top. Paste this code below “Option Compare Database”. What it does is set up a module level variable called mID. When the form is opened, the “Form_Open” event fires. It opens the table you are adding into (replace “table1” with the name of your table) and goes to the end and gets the last ID and puts it into mID for use later. Because the ID is a key field, the highest number will always be in the last record. Then, the BeforeInsert event (which fires as soon as you start typing in a new record) will copy whatever is in mID into the form control called “txtID” (replace this with the name of the control on your form). You should also set the Enabled property to NO for the control I am calling “txtID”.

I hope this helps,
Gwenda

Dim mID As Integer

Private Sub Form_Open(Cancel As Integer)

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open “table1”, CurrentProject.Connection, adOpenDynamic
rst.MoveLast
mID = rst!id
rst.Close
Set rst = Nothing
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
mID = mID + 1
Me.txtID = mID
End Sub

Reply | Quote

Hi

I went in to my design view of sub form, I was not sure about substitute my table name and control name so I just used the copy of the database. I pasted the coding under “Option Compare Database” saved and tried to move on when I received this Visual Basics comment

“Invailed SQL statement expected “Delete” “Insert” “Procedure” “Select” or Update.

Help.

Reply | Quote

Hi Irene,
It could be one or two things or both …
1) you *definitely* have to replace “table1” with the name of the table your form is based on and you also have to replace the control I called “txtID” with your control name.
2) perhaps you do not have a reference set to ADO. To do this, go to the code window and choose TOOLS, References and check to see if “Microsoft ActiveX Data Objects [2.0] Library” is selected. If not, scroll through the list and select it and then try the code again.

Reply | Quote

Irene – If you attach the database here then I will be very helpful. Also I just realized there is another place where you have to change the code: the line that says “mID = rst!id” – change “id” to the name of the ListID field in your table.

Reply | Quote

I tend to use another table to hold the ID number.
When I need to get the “next” number (usually in the Form_BeforeUpdate event) I just update the ID in the special table by 1 then read the special table’s ID number.
This takes care of ever increasing numbers as well as the multi user situation.
Pat

Reply | Quote

Thanks Pat. I used your idea but I got the “next” number on the Form_BeforeInsert event which worked beautifully. As soon as the new record is dirtied, then the ID number is generated for it.

Reply | Quote

If your current ID number are in a Long Integer field, then there is an easy way to use an AutoNumber.

Create a NEW table that has the same structure as the current table, except make the ID column an AutoNumber. Then create an append query that appends the current data into this new table. The ID column should be appended to the Autonumber column.

The Autonumber will take on the ID number of the current table and your next autonumber will be one more than your highest current ID.

If this is a one of, then you should be fine.

Reply | Quote

Thank you Bryan and Pat- I am going to use Pat’s idea because the ID field is actually a Text field and some of the entries contain characters as well as numbers.

Reply | Quote

Another option if you don’t want a table to get too large using an append query storing all increments, Create a table with only one row and record set to 1. Have code launch a make table query that gets the record or “1” from the table, adds one to it and then re-creates the table with the new number. This way the table only stores one record and does not grow when using an append query. I did this the other evening when I needed to autoincrement a datafield and it worked quite well.

Just another idea.

Reply | Quote

Hi Gary – The code I wrote doesn’t include an append query. It’s a table with only one record with one field called “NextIDNumber”. In the BeforeInsert event of the form, I open a recordset based on this table, grab the NextIDNumber and put in on the form, increment “NextIDNumber” by 1 and .Update.
So, I think I’m doing it the same way you are.

Reply | Quote

We are doing it the same way, however, I believe your way is much more elegant.

Reply | Quote

What happens when you delete a record?

Reply | Quote

Would you like to explain your question more fully.
Obviously you don’t mean delete the table which holds the “next” number.
Pat

Reply | Quote

For the database I am using, nothing happens when a record is deleted. The increment only happens on the on click event of a button that the user must press to add data to the database. Thus when a record is deleted, the tables are all set up with cascade delete enabled so all of the records in other tables associated with the original ID are deleted. I do not re-use any numbers that were deleted. It would cause too much of a headache to maintain it. (It also isn’t necessary)

Reply | Quote

The problem putting it in the BeforeInsert is that what happens if the user decides not to insert the record, you will end up with a gap in the numbers.
That’s why I would put it in the BeforeUpdate event of the form and test if we are writing a NEW record.
Pat

Reply | Quote

Hi Pat,
I’m with Gary on this one. I don’t care about gaps in the numbers.

Reply | Quote