ERROR in ACCESS 2010

If references were missing, just unchecking them will not solve the problem -it'll just allow the database to open.

You'll need to identify these again, and select them from their new list so that they can reference the object from the new location. The missing just meant I can't find it where I expected it - you'll have to help access out. If these were 3rd party dlls, you may have to ensure these still exist. Many of the ocx's that shipped with Access 2003, are not there in 2010. You might need to locate a copy and install them - or replace the functionality with new functionality - e.g. the calendar ocx with the built in date picker.

Kelvin

You cannot use the name or an index in an ORDER BY clause, only the name(s) of field(s).  This has always been the case, so something else must have changed in your migration from Access 2003 to Access 2010.

Try using the two field names instead:  ORIGIN_NO, DEST_NO

Best wishes,
Graham Mandeno [Access MVP 1996-2014]

@kelvinsparks
I checked the references of both versions and as seen above, they are all there.

@GrahamMandeno
When I replaced ORIGIN_DEST with ORIGIN_NO, DEST_NO in Order By field, I got no error when open Mile form, but as I moved from one record to another, I got an error.

Sorry, here is References in ACCESS 2010

I don't remember why I created index name "ORIGIN_DEST" when there was another index "ORIGIN_NO" with the same field "ORIGIN_NO".
I developed this > 15 years ago, so don't recall much.
The MILE table contains ORIGIN_NO and DESTINATION_NO in numeric value.
So when the user opens the form, I need to display actual cities or place names instead of numbers. So I perform a lookup in both Origin and Destination table. But the funny thing is that I am using FindFirst function that does not require indexes.
Here is my code in "On Current" event.

Private Sub DEST_NO_LostFocus()
  Dim db As Database
    Dim rst As Recordset

    Set db = CurrentDb()
    Set rst = db.OpenRecordset("Destination")
    ' rst.Index = "Dest_No"
    rst.FindFirst "[dest_no] =" & Me!DEST_NO
   
    If rst.NoMatch Then
        MsgBox "Destination Number is Invalid or Empty!"
    Else
        Me!DestNameDisplay = rst!NAME
        Me!DestCity = rst!CITY
    End If
    rst.Close
End Sub

Private Sub ORIGIN_NO_LostFocus()
  Dim db As Database
  Dim rst As Recordset

    Set db = CurrentDb()
    Set rst = db.OpenRecordset("Origin")
    ' rst.Index = "Origin_No"
    rst.FindFirst "[origin_no] =" & Me!Origin_No
   
    If rst.NoMatch Then
        MsgBox "Origin Number is Invalid or Empty!"
    Else
        Me!OriginNameDisplay = rst!NAME
        Me!OriginCity = rst!CITY
    End If
    rst.Close
End Sub

@Norm Dickinson
When I open MILE table, I see some records with blank ORIGIN_No and DEST_NO. ORIGIN_No and DEST_NO should NOT have blank data because I used them to look up actual city names in ORIGIN and DESTINATION tables to display actual city names in MILE form.
So when all the users are gone tonight, I will remove these blank records and try again.
I will keep you posted.

ORIGIN_DEST is a two-field index, comprising ORIGIN_NO and DEST_NO.  The only reason I can see for doing this is if you wanted to constrain the table such that you could not repeat the same pair of origin and destination.  If you want this constraint, make the index unique, otherwise you might as well delete the index.

What is the error you got when moving from one record to another?  Does your form have any code that executes when the record changes (Form_Current)?

For your form to display the origin and destination names, instead of numbers, You can use combo boxes bound to Origin_NO and DEST_NO respectively, with RowSources based on the Origin and Destination tables, and showing the NAME and CITY fields.

Or, you can base your form on a query which has joins to the Origin and Destination tables, and includes the NAME and CITY fields.

By the way, "NAME" is a reserved word in Access and if you use it as a field name then it is only a matter of time before your application will break.

-- Graham

@GrahamMandeno
After trying to remove empty records from the table tonight, if I still get the same error, I will try your suggestions.

It is really strange that this worked for many years in ACCESS 2003, but all of sudden, it became a problem in V2010.

I removed those blank records shown in ID: 39954423 and there is no more error message.

There were a lot of changes made between 2003 and 2010, and many things that worked in 2003 (but should not have) began to fail in 2007/2010. From what I can tell of your specific situation, this should not have worked in 2003 (since you're ordering by an INDEX and not a FIELD or FIELDS), and it was just pure luck that 2003 let you do it.

I'm curious why you'd order by a INDEX of a table instead of on the FIELDS in that index? You can order by on multiple fields:

Origin_No, Dest_No DESC

If it were me, I'd change that Order By clause to be compliant with standard programming practices.

Let me look into your suggestions. I like to do it right and removal of those empty records should have generated the same error in 2003 . Unfortunately I do not recall why I had to create an index like that, but there had to be a reason for that.
Sometime today I will post code details along with screen shots so that we can discuss.

I can understand creating a multi-field index. That's common.

What I don't understand is setting a Form's OrderBy field to the name of the Index - that should have failed (even in 2003). There may be something else going on - for example, you may have a control named that - but I'd be curious to know what.

I know. I am curious too.
I have been tied up all day today, but when I go home tonight, i will post some code and stuff.
Thanks for your patience.

In attached, I included Table design/ Form Design / related coding.
System-Overview.doc

So your trouble turned out to be bad data?

Yes. There were several records where indexed fields were empty. Once I purged them, error was gone. But I am still curious as to how I got away from the error in Access 2003...