Random Account Number

[Simon]

I would like account number to be a 5 digit Number and every time one
is created i would like it a random number as long as that number has
not already been used

I just do not like the idea of giving Cusomer 0001, 0002, as it does
not look like we have been in business long, would like random number
like 5849, 2179, 0485, ect


How would i do this in access


Thankls

 

[Joerg Ackermann]

Hallo,

I would like account number to be a 5 digit Number and every time one
is created i would like it a random number as long as that number has
not already been used

I just do not like the idea of giving Cusomer 0001, 0002, as it does
not look like we have been in business long, would like random number
like 5849, 2179, 0485, ect

How would i do this in access

Use a function like this:

If AccountNumber is a string:

Public Function GetNewAccountNumber() As String

Dim strAccountNumber As String

Do
strAccountNumber = Format(Int(100000 * Rnd()), "00000")
Loop While DCount("*", "tblCustomers", "AccountNumber='" &
strAccountNumber & "'") > 0

GetNewAccountNumber = strAccountNumber

End Function

If AccountNumber is a Long:

Public Function GetNewAccountNumber() As Long

Dim lngAccountNumber As Long
Do
lngAccountNumber = Int(100000 * Rnd())
Loop While DCount("*", "tblCustomers", "AccountNumber=" &
lngAccountNumber) > 0

GetNewAccountNumber = lngAccountNumber

End Function


check field- and tablenames

Joerg

 

[John Vinson]

I would like account number to be a 5 digit Number and every time one
is created i would like it a random number as long as that number has
not already been used

I just do not like the idea of giving Cusomer 0001, 0002, as it does
not look like we have been in business long, would like random number
like 5849, 2179, 0485, ect


How would i do this in access


Thankls

Four digit (per your examples) or five digit (per your paragraph)?

In either case, certainly for the four-digit (10000 numbers), probably
for the five, I'd suggest creating a table tblNext with two fields:
AccountNo and Sequence, Long Integer and Double; make Sequence the
Primary Key. You can fill this table with a one-time VBA program:

Public Sub FillAccounts()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngNext As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("tblNext", dbOpenDynaset)
For lngNext = 1 to 10000 ' or 100000 for five digits
rs.AddNew
rs!AccountNo = lngNext
rs!Sequence = Rnd()
rs.Update
Next lngNext
rs.Close
Set rs = Nothing
End Sub

This table can then be used from a Form to populate the account number
for each customer; in the Form's BeforeInsert event use

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim rs As DAO.Recordset
Dim strSQL As String
Dim db As DAO.Database
Set db = CurrentDb
strSQL = "SELECT TOP 1 AccountNo FROM tblNext " & _
"LEFT JOIN Customers ON Customers.AccountNo = tblNext.AccountNo " & _
"WHERE Customers.AccountNo IS NULL ORDER BY Sequence;"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
Me.txtAccountNo = rs!AccountNo
rs.Close
Set rs = Nothing
End Sub

This will return unused (due to the Left Join) account numbers in
scrambled order (since you're sorting the 10000 records by a random
value).


John W. Vinson[MVP]