Lookup Table Trouble

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

I'm new to Access, and have two questions and am having
trouble getting a form to work as I would like it.

Some background: this is an equipment tracking database,
and I have split the equipment data into two tables, a
core equipment data table, and an equipment purchases sub-
table. The two tables are linked using the equipment ID
with a 1:1 relationship. The core equipment table has
links to several lookup tables that store data shared by
many pieces of equipment: equipment type, Manufacturer,
Service Provider, Vendor, Location, Owner, and
Department. I set up relationships with these tables as
1:many.

So here's what I did: after setting up all of these
relationships in the relationships window (and
establishing referential integrity with all of the key
fields), I started working on a form that would access
all of these fields for data entry. I based the initial
form on a query that displays the fields from the both
the equipment and purchases tables.

I modified the fields in the equipment table that include
all of the foreign keys to the lookup tables, using the
Lookup Table wizard to display the related data instead
of the keys - and ended up with tons of redundant
relationships in the relationship window! I didn't care
for that very much, but I was able to add those fields to
the form as combo boxes (which is exactly what I wanted),
and the form displays all of the valued from the related
tables. Not wanting all of these redundant
relationships, however, I deleted all of the newly
created relationships (selecting the links and removing
them), and changed the Display Control (in the table
design) back from Combo Box to Text Box.

This I thought would get me back to where I was before,
where I could re-build the form using the existing table
relationships without relying on the Lookup Wizard...

Now here's where the questions come in:
1. Why does my form still work? I am still able to use
the form, with combo boxes, even though I have deleted
the relationships and the lookup controls they were built
with. Is it because the query has been replicated in
building the control, or are the relationships still
there, lurking around somewhere I can't see them?

If we take a look at one of the combo boxes retrieving
data from a linked lookup table(Combo Box:EqTypeID), the
form properties show:

Control Source......EqTypeID (the primary key of the
related table)
Row Source Type.....Table/Query
Row Source..........SELECT[tblEquipTypes]
Bound Column........1

Can I keep these combo boxes on my form, or should I
delete them and create them some other way?

My second question is related to the same form, but
different in nature:
2. When I use the form, I am required to complete entries
for all fields using foreign keys. If I try to save an
incomplete record (maybe I don't have all of the eq
information yet), I get a dialog that reads: "You cannot
add or change a record because a related record is
required in Table 'tablename'".

How can I modify the form and avoid this situation?

Thanks in advance to anyone having the patience to read
through this verbose explanation and deepest gratitude
for anyone kind enough to propose a solution.

Scott
 
Scott said:
I modified the fields in the equipment table that include
all of the foreign keys to the lookup tables, using the
Lookup Table wizard to display the related data instead
of the keys - and ended up with tons of redundant
relationships in the relationship window!
Click to expand...

That's one of the unfortunate outcomes of using the lookup wizard in a
table. It will create a relationship (and extra indexes), even if one
already exists.

I didn't care
for that very much, but I was able to add those fields to
the form as combo boxes (which is exactly what I wanted),
and the form displays all of the valued from the related
tables.
Click to expand...

That is one of the benefits of using the lookup wizard in a table. Saves
you a couple of seconds when you add such a field to a form. You could have
used the combobox wizard on the form to lookup the value in another tables
(note this would not create the extra relationship/index).

Not wanting all of these redundant
relationships, however, I deleted all of the newly
created relationships (selecting the links and removing
them), and changed the Display Control (in the table
design) back from Combo Box to Text Box.
Good.


This I thought would get me back to where I was before,
where I could re-build the form using the existing table
relationships without relying on the Lookup Wizard...
Click to expand...

No need.
Now here's where the questions come in:
1. Why does my form still work? I am still able to use
the form, with combo boxes, even though I have deleted
the relationships and the lookup controls they were built
with.
Click to expand...

When you made the form, it inherited properties from the tables/queries that
you used at that time. Changing the properties of the field after the fact,
will not cascade to the form. Also you didn't delete *all* the
relationships, just the extra ones.
Can I keep these combo boxes on my form, or should I
delete them and create them some other way?
Click to expand...

Keep them. There's nothing wrong with them as is. In fact if you deleted
them, and then recreated using the form combobox wizard, you'd end up with
the same properties.
My second question is related to the same form, but
different in nature:
2. When I use the form, I am required to complete entries
for all fields using foreign keys. If I try to save an
incomplete record (maybe I don't have all of the eq
information yet), I get a dialog that reads: "You cannot
add or change a record because a related record is
required in Table 'tablename'".

How can I modify the form and avoid this situation?
Click to expand...

If you don't have all of the equipment information, then you can't really
make the field required.
 
Joan - thanks for helping me better understand these
functions. With so many wizards to do the work for you, it
becomes harder to understand what's really happening in
there. It's great to get the perspecitve of someone who
knows what goes on behind the scenes...

As for the required fields, it turned out I had some
default values, and have been able to use the form as
intended after deleting them.

Many thanks!!!

Scott
 
Back
Top