Increasing number in Customer ID field with existing records

[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

 

[Joseph Meehan]

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.

 

[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")

 

[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

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

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

 

[ortaias]

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