By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,946 Members | 1,676 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,946 IT Pros & Developers. It's quick & easy.

Random unique numbers for access records

P: 171
I have an access table with records having unique numbers in one field. I would like to duplicate the record except the unique number and while duplicating I like to give a different number.
I have found a code which can generate the random numbers but I dont know where to put it in the button click which creates the duplicate record and cross check the table to find wether the number has been already used.
I am duplicating the record through a bound form button click and the event is as below: Can anyone help me to re arrange or modify the code to achieve the result. Thank you in advance

Expand|Select|Wrap|Line Numbers
  1. Private Sub CmdDupe_Click()
  3.        If MsgBox("Press Ok to create a duplicate record with the same details." & vbNewLine & "Cancel to exit.", vbQuestion + vbOKCancel, "Duplicate Record") = vbCancel Then
  4.     Exit Sub
  5.     Else
  6.         'Duplicate the main record: add to form's clone.
  7.         With Me.RecordsetClone
  8.             .AddNew
  11. intHighNumber = 99999
  12. intLowNumber = 25001
  14. For i = 1 To 5
  15.     Randomize
  16.    intnumber = Int((intHighNumber - intLowNumber + 1) * Rnd + intLowNumber)
  17.  Me.uniquenumberfield = intnumber
  18. If DLookup("uniquenumberfield ", "tbl", "uniquenumberfield =" & Me.uniquenumberfield ) Then
  19. MsgBox "Number - " & Me.uniquenumberfield & " already used. Click again to generate unique number", vbCritical + vbOKOnly, "uniquenumberfield Dupe"
  21. Me.uniquenumberfield = ""
  22. Me.uniquenumberfield.SetFocus
  23. End If
  25. Exit Sub
  26.  intnumber = Int((intHighNumber - intLowNumber + 1) * Rnd + intLowNumber)
  28.    Me.uniquenumberfield = intnumber
  29. Next
  31.                 !otherfields1= Me.otherfields1
  32.                 !otherfields2= Me.otherfields2
  34.                 'etc for other fields.
  35.             .Update
  37.             .Bookmark = .LastModified
  39.             'Display the new duplicate.
  41.              Forms!formname.Requery
  43.         End With
  44.     End If
  46. Exit_Handler:
  47.     Exit Sub
  49. Err_Handler:
  50.     MsgBox "Error " & Err.number & " - " & Err.Description, , "Error"
  51.     Resume Exit_Handler 
  52. End Sub
Mar 8 '14 #1
Share this Question
Share on Google+
15 Replies

P: 104
I didn't even look at your code yet, but do you have an auto-number primary key field? I guess the next question is can you create a duplicate record in the same table?
Mar 8 '14 #2

P: 171
The records are actually imported from a log file. And the log file has a number field which is unique. I need to create the same record sometimes but the table wont allow duplicate for the unique number. That is the reason i want to generate a random number first, then check in the table whether the number exists, if exists then generate another number and save the record. After saving the record, form to requery and show the last saved record (duplicate record)
Mar 8 '14 #3

P: 104
Can you change the option for the field to 'indexed(duplicates ok)' in design view? It might be set to 'no duplicates' already even though that might not be necessary for your application.
Mar 8 '14 #4

P: 171
I need the field as no duplicates. The above code generates numbers but i want to do a dlookup after generating the number and if the generated number is already in the table with another record then randomly generate and check for a unique number and append the record to table
Mar 8 '14 #5

P: 104
Do you know anything about using Database and Recordset Object variables in VBA? I think you may need to venture into this if you're talking about record manipulation.
Mar 8 '14 #6

P: 171
I think my request was to correct the above code. If you are unable to help please leave it and hope someone else can help me in this aspect
Mar 8 '14 #7

P: 104
My best suggestion is to declare a recordset and run some tests on the field in question. If the number already exists assign it a new value with the edit method. There are a lot of resources for generating a random number all over the internet. Once you assign a new number test it again to see if the new value already exists. If not then save, if so repeat the process.

I hope this helps...
Good luck
Mar 8 '14 #8

P: 171
Thank u GKJR
The above code does half of the requirement and I am not a vb expert. So I am looking for a re arrange of the code lines which generate the number, check the number if already any record has got the same, if then generate again till if can generate a unique else save the new record with the number generated.
Hope my request is clear
Mar 8 '14 #9

Expert Mod 10K+
P: 12,430
1. Remove your exit sub, not even sure why that's in there.

2. Change your loop to an infinite loop and exit the loop only when you've found a non-duplicated number.

3. Assign the number to the field only after you've found a unique number.
Mar 9 '14 #10

P: 171
Dear Rabbit
Thank you for your reply. But your point number 2 and 3 is what I want the code to do. I am not an expert in VB. It will be a great help if you could help me to re do my above code as per your instruction
Mar 11 '14 #11

Expert Mod 10K+
P: 12,430
This is not a code writing service but if you post your attempt at the solution using my suggestions, we can help guide you along.
Mar 11 '14 #12

P: 171
Sorry for the misunderstanding. I requested for a help to re arrange the code I already posted.
I have tried something between 1 and 10 but after reaching the last new number generated i get the error about table cannot accept the changes due to duplicate. but i want the code to split the record and give an option to enter the uniqnum manually
the modified code as below
Expand|Select|Wrap|Line Numbers
  1. with Me.RecordsetClone
  2.            .AddNew
  3. intHighNumber = 10 
  4. intLowNumber = 1 
  5. For i = 1 To 5
  6.     Randomize
  7.    intnumber = Int((intHighNumber - intLowNumber + 1) * Rnd + intLowNumber)
  9. If DLookup("uniqnum", "tbl", "uniqnum=" & intnumber) Then
  11. 'continue generating
  12. Randomize
  13. intnumber = Int((intHighNumber - intLowNumber + 1) * Rnd + intLowNumber)
  14. Else
  16. Me.newnum = intnumber
  17.  'newnum is textbox to store the value
  18. End If
  19. Next
  20.                 !uniqnum= Me.newnum
  21. !otherfileds=me.otherfields
  22. .update
  23. me.bookmark=me.lastmodified
  24. end with
Mar 14 '14 #13

Expert Mod 10K+
P: 12,430
Rearranging the code will not fix the problems in the code. You need to change the code using what I laid out in post #10.
Mar 14 '14 #14

P: 171
Thank you for the quick response.
What I am strugling is that I am not familiar with writing an infinite loop.
Is it something I have to try with
Do While Not before the Dlookup then randomize
after that
assign the value
and start adding the fields to tbl
Mar 14 '14 #15

Expert Mod 10K+
P: 12,430
Yes, you'll want to use a Do While loop instead of a For loop. You'll want to generate and check the uniqueness within the loop and exit the loop only when you've found a unique number.
Mar 14 '14 #16

Post your reply

Sign in to post your reply or Sign up for a free account.