Form Design / Relationship Issues

[brharrii]

I use access 2010. I'm self taught in access and have been learning it for about the last 4 months.



The database I'm working on is a specification database for Products our plant creates. I have my tables created and populated as they need to be, now it is just a matter of setting up forms / reports to allow interaction and interpretation of the data.

This is a simplified version of what I'm trying I have:

tblProductInfo - Main table
- ID
- Description
- Customer
- RawMaterial
- Sauce
- MasterCase

Sauce
- ID
- Description
- Amount
- IngredientStatement (Memo Field)

tblPackaging
- ID
- Description
- Type (mastercase)
- PalletConfiguration

tblRawMaterial
- ID
- Description
- Species

tblCustomers
- ID
- Customer
- ProductUse


I need to setup a form that will allow me to create a new product, type in the ID number and description and then choose the Customer, raw material, and Mastercase from comboboxes. This part I have figured out.

The part that is getting me is that once I have chosen the Mastercase, I already know what the ID and Pallet configuration will be for that specific master case. That information is stored in tblPackaging. I'd like to select a master case and have the ID and Pallet configuration appear on the form automatically referencing the corresponding information from tblMasterCase. Likewise when I choose the sauce, the customer, and the raw material.

I had started an approach to this that involved using unbound text boxes and editing their control source with something like:

=[Raw Material 1].[column](0)

It worked fine up until I got to tblSauce where I needed to add a memo field. The contents of tblSauce.Ingredientstatement was truncated at 255 characters every time it was displayed on the form. Someone suggested I use a dLookup (which I can still look into, right now I am not very good with them yet), and then I ran into an issue where all my unbound text boxes stopped displaying their contents for some reason and instead all showed #NAME. I rebuilt them all and they worked again until the next time I signed out of the database they all went back to #NAME.

I then tried editing table relationships thinking I'd be able to relate the tables in such a way that the database would understand that when I choose a master case in tblProductInfo, that mastercase carries with it all of the specifics detailed in tblPackaging. Here I ran into problems when I attempted to enforce referential integrity, and determining appropriate join types. I tested with it for a few hours but was never able to get the form to do exactly what I wanted.

Finally I tried building a new form and just dragging the tbl fields that I wanted onto the form, but unfrotunately, without the relationships properly setup, they didn't display like I wanted them to.

So, my question is, what is the best way to go about this? Do I just need to learn how to do a dlookup and I'll have this thing figured out? or is there an easier way to do what I'm trying to do? If it is just a matter of learning the dlookup, does anyone have a good reference that explains how they work, and how I can implement them to design a multi-table form?

Thank you for your help!
Bruce

[Rod]

Hi Bruce,

First step is to verify the relationship between tblPackaging and tblProductInfo; your lists are a little ambiguous. Hopefully you have defined the database relationship as:

tblPackaging!ID (1:M) tblProductInfo!MasterCase

tblProductInfo !MasterCase should be defined as a long integer with a default value of null. Null means an unknown or unspecified relationship and the RDMS will not attempt to enforce relational integrity for nulls.

OK, when creating a new product record (or indeed amending an existing one), the RDMS will bitch if you attempt to put a non-null value in tblProductInfo!MasterCase that is not a primary key on tblPackaging. In order to alleviate this most designers arrange some kind of pick list of valid keys. The popular schemes involve combo boxes, list boxes or subform listings. You too have recognised that this would be a good solution. You wish to select a valid packaging key and have the associated packaging attributes displayed.

As always the number of ways of doing this is one more than the number of Access experts in the room at the time.

The simplest solution, provided that other factors do not prevent it is to base your form not on tblProductInfo but on a query that combines tblProductInfo with tblPackaging. When you design the form based on this query, disable and lock all the packaging fields; you don't want your users altering packaging details when they are supposed to be entering product details! Access will take care of the synchronisation and will display the appropriate packaging attributes. You may need to invoke a refresh or requery but cross that bridge if necessary when you come to it.

I am assuming that you are familiar with combo boxes, bound columns, etc. for designing the picking mechanism. If you're not or there is any other help I can offer, get back to me.

[brharrii]

Deleted message

[brharrii]

Rod,

Thank you very much for your response. I think this is the part that I am not doing correctly:

base your form not on tblProductInfo but on a query that combines tblProductInfo with tblPackaging. When you design the form based on this query, disable and lock all the packaging fields;

So would I just set the form record source to something like:

SELECT tblProductInfo.*, tblSauce.*
FROM tblSauce INNER JOIN tblProductInfo ON tblSauce.Description = tblProductInfo.Sauce;

[Rod]

Busy today and Sunday, get back to you Monday but looks as though you're going in the right direction although joining tables on values other than primary/foreign keys iis not always good practice.

[Rod]

Found a little time today, Sunday, so here goes.

I would set up your DB like this:





OK, I'm unsure of the relationship between products and customers but leave that aside for now. The ERD for these two tables looks like this.



And the query looks like this.

SELECT tblProductInfo.*, tblSauce.*
FROM tblSauce RIGHT JOIN tblProductInfo ON tblSauce.ID = tblProductInfo.SauceID;

I don't think INNER JOIN is what you wanted. I suggest you read up on the different types of join: INNER, RIGHT and LEFT.

Have a think about this and get back to me.