increment number set up by new record per client (1 Viewer)

Leo_Polla_Psemata

Registered User.
Local time
Today, 03:38
Joined
Mar 24, 2014
Messages
364
MyCounter = Nz(DMax("MyCounter", "CustomerT")) + 1

Hi
With the above line on BeforeInsert event, we can create our own record counter.
For example "invoice number" on each invoice or "quotation number" for each sales offer we send to our cliants.

The problem is that then, our client, can see how many invoices or offers we have issued/sent.

Question
Could we make an increment number based on client account? Set up an increment number based on by new record per client.

For example
Company "ABC" will received quotation number "ABC_001" , for their first offer, "ABC_002" for their second
Company "DEF" will received quotation "DEF_001" , for their first offer, "DEF_002" for their second and so on...

1.ABC_001
2.DEF_001
3.DEF_002
4.ABC_002
5.GHI_001
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:38
Joined
May 7, 2009
Messages
19,169
you need to change MyCounter to Short Text.

Code:
Public Function MyCounter(Byval Company As String) As String
Dim iX As Long, sCounter As String
iX = 1
sCounter = DMax("MyCounter", "CustomerT", "MyCounter Like '" & Company & "*'") & ""
If Len(sCounter) Then
    iX = Val(Replace$(Replace$(sCounter,Company, ""), "_", "")) + 1
End If
MyCounter = Company & "_" & Format$(iX, "000")
End Function

you will need to insert "MyCounter" when you are selecting a Company on your data entry:

private sub Company_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me!Invoice = MyCounter(Me!Company)
End If
end sub
 

Users who are viewing this thread

Top Bottom