Enforcing uniqueness of Primary Key with Foreign Key

[Blue Hornet]

Hi, and thanks for your attention.

I'd like to show what I want by example. Let's say I have two tables:
tblState, consisting of Autonumber (Key field) and State (required, no dups)
and
tblTown, which is made of Autonumber (Key field), StateID (lookup into tblState, required), and Town (required, DUPS ALLOWED--because a town name can exist in several states).

How can I enforce uniqueness of the Town & State combination without making Town a unique field in its own table AND without making Town either the Primary Key OR any part of it? (The reason I don't want there to be a multi-field Key is that it makes expandable queries difficult or impossible, and I want the application to work within forms based on queries.)

Does that make sense? Is there something simple that I'm overlooking?

Thanks again,
Chris

 

[SydneyGeek]

I guess you could try building a query with [State] & " " & [Town] as a calculated field.
When creating or editing a record, you can use the BeforeUpdate event of the form to check whether the combination exists already. If so, cancel the update and display a warning message, taking the user back to Town so they can try again.

A bit vague because I'm not in front of a Access machine at the moment, but it provides a possible solution.

Denis

 

[Blue Hornet]

Denis,

Many thanks for your reply, but I had already been trying along those lines, and without success. But I finally got my answer, in the first pages of Helen Feddema's Expert One-on-One Microsoft Access. She told me something that you may have already understood, but I had no idea about.

I got the book last night, and in the first chapter she mentioned that the use of Lookup Wizard fields (defining a table's Foreign Key field as a lookup into another table's Primary Key, for the benefit of any other readers who don't know already) is actually counter-productive to higher level development. And here I thought that this was a great feature that should be used as much as possible! Who knew!?

Well, I redefined my tables to include the Foreign Key / Primary Key links I already knew that I wanted, but performed the linkages in the Relationships table and by specific lookups in forms (as I think you had already alluded to, and which I have picked up from prior postings). What I had never understood was that the "Lookup Wizard" was actually harming my ability to make the queries I wanted. Within a few minutes of reading what she said, I had revised the Table Definitions, and manually linked the tables in the Relationships view. I didn't even have to modify any form fields, since the only linkages I wanted were Form / SubForm, where the key fields don't even need to be displayed. I did explicitly check and update the Form / SubForm linkages, though.

The multi-field Key field definitions are no problem at all, and never should have been. The queries are properly updatable / expandable, just like I wanted.

The upshot of it was that within an hour and a half of opening the book, I had all of my earlier questions answered, my queries and forms working just right ... so far ... and I can't wait to see what's next.

Thanks again,
Chris

 

[SydneyGeek]

Chris,

Yep, that's one of the classic traps that everyone falls into. One of Access's NON-features, I guess. And they usually point people towards Lookup fields in training courses... BTW, Ive got that book too and it's got heaps of useful stuff.

Cheers
Denis