Custom Autonumber on append 1

[aarondewberry]

All

I have a Table 'tblOrders'. Within this table will be 2 fields 'InvoiceNumber' and 'CompanyName'.

e.g,
CompanyName InvoiceNumber

Athlon ATH001
StockShare STS001
Athlon ATH002
StockShare STS002

I will be using a form 'frmOrders' to add new orders to this table.
What I want to do.... is when I pick Athlon from the combobox, 'cboCompany', on the form. I want to append a new order into the table and the next sequenced InvoiceNumber to be generated. So ATH003 would be next, as per example above.
I could have around 10 different companies with their own InvoiceNumber sequence within this table.

Can anyone help with this please? Or would it be better to take a different approach?

Thanks

 

[PHV]

In the After Update event procedure of cboCompany:

strInvNum = DMax("InvoiceNumber", "tblOrders", "CompanyName='" & Me!cboCompany & "'")
Me!txtInvoiceNumber = Left(strInvNum, 3) & Format(1 + Val(Right(strInvNum, 3)), "000")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[aarondewberry]

Brilliant

Thanks for this PHV