Duplicate Entry Record ID

[mugman17]

I want to create some coding in my form that looks through the table InventoryTransactions at all the entries under RecordID after randomizing a number, to see if there is a duplicate entry, if there is randomize another number until I find one. I am having trouble getting the code to work, I would assume I would need a loop.

I have an external ID (RecordID), and an internalID (DealID Autonumber, no duplicates). I tried to make the RecordID have no duplicates as well, but it would not let me do it. So I think my above idea is the best way.

Thanks

 

[pono1]

If you're sure this is the way you want to go, here's an example that might spur you on to bigger and better things.

Create a test form. Add a textbox and name it RandomTextBox. Add a command button and name it CmdRandonChk. Additionally, I am assuming there is a table named InventoryTranscations in the DB with at least one field named RecordID and formatted as numeric. For testing, you might want to create this table in a test DB and input 5 records -- say, 1,2,3,4,5.

On the command button's click event, paste in the following code.

Private Sub CmdRandomChk_Click()

Dim RandomValue As Variant
Randomize ' Initialize random-number generator.

'identify this section of the code
'so we can return here if there's a dup
CreateRandomNum:

RandomValue = Int((10 * Rnd) + 1) 'create random value Me.RandomTextBox.Value = RandomValue 'assign to textbox

'is this random number already in my table?
If (Not IsNull(DLookup("[RecordID]", _
"InventoryTransactions", _
"[RecordID] = Form![RandomTextBox]"))) Then

'test
MsgBox "This is a dup -- I will do your bidding and try again"
'test

'go back and do it again
GoTo CreateRandomNum

End If

End Sub

Notice that this is not a loop, per se --- but a touch of "spaghetti code," with control passing back up to CreateRandomNum if there's a duplicate number in your table. Play with it and I'm sure it'll make sense in time.

Regards,
Tim

 

[mugman17]

Works like a charm. Thank you very much for your help.

 

[pono1]

Happy to hear it. An afterthought: be sure your supply of random numbers always exceeds the number of records in your table, esp if you use something like the code sample, since, as it now stands, it will endlessly loop if it cannot create a non-dup number.
You can you insert a DCount function to check this in the code, something like,

If number of records in table >= my random supply then
msgbox "Sorry, User. Table Full."
exit sub
end if

Regards,
Tim

 

[mugman17]

The company wants an internal ID for their records, and an external ID for the clients to see. This is a random number, so the clients dont know who many deals they do.