Auto Fill Custom Number on New Record

[Felix Oehl]

I have a basic database set up for keeping track of Warranty Claims which
uses two tables, one for each of the claims and one for all the parts within
those claims. These are linked on a one to many relationship by the ClaimID
number.

When I create a new record using the form I've made, I want the ClaimID for
the new record to autopopulate with the next claim number in the sequence.
The problem I have is that I need it to be an alphanumeric code. For example
WPC8001, WPC8002 etc.

Can anyone tell me how I can get this to work? I'm new to Access and I'm
afrid my programming experience is pretty minmal too.

Thanks.

 

[Al Campagna]

Felix,
Is the WPC portion of the ClaimID, always the same, or can it vary?
Assume you have a num field called IDNum... and the max value is 1422.
To increment a number whenever a new record is created set the
Default Value for your IDNum control to...
=NZ(DMax("Val([CustID])","tblCustomers")+1,0)

This will add 1 to the maximum current value of IDNum

Now just concatenate in a calculated field...
="WPC" & IDNum
will display "WPC1423"
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Tom van Stiphout]

On Fri, 3 Apr 2009 01:39:02 -0700, Felix Oehl <Felix
(e-mail address removed)> wrote:

This is a FAQ. You should have no trouble finding information. Here is
one link:
http://www.eggheadcafe.com/conversation.aspx?messageid=31240584&threadid=31218478

-Tom.
Microsoft Access MVP