Page 1 of 3 123 LastLast
Results 1 to 15 of 37

Thread: Random Records

  1. #1
    Join Date
    Jul 2003
    Posts
    292

    Unanswered: Random Records

    I have an app that randomly picks out 5 employees to get tested.. In this program it picks out five and puts them back in the table to get picked again..

    I was wonder if there a way to instead of putting them back in.. once they get picked.. take them off the list.. until the last employee has been selected to test. .. once the last employee is selected.. it starts over again..

    ...

    I was also thinking.. is there a way .. the user can choose how many employees to get randomly tested ?

    I have some code here that just randomly picks out 5 employees.. I'm having trouble getting them out of the list to choose from again .. and put back to test..

    Code:
    Private Sub GetEmployees()
    Dim intRnd As Integer
    Dim intRndHi As Integer
    Dim intRndLo As Integer
    Dim strSQL, strCurrent As String
    
    Randomize
    
    strCurrent = Date
    Me.RecordSource = "SELECT COUNT ([Name]) AS NoName FROM Table1"
    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 "
        Me.RecordSource = strSQL
        DoCmd.GoToRecord acDataForm, "frmRandom", acGoTo, intRnd
        Me![Tested] = Format(Date, "Short Date")
        Me.Refresh
    End If
    Exit_GetEmployees:
        Exit Sub
    
    intRnd = ""
    intRndHi = ""
    intRndLo = ""
    
    '------------------------------------------------------------
        
    End Sub
    Private Sub Command0_Click()
    Dim intCounter As Integer
        For intCounter = 1 To 5
            Call GetEmployees 'Test 5 DuPont Employees
            Me.Requery
        Next intCounter
    End Sub

    thanks for any help



    I did a little digging and came up with how to ask the user how many employees they want to test.. now I need to find out.. how to randomly choose these employees, set them aside once they are picked.. and until all employees have been selected..

    once all employee have been selected.. to run the test again..

    and this is what i did to change the random picking of 5 to letting the user pick how many they want..

    changed this part
    Code:
    Private Sub Command0_Click()
    Dim intCounter As Integer
        For intCounter = 1 To 5
            Call GetEmployees 'Test 5 DuPont Employees
            Me.Requery
        Next intCounter
    End Sub
    to

    Code:
    Dim intCounter As Integer
    For intCounter = 1 To CInt(InputBox("Enter Number of Employees to test"))
    Call GetEmployees 'Test 5 DuPont Employees
    Me.Requery
    Next intCounter
    End Sub

    thanks
    Last edited by TonyT; 01-08-04 at 15:25.

  2. #2
    Join Date
    Oct 2003
    Location
    US
    Posts
    343

    Re: Random Records

    Originally posted by TonyT
    I have an app that randomly picks out 5 employees to get tested.. In this program it picks out five and puts them back in the table to get picked again..

    I was wonder if there a way to instead of putting them back in.. once they get picked.. take them off the list.. until the last employee has been selected to test. .. once the last employee is selected.. it starts over again..

    ...

    I was also thinking.. is there a way .. the user can choose how many employees to get randomly tested ?

    I have some code here that just randomly picks out 5 employees.. I'm having trouble getting them out of the list to choose from again .. and put back to test..

    Code:
    Private Sub GetEmployees()
    Dim intRnd As Integer
    Dim intRndHi As Integer
    Dim intRndLo As Integer
    Dim strSQL, strCurrent As String
    
    Randomize
    
    strCurrent = Date
    Me.RecordSource = "SELECT COUNT ([Name]) AS NoName FROM Table1"
    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 "
        Me.RecordSource = strSQL
        DoCmd.GoToRecord acDataForm, "frmRandom", acGoTo, intRnd
        Me![Tested] = Format(Date, "Short Date")
        Me.Refresh
    End If
    Exit_GetEmployees:
        Exit Sub
    
    intRnd = ""
    intRndHi = ""
    intRndLo = ""
    
    '------------------------------------------------------------
        
    End Sub
    Private Sub Command0_Click()
    Dim intCounter As Integer
        For intCounter = 1 To 5
            Call GetEmployees 'Test 5 DuPont Employees
            Me.Requery
        Next intCounter
    End Sub

    thanks for any help



    I did a little digging and came up with how to ask the user how many employees they want to test.. now I need to find out.. how to randomly choose these employees, set them aside once they are picked.. and until all employees have been selected..

    once all employee have been selected.. to run the test again..

    and this is what i did to change the random picking of 5 to letting the user pick how many they want..

    changed this part
    Code:
    Private Sub Command0_Click()
    Dim intCounter As Integer
        For intCounter = 1 To 5
            Call GetEmployees 'Test 5 DuPont Employees
            Me.Requery
        Next intCounter
    End Sub
    to

    Code:
    Dim intCounter As Integer
    For intCounter = 1 To CInt(InputBox("Enter Number of Employees to test"))
    Call GetEmployees 'Test 5 DuPont Employees
    Me.Requery
    Next intCounter
    End Sub

    thanks

  3. #3
    Join Date
    Oct 2003
    Location
    US
    Posts
    343

    Re: Random Records

    Originally posted by TonyT
    I have an app that randomly picks out 5 employees to get tested.. In this program it picks out five and puts them back in the table to get picked again..

    I was wonder if there a way to instead of putting them back in.. once they get picked.. take them off the list.. until the last employee has been selected to test. .. once the last employee is selected.. it starts over again..

    ...

    I was also thinking.. is there a way .. the user can choose how many employees to get randomly tested ?

    I have some code here that just randomly picks out 5 employees.. I'm having trouble getting them out of the list to choose from again .. and put back to test..

    Code:
    Private Sub GetEmployees()
    Dim intRnd As Integer
    Dim intRndHi As Integer
    Dim intRndLo As Integer
    Dim strSQL, strCurrent As String
    
    Randomize
    
    strCurrent = Date
    Me.RecordSource = "SELECT COUNT ([Name]) AS NoName FROM Table1"
    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 "
        Me.RecordSource = strSQL
        DoCmd.GoToRecord acDataForm, "frmRandom", acGoTo, intRnd
        Me![Tested] = Format(Date, "Short Date")
        Me.Refresh
    End If
    Exit_GetEmployees:
        Exit Sub
    
    intRnd = ""
    intRndHi = ""
    intRndLo = ""
    
    '------------------------------------------------------------
        
    End Sub
    Private Sub Command0_Click()
    Dim intCounter As Integer
        For intCounter = 1 To 5
            Call GetEmployees 'Test 5 DuPont Employees
            Me.Requery
        Next intCounter
    End Sub

    thanks for any help



    I did a little digging and came up with how to ask the user how many employees they want to test.. now I need to find out.. how to randomly choose these employees, set them aside once they are picked.. and until all employees have been selected..

    once all employee have been selected.. to run the test again..

    and this is what i did to change the random picking of 5 to letting the user pick how many they want..

    changed this part
    Code:
    Private Sub Command0_Click()
    Dim intCounter As Integer
        For intCounter = 1 To 5
            Call GetEmployees 'Test 5 DuPont Employees
            Me.Requery
        Next intCounter
    End Sub
    to

    Code:
    Dim intCounter As Integer
    For intCounter = 1 To CInt(InputBox("Enter Number of Employees to test"))
    Call GetEmployees 'Test 5 DuPont Employees
    Me.Requery
    Next intCounter
    End Sub

    thanks

  4. #4
    Join Date
    Oct 2003
    Location
    US
    Posts
    343

    Re: Random Records

    To prevent reselection of an employee, place a flag field in your table and whenever that employee is selected flag him. In your sql

    "SELECT * FROM TABLE1" change it to
    "SELECT * FROM TABLE1 WHERE FLAG =0"



    Originally posted by TonyT
    I have an app that randomly picks out 5 employees to get tested.. In this program it picks out five and puts them back in the table to get picked again..

    I was wonder if there a way to instead of putting them back in.. once they get picked.. take them off the list.. until the last employee has been selected to test. .. once the last employee is selected.. it starts over again..

    ...

    I was also thinking.. is there a way .. the user can choose how many employees to get randomly tested ?

    I have some code here that just randomly picks out 5 employees.. I'm having trouble getting them out of the list to choose from again .. and put back to test..

    Code:
    Private Sub GetEmployees()
    Dim intRnd As Integer
    Dim intRndHi As Integer
    Dim intRndLo As Integer
    Dim strSQL, strCurrent As String
    
    Randomize
    
    strCurrent = Date
    Me.RecordSource = "SELECT COUNT ([Name]) AS NoName FROM Table1"
    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 "
        Me.RecordSource = strSQL
        DoCmd.GoToRecord acDataForm, "frmRandom", acGoTo, intRnd
        Me![Tested] = Format(Date, "Short Date")
        Me.Refresh
    End If
    Exit_GetEmployees:
        Exit Sub
    
    intRnd = ""
    intRndHi = ""
    intRndLo = ""
    
    '------------------------------------------------------------
        
    End Sub
    Private Sub Command0_Click()
    Dim intCounter As Integer
        For intCounter = 1 To 5
            Call GetEmployees 'Test 5 DuPont Employees
            Me.Requery
        Next intCounter
    End Sub

    thanks for any help



    I did a little digging and came up with how to ask the user how many employees they want to test.. now I need to find out.. how to randomly choose these employees, set them aside once they are picked.. and until all employees have been selected..

    once all employee have been selected.. to run the test again..

    and this is what i did to change the random picking of 5 to letting the user pick how many they want..

    changed this part
    Code:
    Private Sub Command0_Click()
    Dim intCounter As Integer
        For intCounter = 1 To 5
            Call GetEmployees 'Test 5 DuPont Employees
            Me.Requery
        Next intCounter
    End Sub
    to

    Code:
    Dim intCounter As Integer
    For intCounter = 1 To CInt(InputBox("Enter Number of Employees to test"))
    Call GetEmployees 'Test 5 DuPont Employees
    Me.Requery
    Next intCounter
    End Sub

    thanks

  5. #5
    Join Date
    Jul 2003
    Posts
    292
    Some also suggested that.. They suggested to .. put a yes/no field in my table1.. and when that employee is selected to switch it to yes.. then once all employee have been switched to yes...

    Select * From Table1 Where Test = False;

    Then when everyone has been tested, run and update query to update Test to False


    But I don't understand.. since I am new =/

    any ideas ?

    thanks

  6. #6
    Join Date
    Jul 2003
    Posts
    292
    and if I do switch it.. to say.. 0 or False... and I'm telling it not to select 0 or False.. What will happen when all employee have been selected and all are set to either 0 or False.. Wouldn't that end the program?

    What I was wanting for it to do was.. once all employee were selected once.. then startover..

    thanks

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by TonyT
    and if I do switch it.. to say.. 0 or False... and I'm telling it not to select 0 or False.. What will happen when all employee have been selected and all are set to either 0 or False.. Wouldn't that end the program?

    What I was wanting for it to do was.. once all employee were selected once.. then startover..

    thanks
    What you do then is test for everyone being tested and reset them back to not tested if so ...

  8. #8
    Join Date
    Jul 2003
    Posts
    292

    Unhappy

    how do i do that ?

  9. #9
    Join Date
    Dec 2003
    Posts
    454
    When your query returns 0 row you need to set the flag IsTested = False for all rows

  10. #10
    Join Date
    Jul 2003
    Posts
    292
    First things first,

    Ok in my table1 I added a field called "AllTested" and its data type to yes/no .. now how do i get it to put a check in that field.. ?

    thanks

  11. #11
    Join Date
    Dec 2003
    Posts
    454
    You can count the row with AllTested = No. If count > 0 then do your query to select .... you need to get the ID (primary key) for the selected rows and then update these rows to AllTested = Yes.

    When the count = 0, and then you need to update the table to set AllTested = No for all rows

  12. #12
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    No what you need to do is before you set to pick the employee to test, first check if all employees are checked and Set a flag variable. use SQL like that

    Dim flgAllEmpChecked as Boolean
    if DCount("EMPLOYEE_NO","EMPLOYEES","EMPLOYEE_TESTED= Yes")
    >0 then
    flgAllEmployeeChecked = False
    Else
    flgAllEmployeeChecked=True
    endif

    based on this flag you can either procedd to pick employees or if the flag is true then update your table to reset "EMPLOYEE_TESTED filed to No





    Originally posted by TonyT
    First things first,

    Ok in my table1 I added a field called "AllTested" and its data type to yes/no .. now how do i get it to put a check in that field.. ?

    thanks

  13. #13
    Join Date
    Jul 2003
    Posts
    292

    Angry

    ok now im am offically lost .....

  14. #14
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    Let me try to explain again. In your table "EMPLOYEES" have a field of type Tes/No and call it as EMPLOYEE_TESTED.

    First on top of of all your code you should have a check to see if all employess have been tested. If yes then you need to reset this flag to No and then proceed with your porcess of picking the employees.

    Then filter out all the employees who have been tested by using the SQL I gave you earlier

    Whenever an employee is picked for testing (in your routine) add a little more code and set this field to Yes. This will indicate that the employee has been tested.

    Are you still lost ??

    Originally posted by TonyT
    ok now im am offically lost .....

  15. #15
    Join Date
    Jul 2003
    Posts
    292
    Ok I think I may have understand this a little more...

    let me get this straight...

    ok In my sql..

    change..

    "SELECT * FROM TABLE1" change it to
    "SELECT * FROM TABLE1 WHERE FLAG =0" <~~ should this be "YES" instead of 0?

    Now on top of my code.. place the code you gave me.. here ?

    Code:
    ....
    Private Sub GetEmployees()
    Dim intRnd As Integer
    Dim intRndHi As Integer
    Dim intRndLo As Integer
    Dim strSQL, strCurrent As String
    Dim flgAllEmpChecked as Boolean
    if DCount("Payroll","Table1","EMPLOYEE_TESTED=Yes")>0 then
    flgAllEmployeeChecked = False
    Else
    flgAllEmployeeChecked=True
    endif
    
    Randomize
    
    strCurrent = Date
    Me.RecordSource = "SELECT COUNT ([Name]) AS NoName FROM Table1"
    If Me![NoName] = 0 Then
    MsgBox "Warning! No Records Selected!"
        Exit Sub
    Else
        intRndLo = 1
        intRndHi = Me![NoName] ....
    Is that correct ?/

    now all i need is to set up my code to where whenever the employees get picked to set to fields "employee_tested" to yes ?


    thanks for clearing up

Posting Permissions

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