Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Jul 2003
    Posts
    292

    Question Unanswered: Randomizing numbers...

    I am pretty new to Access97.. and VB.. soo please bare with me.. I have some code.. which randomly picks out an employee for a drug screen.. after the employee gets selected.. he/she will not get picked again until 2 years later.. A friend and I came up with the code.. but it only picks out one employee..

    The user wants 21 employees to get picked out..

    The code I have here only randomly picks out 1 employee.. how wouldi get it to pick and display out 21 employee and set the corresponding date ?.. This is probably an easy question for all programming gurus..but I been working on this to long and seek help.. thanks alot

    this is the code behind my button " get employee "
    =================================

    Private Sub Command0_Click()
    On Error GoTo Err_Command0_Click
    Dim intRnd, intRndHi, intRndLo As Integer
    Dim strSQL, strCurrent As String
    Dim lpBuff As String * 25 ' Set Up NTUSER NAME
    Dim UserNameLong As Long ' Set Up NTUSER NAME
    Dim NTUserName As String ' Set Up NTUSER NAME
    UserNameLong = GetUserName(lpBuff, 25) ' Set Up NTUSER NAME
    NTUserName = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1) ' Set Up NTUSER NAME
    NTUserName = "" & NTUserName ' Set Up NTUSER NAME
    strCurrent = Date
    Me.RecordSource = "SELECT COUNT ([Name]) AS NoName FROM Table1 WHERE [NextTestDate] < #" & strCurrent & "# OR ISNULL([Tested]);"
    MsgBox Me.Name & Me.RecordSource
    If Me![NoName] = 0 Then
    MsgBox "Warning! No Records Selected!"
    Exit Sub
    Else
    intRndLo = 1
    intRndHi = Me![NoName]
    MsgBox "Press OK to Select Employee"
    intRnd = Int((intRndHi - intRndLo + 1) * Rnd + intRndLo)
    strSQL = "SELECT * FROM Table1 WHERE [NextTestDate] < #" & strCurrent & "# OR ISNULL([Tested]) ORDER BY [Name];"
    Me.RecordSource = strSQL
    DoCmd.GoToRecord acDataForm, "frmRandom", acGoTo, intRnd
    Me![Tested] = Format(Date, "Short Date")
    Me![NextTestDate] = DateAdd("yyyy", 2, Me![Tested])
    Me![UpdatedBy] = NTUserName ' Set Up NTUSER NAME
    Me.Refresh
    SendToTextBox
    End If

    Exit_Command0_Click:
    Exit Sub

    Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click

    intRnd = ""
    intRndHi = ""
    intRndLo = ""


    End Sub


    THis part of the code would be where I display the employee
    =======================================

    Private Sub SendToTextBox()
    Me.Form.AllowEdits = True 'read only
    Me.txt_Name.SetFocus
    txt_Name.Text = Me![Name]
    'Me.txt_Name2.SetFocus
    'txt_Name2.Text = Me![Name]
    Me.txt_Shift.SetFocus
    txt_Shift.Text = Me![Shift]
    Me.txt_Payroll.SetFocus
    txt_Payroll.Text = Me![Payroll]
    Me.txt_Supervisor.SetFocus
    txt_Supervisor.Text = Me![Supervisor]
    Me.txt_SuperExt.SetFocus
    txt_SuperExt.Text = Me![SuperExt]
    Me.Form.AllowEdits = False 'read only
    End Sub


    Again thank you very much

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    first thoughts:

    all that SetFocus stuff is pretty boring.
    if, instead of .Text you use .Value you can drop the ShiftFocus
    (frankly, i don't know why mr gates included .Text!)

    a really lazy way to get where you want to go would be:

    private sub get21employees()
    dim loupe as integer
    for loupe = 1 to 21
    Command0_Click
    next
    end sub

    more 'fisticated would be
    SELECT TOP 21 (instead of SELECT *) in your SQL
    but then you need to print the query results or make a report based on your query or....

    izy

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    and
    Me!Form.AllowEdits = FALSE 'read only

    ahaaaa... i read the code: .AllowEdits is only for form-users, code can always write whatever the .AllowEdits setting.

    izy
    Last edited by izyrider; 07-18-03 at 16:21.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    also, check out "Randomize" in help: you are not even pseudo-random without it.

    izy

  5. #5
    Join Date
    Jul 2003
    Posts
    292
    could you explain more in detail.. thanks for the response though..

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    actually, the more i think about it the problem of pulling 21 records from a table at random is not that simple... i'll sleep on it, and if you don't get a decent answer from someone else by tomorrow i'll get back to you.

    izy

  7. #7
    Join Date
    Jul 2003
    Posts
    292
    thanks soo much izy

    -Tony

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    randomize?

    rnd always delivers the same (laaaaaaarge) sequence of pseudo-random numbers.

    randomize determines where you start in that sequence. without randomize, you start at the beginning EVERY TIME so you are not "random"

    randomize

    on it's own, seeds the pseudo-random number generator with a vlaue from the PC clock (you can also add your own argument, but then you get less "random")

    izy

  9. #9
    Join Date
    Jul 2003
    Posts
    292
    Yea true .. It does become less random.. as I explained to the user.. but thats what they want.. and w/o their wants... I would not be here.. =)

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    yup: the folk with the money get what they ask for (which is sometimes not what they want or need, 'cos they sometimes forget to think).

    what are you looking for:

    -- Access' most random possible selection of 21 folk who have not been hit in the past two years

    or

    -- some sort of inadequate compromise



    sorry, can't help with the second.

    izy

  11. #11
    Join Date
    Jul 2003
    Posts
    292
    actually.. What I'm looking for is..

    21 random employee gets picked.. when they get picked.. I stamped todays date on them.. then added 2 years to that ... Because when an employee gets picked.. they will not get tested again .. until 2 years from when they were last tested..

    The code I have was for 1 employee only.. I wanted to pick and display 21 at a time not one at a time..

    Thanks Hope I was more clear

    -Tony

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    tomorrow. izy

  13. #13
    Join Date
    Jul 2003
    Posts
    292
    again .. Thanks so much

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hi Tony,

    sorry it is no longer "tomorrow", but the weekend weather was too nice to sit over a pc screen.

    attached is a possibility:

    it takes a different approach. instead of Rnd, it uses stored queries and appends into a table with a random autonumber field.

    i've run many thousands of test loops and it doesn't seem to be too biased. worst result on 1000 loops was 10% between most and least frequently"hit" record. i had one series of test runs where "three" came up regularly as the most-hit, but then this strange phenomenon went away.

    other experiments didn't work. i tried adding a field = Rnd in a query, but every row had the same value (!!).

    im also playing with a code solution - i'll let youknow if it works out.

    izy

    attachment removed. izy
    Last edited by izyrider; 07-21-03 at 13:55.

  15. #15
    Join Date
    Jul 2003
    Posts
    292
    izy,

    WHen I open your database.. I get an error.. unrecognized database format ?? I'm sorry I failed to mention that I have access 97 if that makes a difference as to why i can't open the database..

    Thanks for your help..

    Tony

Posting Permissions

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