Need help in VBA Funtion for Autogenerating a Number next to the result of the Funtion

[FA]

Hi freinds,
I have a funtion like the following:
Public Function FindingNo(S As String, D As Date) As String
FindingNo = S & Format$(D, "dd\/mm\/yyyy")

End Function
When i pass S = ABCDE and D = 01/02/2006 it gives me
FindingNo = ABCDE01/02/2006

I want to add something to it.
For the same S and same D i want add a autonumber next to the result so
for the first record for S and D i want to see
ABCDE01/02/2006001
for the second record for the same S and D i want to see
ABCDE01/02/2006002 and so on.
S and D has a one to many relationship with Finding No so there can be
many finding no for the same S and D but i want to differentiate each
Finding No so i have to have a unique number that automatically
populate in the end or each Finding No.

Can some one help me please??

 

[Guest]

Your function has no knowledge of previously allocated numbers.
You need a table to hold the previously allocated number so that you can
increment the digits.
This table will have 2 columns
FindingNo
Modifier
Your function will insert its current result to this table.
To get the next modifier, you will scan the table for the Finding No, if it
is not found you will insert a row with the modifier '01', if it is found,
you will increment the modifier and update the row.
If this is a multi-user system, you will have to make sure this routine
cannot generate duplicate modifiers.

-Dorian