Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100

    Unanswered: RND() Genertaing same number on each field during update

    I'm using the following code to generate a random ID code for a list of users. It used to work properly, now when I run it it uses the same random number for each field (which results in duplicates) rather than a new number each time.
    Code:
    Left([Name],3) & (Round((Rnd()*(9999-1000)+1000),0)) & Left([Zip],5)
    Any suggestions?
    BillS

  2. #2
    Join Date
    Apr 2006
    Posts
    157
    autonumber... kidding, actually.
    several months ago, i realized that autocreation of primary keys are painstakingly unnecessary... I'd usually end up extracting a coded key from information supplied by the user, or something like that...

    i always liked the idea of keys holding sensible info relative to the record itself
    Only quitters quit!

  3. #3
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100
    This is not a primary key and if you notice it does comprise SOME usable information.
    BillS

  4. #4
    Join Date
    Apr 2006
    Posts
    157
    have you tried using a loop to keep generating the random ID until you come up with a unique value?
    Only quitters quit!

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You need to call Randomize just before the Rnd call. Is you code in a module? Or in a query? If it is in a query then I would suggest creating a function in a module that takes the name and zip as parameters and then creates the code for you.

  6. #6
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100
    It's currently in a query and for some reason used to work just fine. Might you all be able to provide some sample code for what you are talking about? I can usually tweak VB code to my needs, but I'm not good at starting from scratch. Thanks!
    BillS

  7. #7
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Here is some code for what I was talking about. Put it in a module in your database.

    Code:
    Public Function GetCode(varName As Variant, _
                            varZip As Variant) As String
    
        Dim strName As String
        Dim strZip As String
        
        Randomize
        
        'Check to make sure the name and zip are not null, if so put in default values
        strName = Left(Nz(varName, "NONAME"), 3)
        strZip = Left(Nz(varZip, "NOZIP"), 5)
        
        GetCode = strName & (Round((Rnd() * (9999 - 1000) + 1000), 0)) & strZip
        
    End Function
    Then in your query add a field similar to:

    Code:GetCode([Name],[Zip])

    For each record, GetCode is called and the Name and Zip will be passed to the function and a code will be created.

    Also, when you add the code to a module, you can test the code by typing
    ?GetCode("Test","60126") in the Immediate Window (View/Immediate Window). When you press enter the GetCode function should be called and a code should be returned.

  8. #8
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100
    Thanks. When I try to test the module, I get:
    Compile Error:
    Expected variable or procedure, not module.
    BillS

  9. #9
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Did you save the module? Is the module name the same as the function name? GetCode?

    Try renaming your module to modGetCode.

  10. #10
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100
    Yes I did save it and I tried to new name. Same error.
    BillS

  11. #11
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    What happens if you open the module and go to Debug/Compile Your Database?

  12. #12
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100
    The menu option turns gray, which I take to mean it was successful.
    BillS

  13. #13
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100
    Here's the window:
    http://tinyurl.com/29gqe5
    BillS

  14. #14
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    It sounds like the code compiles. How are you calling the code? In a query? Or in the Immediate window?

  15. #15
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100
    The Immediate window.
    BillS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •