Generate increment numbers automatically

[Sangeetha]

I have a table for employment contracts. I have field for Autonumber. This
field gives the autonumber for the number of contracts. The fields are CID
(Autonumber),ENO (Employee number, lookup wizard), C_NO(Contract number).
One employee can have more than one contract as a new contract will be
issued to him/her at the expiry of the contract. In such case, I would like
to C_NO, increment by itself and it should be automated. I know it can be
done, by writing a macro. Can anyone help on this please.

 

[Al Campagna]

Sangeetha,
Not sure I understand your setup, but... assuming the C_No is a Long
numeric,
non-autonumber...
Use your own object names...

=NZ(DMax("[C_No]","tblYourTable"),0) +1
--
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."

 

[Dale Fye]

You don't indicate how you are creating the new contract, so how and when
you call this function would depend on how you are creating the contract, but
here is what I generally use:

me.C_No = NZ(DMAX("C_No", "yourTableName", "ENO = " & me.cbo_ENO), 0) + 1

I would typically put this in the BeforeUpdate event of a form, and would
only do it if me.NewRecord evaluates to True. However, since you will
probably not have more than one person creating new contracts for the same
individual at the same time, you could do this in the AfterUpdate event
associated with the Employee combo box that I would normally have on this
form to select the employee. But you would still need to make sure not to
only generate the new contract number for new records

HTH
Dale