Automatic additions in lookup tables (1 Viewer)

[vangie]

Hi,

I have a table with multiple lookup tables connected to it. I find it frustrating to have to always have to return to the lookup table when I want to add new information to my main table, but I want the new information to be added to the lookup table.

For example: My lookup table contains the colours "red" and "blue". In my main table I have numerous entries that contain new colours: "white", "orange", "purple", etc. Is there a way that I can create a relationship so that the information in my main working table can automatically be put into the lookup tables.

My main table has numerous lookup tables connected to it, and since I am currently in the process of creating a database for my information. I am trying to find an easier way to process it. Especially since the person who will be using the data process is not especially computer literate. So I need to make the input form as simple as possible. I currently use Access97.

Thanks

 

[Rich]

Use the Not In List event of the combo boxes you're selecting the look up values from

 

[Pat Hartman]

You can build your lookup tables from the other table by making totals queries and changing them to make-table or append queries.

Select YourField
From YourTable
Group By YourField;

This query will give you a list of all the distinct values for YourField. Once you have built the lookup table, open the relationships window and create relationships between the main tables and each of the lookup tables. Check the enforce referential integrity box so that "bad" data can no longer be entered in the main table. Populate the combos with the data from the lookup tables. If the lookup tables are so volitile that you feel the need to allow the users to add new values on the fly, you can use the not in list event as Rich suggested. I almost never allow "on the fly entries" and in fact I usually have more stringent security for the lookup tables to prevent sloppy entries.