Form Design Help

[carrod65]

Hello All,



Some quick background on my database:

3 tables at this point

Table 1 - Products - Houses information such as product number (Primary key), product name, purity (we sell chemicals) and some other information specific to that chemical

Table 2 - Suppliers - Houses information of all our different suppliers such as name, address, contact info, etc

Table 3 - Product + Suppliers - This table holds information that is specific to both the product and that supplier. It uses the primary key from table 2 (Product number) and the primary key from table 2 (Supplier number - auto generated) to the table. Then this table holds information that only applies to this product produced from this supplier (Manufacture methods, whether this is our primary supplier for this product, etc)

In the product + suppliers table, the user must select a supplier from a supplier added into the suppliers table, as well as a product number from the products table (I think this is the case, but I am getting problems when I try and make a form)

Here is what I want to do:

I need a form that holds the information from the product table (Chemical name, purity, product number, etc) and then I also want on that form a place where I can select from the different suppliers we have purchased that product from so I can bring up the information about the product + supplier combo.

The reason being, is that we can purchase our product from many different suppliers, and can change back and fourth based on supply.

The way I have it setup I am hoping we will retain all information about old suppliers, but still be able to display the information in a user friendly environment.

I also have a navigation form and a form to add and remove suppliers from that database, but that was simple and I don't need any help.

The one thing I am struggling with is (and I know I am using the wrong terminology here) to determine what a form dictates as the master line - IE which section of the form dictates what the rest of the form will display. I want it to be the primary key (product number) so that when I change that field, the rest changes, but so far I have not been able to achieve this as the rest of the information lags behind.

I have tried to relate the two tables by using a subform (and linked what I thought to be the right master and child fields), and putting it all on the same form, but to no avail.

If anyone can throw me any help, that would be greatly appreciated. If my table relationships are wrong, or if I am going about the forms incorrectly, I am all ears. Any point in the right direction will be a blessing. Thanks everyone.

[June7]

How can you retain info about old suppliers if you allow them to be deleted?

Do you want to provide your project for analysis?

[carrod65]

Hi June,

I really appreciate your reply. I was maybe not clear, I do not want old suppliers to be deleted, i would like to retain that information for future recall.

The file size is still under 3.0 mb so I just took out any confidential information and replaced the suppliers with made up names.

I really appreciate any feedback!

http://www.filefactory.com/file/1tof...ase_r2_0_accdb

[June7]

You haven't set primary key in Country table. I would just use CountryCode as primary key and then don't need CountryID field. Especially since you aren't using CountryID in combobox Lookup anyway.

Could use ProductNumber as primary key.

The form/subform is working fine for me. What do you mean by 'change that field, the rest changes'. Why would you change ProductID for an existing record?

Advise against spaces in names. Better would be: ProductNumber, US_Natural

[carrod65]

Hi June, I appreciate your advice, I will change the field names to not include spaces.

What I was trying to say was that when I change the product number on the form, I wasn't able to get the subform to change along with it to the corresponding subform.

Product ID could be my Primary Key, but as our product numbers are W###### It wasn't letting me plug it in as a number, so I had to use a text column. As the supplier ID's are a auto number field, the text and auto number field weren't compatible for setting up a relationship.

[June7]

Don't understand. The product number pk/fk datatypes has nothing to do with the supplier ID pk/fk datatypes.

Still don't know why you would be changing product number of an existing record.

[carrod65]

Here is what the relationships look like now.

You are right, We would never want to change the product number of an existing record.

What I was trying to ask is how to set a combo box up so that when you pick a record of the combo box, the rest of the form's information corresponds to the record you just selected.

[June7]

Text/Combo/List boxes can be of two flavors.

1. an unbound control, commonly used to enter/select criteria to search for existing record

2. bound control that is used to enter/select value into field of table

Don't have your project with me now but as I recall the combobox is a number 2 variety. If the PK is included in the RowSource and combobox is bound to the PK column, then the PK is saved and the records of form and subform synchronize because of the Master/Child links of the subform container.

I will take another look at the project tonight.

[June7]

Okay, here's where I think the confusion is. There is a textbox for ProductNumber on main form (I saw it as a combobox before). There is a combobox for ProductNumber on subform (did not notice this before). This is backwards. The main form and subform are linked on the ID/ProductID. The main form controls display of related records. Do not try to select ProductID in the subform. It will AUTOMATICALLY be populated as controlled by the Master/Child linking. Select the product record on the main form. Enter data in subform in any field other than ProductID and the record will be initiated and the ProductID field will have the ID value from the main form. Turn the subform combobox into a textbox and lock or hide or delete it. It is not necessary to have a control bound to ProductID for the value to save. Put combobox on main form.