Complicated cide now seems to fail after upgrade to Access 2010

I have a process that allows administrator to request a number of random UAs (Urine Analysis)  from a list of employees. She goes in and asks for 3. 5, 10, whatever and three reports are generated. Then, after results are received she opens a form called unfinished results where she can go in and enter results once completed.

At the next determined interval, she runs the process again for a new random batch and so on. I recently upgraded this FE from Access 2003 to 2010 and at this point the process breaks. But it doesn't appear to break anywhere. I dont see any indication of error.

From the point I updated the FE and point to test data in BE, here's what happens. Process appears to run normally. I select a number the reports are generated.  I go in and enter results and everything appears to work fine. If I go back into results later, as expected the ones I just completed are not there.

But then when I reselect a random number, new reports are generated and the new reports has the new random list IN ADDITION to the ones just previously run. And they are showing up again as NEW UAs. This compounds indefinitely.

Below is the code that is used to run the initial generation of names and reports.   there are additionally on Edit Query and 2 update queries associated somehow.  I have looked thru all the code but I am not very experienced. I suspect something is breaking down either when the results are entered or some sort of requery problem when a new batch is run. This was all done by someone previous. I'm not much of a VBa guy, and I do what I have to do with these databases to keep upgraded and run. I need someone to look at this with a critical eye and help me isolate where I need to look.  If I dont get an error to hang my hat on, I just dont know.

The problem might be in one of the On Open, or Onload commands for one of the forms or reports, I dont know.  But I suspect it is in the Edit or Update queries . . .  

Again. The code is not mine. I know when you see it, you may shudder a bit. I understand. I am not a coder. I just want to get the reports flowing again. So lets start here and ask me where to go next.

Note TC is a special grouping of people. Sometimes UAs need to be done from this special grouping.


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
LVL 2
MrSlithyAsked:
Who is Participating?
 
Nick67Commented:
This bit needs some more than what I gave there
DoCmd.SetWarnings False
'turn off the warnings.  But be careful!
Dim qdf As QueryDef
'run the action queries
Set qdf = db.QueryDefs("UA Schedule Add to Table Query")
qdf1.Execute
Set qdf = db.QueryDefs("UA Update Last ")
qdf.Execute
'turn them on
DoCmd.SetWarnings True

Open in new window


needs the conditional
DoCmd.SetWarnings False
'turn off the warnings.  But be careful!
Dim qdf As QueryDef
If Me.TC.SpecialEffect = 1 Then

'run the action queries
Set qdf = db.QueryDefs("UA Schedule Add to Table Query")
qdf.Execute
Set qdf = db.QueryDefs("UA Update Last ")
qdf.Execute
Else

'run the action queries
Set qdf = db.QueryDefs("UA Schedule Add to Table Query") 'change according to what's in the macro
qdf.Execute
Set qdf = db.QueryDefs("UA Update Last ")'change according to what's in the macro

End If
'turn them on
DoCmd.SetWarnings True
0
 
Peter HutchisonSenior Network Systems SpecialistCommented:
The VBA code looks ok to me.
I think the problem is how Access handles macros, and the the database needs to be in a 'Trusted location' for it to work properly (see Options / Trust Center / Trust Center Settings / Trusted Locations ).
0
 
Nick67Commented:
But then when I reselect a random number, new reports are generated and the new reports has the new random list IN ADDITION to the ones just previously run. And they are showing up again as NEW UAs. This compounds indefinitely.
Again. The code is not mine. I know when you see it, you may shudder a bit.
Hopefully, you shot the person responsible for leaving you this hard to maintain mess

Here it is commented and cleaned up a bit.
Comment out what you have (do you know how to do that?) and replace it with this, and test it

Private Sub Label22_Click()
SendKeys "{ENTER}", True
'This is hard to know what it is doing
'And Windows 7 and sendkeys toggles the NumLOck
'Drives users batty
'That's a problem for another day

Dim db As Database
Dim rst As Recordset
'these as they were all become variants
'Dim Random, Count, i
'The should all be integers
Dim Random As Integer
Dim Count As Integer
Dim i As Integer
Dim Choice As Integer

'bail to Cont: if the users hasn't entered a value
If Nz(Me.[Choose]) = 0 Then
    GoTo Cont
End If
'of course, this doesn't catch the problem if the user enters 'fubar' for fun
Choice = Me.Choose

'select the appropriate query
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


'This code block was dumb
'we have a friend called RecordCount
'Count = 0
'With rst
'.MoveFirst
            'If .BOF = True Then
            'rst.Close
            'End
            'End If
    'Do Until .EOF
    'Count = Count + 1
    '.MoveNext
    'Loop
'End With

'how many records exists?
rst.MoveLast
Count = rst.RecordCount

' the choice is bigger than the recordcount
If Choice > Count Then
    With rst
        .MoveFirst
            Do Until .EOF
                .Edit
                    ![Spot] = False
                    ![Routine] = False
                    'edit them to be all randoms
                    ![Random] = True
                .Update
                .MoveNext
            Loop
    End With
    rst.Close
    'go to report generation
    'normally GoTo is considered a device of the devil, but I'll leave that here
    GoTo Cont
End If

'if we got here then the number chosen is less than the possible quantity
'so we'll randomly pick one to mark as random

i = 0
With rst
    For i = 1 To Choice
        'loop through randomly until you find a candidate
        Do
        .MoveFirst
            Randomize
            Random = Int((Count * Rnd) + 0)
            .Move (Random)
            If ![Spot] = False Then
                If ![Routine] = False Then
                    If ![Random] = False Then
                        'this one is a candidate
                        'mark it random
                        .Edit
                        ![Random] = True
                        .Update
                        'bail on the loop
                        Exit Do
                    End If
                End If
            End If
        Loop
    'the count goes up by one, and we go back to For
    Next i
End With
rst.Close

'the form gets requeried
DoCmd.Requery


'*************HERE'S WHERE I THINK THE PROBLEM IS
'WHAT DO THESE MACROS DO?************************
Cont:
If Me.TC.SpecialEffect = 1 Then
    DoCmd.RunMacro "Add UA"
Else
    DoCmd.RunMacro "Add UA TC"
End If

'the form will close and reports will open
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


Post what these macros are
If Me.TC.SpecialEffect = 1 Then
    DoCmd.RunMacro "Add UA"
Else
    DoCmd.RunMacro "Add UA TC"
End If


because while your code seems to simply mark a Choice number of records to Random = True, and open reports, it doesn't do anything else.  So something we aren't seeing yet, and that isn't operating as expected, is failed to set Random = False.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
MrSlithyAuthor Commented:
Peter, the FE is on the desktop and all BE information is trusted. I do see warning triangles in one of the macros, but I have set to enable all macros. I also have it set to show all maros.

Nick67,  right you are. Guess I should have given that info too.  I see them being called and have looked at them. The ADD UA is here.  The ADD UA TC is similar and refers to queries related to TC if filtered that way at selection.

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><UserInterfaceMacro MinimumClientDesignVersion="14.0.0000.0000"><Statements><Action Name="SendKeys"><Argument Name="Keystrokes">{ENTER}{ENTER}</Argument></Action></Statements></UserInterfaceMacro></UserInterfaceMacros>

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><UserInterfaceMacro MinimumClientDesignVersion="14.0.0000.0000"><Statements><Action Name="OpenQuery"><Argument Name="QueryName">UA Schedule Add to Table Query</Argument><Argument Name="DataMode">Read Only</Argument></Action></Statements></UserInterfaceMacro></UserInterfaceMacros>

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><UserInterfaceMacro MinimumClientDesignVersion="14.0.0000.0000"><Statements><Action Name="CloseWindow"><Argument Name="ObjectType">Query</Argument><Argument Name="ObjectName">UA Schedule Add to Table Query</Argument><Argument Name="Save">No</Argument></Action></Statements></UserInterfaceMacro></UserInterfaceMacros>

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><UserInterfaceMacro MinimumClientDesignVersion="14.0.0000.0000"><Statements><Action Name="SendKeys"><Argument Name="Keystrokes">{ENTER}{ENTER}</Argument></Action></Statements></UserInterfaceMacro></UserInterfaceMacros>

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><UserInterfaceMacro MinimumClientDesignVersion="14.0.0000.0000"><Statements><Action Name="OpenQuery"><Argument Name="QueryName">UA Update Last</Argument><Argument Name="DataMode">Read Only</Argument></Action></Statements></UserInterfaceMacro></UserInterfaceMacros>

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><UserInterfaceMacro MinimumClientDesignVersion="14.0.0000.0000"><Statements><Action Name="CloseWindow"><Argument Name="ObjectType">Query</Argument><Argument Name="ObjectName">UA Schedule Add to Table Query</Argument><Argument Name="Save">No</Argument></Action></Statements></UserInterfaceMacro></UserInterfaceMacros>

I want to go with Peter's response because I do see triangle ! Warning next to the SendKeys and CloseWindow portions. But the first thing I did after seeing that was research and make usre 1. trusts were set up between this FE and any BE and also that ALL Macros are enabled. So I have to assume that the macro is running.

The UA Update Query is an edit query that refers to a LAST UA date field in one of the tables.  If I manually run this query, I get a dialogue that it is going to affect the number of records I would expect to see.  If the first iteration was 5, I would see that it is wanting to upgrade 5 records.  

Right about here is where I sort of fizzle out.
0
 
MrSlithyAuthor Commented:
Nock67, I will try your code as well.  

Thanks
0
 
Nick67Commented:
Macros
Blech.

The upshot of them is that they are supposed to run two (action) queries
One is UA Schedule Add to Table Query
The other, UA Update Last

What do these queries do?
Open them in SQL View, and then post the SQL text string that appears.
I suspect the yellow triangle means they won't run
What the SendKeys is for is a mystery, but they shouldn't need a close window.
You can try removing the triangled statements  and see if the run.

