Solved

Create new Random 6 digit field for each record.

Posted on 2003-11-26
22
889 Views
Last Modified: 2012-06-22
I have a table which holds clients; currently i have the autonumber field generating unique numbers, however, with the constant deleting of records this isn't working as it continues to "increment" and skips many available numbers.  So I would like to assign new random numbers (6 digits) to each one and then I will rename that field to the new ClientID ... but I am having a hard time setting up a recordset.  For some reason, I can't figure out how to say, "Open the clients table and then go thru each record assigning a random 6 digit number to the NEWID field" .... hmmmm
0
Comment
Question by:tljprincess
  • 9
  • 7
  • 2
  • +3
22 Comments
 

Expert Comment

by:SpinManK
ID: 9828723
I think you could use something like this:

Dim rec as DAO.Recordset
Set rec = new CurrentDB.OpenRecordset("TableName")
While Not rec.EOF
  rec(Item("NEWID"))=Int((999999-100000)*Rnd+100000)
  rec.MoveNext
Wend

This should work.
0
 

Expert Comment

by:SpinManK
ID: 9828732
I have an error in the code of my last post, but the following should be right:

Dim rec as DAO.Recordset
Set rec = new CurrentDB.OpenRecordset("TableName")
While Not rec.EOF
  rec(Item("NEWID"))=Int((999999-100000)*Rnd+100000)
  rec.Update     <------- add this line
  rec.MoveNext
Wend

That´s it
0
 

Author Comment

by:tljprincess
ID: 9828752
when i put your code in a module, the SET line and rec.update lines are red as if i need to set a reference .. i have microsoft dao 3.6 obj library set ... any ideas?
0
 
LVL 5

Expert Comment

by:Atropa
ID: 9828774
Here is what I would do..

Dim rsCNN As ADODB.Recordset
Dim NewID as Double
   Randomize                        ' Use this to get a truly random number

    Set rsCNN = New ADODB.Recordset                             ' Name of RecordSet
    rsCNN.CursorType = adOpenStatic                             ' Type of RecordSet
    rsCNN.CursorLocation = adUseServer                          ' Location of Recordset
    ' Open RecordSet
    rsCNN.Open "YOUR_TABLE_NAME_HERE", _
               CurrentProject.Connection, _
               adOpenStatic, adLockReadOnly


    Do Until rsCNN.EOF = True                        ' Loops through until the end of the recordset
        NewID = Fix((9999 * Rnd) + 1)              ' Creates the new id number
        rsCNN.Update "New ClientID", NewID      ' Updates the New ClientID Field
        rsCNN.MoveNext                                   ' Moves to the next record
    Loop


I hope that this works for you.  /Atropa
0
 

Author Comment

by:tljprincess
ID: 9828856
ok, i could not get the 2nd option to work ... do i need a reference and to what?  
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9828890
Hi tljprincess
Try this:

1... Create this sub
' sub to loop through recordset assigning newID
Usage: Call UpdNEWID


Private Sub UpdNEWID
  Dim Myrs as ADODB.Recordset
  Set Myrs = New ADODB.Recordset
 
  Dim cmd As ADODB.Command
  Set cmd = New ADODB.Command
 
  With cmd
    .CommandText = "table1"
    .CommandType = adCmdTable
    Set .ActiveConnection = CurrentProject.Connection
  End With

  Set Myrs = New ADODB.Recordset
  Myrs.CursorLocation = adUseClient
  Myrs.Open cmd, , adOpenDynamic, adLockOptimistic
 
  If Myrs.RecordCount > 0 Then
    Myrs.MoveFirst
    While Not Myrs.EOF
      With Myrs
        .Fields("NEWID").Value = RandomPassword(6)
        .Update
      End With
      Myrs.MoveNext
    Wend
  End If

End Sub



2... Create a new module
Name: modRandFunctions

Paste the Code below in the new module then save it.

