This is a sample of what I require, the database I am building is larger, but if I can grasp this explanation all should be OK.
My database has 4 tables, all are linked in sequence, T1 to T2, T2 to T3, T3 to T4.
For tables 1 to 3, I do not want duplicate entries.
Table 1 Products, 1 of each product.
Table 2 Manufacturer, 1 of each manufacturer.
Table 3 Problem, a listing of common problems but same problem not duplicated.
Table 4 Remedy, this table can have duplicates.
For data input to tables 1 to 3 there are 3 input forms, one for each table.
For data input to table 4, I wish to use a Query based on tables 1 to 4, use a form bound to the query, then select the relevant data from tables 1 to 3 from bound combo boxes to the relevant table on the query form, this then allowing me to input further linked data which will be for table 4.
If the data for the relevant table is not in the combo listing, a double click on that combo box will open the respective table’s input form, I have this part working OK.
This is now where my problem started, for example using table 1, I only used Product, (not ProductID and Product) for the combo selection criteria, the selection worked OK, but an error message stated, selecting this product would create a duplicate, what I was trying to achieve was use something on the existing Product listing. The same happens for tables 2 and 3.
If I allow duplicates the system works OK.
I hope this makes sense.
kevsim
My database has 4 tables, all are linked in sequence, T1 to T2, T2 to T3, T3 to T4.
For tables 1 to 3, I do not want duplicate entries.
Table 1 Products, 1 of each product.
Table 2 Manufacturer, 1 of each manufacturer.
Table 3 Problem, a listing of common problems but same problem not duplicated.
Table 4 Remedy, this table can have duplicates.
For data input to tables 1 to 3 there are 3 input forms, one for each table.
For data input to table 4, I wish to use a Query based on tables 1 to 4, use a form bound to the query, then select the relevant data from tables 1 to 3 from bound combo boxes to the relevant table on the query form, this then allowing me to input further linked data which will be for table 4.
If the data for the relevant table is not in the combo listing, a double click on that combo box will open the respective table’s input form, I have this part working OK.
This is now where my problem started, for example using table 1, I only used Product, (not ProductID and Product) for the combo selection criteria, the selection worked OK, but an error message stated, selecting this product would create a duplicate, what I was trying to achieve was use something on the existing Product listing. The same happens for tables 2 and 3.
If I allow duplicates the system works OK.
I hope this makes sense.
kevsim