*************
GOOD GOD!!
After poking a bit I think I know what the sendkeys were for.
It's an action query, so a confirmation dialog, and a records affected dialog come up.
The SendKeys are to dismiss those.
Gross!
You did shoot him right?
I was initially going to say a flesh wound, but he really should be given a limp.
0
 
MrSlithyAuthor Commented:
Nick, BTW, WOW! Thanks for all comments and suggestions. I love the explanations you provide!
0
 
MrSlithyAuthor Commented:
Here's the UA Schedule Add to Table Query:

INSERT INTO [UA Results 10] ( Building, [Date Generated], Routine, Spot, Random, OBSCIS, [Last Name], [First Name], Room )
SELECT [UA Schedule Choose Query Form].Building, Date() AS [Date Generated], [UA Schedule Choose Query Form].Routine, [UA Schedule Choose Query Form].Spot, [UA Schedule Choose Query Form].Random, [UA Schedule Choose Query Form].OBSCIS, [UA Schedule Choose Query Form].[Last Name], [UA Schedule Choose Query Form].[First Name], [UA Schedule Choose Query Form].Room
FROM [UA Schedule Choose Query Form]
WHERE ((([UA Schedule Choose Query Form].Building)=10) AND (([UA Schedule Choose Query Form].Routine)=True)) OR ((([UA Schedule Choose Query Form].Building)=10) AND (([UA Schedule Choose Query Form].Spot)=True)) OR ((([UA Schedule Choose Query Form].Building)=10) AND (([UA Schedule Choose Query Form].Random)=True));

And here's the UA Update Last - -This is sort of where I felt things were breaking down.

UPDATE [All Info] SET [All Info].[Last UA] = Date()
WHERE ((([All Info].Routine)=True)) OR ((([All Info].Spot)=True)) OR ((([All Info].Random)=True));
0
 
Nick67Commented:
Ok.
Deep-six the macros and do that the way you are supposed to.
Updated code

Private Sub Label22_Click()
SendKeys "{ENTER}", True
'This is hard to know what it is doing
'And Windows 7 and sendkeys toggles the NumLOck
'Drives users batty
'That's a problem for another day

Dim db As Database
Dim rst As Recordset
'these as they were all become variants
'Dim Random, Count, i
'The should all be integers
Dim Random As Integer
Dim Count As Integer
Dim i As Integer
Dim Choice As Integer

'bail to Cont: if the users hasn't entered a value
If Nz(Me.[Choose]) = 0 Then
    GoTo Cont
End If
'of course, this doesn't catch the problem if the user enters 'fubar' for fun
Choice = Me.Choose

'select the appropriate query
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


'This code block was dumb
'we have a friend called RecordCount
'Count = 0
'With rst
'.MoveFirst
            'If .BOF = True Then
            'rst.Close
            'End
            'End If
    'Do Until .EOF
    'Count = Count + 1
    '.MoveNext
    'Loop
'End With

'how many records exists?
rst.MoveLast
Count = rst.RecordCount

' the choice is bigger than the recordcount
If Choice > Count Then
    With rst
        .MoveFirst
            Do Until .EOF
                .Edit
                    ![Spot] = False
                    ![Routine] = False
                    'edit them to be all randoms
                    ![Random] = True
                .Update
                .MoveNext
            Loop
    End With
    rst.Close
    'go to report generation
    'normally GoTo is considered a device of the devil, but I'll leave that here
    GoTo Cont
End If

'if we got here then the number chosen is less than the possible quantity
'so we'll randomly pick one to mark as random

i = 0
With rst
    For i = 1 To Choice
        'loop through randomly until you find a candidate
        Do
        .MoveFirst
            Randomize
            Random = Int((Count * Rnd) + 0)
            .Move (Random)
            If ![Spot] = False Then
                If ![Routine] = False Then
                    If ![Random] = False Then
                        'this one is a candidate
                        'mark it random
                        .Edit
                        ![Random] = True
                        .Update
                        'bail on the loop
                        Exit Do
                    End If
                End If
            End If
        Loop
    'the count goes up by one, and we go back to For
    Next i
End With
rst.Close

'the form gets requeried
DoCmd.Requery


'*************HERE'S WHERE I THINK THE PROBLEM IS
'WHAT DO THESE MACROS DO?************************
Cont:
'Macros.
'Blech.  I don't think so!
'If Me.TC.SpecialEffect = 1 Then
'    DoCmd.RunMacro "Add UA"
'Else
'    DoCmd.RunMacro "Add UA TC"
'End If

DoCmd.SetWarnings False
'turn off the warnings.  But be careful!
Dim qdf As QueryDef
'run the action queries
Set qdf = currentdb.QueryDefs("UA Schedule Add to Table Query")
qdf.Execute
Set qdf = currentdb.QueryDefs("UA Update Last ")
qdf.Execute
'turn the warnings on
DoCmd.SetWarnings True


