Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Feb 2008
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    question on autonumber (2000)

    question - i don't know if this is possible, can anyone help me either tell me its not possible or how to do it if it is -

    I have a database for time accounting with three tables - clients, employees, input data. i want an autonumber for each input but based on the client, not just consecutive. ie, for the first input for client #1500, i want 1, then 2 for the second input for #1500. when i enter for client #1600, i want to start again with 1 for my autonumber. is there a way to do this? thanks in advance for any help anyone can give me.

    judy

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: question on autonumber (2000)

    There isn't any way that I know of using the Access autonumber field. It can be done however using code. The basic idea is to store the next number to be used in the record for each client. Then when you add a record, you get that number, assign it to the record, increment it in the client table and save both records. It will involve some VBA and either DAO or ADO depending on your comfort level.
    Wendell

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: question on autonumber (2000)

    Another way to do this (without storing additional information) is to set the value of the ID field on your input table to something like this:

    AutoID=DMax("AutoID","InputTable","Left(CStr(AutoI D),4)='" & [Client] & "'"

    where AutoID is the name of the "autonumber" field on the InputTable and Client is the 4-digit client number on the form. Note that this only works if Client is always 4 digits. If not, you may have to adjust this.

Posting Permissions

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