Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jun 2004
    Posts
    19

    Unanswered: Random Program problem..

    I have a random program.. and I am having a some trouble with...

    The random part works fine.. but as I enter in how many users I want to randomly pick.. it does not pull back that number of employees .. I friend help with this.. so please bare with me..

    thanks

    Code:
    Private Sub GetEmployeeNON()
    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]
    	intRnd = Int((intRndHi - intRndLo + 1) * Rnd + intRndLo)
    	strSQL = "SELECT * FROM Table1"
    	Me.RecordSource = strSQL
    	DoCmd.GoToRecord acDataForm, "frmNON", acGoTo, intRnd    
    	Me![N_Tested] = Format(Date, "Short Date")
    	Me.Refresh
    End If
    Exit_GetEmployeeNON:    
    	Exit Sub
    
    intRnd = ""
    intRndHi = ""
    intRndLo = ""
    
    End Sub
    Private Sub Command0_Click()
    On Error GoTo Err_bSave_Click
    Dim intCounter As Integer
    For intCounter = 1 To CInt(InputBox("Enter Number of Employees to test"))
    Call GetEmployeeNON 
    Me.Requery
    Next intCounter
    Exit_bSave_Click:
    Err_bSave_Click:
    	If Err = 13 Then 'The command or action Undo is not available now
    		Exit Sub
    	Else
    		MsgBox Err.Number, Err.Description        
    		Resume Exit_bSave_Click
    	End If
    End Sub

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I don't see where the program flow is supposed to pause between testing employees to give you any kind of feedback.

    What is your desired outcome here?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jun 2004
    Posts
    19
    When I run the program.. and it ask for how many employees i want to test.. I enter in the number of employees .. and it runs fine the first test..

    but then i go and keep testing.. entering more numbers to test..

    after awhile the number of employees dont match.. like I may pick out 30 and it will only return 25

    I think what its trying to do is.. after a few testings.. I now have these employees that have been tested today..

    So when the program runs through the list of employees to pick and finds that it has already picked that employee before, then its skips it but still counts that as a pick..

    I have no problems if i choose a small number on a different date.. but when i choose a higher number then i do not get the results that i ask for...

    thanks for the response..

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What is this "return". I don't see anywhere that is returning a recordset of all the tested employees.

    ???
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jun 2004
    Posts
    19
    I believe the problems lies in my SQL
    Code:
    strSQL = "SELECT * FROM Table1"
    I tried changing it to..
    Code:
    strSQL = "SELECT * FROM qryTable1 WHERE (((qryTable1.N_Tested)<>Date()));"
    but when i run the program i get an error if records are picked twice..

    data type mismatch..

    any thoughts ?

    thanks

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ahhh ok, now I see how you're using that function...

    Knee-jerk reflex is to make sure the data in N_Tested is valid the second time around. By hand, just go in and see if it's still a valid date format.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Jun 2004
    Posts
    19
    I'm not sure I understand what you mean... if you could elaborate that would be great..

    all dates in that field have the same format.. some may be blank.. since new employees may be added.. and if they are not tested yet.. they would not have a date..

    would that cause the error.. and how would i fix that..

    I feel were getting close..

    thanks

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You said the error occurs when a given record is tested twice... Did I misunderstand? I'm wondering if the values are being skewed during the second insert.

    Null wouldn't cause a type error in that context.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Jun 2004
    Posts
    19

    Unhappy

    no you have it correct..

    ok.. I did a little changing up.. and also fixed this line.. so that I dont throw my count off...
    Code:
    Me.RecordSource = "SELECT COUNT ([Name]) AS NoName FROM qryTable1 WHERE N_Tested <> Format(Date, "Short Date")"
    and

    Code:
    strSQL = "SELECT * FROM qryTable1 WHERE N_Tested <> Format(Date, "Short Date")"

    I'm getting an error

    compile error:

    Expected : end of statement


    I think im losing myself here...
    help!

    thanks =)

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Escape the quotes

    strSQL = "SELECT * FROM qryTable1 WHERE N_Tested <> Format(Date, ""Short Date"")"
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    Join Date
    Jun 2004
    Posts
    19
    I get a box that pops up..

    asking me..

    Enter the parameter value
    Date ?

    thats wierd..

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Try

    now

    instead
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  13. #13
    Join Date
    Jun 2004
    Posts
    19
    kool!..

    Now seem to have let me pass.. but.. I'm getting an error..

    I did several test.. first few test passed.. but the 3rd 4th..etc.. attempt..

    I'm getting an error

    Run-Time error '2105'

    You can't go to the specifed record..

    You may be at the end of a recordset.

    any ideas?

    thanks

  14. #14
    Join Date
    Jun 2004
    Posts
    19
    Ok I think I got it now..

    I fergot to add the code to the top portion of my SQL COUNT

    SO I added the code in..

    Me.RecordSource = "SELECT COUNT ([Name]) AS NoName FROM qryTable1 WHERE N_Tested <> Format(Now, ""Short Date"")"

    and my other SQL

    strSQL = "SELECT * FROM qryTable1 WHERE N_Tested <> Format(Now, ""Short Date"")"

    and this goes through my entire table .. but I do have one minor problem..

    new employees get added to the database.. and which would cause some N_Tested fields to be blank...

    So how would i add an is null to my SQL to fix this issue..

    thanks you been great help!

  15. #15
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Format(nz(N_Tested), "Short Date")

    That SHOULD return 12/30/1899 if I recall...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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