'the form will close and reports will open
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

0
 
MrSlithyAuthor Commented:
BTW, the guy who designed these did it back in Access 97 and it has survived two upgrades over the last 10 years, to 2003 and now 2010. I curse his name regularly, but at the same time . . .it's amazing how these databases chug on. I have made a few shoddy changes over the years  - - -but this stuff is all his.
0
 
Nick67Commented:
VBA and Access are a great platform, it's just a pity that MS keeps losing sight of it.  No useful upgrades since A2003, except native PDF support.

Does this all make sense?
0
 
MrSlithyAuthor Commented:
I have replaced the code with yours. Other than a space at the end of a name that I corrected, it runs fine. No errors. But I am still getting all previous entries added to any new list that's generated.  So, what you cleaned up seems to make no difference in end result.

Maybe I should look more at the form where results are entered?  But when I enter results and then go out and back it looks like they were accepted because the form looks for unfinished results and these are gone.

They of course show up again as what I thought was NEW UAs but maybe somehow things aren't getting completed properly. Heading home in a few, I'll pick up with anything else you want to look at tomorrow, I'm amazed how quick you are at analyzing things.  Obviously, I'm not!

I've even looked into any events that may be associated with the  one of the reports that might be involved in some sort of refresh but they all look rather straight forward.

Again, all this worked in Access 2003 and appeared to work in 2010 until I ran a second batch in testing.
0
 
MrSlithyAuthor Commented:
I'm tracking I thin.  But I am starting to get a bit lost. Here's what I have so far before your suggested modification just a few minutes ago. Can you put all that in here? And also did you see that so far, your code changes dont fix the problem?

Private Sub Label22_Click()
SendKeys "{ENTER}", True
'This is hard to know what it is doing
'And Windows 7 and sendkeys toggles the NumLOck
'Drives users batty
'That's a problem for another day

Dim db As Database
Dim rst As Recordset
'these as they were all become variants
'Dim Random, Count, i
'The should all be integers
Dim Random As Integer
Dim Count As Integer
Dim i As Integer
Dim Choice As Integer

'bail to Cont: if the users hasn't entered a value
If Nz(Me.[Choose]) = 0 Then
    GoTo Cont
End If
'of course, this doesn't catch the problem if the user enters 'fubar' for fun
Choice = Me.Choose

'select the appropriate query
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


'This code block was dumb
'we have a friend called RecordCount
'Count = 0
'With rst
'.MoveFirst
            'If .BOF = True Then
            'rst.Close
            'End
            'End If
    'Do Until .EOF
    'Count = Count + 1
    '.MoveNext
    'Loop
'End With

'how many records exists?
rst.MoveLast
Count = rst.RecordCount

' the choice is bigger than the recordcount
If Choice > Count Then
    With rst
        .MoveFirst
            Do Until .EOF
                .Edit
                    ![Spot] = False
                    ![Routine] = False
                    'edit them to be all randoms
                    ![Random] = True
                .update
                .MoveNext
            Loop
    End With
    rst.Close
    'go to report generation
    'normally GoTo is considered a device of the devil, but I'll leave that here
    GoTo Cont
End If

'if we got here then the number chosen is less than the possible quantity
'so we'll randomly pick one to mark as random

i = 0
With rst
    For i = 1 To Choice
        'loop through randomly until you find a candidate
        Do
        .MoveFirst
            Randomize
            Random = Int((Count * Rnd) + 0)
            .Move (Random)
            If ![Spot] = False Then
                If ![Routine] = False Then
                    If ![Random] = False Then
                        'this one is a candidate
                        'mark it random
                        .Edit
                        ![Random] = True
                        .update
                        'bail on the loop
                        Exit Do
                    End If
                End If
            End If
        Loop
    'the count goes up by one, and we go back to For
    Next i
End With
rst.Close

'the form gets requeried
DoCmd.Requery


'*************HERE'S WHERE I THINK THE PROBLEM IS
'WHAT DO THESE MACROS DO?************************
Cont:
'Macros.
'Blech.  I don't think so!
'If Me.TC.SpecialEffect = 1 Then
'    DoCmd.RunMacro "Add UA"
'Else
'    DoCmd.RunMacro "Add UA TC"
'End If

DoCmd.SetWarnings False
'turn off the warnings.  But be careful!
Dim qdf As QueryDef
'run the action queries
Set qdf = CurrentDb.QueryDefs("UA Schedule Add to Table Query")
qdf.Execute
Set qdf = CurrentDb.QueryDefs("UA Update Last")
qdf.Execute
'turn the warnings on
DoCmd.SetWarnings True


'the form will close and reports will open
DoCmd.Close
DoCmd.OpenReport "UA Chain Of Custody", acViewPreview
DoCmd.OpenReport "UA Incident Report", acViewPreview
DoCmd.OpenReport "UA Schedule", acViewPreview
End Sub
0
 
