Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automatically increment without Autonumber (Access 2000)

    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?

  2. #2
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically increment without Autonumber (Access 2000)

    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

  3. #3
    New Lounger
    Join Date
    Jan 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically increment without Autonumber (Access 2000)

    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.

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically increment without Autonumber (Access 2000)

    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.

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically increment without Autonumber (Access 2000)

    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.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Automatically increment without Autonumber (Access 2000)

    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 <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  7. #7
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically increment without Autonumber (Access 2000)

    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.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  8. #8
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically increment without Autonumber (Access 2000)

    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.

  9. #9
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically increment without Autonumber (Access 2000)

    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.

  10. #10
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Automatically increment without Autonumber (Access 2000)

    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.
    Regards,

    Gary
    (It's been a while!)

  11. #11
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically increment without Autonumber (Access 2000)

    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.

  12. #12
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Automatically increment without Autonumber (Access 2000)

    We are doing it the same way, however, I believe your way is much more elegant. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,

    Gary
    (It's been a while!)

  13. #13
    New Lounger
    Join Date
    Jan 2003
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically increment without Autonumber (Access 2000)

    What happens when you delete a record?

  14. #14
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Automatically increment without Autonumber (Access 2000)

    Would you like to explain your question more fully.
    Obviously you don't mean delete the table which holds the "next" number.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Automatically increment without Autonumber (Access 2000)

    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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •