Solved

ERROR in ACCESS 2010

Posted on 2014-03-25
17
439 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:sglee
Comment Utility
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
Comment Utility
References - Access 2010Sorry, here is References in ACCESS 2010
0
 

Author Comment

by:sglee
Comment Utility
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
Comment Utility
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
Comment Utility
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:sglee
Comment Utility
@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
Comment Utility
I removed those blank records shown in ID: 39954423 and there is no more error message.
0
 
LVL 84
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
In attached, I included Table design/ Form Design / related coding.
System-Overview.doc
0
 
LVL 84
Comment Utility
So your trouble turned out to be bad data?
0
 

Author Comment

by:sglee
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now