Solved

ERROR in ACCESS 2010

Posted on 2014-03-25
17
451 Views
Last Modified: 2014-03-31
Main Menu - Error when opening MILE formIndexes in Mile TableMile - TableMile - FormMile Form Data PropertyHi,
 
 I have created a database in ACCESS 2003 and it has been running just fine.
 Users got ACCESS 2010 and all of sudden when an user tries to open a form, it generates an error. But no error is shown when the same form is open in ACCESS 2003 PC.
  I have currently back-end MDB that contains tables on the server.
  Each user has front-end MDB (on their PC) that contains everything else but the tables which are linked to the back-end MDB.

  When I ran this database on ACCESS 2010 first time, there were some errors that popped up and I took care of them by going into "REFERENCES" and uncheck some boxes that began with "MISSING Microsoft xxxxxxx".

  As you can see in the screenshots, The table "Mile" has index file called "ORIGIN_DEST" and when the form is open, it is used in "Order By" field under data tab of the form. So I wonder how it is ok with ACCESS 2003, but it is an error in ACCESS 2010.

Thanks.
0
Comment
Question by:sglee
  • 10
  • 3
  • 2
  • +2
17 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39954161
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
0
 
LVL 13

Accepted Solution

by:
Norm Dickinson earned 500 total points
ID: 39954167
I've seen this happen when there was some bad data in one of the tables. Correct the erroneous value (usually a mismatch of some sort) and it may work.
0
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 39954172
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]
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:sglee
ID: 39954235
References - Access 2003@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.
0
 

Author Comment

by:sglee
ID: 39954238
References - Access 2010Sorry, here is References in ACCESS 2010
0
 

Author Comment

by:sglee
ID: 39954361
Indexes in Mile tableI 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
0
 

Author Comment

by:sglee
ID: 39954423
Mile Table Data Contents@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.
0
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 39954530
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
0
 

Author Comment

by:sglee
ID: 39954638
@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.
0
 

Author Comment

by:sglee
ID: 39954664
I removed those blank records shown in ID: 39954423 and there is no more error message.
0
 
LVL 84
ID: 39955619
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.
0
 

Author Comment

by:sglee
ID: 39955875
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.
0
 
LVL 84
ID: 39957362
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.
0
 

Author Comment

by:sglee
ID: 39957398
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.
0
 

Author Comment

by:sglee
ID: 39957870
In attached, I included Table design/ Form Design / related coding.
System-Overview.doc
0
 
LVL 84
ID: 39966183
So your trouble turned out to be bad data?
0
 

Author Comment

by:sglee
ID: 39966262
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...
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

860 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