auto-number for multi criteria or

[jaytejani]

hi all

i m quite fresh in access, i have form that i want auto number for different value or criteria

my table is like that with some sample data

Voucher_no | Voucher_type |
1 | Receipt
2 Receipt
3 Receipt
1 Payment


2 Payment
1 Journal
2 Journal


i want Voucher_no as Auto number for different Voucher_type

is that possible if so, then how????

[John_G]

You can generate the Voucher_No automatically, but you cannot use Autonumber type. You would have to use a small bit of VBA code to do it.

Basically, you would look at the voucher_type, find the maximum currently existing Voucher_no, and add 1 to it.

This is an example of what you might do:

NextNumber = nz(dmax("Voucher_No","YourTableName","Voucher_type = '" & VoucherType & "'"),0) + 1
names in italics represent names in your own database.

I ses Nz to handle the case where the new voucher type isn't in the table yet - you would get an error otherwise.

John

[jaytejani]

Thanks buddy for replay
one more favor can u do where should i put this code, i mean in which property

[John_G]

If you are the only user of the database, you could put the code in the After Update event of the Voucher Type. The code will run every time you type or select a value in the Voucher_Type field, so the Voucher No. could change if you change the voucher type.