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!"
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
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.
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..
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.