Vba Works In 2007, Not 2010 - Loop And Record Update

[sceptar17]

The code below is a part of a scheduling database that my department uses. The user is able to indicate a scheduling priority by request type (the records in FrmPrioritySheet) and then the code is to look at all the requests for the chosen point in time (underlying records in FrmAutoPrioritize) and prioritize them by assigning the appropriate priority level to the request. The user is also able to limit how many hours of each request type can be scheduled (this is regulated using the total hours reference compared to the FrmPrioritySheet.MaxTotal).

This code worked fine in Access 2007. When I try to run this code in 2010, though, it doesn't work. It does not produce an error, it just runs and doesn't set the priority for the underlying records. I set up a message box as part of the loop and it seems to be cycling through the records in FrmPrioritySheet but not ever moving on to the next record in FrmAutoPrioritize. Again, though, this same code works in Access 2007. We are using the 32 bit version of Access 2010.

So, I guess I have two questions. Since I know the code works in 2007, does anyone have any thoughts on why it may not work the same in 2010? All references are set the same (except 2007 is using VB6 and 2010 is using VB7, although from what I read it shouldn't have any impacts). There is similar code elsewhere in the db (comparing two recordsets from forms and updating them based off of each other) that is also not working and I assume if I can figure out why this one isn't I can fix that as well. And secondly, perhaps to avoid that issue, is there a better way to do this? For example, to open up the recordsets without opening the forms? I inherited this code and although I can understand what it is doing, I do not know enough to understand all the other ways this same thing could be accomplished. Maybe the issue will follow a rewrite to a different method but perhaps not.

'Open auto-prioritize form, include request data that will be manipulated    
        'Includes filters from request sheet    
    DoCmd.OpenForm "FrmAutoPrioritize", acNormal, , , acFormEdit, acHidden
        'Open priority sheet with default priorities by request type
    DoCmd.OpenForm "FrmPrioritySheet", acNormal, , , acFormEdit, acHidden
 
 
    Forms!FrmPrioritySheet.Recordset.MoveFirst
 
    ' Cycle through each priority level
    Do While Not Forms!FrmPrioritySheet.Recordset.EOF
          totalHours = 0
        If Not Forms!FrmPrioritySheet.IsCat Then
        Forms!FrmAutoPrioritize.OrderBy = "Type, Duration"
        Forms!FrmAutoPrioritize.OrderByOn = True
        Forms!FrmAutoPrioritize.Recordset.MoveFirst
        ' Cycle through each request
        While (Forms!FrmAutoPrioritize.Type <= Forms!FrmPrioritySheet.PriorityType And _
        Not Forms!FrmAutoPrioritize.Recordset.EOF)
                 If Forms!FrmAutoPrioritize.Type = Forms!FrmPrioritySheet.PriorityType And _
            Forms!FrmAutoPrioritize.Priority = 100 And _
            (totalHours + Forms!FrmAutoPrioritize.Duration) <= Forms!FrmPrioritySheet.MaxTotal Then
             Forms!FrmAutoPrioritize.Priority = Forms!FrmPrioritySheet.PriorityValue
                 End If 
           totalHours = totalHours + Forms!FrmAutoPrioritize.Duration
        End If
    Forms!FrmAutoPrioritize.Recordset.MoveNext
     Wend
     Forms!FrmPrioritySheet.Recordset.MoveNext
Loop
 

[NeoPa]

I've seen threads recently where similar code problems occur in 2010. The answer was to ensure that the ADODB library is referenced in the project. Also make sure that the project compiles (See When Posting (VBA or SQL) Code).

[sierra7]

What a novel idea! Open two forms, hidden, then cycle through their record sets changing values in the forms.
NeoPa is right to point out that Access 2010 will not have the ADO DB library as it has reverted to used the DAO instead. This is certainly an issue when moving from Access 2003, but 2007 to 2010 should be OK.

With DAO you have to use .Edit and .Update statements before updating a member of the recordset. They are not present in your code but then you are updating fields on a Form, not actually manipulating the recordset.

What worries me more is that I can see two While's and only one MoveNext. There is only one Wend. Is this OK? Personally I use Loop but would still expect two of them.

Are you sure this runs ok in 2007?
S7

[sceptar17]

NeoPa and sierra7, thank you for your quick replies. To answer your questions:

- I accidentally left off the last two lines of code. I've updated the code above.
- I do know that it runs in 2007. I actually have to have a guy in another department run a couple things in the database for me each week because he has Access 2007.
- Everything does compile.
- I tried adding ab ADODB reference (C:\Program Files\Common Files\System\ADO\msado25.tlb) which was the location referenced at http://allenbrowne.com/ser-38.html. Is that the correct one?
- So now the code stops at the Forms!FrmAutoPrioritize.Priority = Forms!FrmPrioritySheet.PriorityValue line (where it actually passes the priority assignment) and gives an error that I can't assign a value to that object (Run-time 440).

Thoughts?

[sierra7]

Hi again,
First of all I cannot see the line of code you are referring to...

However, question, are you running on a SQL Server backend?
Some recordsets are not updatable unless they are defined with a unique index, and a timestamp field helps.

Can you make your form visible and update the field manually?

S7

[sceptar17]

Sorry for the confusion - I added the code to the code box in the original post (thought it would make it easier to have all of it in one place.) The backend is Sharepoint. I'll try and open the form and update it manually. And I'll sniff around and see if there are other posts with issues around 2010 and Sharepoint since it works ok in 2007.

[NeoPa]

You sound like a member who responds intelligently to posts. Always good news. Let me just suggest here that identifying lines of code is easier and more reliable using line numbers (EG. Line #14 etc).

PS. Updating the original post made sense I feel.

[sceptar17]

Line numbers it is, thanks for the reminder. (It was line #23 for what it's worth.)

Anyway, I believe the issue has been solved, although I don't completely understand why. I changed the form that is being updated to open as a datasheet instead of in form view and voila, works perfectly. I dug through the settings on the form and still can't figure out why the form view wasn't working.

So, I'll live to get in over my head another day. Thanks to both of you for your help and courtesy, very much appreciated.

[NeoPa]

I'm pleased that you managed to resolve your issue.

We don't generally encourage members to award the Best Answer to one of their own posts though. There are some rare occasions when it makes sense, but not this one I'm afraid.

[sceptar17]

I thought perhaps it was a way to get the answer to the top of the question so folks didn't have to dig. Sorry about that. (It did seem a little egotistical at the time now that you mention it.)

Thanks again for your help.