Control display error when no data

[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. The other is a metaphysics
description.

If both of these exist, it works fine. If one or the other does not
exist I get an error message and cannot save the record.

This info is in a lookup table. I am NOT trying to copy this into the
new table, the reason for the field is so that the person entering
data will know whether a description for that category of specimen
exists. Some will never need one, but if one does and it's not there
the data entry person will know to enter it through a separate popup
form.

Is there a way I can get this control to display the data when it
exists and not give an error message when the data does not exist?

 

[Larry Daugherty]

Yes, but you're keeping secrets so we can't give you complete help.
Post back telling us how

When the name of the specimen is entered,

happens and then you can get answers specific to your issue. The
first thing is that those *controls*, not *fields* where the data is
filled in if it exists should not be bound to fields anywhere.

I'll give you an example which may or may not work for you: If all of
the possible minerals are already in a table along the lines of

tblMineral
MineralID Autonumber
MineralName Text
MineralogyDesc Text
MetaphysicsDesc Text
Note Text

Because it's used to hold a list for looking up things it's a lookup
table.

In the form under discussion, create a textbox for the Mineralogy
Description and another textbox for the Metaphysical Description.
Leave these controls unbound. Next, create a combobox with the
Wizards turned on. Make it as wide as you can. The Wizard will offer
you choices about what to do. You want the combobox to lookup values
in a table or query. Choose tblMineral. Select the first four
fields. Allow the wizard to hide the key field. Click on. Identify
the field where you want the mineral name to be entered. Click on.
Give the text for the label. Depending on your version of Access you
may or may not be able to name the control within the wizard. If not
then I'd rename it right away and go find the AfterUpdate event code
it created using some really creative name like "combo5". Do a find
and replace in that procedure to replace their name with yours.
Making sure the one you chose is the one you put in the Name property
of the combobox.. I'd call the combobox cboFindMineral.

Just to test things so far you could switch from design mode to run
mode and lookup a mineral. That mineral name should be entered in the
designated field/control. If that worked then get back into the
AfterUpdate code and add a couple of lines just before the code exits:

me!MyMineral = me!cboMineral.column(2)
me!myMetaphys = me!cboMineral.column(3)

Save to disk and go back to run mode. Now when you choose a mineral
its descriptions should appear in the textboxes.

Working with collections can versus properties can drive you crazy
until you force the awareness of what's going on. Collections, such
as columns, are zero based. Properties are decimal or "1" based.
Looking at the properties you'll see that you have 4 columns in the
combobox. The bound column is assignable and the widths of columns
can be tweaked.

Anyway, that should give you some basis for solving your problem.

HTH