On Thu, 14 Jan 2010 09:39:22 -0800 (PST), esn wrote:

I'm not trying to insult anyone who's trying to help me, but I don't
feel like anyone but Gina is actually making any effort to help. Any
post that mentions "data entry" or "forms" is ignoring my situation.

Ok, I'm really confused now. In your original post you say, to quote:

It works properly when entering
data, meaning it won't let the enterer remove the focus from the field
if the text they've entered isn't in the list. But for some reason it
will allow text values to be pasted into the field even if they aren't
in the list.

So it would seem that you ARE doing data entry - by copying and pasting -
directly into a table.

Tables aren't designed for data entry; lookup fields aren't designed to do
what you're asking them to do.

No one here has actually suggested an alternative to my methods that
would work given my circumstances, unless they are really implying
that I should re-renter 3 years worth of data from 3 different
projects using forms.

Certainly not. A form is just a tool, a window to enable manual interaction
with the data.

Maybe I'm confused on the use of forms for
importing preexisting data and that's my issue. Would it even be
possible to use forms to do what I'm trying to do? Meaning import
data from various files in various formats?

Importing should probably be done using File... Get External Data... Link to
link to the external file, and then running an Append query to migrate the
data into your table. If you have Lookup Fields in your table, then the table
in fact contains a numeric ID, not the text; if you do not have that numeric
value in the external file that you're attempting to import, your task becomes
more complex - you'll need to join to the lookup table to ascertain the
numeric value, if it exists, and (perhaps) to create new records in the lookup
table if the incoming data does not match any existing record.

As for commenting on my
"end-users" etc., I know them (and my situation) a lot better than you
do. It's not possible for them to do their jobs without writing
queries and it wouldn't be possible for me to write every possible
query they might need for them. I'm working in a completely different
field than most of the people who use Access, and juggling several
different databases from several different projects. Making them as
intuitive as possible is indeed high on my list of priorities, which
is why I'm using a lookup field in the first place. The databases
I've designed for my own use don't have a single lookup field, but my
boss thinks they're the bees knees and doesn't want to stare at a
bunch of foreign keys that don't mean anything to her. You can take
it up with her if you think that makes her an idiot.

On the contrary, I think she's perfectly right to want to see the human
meaningful data. That doesn't mean that she should be routinely using *TABLES*
to interact with the data. They're simply not the right tool to do so! Table
datasheets (even with lookup fields and subdatasheets) are very limited in
functionality. If you choose to sacrifice the functionality of forms, then you
are stuck with the limited capabilities of table datasheets.

For now I need a
way to import preexisting data and data from other sources that will
ensure that it complies with the data integrity rules I've set up.
Using a lookup field is the simplest way I know of to do that. I
could also import the data into new tables and use subselect queries
to compare the values in the imported table to the allowable ones, but
that leaves me with no simple way of correcting the errors, and I
still need to copy and paste or append the data to the existing table
when it's corrected. What advantages would that really have in my
situation? And how could that possibly be more intuitive?

You'll need to explain how your current situation is better. Obviously it
isn't, because it lets you append garbage data. If you have garbage data in
your input files, then - at some point - you MUST do the comparison to
allowable entries; this can be automated to a great extent, but there'll still
be cases where the computer CANNOT ascertain the correct value, and a human
interaction will be needed.


I'm here looking for help with MY SITUATION, not to be your soapbox.
I don't need a lecture but a couple suggestions would be nice. And
don't worry, when all the data is finally brought together and I know
it's accurate, I'll work on convincing my boss to let me convert that
lookup to a simple foreign key, and the comboboxes I have in my forms
will do their job. Getting to that point is my issue.

If you could explain a bit more about the nature of the data you're importing
(you have not yet done so), the nature of the tables into which you're
importing (you have not yet done so), the nature of the mismatches (we don't
know that either), we'll be glad to do so.

I apologize of the didactic tone of my previous posts... but given your
intense tone in the message to which I'm replying, I'll wait a bit to
apologize for this one. I'd like to be able to help, but I really do need to
understand the question first.
--

John W. Vinson [MVP]