Solved

Access 2003 process is dropping records

Posted on 2014-01-23
11
427 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 27

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 27

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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 27

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
 
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 27

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 27

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 27

Expert Comment

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

738 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