Updating From A Common Form

[kevsim]

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

 

[giacomo]

Maybe I'm not understanding the problem fully, but you have indicated that you have 4 tables. Sounds as if you have created a table for each entity which is great. However, where are you bringing it all together? Is there a table that says you had problem X with product Y and chose remedy Z?

 

[kevsim]

giacomo,
Thank you for the reply.
I have a table for Products, Manufacturer, Common Problems and Remedy (table 4).
I have a seperate input form for each table, except remedy, table 4.

These 4 tables are joined as a query and bound to the remedy form.

What I am trying to do is use the query for the remedy form input.
On this form I have combo boxes for the first 3 tables.
I make my selection from each combo box, then type in the information for the remedy and the remedy table would update.
The remedy table would have a multitude of fixes far too many for using a combo box..

Hope this makes sense.

kevsim

 

[kevsim]

giacomo,
Could you please assist me further.

kevsim

 

[giacomo]

Kevsim,

Sorry for the delay in getting back to you. I guess I'm still a little confused about how the tables relate... is it that the remedy table has foreign keys to the others? Sorry if I'm being dense.

Let's say that it is as I assume, then you really should add an autonumber primary key to the products, manufacturer, and problem tables. When you do this then your remedy table would look something like this:

tblRemedy
RemedyID, ManufacturerID, ProductID, ProblemID, Remedy

If you use this kind of layout then you would enter your remedies into a form that is bound directly to the remedy table instead of a query. You can use combo's to provide the data for the foreign keys this way and you shouldn't get any errors when doing your data-entry.

hth,
Giacomo

 

[kevsim]

giacomo,
Thanks for the info.
I have been away, I will post further after I try.

kevsim

 

[kevsim]

giacomo,
I tried and all worked OK.

When designing a database is there any protocol as to the number of ID links you have in a table?

Once more, thank you.

kevsim

 

[giacomo]

When designing a database is there any protocol as to the number of ID links you have in a table?

no, not at all. Your tables should have a primary key that uniquely identifies a record and foreign keys (what you're calling an ID link) that enable the tables to be related.

hth,
Giacomo

 

[kevsim]

giacomo,
Once more , thank you.

kevsim

 

[giacomo]

no problem... glad I could help!

Giacomo