Firstly do not rely on an autonumber to give you the number of rows in a
table. All it guarantees is a unique value for each row. If a user
begins
to insert a record and then abandons it for instance there will be break
in
the number sequence. To get the total number of contracts, i.e. rows in
the
table, you just need to include a text box control on your data entry form
or
in a report with a ControlSource of:
=DCount("*","Contracts")
where Contracts is the table name.
If you wish to store the contract number for each employee in the table,
starting at 1 for each employee then put the following code in the
AfterUpdate event procedure of the ENO control on your data entry form:
If Me.NewRecord Then
Me.C_NO = Nz(DMax("C_NO","Contracts","ENO = " & [ENO]),0)+1
End If
When adding a new record this will look up the highest existing C_NO value
for the current employee and add 1. The Nz function returns a zero if
there
are no existing records for the employee in question, so adding 1 to that
gives 1.
However, if you also have a column in the table such as ContractDate you
don't really need to store the contract numbers for each employee at all.
They can be computed and shown in an unbound text box in your data entry
form
or in a report with a ControlSource of:
=DCount("*","Contracts", "ENO = " & [ENO] & " And ContractDate <= #" &
Format
([ContractDate],"yyyy-mm-dd") & "#")
BTW I see that you've used the 'lookup wizard'. I'd strongly recommend
that
you avoid this feature in future. For reasons why see:
http://www.mvps.org/access/lookupfields.htm
You can achieve the same functionality by using a combo box bound to the
ENO
field on your data entry form. Data should only ever be entered or edited
in
a form, never in the raw datasheet view of a table, so using the 'lookup
wizard' in table design serves no purpose, other than to cause confusion
as
to the real values in the column.
Ken Sheridan
Stafford, England
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.
! I code option button groups so I