Customer Number Generator

[jcarroll3102]

I am trying to create a database that has a form with customer number, customer name, amount, and date.

I need the loan number to be automatically generated 10 digits. Digits 8 and 9 need to be 12 more than the previous number. The tenth digit needs to be Zero if sum of digits 1-9 are even, five if sum is odd

Ex: 1100012340
1100012465

This is my first Access post, I would appreciate any help you guys could provide

 

[wongm003]

Here is code you can use to generate the next number... note this function will return a numeric value without leading zeroes. Also need to provide the table and field name where appropriate...

Public Function loan_number() As Long
'=================================================
' This function will return the next loan number
'=================================================
On Error GoTo hndl_err
    Dim strTemp As String
    Dim lngNew As Long
    Dim intTot As Integer, intCD As Integer

    'determine next loan number
    lngNew = max_value("table name", "field name") + 120
    strTemp = Format(CStr(lngNew), "0000000000")

    intTot = 0

    'find sum of first 9 digits
    For X = 1 To 9
        intTot = intTot + CInt(Mid(strTemp, X, 1))
    Next X

    'determine check digit
    If intTot Mod 2 = 0 Then
        intCD = 0
    Else
        intCD = 5
    End If
    
    'return first 9 digits with check digit
    strTemp = Left(strTemp, 9) & intCD
    loan_number = CLng(Left(strTemp, 9) & intCD)
    Exit Function
    
hndl_err:
    loan_number = 0

End Function

Private Function max_value(strTbl, strFld) As Long
'=================================================
' This function will return the last value in
' table(strTbl) field(strFld)
'=================================================
On Error GoTo hndl_err

    Dim rst As Recordset
    Set rst = New Recordset
    
    strSQL = _
        "SELECT Max([" & strTbl & "].[" & strFld & "]) " & _
        "FROM [" & strTbl & "];"

    rst.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic
    max_value = rst(0)
    
normal_exit:
On Error Resume Next
    rst.Close
    Exit Function
    
hndl_err:
    max_value = 0
    Resume normal_exit
    
End Function

 

[SydneyGeek]

Hi, and welcome to the Board!
This code will do what you want.
Note the following assumptions:
1. Your table is called tblLoan, and the key field is LoanNo
2. You already have a valid Loan Number in the key field, and just need to increment from there

Do the following:
1. In Design View in the form, select its Properties and go to the Events tab.
2. Double-click the blank line next to On Current, so you see Event Procedure. Now click the Builder (...) button at the end of that line to go to the code window.
3. Paste this code, replacing the Private Sub Form_Current() and End Sub lines.

Private Sub Form_Current()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim sSQL As String
Dim lngCheck As Long

If Me.NewRecord Then
    Me.LoanNo.Enabled = False
    Me.LoanNo.Locked = True
    sSQL = "SELECT TOP 1 LoanNo FROM tblLoan ORDER BY LoanNo DESC;"
    Set dbs = CurrentDb()
    Set qdf = dbs.CreateQueryDef("", sSQL)
    Set rst = qdf.OpenRecordset
    lngCheck = rst!LoanNo
    rst.Close
    If lngCheck Mod 5 = 0 Then
        Me.LoanNo = lngCheck + 125
    Else
        Me.LoanNo = lngCheck + 115
    End If
    Set rst = Nothing
    Set qdf = Nothing
    Set dbs = Nothing
Else
    Me.LoanNo.Enabled = False
    Me.LoanNo.Locked = True
End If
End Sub

What it does:
When you go to a new record,
1. The code unlocks the LoanNo field for editing
2. The code grabs the last entered value and adds 125 if it ended in 0, or 115 if it ended in 5.

For old records, the field is locked to prevent users from making random changes.

Denis

 

[wongm003]

Hi Denis,

Not sure if your function will always generate the correct number...
ex. if last number was 1000000135 your function would generate 1000000260 I think it should be 1000000250.

 

[SydneyGeek]

Thanks for the note.

If this

    If lngCheck Mod 5 = 0 Then
        Me.LoanNo = lngCheck + 125
    Else
        Me.LoanNo = lngCheck + 115
    End If

is changed to this

    If lngCheck Mod 10 = 0 Then
        Me.LoanNo = lngCheck + 125
    Else
        Me.LoanNo = lngCheck + 115
    End If

then it will work

Denis

 

[wongm003]

Hi Denis,

Sorry.... another example.
Last number = 1000000490 your function would give 1000000615, should be 1000000610 (sum of digits 1 through 9 = 8).

That's why in my code I added the digits up then mod by 2 to determine the check digit.

 

[SydneyGeek]

Yes, you're right. I have an example at work where I did something similar to your approach but I don't have it available now.

Looks like your solution is the one to go with.

Denis

 

[SydneyGeek]

Another go...

I reworked my original approach. The main difference between my code and wongm003's is that mine uses a custom function to determine how many digits you will add.
Event procedure:

Private Sub Form_Current()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim sSQL As String
Dim lngCheck As Long

If Me.NewRecord Then
    Me.LoanNo.Enabled = False
    Me.LoanNo.Locked = True
    sSQL = "SELECT TOP 1 LoanNo FROM tblLoan ORDER BY LoanNo DESC;"
    Set dbs = CurrentDb()
    Set qdf = dbs.CreateQueryDef("", sSQL)
    Set rst = qdf.OpenRecordset
    lngCheck = (rst!LoanNo \ 10) * 10
    rst.Close
    If SumDigits(lngCheck, 9) Mod 2 = 0 Then
        Me.LoanNo = lngCheck + 120
    Else
        Me.LoanNo = lngCheck + 125
    End If
    Set rst = Nothing
    Set qdf = Nothing
    Set dbs = Nothing
Else
    Me.LoanNo.Enabled = False
    Me.LoanNo.Locked = True
End If
End Sub

Custom function (you can place this in the form's code module to restrict its use to this form only, or in a standard code module to make it useable anywhere in the database):

Function SumDigits(lngValue As Long, x As Integer) As Integer
    'counts the first x digits of values in a field
    Dim sValue As String
    Dim i As Integer, iSum As Integer
    sValue = CStr(lngValue)
    iSum = 0
    If x > Len(sValue) Then
        For i = 1 To Len(sValue)
            iSum = iSum + Mid(sValue, i, 1)
        Next i
    Else
        For i = 1 To x
            iSum = iSum + Mid(sValue, i, 1)
        Next i
    End If
    SumDigits = iSum
End Function

Denis

 

[jcarroll3102]

This last solution seems to be doing to the trick, I've gone through a couple test runs. The only problem, and I should have mentioned this before, is this is a shared database. When two people are in the database and try to add a record they get an error that it is trying to create the same record. Is there a work around for this?

Thanks so much for all your help

 

[SydneyGeek]

As I recall, I got around this on one system I built, by generating the ID as soon as the record was created and then saving the record. That lets the next user take the next available record. If the first user cancelled, they would delete the record.

That worked for the client because it didn't matter to them if there were gaps in the sequence. Does that matter to you? If it does, another approach may be required. I'll see what I can dig up.

EDIT -- just found this: A KB article that recommends generating the custom counter in another table, then retrieving it when required.

Denis