Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Feb 2005
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto Numbering New Records with Existing Records (2002)

    Hello:

    I have a number of Resellers in a DB, each with a unique Reseller ID. I want to create a form that will automatically create a new Reseller ID when I add a new reseller. I've tried the autonumber feature, but it won't work since I already have existing data in the Reseller ID fields. I also need to have the new Reseller ID numbers to begin at 2500 and go up by an increment of 1 each time one is added.

    Any help would be greatly appreciated.

    Thanks,
    Craig

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Numbering New Records with Existing Records (2002)

    You can use an AutoNumber field. See <post#=474759>post 474759</post#> by Francois.

  3. #3
    New Lounger
    Join Date
    Feb 2005
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Numbering New Records with Existing Records (2002)

    Hans:

    My problem is a little more complicated. I'm trying to add auto-numbering to a field that already has 2000 or so records that are NOT auto-numbered. As such, I want each additional record to be autonumbered starting at the correct number, but need to keep the existing data in the existing fields.

    Any thoughts?

    Thanks,
    Craig

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Numbering New Records with Existing Records (2002)

    Are multiple users going to be adding new records simultaneously?

  5. #5
    New Lounger
    Join Date
    Feb 2005
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Numbering New Records with Existing Records (2002)

    No, more likely a single user will be adding the accounts...just wanted to auto-populate the field with a unique, sequential Reseller ID.

    Thanks,
    Craig

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Numbering New Records with Existing Records (2002)

    You can use code in the Before Update event of a form based on the Resellers table:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord Then
    Me.[ResellerID] = CLng(DMax("ResellerID", "tblResellers")) + 1
    End If
    End Sub

    where ResellerID and tblResellers must be replaced with the correct names. If you want the new number to be visible the moment the user starts to type in a new record, use the BeforeInsert event. This is more risky if multiple users add records.

  7. #7
    New Lounger
    Join Date
    Feb 2005
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Numbering New Records with Existing Records (2002)

    Thanks Hans!

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Numbering New Records with Existing Records (2002)

    How would this be done in mutiuser database
    threecrow

    Don&#39;t make excuses. It&#39;s what you do, not why you didn&#39;t.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Numbering New Records with Existing Records (2002)

    In a multiuser environment, you shouldn't use the Before Insert event, because it could lead to the same number being used twice:
    - Most recent number is 2500.
    - User A starts typing in a new record, gets number 2501.
    - Before User A has saved the new record, User B starts a new record, and gets number 2501 too.
    Using the Before Update event is safer, because the new number is only assigned when the user saves a record.

Posting Permissions

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