Sequential Number Problem

[Sheila.deJesus]

I've tried using DMax codes shared by the generous members of this group. But i can't get the results i needed. :-(

My table consists of the following fields:

ColID - PK
PolID - FK
AmountPaid
ORDate
ORNo

I added another field which i named "ColNo" - this field should assign a sequential number to a new payment record for a specific PolID.

My table looks like this:

ColID PolID AmountPaid ORDate
11125 123 2,000 10-Sep-06
11126 123 2,000 10-Oct-06
11127 123 2,000 10-Nov-06
11128 456 1,000 8-Aug-06
11129 456 1,000 8-Sep-06
11130 789 4,000 21-Aug-06

Again, my idea is to create a sequential number for the ColNo field:

ColID PolID AmountPaid ORDate ColNo
11125 123 2,000 10-Sep-06 1
11126 123 2,000 10-Oct-06 2
11127 123 2,000 10-Nov-06 3
11128 456 1,000 8-Aug-06 1
11129 456 1,000 8-Sep-06 2
11130 789 4,000 21-Aug-06 1

I placed the code Nz{DMax("field name","table name"),0)+1 on the control source of the ColNo field and i also tried copying numerous similar codes to the BeforeInsert, OnCurrent, etc.. events, but the result is ALWAYS THE NEXT MAXIMUM NUMBERFOR THE "ENTIRE" POLID.

What i mean is, if i enter a new record, it would give me a sequential number of "4". I figured out this is because 3 is the last maximum number as i have shown on my sample table. Even if i use the PolID "789", the number shown on the ColNo field results to "4".

IT SHOULD HAVE BEEN "2". Considering that the last ColNo used for PolID 456 was "1".

Hope someone could help me on this..


Thanks!
Sheila

 

[pbaldy]

You need a criteria in your DMax().

 

[Sheila.deJesus]

Hi Paul. Thanks for your reply. Any idea on how to do so? Sorry, i'm a novice.

 

[Sheila.deJesus]

Hi Paul. Thanks for your reply. Any idea on how to do so? Soory, I'm a novice.

Sheila

 

[pbaldy]

http://www.mvps.org/access/general/gen0018.htm