Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    292

    Unanswered: looping a program to run 5 times

    I have an access application that runs 1 time and puts that in a query..

    What I want to know is .. how do I get it to loop five times ?.. This is a random drug testing database.. picking out 1 where i need to pick out 5..

    any ideas.. thanks

    heres the code:

    Code:
    Private Sub GetEmployees()
    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"
    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![NextTestDate] = DateAdd("yyyy", 2, Me![Tested])
        Me![UpdatedBy] = NTUserName ' Set Up NTUSER NAME
        Me.Refresh
       Command26_Click
    End If
        
    Exit_GetEmployees:
        Exit Sub
    
    intRnd = ""
    intRndHi = ""
    intRndLo = ""
    
        
    End Sub


    thanks


    I tried adding this line to the code.. but i get an error.. wrong number of arguements..

    Code:
    Private Sub Command0_Click()
    GetEmployees 5 'Test 5 DuPont Employees
    Me.Requery
    End Sub
    again thanks
    Last edited by TonyT; 01-02-04 at 12:07.

  2. #2
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    To have a procedure reoccur several times, a "for loop" can be used.

    One reason your code gives the error is because you are calling a function that does not take any parameters.
    Private Sub Command0_Click()
    GetEmployees 5 'Test 5 DuPont Employees
    Me.Requery
    End Sub

    To repeat the call to that function five times do this.
    Private Sub Command0_Click()
    dim intI as integer
    For intI = 1 to 5
    Call GetEmployees 'Test 5 DuPont Employees
    Me.Requery
    Next intI
    End Sub


    Hope that helps.

    Though I don't completely understand what you're doing, if you need to update two fields in five separate records, I would consider researching the update command.

    dim strSQL as string
    strSQL = "UPDATE table " & _
    "SET [Field1] = 'Data1', [Field2] = 'Data2' " & _
    "WHERE ([field3] = " & intnumber? & ");"
    CurrentProject.Connection.Execute strSQL
    Last edited by jpshay; 01-02-04 at 13:16.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  3. #3
    Join Date
    Jul 2003
    Posts
    292
    jpshay,

    Thanks so much for the response.. I tried the loop and it worked out great.. I was looking over the code.. and ran it a few times.. and noticed.. this is not truely random.. It's picking out the names randomly yes.. but I want for it to be able to pick that name again.. say I hit a button "get employees" So it goes and picks out 5 employees to be tested.. after that first test.. the names that were just tested should go back in the pool of employees to get tested again..

    Here I have - after the employees are tested they arent tested again.. This code was written way back from someone else.. and I'm stuck at trying to revamp it..

    have any ideas.. on how to do this.. thanks so much

  4. #4
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Can't help you much, but with a few suggestions.

    Q: Is it possible that the recordsource for the form excludes people who have been tested? I would examine that.

    Q: Do you want the five employees chosen to be unique? If you make it truly random (each employee placed back into the hopper after they are selected), it is possible that the same name could be selected more than once. You would need to check to see whether the person currently chosen, was already chosen. That could get a little complicated.

    I don't know what is happening, how records get marked, etc. But I would consider doing the follolwing.
    Make a simple table of testees.
    When you want to select a quintet, do this.
    1) Delete all current records of the testees table
    2) Call the function that loops,
    3) which in turn randomly selects a name and inserts record to the testee table, which loops all other entries checking to see that the name selected is unique.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  5. #5
    Join Date
    Jul 2003
    Posts
    292
    thanks for the help.. jpshay...

    I'll work on this a little more and get back to you..

    thanks

  6. #6
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Happy New Year!
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

Posting Permissions

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