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:
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.Code:=[Raw Material 1].[column](0)
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