Nick67Commented:
I need the macro Add UA TC
and then we'll keep poking.
0
 
MrSlithyAuthor Commented:
I left all your commenting in of course. IT really helps me see what is going on. doesnt include what you were trying to show me in ID: 40776099 your last entry. I was hoping you would toss it in here it needs to go.
0
 
Nick67Commented:
When you get back in describe how it is supposed to function.
Your first code posting only changed Random = True, ran some macros and opened reports
The one macro
UA Schedule Add to Table Query
appends records to the recordset of
UA Schedule Choose Query Form

The other one update [All Info].[Last UA] to today's date
0
 
Nick67Commented:
I left all your commenting in of course. IT really helps me see what is going on.
That's what it's for and enough commenting should be in your code that when you get hit by a bus, someone can make heads-or-tails of the logic of your operation.

Not every procedure needs the full-blown commenting, but enough of them need to have it that you can get a sense of the pattern.

And anything really complex needs it!
0
 
MrSlithyAuthor Commented:
OK . But the Add UA TC only runs on a certain portion of people if TC filter is used at selection. All my testing has not even got to TC people yet, just general employees.  But yeah, I see that if that macro needs to be pitched as well and inserted in code . . .  makes sense!  Here it is - I'll get back with you tomorrow. Thank you Obi Wan, your my only hope!

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><UserInterfaceMacro MinimumClientDesignVersion="14.0.0000.0000"><Statements><Action Name="SendKeys"><Argument Name="Keystrokes">{ENTER}{ENTER}</Argument></Action>

<Action Name="OpenQuery"><Argument Name="QueryName">UA Schedule Add to Table Query TC</Argument><Argument Name="DataMode">Read Only</Argument></Action>

<Action Name="CloseWindow"><Argument Name="ObjectType">Query</Argument><Argument Name="ObjectName">UA Schedule Add to Table Query TC</Argument><Argument Name="Save">No</Argument></Action>

<Action Name="SendKeys"><Argument Name="Keystrokes">{ENTER}{ENTER}</Argument></Action><Action Name="OpenQuery"><Argument Name="QueryName">UA Update Last</Argument><Argument Name="DataMode">Read Only</Argument></Action>

<Action Name="CloseWindow"><Argument Name="ObjectType">Query</Argument><Argument Name="ObjectName">UA Schedule Add to Table Query</Argument><Argument Name="Save">No</Argument></Action></Statements></UserInterfaceMacro></UserInterfaceMacros>
0
 
Nick67Commented:
The front half of the code works by updating Random to true.
Now we have to find out where something is setting random to false.

Your homework, my padawan is to CTRL-F the entire codebase looking for
                        .Edit
                         ![Random] = False
                         .update
hopefully it was done in code.

Otherwise we're looking for
                            DoCmd.RunMacro
and seeing what macros there are and what they do.
0
 
MrSlithyAuthor Commented:
I bit more of an explanation of the process:
Random selection produces three reports which are what get distributed to record UAs. When UAs are complete, I go to an unfinished UA form and enter the completion date for UAs.  This information gets transferred to [All Info][last UA]

Then, the process is repeated next time a batch needs to be generated. The problem I have is that, after completion, and after results have been entered - - - I no longer see these as showing up on Unfinished reports. But I see them all regenerated AGAIN as new UAs -  in addition to whatever random number for that round.

When you say I need to thru the entire dataset, I assume you are talking about more than just that on Click event we have been working with. I experience with Access has been jumping in and out of code builder at different points.

I believe we also need to look at what happens when I go in and enter results. Like I said, it gives the appearance of working but maybe something isn't happening there.  

I just talked with the lady that used to run these.  Back in Access 2003 she often had to generate a new series of reports before all the results were back on the first. And she never saw any of the original names being repeated of they were still undone.
0
 
Nick67Commented:
When you say I need to thru the entire
I mean all the VBA code.
The Click event we've looked at, marks some records from the 'UA Schedule ...whatever' query to random = true and that's all it does

The problem I have is that, after completion, and after results have been entered - - - I no longer see these as showing up on Unfinished reports. But I see them all regenerated AGAIN as new UAs -  in addition to whatever random number for that round.

Because I only have logic to guide me, that leaves me a bit fuzzy but I no longer see these as showing up on Unfinished reports. suggests that whatever process does that, whatever field in the database indicates something is unfinished, it isn't happening.

But I see them all regenerated AGAIN as new UAs -  in addition to whatever random number for that round.
That makes some sense.  The code we've worked on takes random records that have Random = False and sets them to Random = True to make them be new UAs.  When something fails to set that back to false, they're going to keeping appearing in the reports.  Eventually, all of them will become random = true and be stuck on the reports perpetually.

So you need to find the broken mechanism that sets Random = False.
It could be VBA code. We've seen random set to true in code
It could be an action query (given what we saw before with sendkeys, I doubt that it was done through a query alone)
It could be a macro that runs an action query

