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

 

[brharrii]

Thanks Pat,

I'll try that. So if I change all of the PK and FK to match, does that mean that in my main table, if I look up a customer, I wont be able to see their name, just their ID?

Would it cause any problems if I were to set the primary key as the descriptions instead of the IDs?

Another Issue I'm raelizing as I'm look at this is that there are 4 "rawmaterial" fields in the tblProductInfo. "rawmaterial1", "rawmaterial2", "rawmaterial3", "rawmatierial4". All 4 of these need to be able to contain different values but all draw from the same tblRawMaterial. How would I coordinate their PK / FK?

Thanks Pat!

 

[brharrii]

Pat thank you for your response.

So in the tblRawMaterial, I would need to have 4 unique ID numbers, one for each raw material? or are you saying to create 4 completely different tables?

*as a side note, the RawMaterial1 will be present on 99% of the specifications. Rawmaterial2-4 may or may not always be used, depending on the product in question.

This also makes me question 'tblpackaging'. We use multiple types of packaging which are all stored in the same table, (master cases, liners, dividers, films, retail cases, inserts, etc.). A product may use one of each packagetype or it may only use certain packagetypes and not use others. (you would never see multiples of the same package type, no product would use 2 master cases or 2 dividers. The only exception being liners, which are divided into 2 groups, those over 50lbs and those under 50lbs. A product may use 2 liners, but never 2 that are both over 50 lbs or 2 that are both under 50 lbs).

the table looks like this:


tblPackaging
- PackageID - PK
- PackageDesc - Text description
- PackageType - Master Case, Retail Case, Liner, etc

Under the main table all of the following fields need to relate to tblPackaging (each one of them also has corresponding package types in tblPackaging.[packagetype]):

tblProductInfo
- FilmTopID
- FilmBottomID
- InsertID
- Liner1ID
- Liner2ID
- RetailCaseID
- DividerID
- ComponentID
- MasterCaseID
- MasterCaseBottomID

Currently each one of these fields are FKs that use a sql query to refer to tblPackaging and identify the correct [PackageType] to know which items are to be displayed in the combobox. Does this seem to be the best way to go about it? Or do you think I would benefit by doing something similar with tblPackaging as what you've proposed with tblRawMaterial (creating multiple Tables).

Thanks again for all your help!