Using required and unique on fields linked in relationships

[anthgav]

Hi



I have a database used for managing warehouse locations. I have a few fields set up in relationships. I make queries from excel into pivot tables which work fine.

I wanted to enhance the access database by turning on REQUIRED and UNIQUE for some of the fields in Access. When I activate these the existing queries that are set up in Excel pivot tables still work fine. When I want to create a new query I get "Type mismatch in expression" error. I know these errors happen when you have different field types such as short text and a number linked to each other. it also seems I get these errors when using REQUIRED and UNIQUE on fields in a relationship.

For example I have a table called Warehouse, the index for Warehouse is automatically set to UNIQUE.


I also have a table called Area. In Area is a field called warehouse which has a relationship to the index in the Warehouse table. the warehouse field is set to REQUIRED.


I have similar relationship fields through out the database.

Is it possible to use REQUIRED and UNIQUE in relationship fields, if so what can I do to to prevent mismatch errors with them? Perhaps I'm doing something incorrect.

Thanks

[June7]

Sounds like the value of warehouse in Area table is actually the text description from warehouse field of Warehouse table and not the warehouse_id. This will cause the mismatch error.

[anthgav]

Thanks for that, what you said is correct. I've had to change all my lookup fields to show ID instead of the description to make UNIQUE and REQUIRE work. All my fields now show the ID.

Is there a way to achieve using REQUIRE and UNIQUE but to have the description show in the lookup field instead of the ID?

[June7]

I never set lookups in tables. I build comboboxes on form.

Either way, set properties:

RowSource: SELECT warehouse_ID; warehouse FROM Warehouse ORDER BY warehouse;
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0";1"

Advise you not give objects/fields/controls same names. I would use Warehouses and Areas (note the plural) as table names.

[anthgav]

I've been getting my head around making these link without using relationships and I'm finally grasping it using combo boxes. I've linked the tables in the combo box using the warehouse fields so that I get a description name instead of an ID number in the field, is that the correct way of doing it? Also what if I want referential integrity, can I only set that using relationship look ups?

[June7]

Whether you call it a 'lookup' or a related dependent table, controlling referential integrity requires to either set relationship links in Relationship builder and set the Enforce checkbox for the link or to carefully manage data integrity with db interface design and code.

If you don't save warehouse_ID as foreign key then it really serves no purpose for this situation and is not even needed in the combobox RowSource.