Error when no description is present

M

Mary Hartman

I have developed a mineralogy database in Access 2003.

When the name of the specimen is entered, there are two fields that
fill in automatically from information in two other tables.

One is the mineralogy description. There is a metaphysics
description.

If both of these exist, it works fine. If one or the other does not
exist I get an error that says "can't find mineralogy" or "can't find
metaphysics" for such and such. All don't and will never have both
descriptions.

Is there a way to work around this error so that if the description
doesn't exist Access will enter a null value into the field.

Thank you!
 
J

Jeff Boyce

Mary

I can't be sure from your description, but it sounds like you are having
Access enter data redundantly ("fill in automatically ...").

This is a common approach when you only have a spreadsheet to use, but as a
relational database, Access means you DON'T have to double up like this.

I'll guess you have a table with specimen names (else how would Access know
which minerology and/or metaphysics rows to look for?). If those other two
tables have some way of connecting (?specimen name?), then use a query to
display the information, don't re-store it.

Or have I misunderstood what your application is doing?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
M

Mary Hartman

Actually, the data descriptions are being filled in on this form
almost for display reasons only. They are NOT being re-saved to a
redundant field in another table.

This is just so that whoever is filling in the data can see whether or
nott there is a description for that field. If there isn't and one is
needed they fill that in on a popup form.
 
J

Jeff Boyce

Mary

Great! So you'll want to take a look at the .Column() property, which
returns the data that's in the 'other' columns of your query.

To get around the "some are, some aren't" situation, use a query. In your
query, use "directional joins" (my terminology). Highlight the join line,
right-click, and pick all of your main table and ANY of the related table.

This way, you still get the main table values, even if no corresponding
value exists in the "child" table.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Top