# Customer Number Generator

#### jcarroll3102

##### New Member
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

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
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 & "];"

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

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

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

Replies
1
Views
454
Replies
3
Views
323
Replies
5
Views
595
Replies
3
Views
305
Replies
2
Views
2K

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.

### Which adblocker are you using?

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

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