Whatever it is, it worked in A2003 but not in A2007
Given that I develop in A2003, and the users run A2003, ran 2007 til we got rid of it, and run 2010 and 2013 concurrently without grief, my money is on another challenged macro.
0
 
MrSlithyAuthor Commented:
Got it. I think.  First I added the update to the code where you said above that a section needed more work. Ran the reports to verify all that wasnt causing a problem somewhere.  Everything ran, with same results of course. But at least I figured out where it needed to go on my own! I was a bit foggy by end of day yesterday!

And I like the logic where we know why they are getting back on reports. Not being set back to False, I mean.

I got to this On Click event via ... and code builder. When I go in, my cursor is in that event, but I see others above and below.  But all of that is just associated with that single form, right? So when you say I need to go thru entire data-set looking for either the  ![Random] = False or other Macros I have missed, that means I need to go into the code for all reports/forms associated with this entire process and see what else is happening and where the  ![Random] = False is not working. Right? I appreciate your patience.
0
 
Nick67Commented:
But all of that is just associated with that single form, right?
Correct.
I need to go into the code for all reports/forms associated with this entire process and see what else is happening and where the  ![Random] = False is not working.

That's my guess.
The code you posted shows a choice number of randomly selected records having being set to Random= true only in cases where Spot, Routine and Random were all false to begin with, and then the reports run.
So elsewhere that I haven't seen, there is a process to take those reports, get data, enter it, mark these as finished (I suspect [All Info].[Last UA] for that.

But in order for these NOT to re-appear in the next run of the reports, something has to change the condition that made them appear there in the first place -- and from what you posted, its the code that makes Random = true that makes them appear on the report.

So you take these reports and people pee in cups.
Results are delivered and then entered in some place.
And those results are matched to entries on the report.

The form/control that confirms that you've entered the UA results completely is likely to be the place that resets the Random=true field -- but I haven't seen anything of that yet!
0
 
MrSlithyAuthor Commented:
Crystal clear! Will get on it!
0
 
MrSlithyAuthor Commented:
I think I found it boss. On that same form, above where we have been looking so far.  These three private Sub's show up immediately above that we have been looking at. I know the password routine in the on open and I see the requery but not sure what is being re queries.  And I dont even see uncheck_Click() in the property sheet list.

Private Sub Uncheck_Click()
On Error GoTo Error
DoCmd.RunCommand acCmdRecordsGoToNew
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("All Info")
With rst
.MoveFirst

    Do Until .EOF
    .Edit
    ![Spot] = False
    ![Routine] = False
    ![Random] = False
    .update
    .MoveNext
    Loop

End With
rst.Close
Me.Requery
Exit Sub
Error: MsgBox "Another user is working in related tables.  Please try again later."
'DoCmd.Close
End
End Sub

'What is below is just the two sections immediately following the one above. They, in turn are followed by the one you
'and I have been working on so far. So, does this mean the form, when it is initially opened, is supposed to be doing the
'change back to false . .  .and that's what is not happening?

Private Sub Form_GotFocus()
Me.Requery
End Sub


Private Sub Form_Open(Cancel As Integer)
Dim PassWord As String
PassWord = InputBox("Please Enter Password")
If PassWord = "Password" Then
' Open Form
Exit Sub
Else
MsgBox ("Sorry. Wrong Password.")
DoCmd.Close acForm, Me.Name
End If
End Sub
0
 
MrSlithyAuthor Commented:
I also know "the another user is working in related tables" was just his. "It's not working and this is my story" . That drove me nuts when I started seeing that error in isolated testing where I knew I was the only one in there.  I got that fixed by modifying the password sub according to something I found online to get that error to go away. Up uniil I did that, in testing it wouldn't let me in regardless.  That's one of the places where I did want to shoot the guy.  Why not say something just as meaningless like "The empire state building is not accessible by porcupines:
0
 
MrSlithyAuthor Commented:
Ind i have found the spot, routine, random fields in the table and sure enough, the names that are regenerating are perpetually checked.  if I manipulate the table and uncheck these manually, they go away!!  So what is preventing this from happening in the code?  I mentioned that I updated the password change . . .  and this was BEFORE I realized this issue was happening. Like I said, I knew tables were not in use by others so I looked up code on passwords and replaced what was in there.  I think I also moved it out of On Open to On Load because wherever it is now is where my cut/paste resource said it was best to put it.

So could it been that fixing password, broke the .edit back to false?? I kind of think not.  

So cool. I know we are getting close. I just love knowing what I can do in the table make it work, Not that that is the answer.
0
 
MrSlithyAuthor Commented:
I thought I had another problem on the FINISHED UAs form where these ones that I was manipulating were not showing up as DONE,  But it turns out the end date search on that form was not updating properly to today's date.  I had originally posted this as an additional problem to consider. But it has nothing to do with the  procedure hanging up somewhere else or in addition to. So I think the big problem is still the one identified. I've looked at it several times this afternoon and cant figure it out.

Yesterday you and I were almost discussing real time.  Between then and now I am just adding anything as I continue the discovery.
0
 
