Insert Query, RANDOM number replition problem. Using loop. Not WORKING please help..

[rajgoyal00]

Hi,



I am trying to insert a random code into [Car_Parts].[Product Code]
But I want to make sure I don't do a duplicate value.
Its not working...

Please tell me what am I doing wrong.. Here's the code.


Dim Product_Code
Dim Prod_Code_Check

Product_Code = "STS" & Round(Rnd * 999999, 0)



Do Until [Product_Code_Check] <> Product_Code

Product_Code_Check = [Car_Parts].[Product Code]

Loop





PLEASE HELP!!!! Thanks

[pbaldy]

Not sure what that loop will do, since you never set Prod_Code_Check. In any case, I'd probably do something like:

1. Set Variable to random code
2. Use DCount() to see if that code already exists
3. If it does, use GoTo to go back to step 1

[RuralGuy]

And to add to Paul's response, remember that Rnd() requires Randomize to change the seed or you get the same number all of the time.

[rajgoyal00]

Thank You Paul, I was able to find the solution, using your advice.
Thanks you also for the advice Rural Guy. Am not a pro at this, just learning as I go. Really appreciate all of you good folks on these forums..

[pbaldy]

We were happy to help. I forgot about the need for Randomize. It might help a future searcher if you post the working code.

[rajgoyal00]

Thanks again for the help!!!
I dont know how to use randomize, This is the code Used and its working

Step1:
Dim Product_Code
Dim Prod_Code_Check

Product_Code = "STS" & Round(Rnd * 999999, 0)
Prod_Code_Check = DCount("[Product Code]", "[Car_Parts]", "[Car_Parts].[Product Code] = '" & Product_Code & "'")
If Prod_Code_Check > 0 Then
GoTo Step1
End If