Question on a module, Public Function... Not sure how to word this,, Code is attached...

[d9pierce1]

Hi all,
Merry Christmas

I ran into something today and I found a way around it by just leaving a blank table "tblTransaction" but I have 6 forms that use this same code (Below)
and just wondering if it is possible to add the following to this or should I have different MakeTranCodes() for each form....?

Existing code is:

Public Function MakeTransCode() As String


    ' number from 000000 to 999999
    ' no 1s or 0s
    ' starts with T
    ' cant repeat
    
    Const MAXLOOP = 10000
    Randomize
    
    Dim Counter As Long
    Dim IsValid As Boolean
    Dim s As String
    Dim TransactionID As Long
    
    IsValid = False
    Counter = 0
    While Not IsValid And Counter < MAXLOOP
        Counter = Counter + 1
        IsValid = True
        s = "T" & Format(Int(Rnd * 1000000), "000000")
        If InStr(s, "0") <> 0 Or InStr(s, "1") <> 0 Then
            IsValid = False
        Else
            ' check if in table
            TransactionID = Nz(DLookup("TransactionID", "tblTransaction", "TransCode='" & s & "'"), 0)
            If TransactionID <> 0 Then IsValid = False
        End If
    Wend
    
    If Counter >= MAXLOOP Then
        MsgBox "WARNING: MakeTransCode Max Looped"
        Exit Function
    End If
    
    MakeTransCode = s


End Function

Code I want to replace the code in RED above with:

TransactionID = Nz(DLookup("TransactionID", "tblTransactionDeposit", "TransCode='" & s & "'"), 0)


TransactionID = Nz(DLookup("TransactionID", "tblTransactionOpenBalance", "TransCode='" & s & "'"), 0)


TransactionID = Nz(DLookup("TransactionID", "tblTransactionPayment", "TransCode='" & s & "'"), 0)


TransactionID = Nz(DLookup("TransactionID", "tblTransactionPurchase", "TransCode='" & s & "'"), 0)


TransactionID = Nz(DLookup("TransactionID", "tblTransactionRF", "TransCode='" & s & "'"), 0)


TransactionID = Nz(DLookup("TransactionID", "tblTransactionTransfer", "TransCode='" & s & "'"), 0)

Sorry for poor description but i am not sure just how to ask this question....


Thanks
Dave

[Minty]

Why not just have a table with an autonumber (always unique) , insert a new record and retrieve the value?
You can set an autonumber to be random, rather than incremental if that helps in your process.

You can wrap the whole thing into a function to generate a unique number.

[June7]

Boolean variables instantiate as False by default and number variables instantiate as 0 by default. So the first lines setting these variables are not necessary. Just as did not need a line setting string variable to empty string - it already is by default.

Exactly what did you have to work around? Why is leaving a blank table a solution? Not clear to me what you need.

[d9pierce1]

Hi June7
Not real sure even if I need anything as I am not clear on what that line of code does?
'Check if in table
Checking I would assume that it has a TransactionID to create a TransCode?
I took out the table that was not used as couldnt use the same table for all the forms.... Gave me the same ID in each form...
or should i say created a identical record in each form so i made different tables and when i removed the transaction table, then i
got an error of coarse. I am not real sure I even need to check this or have that line in their...? I took a class and this was one part of it
but i dont remember what or why this was done this way... I can just leave the blank table in there to eliminate the code error as my other forms
work very well as long as that table is there. I jsut thought it strange and wondered if i could combine the others into that as AND, OR, IF, ElseIf, and so on
but not sure how I would do it.
Thanks

[Welshgasman]

Why not pass in the table name?
However it seems your db is not normalized correctly?
I would expect a field to identify what type of transaction it was, not a table for each?

[Minty]

If you use the same function on every form you won't get a duplicate number.

Create a table with a Autonumber - Lets call it tl_TransIDs and have two fields in it the AutoNumber and a date field, then simply call the function when you want an new unique ID

Public Function fnHeaderID() As Long
    
    Dim sSql As String
    Dim db As Database
    
    sSql = "INSERT INTO tl_TransIDs (TheDateFieldName ) Values (Now())"
    Set db = CurrentDb
    
    db.Execute sSql, dbSeeChanges
    fnHeaderID = db.OpenRecordset("SELECT @@IDENTITY")(0)
    Set db = Nothing


End Function

This will return the last autonumber inserted into the table. It will be unique.
You can format it into something else for display purposes.

[d9pierce1]

Thank you Minty,
I will try this method and after reading it over several times starting to see what it does, keep in mind I dont do this a lot...
I do have a question, Is the ("SELECT @@IDENTITY")(0) something I replace with something or does that stay as is? I have
never seen this nor used it before. Just want to make sure i understand before starting to use it.
Thanks so much
Dave

[June7]

It stays as is. It is retrieving the autonumber ID just created by the db.Execute INSERT action.

[d9pierce1]

thank you all,
Have a great night and holiday season
Will mark solved.
Dave