MrSlithy
asked on
Access 2003 process is dropping records
I have an access 2003 Form that shows a list of people. There is a input on the form where someone types in a number and then the form generates a series of reports for the number of people chosen.
So, you open the form and type in the umber 10 and the click on a generate button. The result should be several reports based on 10 random users that were chosen via the code.
In the past, this process had broken with a file locking and I had to increase MAXFiles and have this running in an autoexec file.
Now, for no apparent reason (I cannot point out any network/files permission change to account for this) this process does two things odd:
1. If I type in 10 it runs the report based on 7, or 9 or some other . . . but not 10, But once and a while it works and spits out the 10 I am expecting.
2. I used to be able to type in a number and then just click generate report. Now it appears that I have to type in the number and then click somewhere else first, before I generate the report. If I go straight to Generate report, it always brings back zero reports.
I don't know if the two issues are related. Here;s the code that runs when I generate the report. I am a computer network tech, not a database guy but I have to know all. And that's why I come running to you-Grin
Private Sub Label22_Click()
SendKeys "{ENTER}", True
Dim db As Database
Dim rst As Recordset
Dim Random, Count, i
Dim Choice As Integer
If Nz([Choose]) = 0 Then GoTo Cont
Choice = Me.Choose
Set db = CurrentDb
If [TC].SpecialEffect = 1 Then
Set rst = db.OpenRecordset("UA Schedule Choose Query")
Else
Set rst = db.OpenRecordset("UA Schedule TC")
End If
Count = 0
With rst
.MoveFirst
If .BOF = True Then
rst.Close
End
End If
Do Until .EOF
Count = Count + 1
.MoveNext
Loop
End With
If Choice > Count Then
With rst
.MoveFirst
Do Until .EOF
.Edit
![Spot] = False
![Routine] = False
![Random] = True
.update
.MoveNext
Loop
End With
rst.Close
GoTo Cont
End If
i = 0
With rst
For i = 1 To Choice
Do
.MoveFirst
Randomize
Random = Int((Count * Rnd) + 0)
.Move (Random)
If ![Spot] = False Then
If ![Routine] = False Then
If ![Random] = False Then
Exit Do
End If
End If
End If
Loop
.Edit
![Random] = True
.update
Next i
End With
rst.Close
DoCmd.Requery
Cont:
If Me.TC.SpecialEffect = 1 Then
DoCmd.RunMacro "Add UA"
Else
DoCmd.RunMacro "Add UA TC"
End If
DoCmd.Close
DoCmd.OpenReport "UA Chain Of Custody", acViewPreview
DoCmd.OpenReport "UA Incident Report", acViewPreview
DoCmd.OpenReport "UA Schedule", acViewPreview
End Sub
So, you open the form and type in the umber 10 and the click on a generate button. The result should be several reports based on 10 random users that were chosen via the code.
In the past, this process had broken with a file locking and I had to increase MAXFiles and have this running in an autoexec file.
Now, for no apparent reason (I cannot point out any network/files permission change to account for this) this process does two things odd:
1. If I type in 10 it runs the report based on 7, or 9 or some other . . . but not 10, But once and a while it works and spits out the 10 I am expecting.
2. I used to be able to type in a number and then just click generate report. Now it appears that I have to type in the number and then click somewhere else first, before I generate the report. If I go straight to Generate report, it always brings back zero reports.
I don't know if the two issues are related. Here;s the code that runs when I generate the report. I am a computer network tech, not a database guy but I have to know all. And that's why I come running to you-Grin
Private Sub Label22_Click()
SendKeys "{ENTER}", True
Dim db As Database
Dim rst As Recordset
Dim Random, Count, i
Dim Choice As Integer
If Nz([Choose]) = 0 Then GoTo Cont
Choice = Me.Choose
Set db = CurrentDb
If [TC].SpecialEffect = 1 Then
Set rst = db.OpenRecordset("UA Schedule Choose Query")
Else
Set rst = db.OpenRecordset("UA Schedule TC")
End If
Count = 0
With rst
.MoveFirst
If .BOF = True Then
rst.Close
End
End If
Do Until .EOF
Count = Count + 1
.MoveNext
Loop
End With
If Choice > Count Then
With rst
.MoveFirst
Do Until .EOF
.Edit
![Spot] = False
![Routine] = False
![Random] = True
.update
.MoveNext
Loop
End With
rst.Close
GoTo Cont
End If
i = 0
With rst
For i = 1 To Choice
Do
.MoveFirst
Randomize
Random = Int((Count * Rnd) + 0)
.Move (Random)
If ![Spot] = False Then
If ![Routine] = False Then
If ![Random] = False Then
Exit Do
End If
End If
End If
Loop
.Edit
![Random] = True
.update
Next i
End With
rst.Close
DoCmd.Requery
Cont:
If Me.TC.SpecialEffect = 1 Then
DoCmd.RunMacro "Add UA"
Else
DoCmd.RunMacro "Add UA TC"
End If
DoCmd.Close
DoCmd.OpenReport "UA Chain Of Custody", acViewPreview
DoCmd.OpenReport "UA Incident Report", acViewPreview
DoCmd.OpenReport "UA Schedule", acViewPreview
End Sub
Can you upload a sample file (without ant confidential info)?
ASKER
wish I could but no. I know. probably hard to help troubleshoot.
In which case I'm reduced to guessing. It would seem that your reports are filtered to show only records where random is true. That part should be working right.
I believe the problem is the the way you randomize, it is possible that the Random variable will be the same multiple times within the loop, which causes less records then the number entered in the Choose textbox to be flagged random=true.
You have to keep track of the Random variable, and only increment i if it wasn't previously picked.
I believe the problem is the the way you randomize, it is possible that the Random variable will be the same multiple times within the loop, which causes less records then the number entered in the Choose textbox to be flagged random=true.
You have to keep track of the Random variable, and only increment i if it wasn't previously picked.
ASKER
Hey, that make's sense I think. if I may:
If the randomness chooses the same record more than once the next effect is that it would produce less records? Is that what you mean?
Then I guess I find myself wondering why did this just start happening. You know, it HAS been a while since I compacted and repaired. I probably should have done that before I ever posted here. Let me do that.
But do you see any relation between the dropped records and the fact that we have to click somewhere outside the input field where we type in the number and then generate, Whereas before, this would happen without having to take this intervening step of clciking somewhere else to close that field. Ths started happening at the same time records were seeming to drop.
If the randomness chooses the same record more than once the next effect is that it would produce less records? Is that what you mean?
Then I guess I find myself wondering why did this just start happening. You know, it HAS been a while since I compacted and repaired. I probably should have done that before I ever posted here. Let me do that.
But do you see any relation between the dropped records and the fact that we have to click somewhere outside the input field where we type in the number and then generate, Whereas before, this would happen without having to take this intervening step of clciking somewhere else to close that field. Ths started happening at the same time records were seeming to drop.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for thinking out loud with me. How about this - I cannot see anything that has changed procedurally. So, maybe the random function is not working like it used to. There are many of records to choose from. It's not like the database was recently downsized so there are fewer records.
Im not sure how I would add a function that would bypass the same record and not allow it to be considered more than once.
I also wonder if there is some corruption in records somehow so, as the random process gets to a certain record, it encounters a problem so it just quits. I dont know how to view what is happening here.
Im not sure how I would add a function that would bypass the same record and not allow it to be considered more than once.
I also wonder if there is some corruption in records somehow so, as the random process gets to a certain record, it encounters a problem so it just quits. I dont know how to view what is happening here.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wow. So cool. it's going to be a few days before I get back to it. Thank you so much. Everything you say makes perfect sense! I'll let you know. This might have to sit and cook for a while!
ASKER
Thanks so much for bearing with me as I sorted thru this some. I was able to make some changes based on your recommendations and examples, although it was quite a bit to digest. I also found some corrupt data that maybe was a factor. I wish I knew more to explain it better, but keying in on difference between random and unique random, that was key.
You're welcome.