Solved

Access 2003 process is dropping records

Posted on 2014-01-23
11
421 Views
Last Modified: 2014-02-05
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
0
Comment
Question by:MrSlithy
  • 6
  • 5
11 Comments
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39804167
Can you upload a sample file (without ant confidential info)?
0
 
LVL 2

Author Comment

by:MrSlithy
ID: 39804356
wish I could but no. I know. probably hard to help troubleshoot.
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39804849
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.
0
 
LVL 2

Author Comment

by:MrSlithy
ID: 39805007
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.
0
 
LVL 26

Assisted Solution

by:MacroShadow
MacroShadow earned 500 total points
ID: 39805635
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?
Yes, exactly.

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.
Always a good idea, but i don't think it will help, it seems related to the way the code is constructed.

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 clicking somewhere else to close that field.  This started happening at the same time records were seeming to drop.
I don't think so, but I can't know for sure, in fact I don't see why that should happen at all.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 2

Author Comment

by:MrSlithy
ID: 39807139
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.
0
 
LVL 26

Assisted Solution

by:MacroShadow
MacroShadow earned 500 total points
ID: 39809462
So, maybe the random function is not working like it used to.
The random function is working fine, the problem, you're having is that you need not only random numbers but unique random numbers. Your code creates random but not unique numbers.

I'm not sure how I would add a function that would bypass the same record and not allow it to be considered more than once.
Basically you create an array to hold the chosen numbers, only if the newest number doesn't exist in the array do you update the record and continue the loop.

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 don't know how to view what is happening here.
Highly unlikely. If there was corruption you would see it in more than one case. Additionally when errors are encountered the program will let you know (unless error handling was used to exit the function gracefully, which isn't the case in the code you posted).


As to the problem, having to click somewhere else for the code to work, I just realized that you are using the click event of a label. That explains it, the focus isn't transferred away from the textbox (unless you click somewhere else first). The value of the textbox is only "registered" when it loses focus. The proper way to solve this would be to use the click event of a command button instead of a label.
0
 
LVL 26

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 39811098
I would suggest trying this:
Use this instead of your code, and import the attached module to your project.
Private Sub Label22_Click()
    SendKeys "{ENTER}", True

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim Random As Integer, Count As Integer, Choice As Integer, i As Integer
    Dim Res As Variant

    If Nz(Me.Choose, 0) = 0 Then GoTo Cont
    Choice = Me.Choose

    Res = UniqueRandomLongs(Minimum:=1, Maximum:=Choice, Number:=Choice)

    Set db = CurrentDb
    If Me.TC.SpecialEffect = 1 Then
        Set rst = db.OpenRecordset("UA Schedule Choose Query")
    Else
        Set rst = db.OpenRecordset("UA Schedule TC")
    End If

    If Not (rs.BOF And rs.EOF) Then
        With rst
            rs.MoveLast
            Count = .RecordCount
        End With
    End If

    If Choice > Count Then
        With rst
            .MoveFirst
            Do Until .EOF
                .Edit
                ![Spot] = False
                ![Routine] = False
                ![Random] = True
                .Update
                .MoveNext
            Loop
        End With
        GoTo Cont
    End If

    With rst
        If IsArrayAllocated(Res) Then
            For i = LBound(Res) To UBound(Res)
                .MoveFirst
                .Move (Res(i))
                If ![Spot] = False And ![Routine] = False And ![Random] = False Then
                    .Edit
                    ![Random] = True
                    .Update
                End If
            Next i
        End If
    End With

    DoCmd.Requery

Cont:
    If Me.TC.SpecialEffect = 1 Then
        DoCmd.RunMacro "Add UA"
    Else
        DoCmd.RunMacro "Add UA TC"
    End If

    rst.Close
    Set rst = Nothing
    DoCmd.Close

    DoCmd.OpenReport "UA Chain Of Custody", acViewPreview
    DoCmd.OpenReport "UA Incident Report", acViewPreview
    DoCmd.OpenReport "UA Schedule", acViewPreview
    
End Sub

Open in new window

mdlUniqueRndGenerator.bas
0
 
LVL 2

Author Comment

by:MrSlithy
ID: 39812567
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!
0
 
LVL 2

Author Closing Comment

by:MrSlithy
ID: 39837626
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.
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39837971
You're welcome.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now