Customer Number Generator

jcarroll3102

New Member
Joined
Apr 2, 2005
Messages
5
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

wongm003

Board Regular
Joined
Aug 8, 2005
Messages
237
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...

Code:
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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.

Code:
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

Board Regular
Joined
Aug 8, 2005
Messages
237
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251

ADVERTISEMENT

Thanks for the note.

If this
Code:
    If lngCheck Mod 5 = 0 Then
        Me.LoanNo = lngCheck + 125
    Else
        Me.LoanNo = lngCheck + 115
    End If
is changed to this
Code:
    If lngCheck Mod 10 = 0 Then
        Me.LoanNo = lngCheck + 125
    Else
        Me.LoanNo = lngCheck + 115
    End If
then it will work

Denis
 

wongm003

Board Regular
Joined
Aug 8, 2005
Messages
237
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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:
Code:
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):
Code:
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

New Member
Joined
Apr 2, 2005
Messages
5
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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
 

Forum statistics

Threads
1,136,991
Messages
5,679,004
Members
419,797
Latest member
ikethegenius

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top