Nick67Commented:
So, once upon a time there was a control named Uncheck, that this
Private Sub Uncheck_Click()
was what happened when it was clicked
And absolutely, this is what it opened
Set rst = db.OpenRecordset("All Info") the All Info table or query
And it reset all the flags
It maybe should have looked like this

Private Sub Uncheck_Click()
On Error GoTo Error_Uncheck_Click
'send the form to a new record
DoCmd.RunCommand acCmdRecordsGoToNew

Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("All Info") 'open the All Info table or query

With rst
    'move to the beginning
    .MoveFirst
    'loop until we get to the end
    Do Until .EOF
        .Edit 'setalll these boolean fields to false
            ![Spot] = False
            ![Routine] = False
            ![Random] = False
        .Update
        'move to the next record
        .MoveNext
    'rinse and repeat
    Loop
End With
'close the recordset
rst.Close
db.Close
'requery the form since we screwed with the records
Me.Requery
'bail out
Exit Sub

'we can only get here on an error
Error_Uncheck_Click:
'whatever! frig what a useless error handler
'Error:  MsgBox "Another user is working in related tables.  Please try again later."
MsgBox "Error " & Err.Number & " " & Err.Description & "occurred. The All Info records may not have reset to false."
End Sub

Open in new window


Now, while Uncheck_Click() was once associated with a control, it doesn't have to be.
I can create a Sub with

Private Sub NickSixSevenRocksAndNeedsNoControl_Click()
Msgbox "I rock!"
End sub


and I can call it from anywhere within the module with

Call NickSixSevenRocksAndNeedsNoControl_Click()

I suspect that your Form_Open event had
Call Uncheck_Click() in it at one time.

We also have that mystery
SendKeys "{ENTER}", True
 'This is hard to know what it is doing
 'And Windows 7 and sendkeys toggles the NumLOck
 'Drives users batty
 'That's a problem for another day

at the beginning.

Perhaps part of what you edited was throwing the focus to a command button called UnCheck and that sendkeys simulated someone clicking it.
0
 
MrSlithyAuthor Commented:
Well I changed the code as suggested. Makes no difference in final result.  And I did nothing in regards  SendKey. The Num Lock problem with Windows 7 when we upgraded from XP ad it rang true as soon as I saw your first comment about it.  

When I initially upgraded to Access 2010 I started seeing that no good error. So I thought all I was doing was removing some outdated code in regards to password. Obviously I bet I did more than I intended.

I will reconvert the original access 2003 database, which i know works, point it to my test data, go in and fix the error message to give me something more meaningful and go from there. So let me re-percolate a bit and I will get back to you when coffee is hot!
0
 
MrSlithyAuthor Commented:
Ok,  When I open the Access  database in Access 2010 (The original before I started trying to "fix" things database) the procedure works fine.  

looking at my notes, when I started trying to resolve THIS problem I also became educated on TRUSTS and set up the proper trust relationship between the FE on desktop and backend(s) on network. In a way, his stupid error message helped because that's what got me educated on all the trust relationships.  I thought I was fixing the password hangup but I actually somehow broke the process AND since I fixed the trust problem at the same time - - - I didnt realize that this was actually what was causing the issue.  

