Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 514
  • Last Modified:

Acess 2010 table throwing error when creating a lookup field.

I  am trying to create a simple table in access as part of a training lesson.  Every time I try to create a lookup, I get two errors, back to back.  They are attached as screen shots.  I'm not sure whats causing them, as I don't have a lot of experience with access, so any help would be much appreciated.
I've made sure that all tables are closed, and am getting the error whenever I save the table. 1st error received when creating a lookup filed.
 Second error received.
0
mrodriques
Asked:
mrodriques
  • 5
  • 3
  • 2
  • +1
2 Solutions
 
mrodriquesAuthor Commented:
Just a few observations.  I'm not sure if they are important or not.  I am running Access 2010.  I previously ran 2003 and DID NOT have this issue.  I only have the issue if I am running a lookup filed that needs to create a relationship to another table.  If I use the wizard and select enter my own values, I DO NOT get the errors.

Thanks
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm not sure why you have the error, but in general you should NOT use lookup fields defined directly in your tables. It's perfectly fine to have them on your forms and reports, but most professional developers stay far away from table-defined lookups.

Have you tried saving the table before you do this?

You may have a corrupt database. Try building a new, blank database and importing everything into it.

It also is possible that your installation of Access is corrupt, especially given your second error message. Have you tried to Repair or Reinstall Access/Office.

0
 
GRayLCommented:
I would throw an error at anybody who tries to design a lookup field into a table ;-)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
mrodriquesAuthor Commented:
I get that, and thanks for you advice.  As I said, I'm new to access, and appreciate any advice I can get.  Thing is, I'm going through this training course, and that's the lesson that I was on when I got the error.  The error only occurs when I am creating a lookup field using a wizard, and only when I am creating a relationship to another table.  If I close the table and manually create the relationship, it works fine.
0
 
GRayLCommented:
I don't believe you can 'create' a relationship.  It has to exist - in your Relatilnships diagram.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Did you review the suggestions I made? Have you tried them?
0
 
mrodriquesAuthor Commented:
Yes LSM.  I  appreciate them, and I did.  I am getting the same error on both new databases (2 tables only), existing databases that I attempt to modify, as well as database templates that I  try to modify.  I also tried both reapiring and then reinstalling Access.  Al to no avail.

Not to be too presumptuous GRayL, but when I am creating a table with a look-up, and I use the look-up wizard, I  get a message saying "table must be saved before relationship can be created", then it fails out.  I can go in and manually create teh relationship.
0
 
mrodriquesAuthor Commented:
Additionally, the second error message is kind of misleading.  The look-up wizard actually runs and creates the look-up parameters for me.  It just doesn't create the relationship for me.  I have to close teh table, open relationships and manually create the relationship.
0
 
GRayLCommented:
Most designers agree that, while it may look good initially,  incorporating a lookup field in a table is not a good design practice.  They  are difficult to maintain, especially if the database schema changes -  which always happens early in the project life.   As to why that is happening when you use the look-up Wizard - perhaps the database is flawed - create a new mdb (?? - I don't know what version you are using),  and import all the old objects over using File, Get External Data..., and follow the Wizard.
0
 
Jeffrey CoachmanMIS LiasonCommented:
If it were me, I would skip this "Lesson".

As the other Experts have noted, this wizard is rarely used by professional Developers.
See here if you need more convincing:
http://access.mvps.org/access/lookupfields.htm
;-)

As LSM stated, there is a combobox wizard that you can use in Form design to do the same thing.

Note the first error:
Please make *sure* that the Orders Table is not already open, or in use by another person. (Does the "Lesson" program itself access this file?)
Make *sure* the Orders table is designed properly (from the previous "Lesson" perhaps?)
Check your Datatypes, Properties, ...etc

If you create a *simple* version of this db on your own, does this error still occur?

JeffCoachman
0
 
mrodriquesAuthor Commented:
Please make *sure* that the Orders Table is not already open, or in use by another person. (Does the "Lesson" program itself access this file?)

The lesson file creates a new table every time a new lesson is started, I assume to avoid the issue of someone like me screwing up the table.

As the other Experts have noted, this wizard is rarely used by professional Developers.
See here if you need more convincing:
http://access.mvps.org/access/lookupfields.htm


I wouldn't propose to argue that point with anyone.  You are all clearly more experienced with this sort of thing.  I was just going by the numbers.

If you create a *simple* version of this db on your own, does this error still occur?

It happens whether I am creating a table from scratch, or using a lesson file.  The only time it doesn't happen is if I select to use my own values during the wizard process.  In order to work around it, I OK the two errors, close the table and manually create the relationship in the relationship table.

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now