Link to home
Start Free TrialLog in
Avatar of MrSlithy
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
Avatar of Joe Howard
Joe Howard
Flag of United States of America image

Can you upload a sample file (without ant confidential info)?
Avatar of MrSlithy
MrSlithy

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.
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.
SOLUTION
Avatar of Joe Howard
Joe Howard
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!
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.