Auto number

  • Thread starter Thread starter Jody
  • Start date Start date
J

Jody

I have two tables one will house the main account information and the second
will have the monthly amount charged, on the second table, I want an
autonumber that will tell me what payment number this is for this account.
These accounts all start at various times, so one might be on month 50 and
another on month 1. I want to be able to run reports showing the average by
different month #'s. Is there any way of automating this?
 
I have two tables one will house the main account information and the second
will have the monthly amount charged, on the second table, I want an
autonumber that will tell me what payment number this is for this account.
These accounts all start at various times, so one might be on month 50 and
another on month 1. I want to be able to run reports showing the average by
different month #'s. Is there any way of automating this?
Click to expand...

An Access Autonumber field will not serve your need here... and in fact I
would recommend that you not store this number in your table at all. It would
be easy to use a calculated field such as

DCount("*", "[yourtable]", "[AccountNo] = '" & [AccountNo] & "' AND
PaymentDate <= #" & [PaymentDate] & "#")

to dynamically determine which payment this is.

If you want to keep it in the table anyway, you can use a Long Integer field
in the table (not an autonumber), and calculate which payment number this is
in the Form you use to enter the data. Perhaps in the Subform (bound to the
payment table) use code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!PaymentNo = NZ(DMax("[PaymentNo]", "[Payments]", "[AccountNo] = '" &
Me!AccountNo & "'"))+1
End Sub
 
Back
Top