Related record is required

[Katherine]

Hi. What's wrong with my design?

tblOwnerType (can be Employee, Warehouse, Office, etc.)
OwnerTypeID (Autonumber, primary key)
OwnerType

tblOwner
OwnerID (Autonumber, primary key)
OwnerTypeID (foreign key from tblOwnerType)
OwnerLastName
OwnerFirstName
etc...

tblInventoryItem
InventoryItemID (Autonumber, primary key)
OwnerID (foreign key from tblOwner)

I've created a form from the above tables to enter an item
into inventory. If the OwnerType is Employee, I need the
employee's name. Otherwise, I need no further description
of the owner. There is a problem with this design in that
I'm unable to leave the Owner fields blank, for example
when choosing the OwnerType of Warehouse. The message I
get is "You can't add or change a record because a related
record is required in table 'tblOwner'."

Thanks for any help you can provide!

 

[Ken Snell]

You don't say what the underlying recordsource of the form is, nor how
you're selecting/entering the owner's name, so this is just a guess.

In tblInventoryItem, I assume that OwnerID is set to Required = Yes as a
property?

On your form, you probably are using a combo box that is displaying the
owners' names so that you can select from them, but that combo box likely
either isn't using a query as its row source where the query includes the
OwnerID for the names you're displaying, or else the bound column of the
combo box is not the "column" corresponding to the field that is OwnerID.

Your table design looks fine to me.

 

[Immanuel Sibero]

Hi Katherine,

The problem you're experiencing is in the table tbOwnerType. An employee
does not belong with an office/warehouse. Offices or warehouses are
locations, arent they? In a normalized table, each record describes an
entity of the same class or type as the entities described by other records
in that same table. So, you would not put a record describing an employee
along with records describing an office/warehouse in the same table.


HTH,
Immanuel Sibero

 

[Guest]

Thank you. The underlying recordsource of the form is
tblInventoryItem. I am using a combo box on the form. The
row source is: SELECT DISTINCTROW [tblOwner].[OwnerID],
[tblOwner].[OwnerName] FROM [tblOwner]; In
tblInventoryItem, the OwnerID is set to Required = No. I'm
a beginner and slow at this, but are you saying I should
create a query from tblInventoryItem and then create the
form from the query?

 

[Guest]

Thank you Immanuel. Do you have any suggestions on how to
set up my tables and relationships? I need to track
the "location" of each inventory item AND if the item
is "located" with an employee I need details on the
employee (name, title, etc.) If the item is a printer and
assigned to an office for shared use, I need to know which
office (we have several). If the item has not been
assigned, it would be located in the warehouse. I am so
new at this, but I think you're saying I need a separate
table for employees, one for offices, one for district,
one for the warehouse, etc. How do I relate them to the
Inventory Item table? Thanks again. I'm really
struggling with this one.

 

[Ken Snell]

OK.

Is the bound column of the combo box set to 1? It needs to be.

Is the control source of the combo box set to the OwnerID field? It needs to
be.

--
Ken Snell
<MS ACCESS MVP>

Thank you. The underlying recordsource of the form is
tblInventoryItem. I am using a combo box on the form. The
row source is: SELECT DISTINCTROW [tblOwner].[OwnerID],
[tblOwner].[OwnerName] FROM [tblOwner]; In
tblInventoryItem, the OwnerID is set to Required = No. I'm
a beginner and slow at this, but are you saying I should
create a query from tblInventoryItem and then create the
form from the query?

-----Original Message-----
You don't say what the underlying recordsource of the form is, nor how
you're selecting/entering the owner's name, so this is just a guess.

In tblInventoryItem, I assume that OwnerID is set to Required = Yes as a
property?

On your form, you probably are using a combo box that is displaying the
owners' names so that you can select from them, but that combo box likely
either isn't using a query as its row source where the query includes the
OwnerID for the names you're displaying, or else the bound column of the
combo box is not the "column" corresponding to the field that is OwnerID.

Your table design looks fine to me.
--
Ken Snell
<MS ACCESS MVP>




.

 

[Katherine]

I think so... I'll check when I get back to work on Friday
and reply again then. Thanks Ken. And, Happy New Year!

-----Original Message-----
OK.

Is the bound column of the combo box set to 1? It needs to be.