'==================================
'modRandFunctions
'==================================
Option Compare Database
Option Explicit

Public Function RandomPassword(Optional size As Integer = 8) As String
 
'   Called By   Form(frm_Customers).Procedure(chkWebEnabled_AfterUpdate).Line(8)

  Dim n As Integer
  Dim sRandom As String
  ' max Long = 2,147,483,647  (10 digits)
  ' build password in 9 digit increments
  n = size
  Do While n > 9
    sRandom = sRandom & mRandomLong(9)
    n = n - 9
  Loop
  If n > 0 Then
    sRandom = sRandom & mRandomLong(n)
  End If
  RandomPassword = sRandom
End Function
Private Function mRandomLong(ByVal size As Integer) As String
'   called By   Module(modCustomerFunctions).Procedure(RandomPassword).Line(11)
'               Module(modCustomerFunctions).Procedure(RandomPassword).Line(15)
 
  Dim lLower As Long
  Dim lUpper As Long
  Dim lRandom As Long
  Dim sRandom As String
  Dim i As Integer
 
  ' max Long = 2,147,483,647  (10 digits)
 
  If size < 1 Then size = 1
  If size > 9 Then size = 9
 
  If size = 1 Then
    lLower = 0
  Else
    lLower = 10 ^ (size - 1)
  End If
 
  lUpper = 10 ^ size - 1
 
  Randomize
  lRandom = Int((lUpper - lLower + 1) * Rnd + lLower)
  sRandom = Format(lRandom, String(size, "0"))
 
  'For i = 1 To Len(sRandom) Step 2
  '  Mid(sRandom, i, 1) = Chr(Asc("a") + CInt(Mid(sRandom, i, 1)) + i)
  'Next i
 
  mRandomLong = sRandom
 
End Function
'=========================


Result:
NEWID
270092
932261


Alan


0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9828908
In any code window Tools > References > Microsoft ActiveX Data Objects 2.xx library
0
 

Author Comment

by:tljprincess
ID: 9828926
yes, i have the Microsfot ActiveX Data Objects 2.6 library referenced, fyi
trying 3rd option ... luck to me!
0
 

Author Comment

by:tljprincess
ID: 9828951
I keep getting a compile error on the line "  Dim Myrs As ADODB.Recordset" saying that the user-defined type is not defined. ...
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9828969
Try removing your reference then close the references dialogue then open and add referece again.

Alan
0
 

Author Comment

by:tljprincess
ID: 9829037
adding the reference to the ADO 2.6 library? if yes ... did that and no luck ... when i run the sub i get the error "runtime error 3265 ... item cannot be found in the collection correspoding to the requested name or ordinal." ... i did change the table1 name to reflect my table, "clients new".
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 26

Expert Comment

by:Alan Warren
ID: 9829054
Hi

Create a query that returns the records you want, then copy the sql from the query design view sql

And modify your code

Cange:
  With cmd
    .CommandText = "table1"
    .CommandType = adCmdTable
    Set .ActiveConnection = CurrentProject.Connection
  End With

To:
  With cmd
    .CommandText = "Select..."     '<== your sql
    .CommandType = adCmdText
    Set .ActiveConnection = CurrentProject.Connection
  End With

gettin there!
Alan
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9829132
Hi tljprincess,

The problem is the space in your table name

Encapsulate the table name in square brackets.
and set the command type to adCmdTable

This woks on my machine.

 With cmd
    .CommandText = "[clients new]"
    .CommandType = adCmdTable
    Set .ActiveConnection = CurrentProject.Connection
  End With

Alan :)

0
 

Author Comment

by:tljprincess
ID: 9829240
alan, i can't try this tonight but first thing tomorrow ... brain is fading fast.
THANKS!
0
 

Author Comment

by:tljprincess
ID: 9829251
alan, that worked perfectly ... but how can i make that newid field then become a random autonumber for future records????
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9829256
Set the default value for the field = RandomPassword(6)


