Updating From A Common Form

kevsim

Board Regular
Joined
Apr 26, 2003
Messages
118
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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
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

Board Regular
Joined
Apr 26, 2003
Messages
118
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
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796

ADVERTISEMENT

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

Board Regular
Joined
Apr 26, 2003
Messages
118
giacomo,
Thanks for the info.
I have been away, I will post further after I try.

kevsim
 

kevsim

Board Regular
Joined
Apr 26, 2003
Messages
118

ADVERTISEMENT

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

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,325
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top