I am now going to upgrade the original  databases to 2010 and try to run again without anything else.  If it fails, I know it's back related to something I haven't yet discovered (but there's nothing else to discover we say) If it WORKS then I'm a bonehead.  I'll let you know
0
 
MrSlithyAuthor Commented:
all right, Just a straight conversion and it all works with dummy data connecting to a back end on the network to which only I have access.

That wild goose chase error message has been around for a long time and for a long time, I took it at face value and thought somehow, certain records were locked if they were open by other users using their front ends pointing to the same back end, so maybe I have to kick users out to run this process.  I couldn't figure anything else out.  And doing that all worked.  I already had a way of booting people out of the FEs so I just created a process where the person doing this could giver them the boot. That allowed it to work until we upgraded to Access 2010. Why?  Maybe the back end was just slow to responding originally and the error was more of some sort of a time out?   Because these are all functional databases pointing to multiple back ends and they have all worked fine for years thru previous upgrades.

So when I could no longer get past that error in my upgrade testing, I thought I was updating code for the password . . .
but I might have actually been BREAKING the routine. At the same time I was educated on trusts and fixed what was really causing the error.

tomorrow I will do the following:
1. I will compare the unclick sections in the working upgrade and the broke and try to determine what else of deleted when i thought I was just removing "old code" for password
2. I will reinsert all the changes you gave to me in the first place just because I want it to work better
3. I will change the error message as you suggested to be more relevant
4. Test test and retest on dummy data
5. I will then put this new and repaired FE into service with actual back end data being used by others (but now in trust) and next week have one of the admins try it on live data.

And maybe this is the final AHA! When we went to Windows 7 we went to Office 2010 right away, but a modified install where access 2010 was not installed and old office 2003 products were left on as part of the migration.  maybe that has a lot to do with the entire progression.

I just don't understand, then, why dumping other users out of the old access 2003 got past that stupid error. I may be new to trust relationships, but I was very familiar what I had to do in regards to file permissions to make a local FE work well with network BEs.

Anyway - I'll report back, boss, when I decide to jump back in tomorrow, I have met the enemy - - and he is . . . Me!?
0
 
MrSlithyAuthor Commented:
OK Nick67,

As I said I would do, I have done a new conversion of the original Access 2003 databases and, without any change to the code, tested on both my dummy data and live data, at least as far as getting past the error message. I can get in now.

At the original conversion, I couldn't get past the error. I believe now that I mistakenly thought somehow the password procedure was outdated and copied down new. But I think when doing so, I broke the procedure to .Edit TRUE back to the table. Around the same time, I set up trusts properly.

I was then able to get past the custom error message, and everything appeared to work unitl it had to write back to the table.

I'm a bit amazed at what it took to come back to this. But there still is a problem.  I have started and restarted the front end over and over again, and occasionally the error message does come up. Other times, the form comes up but it is delayed to the point where I expect to see the error. Whereas originally I see now that it was correcting the trust, but there is something else going on in regards to delay.  I don't see this delay when I test to DUMMY backend data which is in a network location set up to be very similar to where the actual data is.

What truly would need to be happening on the back end to legitimize the custom message?  If you recall, the table opening is resetting several items back to TRUE.  At any point during the day, the table it is trying to write to is being linked to on 8-10 other FEs on other users desktops.

I suspect that the original author of the database was getting this message from time to time as well - only the actual Access error message, which he replaced with something a bit more friendly.   I haven't yet reinserted all your suggested code.  I want to do that. but first I want to understand what I need to do to get past this problem,
0
 
Nick67Commented:
For now, just fix the error handler in Uncheck_Click()
Comment out the first line with the apostrophe and replace it
'On Error GoTo Error
On Error GoTo Error_Uncheck_Click

and comment out the useless error handler
and replace it
'Error: MsgBox "Another user is working in related tables.  Please try again later."
 'DoCmd.Close
 'End


'we can only get here on an error
Error_Uncheck_Click:
'whatever! frig what a useless error handler
'Error:  MsgBox "Another user is working in related tables.  Please try again later."
MsgBox "Error " & Err.Number & " " & Err.Description & "occurred. The All Info records may not have reset to false."

When an error gets thrown, post up the error number and description.
That can give us an idea about what is going south, whether it is really a clash of record-locks or something else.
0
 
MrSlithyAuthor Commented:
here was the actual error -

Error 3158
Error Name:      Microsoft Access Error 3158
Error Description:       Could not save record; currently locked by another user.

Not sure why this would happen at times and not others, 10-15 people at a time are in the BE, but every time I get this lockout message, I can also query the data in question and write to it via just making up a query for the same data, and unchecking (setting back to false).

But then I thought that it was kind of silly going back into the form and doing this when the form first opened. Sometimes, they want to re print last results, and the way it was, they would have to go in and choose all those names as a spot test, and change to random.

So I commented out all the part about writing back to the table and added another button on the form where they can access that query and clear out the check boxes themselves.   This way they can reprint if needed, either all or selectively by only unchecking certain individuals.

Nick, I'm really amazed at the amount you helped me and appreciate it. i understand everything about this form, about your improvements and how I need to document with comments more for the next time I look and say "Crap. Who did that!"
0
 
Nick67Commented:
Not sure why this would happen at times and not others
Your app is old and may be carrying record-locking baggage.
If you get into
File | Options | Advanced
you will see some choices for default record locking.
Mine are set for No Locks
and the checkbox for Open databases using record-level locking is checked.

These settings allow users to get at everything, and see BANGs! only if they really do tried to edit simultaneously.
This is more resource intensive.
Back in the day, someone may have opted for different settings for performance reasons.

you helped me and appreciate it
You're welcome.

Nick67
0
 
MrSlithyAuthor Commented:
BEs are still access 2003 - I will review all this when I upgrade them to 2010.   I've already noticed that the 3158 error is NOT showing up somewhere else where it was seen normally.

Not sure why I didnt update both BEs and FEs at once. this worked for me back in the day when i went from 97 to 2003, so I just stuck with it.

Again, thanks.
0
 
Nick67Commented:
You've talked of 'upgrading'
My mdb files are Access 2000 format.
The end users run a mix of A2003, 2010 and 2013.

Nothing has needed to change
Access 97 to Access 2000, yes change in file format was required.
Unless you plan to use things you shouldn't :),
Like multi-value tables and attachment fields,
the accdb file format isn't needful.
0
 
MrSlithyAuthor Commented:
Gotcha. I was leaning that way anyway, so that's why I just planned on FEs initially. Was going to save that research for another day,  

No plans for any new features (to me, a new feature is keeping the old features working!) So I may just let that dog lay where he is.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.