Increment on unique field (change)

[IHulky]

Hi everyone,

I've got the following issue:

I'm building a database with a invoice part, the orders are collected into 1 invoice per customer.

The view is all the records listed beneath each other.

Customer1 - 2008010101
Customer1 - 2008010101
Customer1 - 2008010101
Customer2 - 2008010102
Customer2 - 2008010102
Customer2
Customer3
Customer3

When I enter the field with tab the highest number is listed (Dmax function) what I want to create is that on every change of customer the invoice number is automatically adjusted with 1 instead of manually adjusting the number for a couple of records before adjusting it again.

I've tried some VBA coding but i'm not very good at it:


Code:

Dim StrVestiging As String
StrVestiging = Value

Dim StrValue As String
StrValue = DMin("[txt_Vestiging]", "klantgegevens1")

StrVestiging = -StrValue = StrNumber

    'Factuurnummer = DMax("[Factuurnummer]", "klantgegevens1")
    Factuurnummer = DMax("[Factuurnummer]", "klantgegevens1") + StrNumber

Factuurnummer = DMax("[Factuurnummer]", "klantgegevens1")

End SubAny help is welcome, thanks in advance.

 

[Uncle Gizmo]

I'm not sure I understand your question, so for clarity, is your invoice number based on an auto number field?

 

[IHulky]

Yes and no.

The invoice number was entered manually before, however this took a great deal of time.

The database has been setup to show all the "open" records (invoices) then it is sorted on alfa.

What has been done is that now it is possible to tab through all the records while maintaining a control feature but adding speed.

With the script above the invoice number is incremented bij 1 for each record, however as the database uses combined invoices this doesn't work.

It is possible that 1 customer has more then 12 orders, the customer however only receives 1 invoice. Therefor the increment should only happen when the customer name changes.

 

[IHulky]

Any ideas anyone?