Upgrade to MS Access 2010, Forms erring with You Can't go to the specified record

We just upgraded some of our PCs to MS Office 2010.  We have an Access DB that has been used since Access 2000 and prior for over 10 years.

Suddenly, with this upgrade, many of our datasheet view type forms are not working properly.
When you attempt to select any record below the first displayed record and perform an action (we have a button that then opens a separate form based on the record actively highlighted), we get an error message of "You Can't go to the specified record".

Additionally, you can't navigate from one record to the next using the standard MS Access record selector at the bottom of the form.

I imagine this is a simple setting or something that defaulted incorrectly upon installation?

Please help!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
there are probably more than one reason behind this problem, i suggest that you take a read of the following links

Transitioning Your Existing Access Applications to Access 2007-2010

Access 2010 Development
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Access 2010 also makes use of Trusted Locations. Be sure that your database is installed in a Trusted Location - you can check that by reviewing Access Options - Trusted Location.

Also, many have reported issues when "upsizing" to Access 2010 and keeping the .mdb format. If ALL of your users are now using 2010, then be sure to upsize the database to the new .accdb format. If you must still support users running 2003 or lower, then one good practice is to build a new, blank database in Access 2010 using the older .mdb format, and import everything into that new database. This seems to cure a lot of mysterious ills.

You may also have issues with your references. If you open the VBA Editor, click Tools - References and see if any are marked as MISSING (do this on one of the machines where you're having troubles). If they are, youll have to correct that, and we can help with that if necessary.
Jeffrey CoachmanMIS LiasonCommented:
<No Points wanted>

Perhaps I am a bit paranoid, but...
I always get nervous when jumping 2 or more versions

The info capricorn1 and LSM posted should work for you...

In some extreme cases you might have to rebuild the form though...

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

bkapla1Author Commented:
I should clarify... although the original application was built in an old version of Access... I was using MS Office 2007 prior to this without any issues.  Yes, the database is still in a .mdb format due to some users using older versions of Access.
bkapla1Author Commented:
@LSMConsulting... made sure the location is trusted and didn't see any VBA references missing.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Then try the "move to a new .mdb" trick. Just because it ran in 2007 doesn't mean it'll work as expected in 2010.

We've also seen a definite uptick in code that was "marginal" blowing up as Access moves through the newer versions. Access has always been very forgiving in regard to syntax and code constructs, but as the Access team makes changes, some of those issues are starting to fail.

For example, for years you could include standard WHERE syntax in the FILTER parameter of the OpenReport method. Once 2007 came on the scene, however, this no longer worked, and code that worked perfectly fine in other version failed in 2007.

That's not to say your code is "marginal", but just instead to let you know that these things happen, and quite often the "move to a new .mdb" trick has resolved quite a few of these issues.
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
@ bkapla1

<<I should clarify... although the original application was built in an old version of Access... I was using MS Office 2007 prior to this without any issues.>>

I experience the same thing. with Access 2010. Stiff that works in previous versions stops working in 2010.  I have been able to find workarounds for some of the issues.that will still work with previous versions.

Have you checked to see if the form is filtered? In design view, check the filter property of the form and make sure it is empty.

bkapla1Author Commented:
Tried to recreate the DB by importing objects, but some objects failed to import (there are many many objects), so I just stopped the process.

I may try recreating a single datasheet type form to see what happens, but in the meantime...

I noticed that in the VBA references, one of the libraries was the DAO library, but not the ADO library.  However, some of our VBA code uses ADO.  So, if I add the ADO library, interestingly enough, one the same datasheet view page that failed before, now works (although a different error pops up).  However, the fact that I am now able to select a record in the datasheet view other than the first record and proceed to the next form is interesting.

Any explanation as to if I'm on to something, or is this just coincidental and probably not the real solution?
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
FWIW: In Access 2010 you do not select a DAO library like in previous versions. If you have a DAO lib select then try deselecting it.

See: Where is the DAO Library in Acess 2007 and 2010?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<Tried to recreate the DB by importing objects, but some objects failed to import >

This would indicate corruption of those objects. Have you tried performing maintenance on the file? Before doing any of the following, please be sure to make a backup:

1) Compact the database
2) Compile the database - from the VBA Editor click Debug - Compile. Fix any errors, and continue doing this until the menuitem is disabled
3) Compact again

If that doesn't work try a Decompile. To do that, build a standard desktop shortcut with this as the Target:

"full path to msaccess.exe" "full path to your database" /decompile

Run this, then run the 3 steps above.

If you DO get the database to work, I'd still suggest you move everything to a new, blank database (using the .mdb format) that you've created in 2010.
bkapla1Author Commented:
Sorry for such a delayed response.... out on vacation for the holidays.

I have possibly found at least where the issue is occurring, but need further help.

I found 2 almost identical datasheet forms that use very similar functionality where one form is working and the other is not.

The main difference between the 2 forms is that the one that is erring as mentioned above has a function call in the OnLoad event (GetFieldPrivilege Me, "v_mie_all") of the datasheet subform.

The function is intended to disable various fields based on database user permissions for roles.
If I comment out the function, the form works perfectly (although I'm sure it doesn't disable the various fields of course).

Can you look at the attached VBA function and let me know if anything stands out.  This function was probably written 10 or so years ago I imagine, so I assume something in the code is no longer supported in this newer version of Access.