Is the control source of the combo box set to the OwnerID field? It needs to
be.

--
Ken Snell
<MS ACCESS MVP>

Thank you. The underlying recordsource of the form is
tblInventoryItem. I am using a combo box on the form. The
row source is: SELECT DISTINCTROW [tblOwner].[OwnerID],
[tblOwner].[OwnerName] FROM [tblOwner]; In
tblInventoryItem, the OwnerID is set to Required = No. I'm
a beginner and slow at this, but are you saying I should
create a query from tblInventoryItem and then create the
form from the query?

-----Original Message-----
You don't say what the underlying recordsource of the form is, nor how
you're selecting/entering the owner's name, so this is just a guess.

In tblInventoryItem, I assume that OwnerID is set to Required = Yes as a
property?

On your form, you probably are using a combo box that

is
displaying the

owners' names so that you can select from them, but

that
combo box likely

either isn't using a query as its row source where the query includes the
OwnerID for the names you're displaying, or else the bound column of the
combo box is not the "column" corresponding to the

field
that is OwnerID.

Your table design looks fine to me.
--
Ken Snell
<MS ACCESS MVP>

Hi. What's wrong with my design?

tblOwnerType (can be Employee, Warehouse, Office, etc.)
OwnerTypeID (Autonumber, primary key)
OwnerType

tblOwner
OwnerID (Autonumber, primary key)
OwnerTypeID (foreign key from tblOwnerType)
OwnerLastName
OwnerFirstName
etc...

tblInventoryItem
InventoryItemID (Autonumber, primary key)
OwnerID (foreign key from tblOwner)

I've created a form from the above tables to enter an item
into inventory. If the OwnerType is Employee, I need the
employee's name. Otherwise, I need no further description
of the owner. There is a problem with this design in that
I'm unable to leave the Owner fields blank, for example
when choosing the OwnerType of Warehouse. The message I
get is "You can't add or change a record because a related
record is required in table 'tblOwner'."

Thanks for any help you can provide!



.

.

 

[Van T. Dinh]

My guess is that you have enforced Referential Integrity for the
Relationship between Table tblOwner ("One" side) and Table tblInventory
("Many" side).

If this is enforced, you need a VALID value in the Field OwnerID for each
Record in the Table tblInventory. Thus the JET database will prevent you
from saving any new Record without a valid value for the OwnerID regardless
whether you specified OwnerID Field (in Table tblInventory) is required or
not..

Check the relationship between the above Tables and un-enforce the
Referential Integrity if required.

Check Access Help on Referential Integrity also.

 

[Immanuel Sibero]

Hi

There's probably more than one way to do this. Seems to me that for each
inventory item, all you want to do is to keep track of where it is
(location) and who it is assigned to (owner). You can simply do this with
your existing inventory table and two lookup tables - tblOwner, tblLocation.

Do you need separate tables for warehouse, district, office? Depends on your
need. From your post, I think you just need a location, so no need for
separate tables for warehouse, district, etc. Just add a table of locations,
say, tblLocation.

So you would have an inventory table with ItemID, OwnerID, LocationID.
ItemID is primary key, OwnerID is used to look up tblOwner, LocationID is
used to look up tblLocation. NOTE: This assumes one-to-many relationships
between the lookup tables and the inventory table, otherwise changes are
needed.

Obviously an item would always have a location, but from your post, an
item's status may be unassigned (shared). You could add to tblOwner a record
to account for this status ie. an owner record called <unassigned> or
<shared>, this way you would never have to leave OwnerID blank.


HTH,
Immanuel Sibero

 

[Guest]

You guessed correctly. THANK YOU MUCH!!

-----Original Message-----
My guess is that you have enforced Referential Integrity for the
Relationship between Table tblOwner ("One" side) and Table tblInventory
("Many" side).

If this is enforced, you need a VALID value in the Field OwnerID for each
Record in the Table tblInventory. Thus the JET database will prevent you
from saving any new Record without a valid value for the OwnerID regardless
whether you specified OwnerID Field (in Table tblInventory) is required or
not..

Check the relationship between the above Tables and un- enforce the
Referential Integrity if required.

Check Access Help on Referential Integrity also.

--
HTH
Van T. Dinh
MVP (Access)






.