Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Increasing number in Customer ID field with existing records

Discussion in 'Information Technology' started by Ningle, Jul 28, 2006.

  1. Ningle

    Ningle
    Expand Collapse
    Guest

    I am almost finished creating a new database which has a table with Customer
    Details. The primary key of the table is my Customer ID and I need this to
    increase my 1 each time I enter a new record.

    I have looked into autonumber but the problem I'm having is that I have
    existing records from my current database that I will be transferring over,
    which I can't change the ID of. Over time we have deleted records here and
    there so there are also gaps in the sequence.

    How can I set up my new database to accept the existing Customer ID numbers
    but start "autonumbering" from a certain point onward?

    Also, any tips on how I can get the Customer ID field to show my number as a
    six digit number (ie. 1215 would be shown as 001215)?

    thanks for any help
     
  2. Loading...


  3. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    Ningle wrote:
    > I am almost finished creating a new database which has a table with
    > Customer Details. The primary key of the table is my Customer ID and
    > I need this to increase my 1 each time I enter a new record.
    >
    > I have looked into autonumber but the problem I'm having is that I
    > have existing records from my current database that I will be
    > transferring over, which I can't change the ID of. Over time we have
    > deleted records here and there so there are also gaps in the sequence.
    >
    > How can I set up my new database to accept the existing Customer ID
    > numbers but start "autonumbering" from a certain point onward?



    You use the Dmax function. You don't use autonumber.


    >
    > Also, any tips on how I can get the Customer ID field to show my
    > number as a six digit number (ie. 1215 would be shown as 001215)?


    That is as format issue. You can change formats for different uses and
    the original number does not change.

    >
    > thanks for any help


    --
    Joseph Meehan

    Dia duit
     
  4. Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

    Check this link on how to do that using the form

    http://www.databasedev.co.uk/automatically_increment_value.html

    about the number, you can use

    Format([FieldName],"000000")



    --
    Good Luck
    BS"D


    "Ningle" wrote:

    > I am almost finished creating a new database which has a table with Customer
    > Details. The primary key of the table is my Customer ID and I need this to
    > increase my 1 each time I enter a new record.
    >
    > I have looked into autonumber but the problem I'm having is that I have
    > existing records from my current database that I will be transferring over,
    > which I can't change the ID of. Over time we have deleted records here and
    > there so there are also gaps in the sequence.
    >
    > How can I set up my new database to accept the existing Customer ID numbers
    > but start "autonumbering" from a certain point onward?
    >
    > Also, any tips on how I can get the Customer ID field to show my number as a
    > six digit number (ie. 1215 would be shown as 001215)?
    >
    > thanks for any help
     
  5. Ningle

    Ningle
    Expand Collapse
    Guest

    Many thanks Joseph & Ofer for your replies.

    I should have mentioned in my original question that this is the first time
    I've created a database so am still very new to it all. I've just tried
    searching through Access help & here on the discussion boards about DMax
    function to try and learn about it (what and where to enter etc etc) and I've
    got to admit I don't seem to be grasping it.

    A number of replies here have talked about code but I'm afraid to say I
    don't know how or where to put this?

    Any other pointers or is it likely this is something beyond someone of my
    experience can do?

    Many thanks again

    "Ofer Cohen" wrote:

    > Check this link on how to do that using the form
    >
    > http://www.databasedev.co.uk/automatically_increment_value.html
    >
    > about the number, you can use
    >
    > Format([FieldName],"000000")
    >
    >
    >
    > --
    > Good Luck
    > BS"D
    >
    >
    > "Ningle" wrote:
    >
    > > I am almost finished creating a new database which has a table with Customer
    > > Details. The primary key of the table is my Customer ID and I need this to
    > > increase my 1 each time I enter a new record.
    > >
    > > I have looked into autonumber but the problem I'm having is that I have
    > > existing records from my current database that I will be transferring over,
    > > which I can't change the ID of. Over time we have deleted records here and
    > > there so there are also gaps in the sequence.
    > >
    > > How can I set up my new database to accept the existing Customer ID numbers
    > > but start "autonumbering" from a certain point onward?
    > >
    > > Also, any tips on how I can get the Customer ID field to show my number as a
    > > six digit number (ie. 1215 would be shown as 001215)?
    > >
    > > thanks for any help
     
  6. ortaias@hotmail.com

    ortaias@hotmail.com
    Expand Collapse
    Guest

    Below is code that was written to increment project numbers that also
    incorporate the year as part of the project number (20060001).
    Incrementing the customer number would be virtually the same except for
    the code related to anyalzing the year. This code, in my program, is
    called using the "set value" option of a macro. The field is
    "projectnum" and the expression is "newprojectnum()". This function
    resides in a module called "generate newnumbers".
    ----------------------------------------------------------------------------------------------------------------------
    Function newprojectnum()

    Rem MsgBox "Program Entered"
    cyear = Year(Date)
    lastnum = DMax("[projectnum]", "consistency")
    testyear = Val(Left(lastnum, 4))
    Rem Test to see if too many numbers
    If Val(Right(lastnum, 4)) >= 997 Then
    DoCmd.Beep
    MsgBox "The number of projects exceeds 997. If valid - fix
    programming."
    DoCmd.Quit
    End If
    Rem incrementing the project number for current year
    If testyear = cyear Then
    newprojectnum = lastnum + 1
    End If
    Rem incrementing at the start of a new year
    If testyear <> cyear Then
    newprojectnum = Val(Str(cyear) + "0001")
    End If

    End Function
     

Share This Page