Creating new values in related table without duplicating
I've been reading through various related forum threads, but not sure I've found one which answers my specific question. I'm also blurry-eyed, and may just need a little slap in the face to get me to figure out what's going on.
I've got a database with multiple tables. The main list (table) contains a list of fixtures, quantities, locations, and suppliers, amongst other data. Another table should contain just the fixtures being used. A third table would keep a list of suppliers used.
Now, I could keep this all in one table and use value lists from that, but what I don't want to happen is that if I delete a record in the main list, or even all of the records, that the value lists get lost. However, I don't want to manually create value lists, either. I want them to update as info is entered (but never get deleted). Make sense?
So, essentially, as I build the main list, the lists of fixtures and suppliers will update themselves, and populate into value lists. I've got that part working using related tables and telling the relationship to create new records in the fixtures and supplier databases. However, it creates a new record for every record I create, even if it already exists.. So, if record 1 in the "Main List" contains one WhizBang fixture, a new record is created in the "Fixtures" table, as desired. Now, if I want record 2 in the "Main List" to also be a WhizBang, and I choose WhizBang from my pop-up menu, it creates a second record for WhizBang in "Fixtures".
What I want to do is whenever I enter a new fixture in the "Main List", for it to update the "Fixtures" list, but if the fixture already exists in "Fixtures", it just uses the existing data, instead of creating a new record. Seems simple, and I feel like I've done this before, and am just missing something simple.