I may be able to hunt this down, but it would take me longer than the experts on this site.
' Functionality:
'   Dim and lock all fields on the given form which the current
'   user does not have UPDATE privilege on.
'   If the form is in Datasheet view, also disable these fields.
' Global Variables Read:
'   p_strODBCConnect (initialized in frmAuto)
' Global Variables Written:
'   None
' Input Arguments:
'   objForm: Form or Subform that contains the fields to dim and lock
'     objForm could be either Form or Subform, thus is typed Object
'     instead of Form.
'   strRecordSource: SQL Server view or table name which serves as the
'     eventual record source of the form, and which
'     provides column level access control to be checked against.
'     If strRecordSource does not exist on SQL Server as a table or
'     view, all fields on the current form will be dimmed and locked.
' Implemetation:
'   SQL Server stored procedure "col_upd_priv TableOrViewName" was
'   called via SQL pass-through query to retrieve all the columns
'   the current user has UPDATE privilege on.
' Special Notes:
'   The ODBC connect string is hard-coded and needs be adjusted
'   if moving from development to production server.
Public Sub GetFieldPrivilege(objForm As Object, strRecordSource As String)
    Dim dbs As Database
    Dim qdf As QueryDef
    Dim rst As Recordset
    Dim ctl As Control
    Dim strCriteria As String
    Dim blnDataSheet As Boolean
    Dim errLoop As Error
    On Error GoTo Error_GetFieldPrivilege
    Set dbs = CurrentDb
    Set qdf = dbs.CreateQueryDef("")
    With qdf
        .Connect = p_strODBCConnect
        'The above ODBC Connect String was initialized in frmAuto' On Load event   '.SQL = "col_upd_priv " & strRecordSource (statement for SQL Server 6.5)
        .SQL = "spColumnPrivileges " & strRecordSource
        .ReturnsRecords = True
    End With
    Set rst = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
    'Debug.Print vbCrLf & Date & " " & Time & vbCrLf _    '& "Form Name: " & objForm.name & vbCrLf _    '& "SQL Query: " & qdf.SQL
    ' blnDataSheet is set to true if the DefaultView property of form    ' is 2 (datasheet)
    If TypeOf objForm Is Form Then
        blnDataSheet = IIf(objForm.DefaultView = 2, True, False)
    ElseIf TypeOf objForm Is SubForm Then
    ' objForm is a subform and does not support DefaultView property.
    ' This happens when GetFieldPrivilege is not called from within    ' the form whose fields are to be dimmed and locked.
        blnDataSheet = IIf(objForm.Form.DefaultView = 2, True, False)
        MsgBox "Error: Incorrect argument type for GetFieldPrivilege."
    End If
    For Each ctl In objForm.Controls
        If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox _
        Or ctl.ControlType = acCheckBox Then
            If ctl.ControlSource <> "" Then
                'strCriteria = "COLUMN_NAME='" & ctl.ControlSource & "'" (for SQL Server 6.5)
                strCriteria = "COLUMN_NAME='" & ctl.ControlSource & "' AND PRIVILEGE='UPDATE'"
                rst.FindFirst strCriteria
                If rst.NoMatch Then
                  With ctl
                    ' TextBox and ComboBox controls support BackColor and ForeColor property
                    ' but CheckBox does not
                    If .ControlType = acTextBox Or .ControlType = acComboBox Then
                      .BackColor = 12632256 ' 12632256 - light gray, 16777215 - white
                      .ForeColor = 0 ' 0 - black, 8421504 - gray
                    End If
                    .SpecialEffect = 2  '0 - flat, 2 - sunken
                    .Locked = True
                    'If the default view of form is Datasheet, disable the field
                    If blnDataSheet Then
                        ' The next line of code will generate a run-time error 2164 if the current control has the focus.
                        'Code has been written in the error handler below to
                        'deal with this.
                        .Enabled = False
                    End If
                  End With
                  'Debug.Print strCriteria & ", NO UPDATE"
                  'This is only necessary for some old forms such as f421_feds that
                  'did not have the locked property of fields set to False by default at design time
                  With ctl
                  ' .BackColor = 16777215 'white
                  ' .ForeColor = 0 'black
                  ' .SpecialEffect = 2 'sunken
                    .Locked = False
                  End With
                End If
            End If
        End If
    Next ctl
    Set ctl = Nothing
    Set rst = Nothing
    Set qdf = Nothing
    Set dbs = Nothing
    Exit Sub

    If Err.Number = 2164 Then
        ' This error will occur when we try to disable a field that has the focus
        ' on a form in datasheet view.
        ' We can simply ignore this error because the field is already locked.
        Resume Next
        MsgBox "In GetFieldPrivilege, Error " & Err.Number & " from Source " & Err.Source & ": " _
        & vbCrLf & Err.description
        Debug.Print "In GetFieldPrivilege, Error " & Err.Number & " from Source " & Err.Source & ": " _
        & vbCrLf & Err.description
        If DBEngine.Errors.Count > 0 Then
            Debug.Print "The following database errors occurred when running GetFieldPrivilege: "
            For Each errLoop In DBEngine.Errors
                Debug.Print "Error Number " & errLoop.Number & vbCr & errLoop.description
            Next errLoop
        End If
    End If
End Sub

Open in new window

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I don't see anything in your code that would fail to work in any version of Access since 2000. Really all it does is set the Enabled and Locked properties, and change the SpecialEffects and modify the colors. Those are standard, normal processes.

As I mentioned earlier, it's highly possible that you have a corrupt form. If so, then your only recourse is to recover the form from a known good backup, or recreate it from scratch. Often recreating from scratch is a better solution, since it insures that you don't inherit corruption. You can copy/paste controls and code from one form to another, if need be, although if it's fairly simple I'd just create it entirely anew.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bkapla1Author Commented:
Ok, glad the code looks good.

I did in fact create a new form from scratch albeit copying/pasting the code, etc., but I think I only did the subform.

I'll try the parent form now and see if that makes a difference.

bkapla1Author Commented:
When I recreated the form initially and copied/pasted the actual objects (e.g. buttons, text boxes, etc.), that failed to work.

But, when I created the objects from scratch, it finally worked as expected.

Thanks for the help!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.