Random unique numbers for access records

[rajeevs]

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

Private Sub CmdDupe_Click()
 
       If MsgBox("Press Ok to create a duplicate record with the same details." & vbNewLine & "Cancel to exit.", vbQuestion + vbOKCancel, "Duplicate Record") = vbCancel Then
    Exit Sub
    Else
        'Duplicate the main record: add to form's clone.
        With Me.RecordsetClone
            .AddNew
 
 
intHighNumber = 99999
intLowNumber = 25001
 
For i = 1 To 5
    Randomize
   intnumber = Int((intHighNumber - intLowNumber + 1) * Rnd + intLowNumber)
 Me.uniquenumberfield = intnumber
If DLookup("uniquenumberfield ", "tbl", "uniquenumberfield =" & Me.uniquenumberfield ) Then
MsgBox "Number - " & Me.uniquenumberfield & " already used. Click again to generate unique number", vbCritical + vbOKOnly, "uniquenumberfield Dupe"
 
Me.uniquenumberfield = ""
Me.uniquenumberfield.SetFocus
End If
 
Exit Sub
 intnumber = Int((intHighNumber - intLowNumber + 1) * Rnd + intLowNumber)
 
   Me.uniquenumberfield = intnumber
Next
 
                !otherfields1= Me.otherfields1
                !otherfields2= Me.otherfields2
 
                'etc for other fields.
            .Update
 
            .Bookmark = .LastModified
 
            'Display the new duplicate.
 
             Forms!formname.Requery
 
        End With
    End If
 
Exit_Handler:
    Exit Sub
 
Err_Handler:
    MsgBox "Error " & Err.number & " - " & Err.Description, , "Error"
    Resume Exit_Handler 
End Sub

[GKJR]

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?

[rajeevs]

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)

[GKJR]

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.

[rajeevs]

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

[GKJR]

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.

[rajeevs]

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

[GKJR]

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

[rajeevs]

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

[Rabbit]

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.

[rajeevs]

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

[Rabbit]

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.

[rajeevs]

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

with Me.RecordsetClone
           .AddNew
intHighNumber = 10 
intLowNumber = 1 
For i = 1 To 5
    Randomize
   intnumber = Int((intHighNumber - intLowNumber + 1) * Rnd + intLowNumber)
 
If DLookup("uniqnum", "tbl", "uniqnum=" & intnumber) Then
 
'continue generating
Randomize
intnumber = Int((intHighNumber - intLowNumber + 1) * Rnd + intLowNumber)
Else
 
Me.newnum = intnumber
 'newnum is textbox to store the value
End If
Next
                !uniqnum= Me.newnum
!otherfileds=me.otherfields
.update
me.bookmark=me.lastmodified
end with
 
 

[Rabbit]

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.

[rajeevs]

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
Next
and start adding the fields to tbl

[Rabbit]

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.