How can I Increment Key in Form?

[judyblueyes]

I have a primary key 8 digits numeric, for example 20030001. I want a new record in my form to automatically calculate the next number, in this case 20030002. Is there a way to do this? Something like: new number = last number + 1?
Alternatively, is there a way to make an autonumber start at something other than 1, in this case 20030001? Thanks for any help!

 

[Transcend]

To calculate the next number you can get the highest number
and add one to it by a query

SELECT MAX(IDNO)as IDNO FROM tblTable where IDNO is the number you want to increment.

When you get this value back you add one to it to make the next number.


Transcend

 

[judyblueyes]

I actually had tried this - I put =

![newnumber] in the Control Source but I get the #NAME? in the box instead of the number. Do I need to set something else or put the expression somewhere else? Thanks for your help

 

[DanJR]

try putting the code in the Form's BeforeUpdate event e.g. something like...
[tt]
Private Sub Form_BeforeUpdate(Cancel As Integer)
dim rs as dao.recordset
dim strSQL as string
Dim lngMaxID

strSQL = "SELECT MAX(IDNO)as IDNO FROM tblTable where IDNO"
set rs = currentdb.openrecordset(strsql,dbOpenForwardOnly)
lngMaxID = rs.fields(0)

myControl = lngmaxid+1
set rs=nothing
end sub
[/tt]
Cheers,
Dan