Alan :)
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 9829630
WARNING, the 'random' number you are assigning culd quite easily end up generating a DUPLICATE entry for one that is already in your table.  The is NOTHING to stop the line:

lRandom = Int((lUpper - lLower + 1) * Rnd + lLower)


from generating the SAME value as was generated previously.

AW
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9830146
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9830923
Too true guys!

This function was originally designed to produce a random password, in which case it didn't have to be unique, it just had to associated with a client login. Guess we could easily do a lookup to check that the value doesn't already exist before returning. To do that we'd have to know the destination table to parse it to the function, I'm partial to simple functions my self, you start complicating them then they aren't so functional.

You guys got any thoughts on this, I'm exhausted at the moment.
Too much web-dev.

Appreciations

Alan :)
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 9831725
If you want unique and Random at the same time, then the only POSSIBLE answer is to make use of a SHUFFLE algorithm.  Generate a BLOCK of 6 digit strings, and the shuffle them (like shuffling a deck of card, to put the set of 52 cards - hence no duplicates) into a RANDOM order.

You decide how many 'cards' you need in the set of values that will be shuffled.

AW
0
 
LVL 26

Accepted Solution

by:
Alan Warren earned 250 total points
ID: 9833810
Hi Arthur, Mike, tljprincess,

Firstly I agree with Mikes comments in the link provided, autonumbers (Identity seeds) should be left alone, let Access manage them. They belong to the application and are uses to manage links and referential integrity.
I also agree with Arthurs comment about creating a pool or deck of numbers from which to draw the next available number for assiganaton. This would probably be best done using an algorythm to generate random numbers into a temp table, then selecting distinct from the pool in temp table into a usy_table ordered ascending. Then when a new number is required mynum=(select top 1 from usys_Table), followed by a (delete top 1 from usys_Table) .

Another method I use is for assigning Member numbers which we like to see in sequential order prefixed with the unique branch prefix.

Example:

Function GetNextBranchMemberNum(sPrefix As String) As String
  Dim varNextMemberNum As Variant
  Dim lngNextMemberNumMax As Long
  Dim sWhere As String
  sWhere = "BusPrefix='" & sPrefix & "'"
  varNextMemberNum = Nz(DMax("[CuscardNumber]", "[tbl_Customers]", sWhere), 0)
  lngNextMemberNumMax = Val(Mid(Nz(varNextMemberNum), 2)) + 1
  GetNextBranchMemberNum = sPrefix & Format(lngNextMemberNumMax, "00000000")
End Function

So if we are dealing WEB customer we would assign like this:

MYfield = GetNextBranchMemberNum ("WEB")
Returns: WEB00001417
Next time we run it: WEB00001418
Unless member WEB00001417 has been deleted in which case it returns WEB00001417, no holes!

If we are dealing with Christies Beach customer

MYfield = GetNextBranchMemberNum ("CB1")
Returns: CB100001394
Next time we run it: CB100001395

If we are dealing with Davoren Park customer

MYfield = GetNextBranchMemberNum ("DP1")
Returns: DP100001392
Next time we run it: DP100001393

What we end up with is a sequential listing of members for each branch, the web being considered a branch outlet.
This field can then be indexed (NO DUPLICATES)

We dont use this code anymore as the app has been retired, but the function lives on having been re-incarnated into a  sql server stored procedure as TSQL


tljprincess, sorry for mis-reading your request, my official response regarding autonumbers is to leave them alone, they belong to the application, paticularly when you migrate to sql server, and initiate rollbacks and restores. I consider any modification to autonumbers(Identity Seeds) to be database corruption.

Mike:     http://www.experts-exchange.com/Databases/MS_Access/Q_20810440.html
Arthur:  http://www.experts-exchange.com/Databases/MS_Access/Q_20810439.html

Kindest Regards

Alan :)


0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9834656
Thank you!

Really hope you managed to glean something usefull from this question, I know I did.

Alan :)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now