Solved Main form with sub form containing data from reference tables (1 Viewer)

OverSight

New member
Local time
Today, 10:02
Joined
Jul 29, 2021
Messages
18
I am brand new at using MS Access so I have pretty much zero knowledge of what I'm doing. I have been reading online best I can but just not following or haven't found something that helps me. Apologies if I'm posting in the wrong thread. I will try my best to explain what I'm looking to do.

Situation:

I'm trying to create a database that will be used as a vendor lookup database. In the database users will view/add/modify Vendors. For each Vendor record I am using a form to display the vendor information details.

Along with the Vendor information on this form, I would like to have a section or separate tab that shows a sub form containing a list of products the vendor carries.
In the sub form, I want the user to select from a predefined list of products that I've populated in another table that contains (the commodity code, short description and long description). Once they select this, I want them to be able to add the actual item they have purchased which would be ad hoc.

For example:

User selects, searches from the list of pre populated commodities, (Shown in Blue Font here), pulled from a pre populated table, then will add the actual item they bought under the Goods Supplied Field (Shown in Red Font):

Commodity Code: Commodity Short Description: Commodity Long Description: Goods Supplied:
7510 Office Supplies Office Supplies Pen

I would like this to be unique to each vendor record. I think I will need to have some relationships and I'm guessing some additional tables to do this. I'm willing to attach a sample of what I have done so far. I have been piecing things together using samples from other databases I have found along the way. I'm sure I'm doing things completely wrong so I'm totally willing to accept any advice and critiques as I hope to learn from my mistakes. Thank you in advance.
 

Attachments

  • Test Vendor database v1-1.accdb
    2.8 MB · Views: 445
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:02
Joined
May 21, 2018
Messages
8,463
Do not worry about forms and reports at this time, worry only about properly structured tables. If we can see your tables we can tell you what to add, delete, or modify. It is like building a house. If your frame is plum, square, and in code everything is easy. If it is not, everything is hard, time consuming, and prone to failure.
If you can post just the db with the tables that is the best. Or you can take some screen shots of the table designs and/or the relationship window. Then we will ensure you create the proper relationships. Then we need in plain words (not database terms) what information needs to be tracked and what activities are needed.
 

OverSight

New member
Local time
Today, 10:02
Joined
Jul 29, 2021
Messages
18
Do not worry about forms and reports at this time, worry only about properly structured tables. If we can see your tables we can tell you what to add, delete, or modify. It is like building a house. If your frame is plum, square, and in code everything is easy. If it is not, everything is hard, time consuming, and prone to failure.
If you can post just the db with the tables that is the best. Or you can take some screen shots of the table designs and/or the relationship window. Then we will ensure you create the proper relationships. Then we need in plain words (not database terms) what information needs to be tracked and what activities are needed.
Thank you. I've just uploaded the whole db that I have so far.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:02
Joined
May 21, 2018
Messages
8,463
A couple of things. Naming conventions can make your database and code much easier to understand, and Access lets you hang yourself by not enforcing basic rules.
1. Do not use special characters (except underscore _) or spaces in any names to include object and field names. This will cause you a lot of pain requiring special care.
The contact table is bad, the GSIN table is good. I am guessing two different Sources.
so LastName or Last_Name but not Last Name
State_Province not State/Province
ContactEmail not Contact E-Mail
2. Most people prefix forms, reports, tables, and queries. Often three letter abbrev, but as long as consistent
so tblAttachments or T_Attachments to TableAttachments
qryContacts or Q_Contacts
3. Primary keys and Foreingn keys
I will tell you what I do, and it seems to be very clear when I show others
my PK is
CustomerID_PK (Never Ever just ID)
GSIN_ID_PK

My Foreign Key would then be
CustomerID_FK
GSIN_ID_FK
some people name the PK and FK the same and I find that very confusing
4. Tables should end in S like you have. I also always name the PK the same as the table.
You have Attachments which I would call tblAttachments. My PK would be something like AttachmentID_PK. You call it File which makes it a little harder to know immediately what table it comes from.
5. Remove spaces in form names and consider using a prefix like frmContacDetails. I use frm and subFrm to identify if it is used as a subform.
6. In contacts table you have GSIN. I do not think a contact relates to a single GSIN. Not sure what the represents.
7. The contact table seems to be a mix of Company information and POC information. This may need to get broken up if a company can have multiple POCs. Then in the company table you have information unique to the company (web page, address, company phone). In the POC table you could have information about the people (home phone, first name, last name) and a foreign key to the company. So you then can have more than 1 person pointing to a single company. I assume the Vendor is the company and not the listed person
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:02
Joined
May 21, 2018
Messages
8,463
Along with the Vendor information on this form, I would like to have a section or separate tab that shows a sub form containing a list of products the vendor carries.
The GSIN is a category not a specific end item? So when you say you will have a list of products that the vendor carries you mean a commondity area. If a vendor sells 5.56 mm, 7.62 mm, and 9mm ammo then you are only maintaining the fact that they have GSIN 1305 Ammunition thru 30mm. If they also carry other Small Arms and Parts you are tracking GSIN 1005.

If that is what you are doing then you need a junction table
tblVendors_Commodities
Code:
vendor_Commodity_ID_PK
vendorID_FK
GSIN_ID_FK

Assume vendor 1 carries 1305 and 1005 and vendor 2 carries 1230, 1240, 1240 then your table has this data
Code:
1 1 1305
2 1 1005
3 2 1230
4 2 1240
5 2 1250

I want the user to select from a predefined list of products that I've populated in another table that contains (the commodity code, short description and long description). Once they select this, I want them to be able to add the actual item they have purchased which would be ad hoc.
Now you will likely have some kind of Purchase order Table with details about a PO. Then you need a child table for PurchaseOrderDetails to track the actual items purchased


tblPurchaseOrderDetails
Code:
PurchaseOrderDetails_PK
PurchaseOrder_FK
VendorCommodity_ID_FK
ItemPurchasedDescription
ItemPurchasedQuantity
Other item detail fields.

In the above table if you select VendorCommodity_ID_FK of 2 that says that you purchased small arms parts from vendor 1.

I am guessing here, so is that what you want to do?
 

OverSight

New member
Local time
Today, 10:02
Joined
Jul 29, 2021
Messages
18
A couple of things. Naming conventions can make your database and code much easier to understand, and Access lets you hang yourself by not enforcing basic rules.
1. Do not use special characters (except underscore _) or spaces in any names to include object and field names. This will cause you a lot of pain requiring special care.
The contact table is bad, the GSIN table is good. I am guessing two different Sources.
so LastName or Last_Name but not Last Name
State_Province not State/Province
ContactEmail not Contact E-Mail
2. Most people prefix forms, reports, tables, and queries. Often three letter abbrev, but as long as consistent
so tblAttachments or T_Attachments to TableAttachments
qryContacts or Q_Contacts
3. Primary keys and Foreingn keys
I will tell you what I do, and it seems to be very clear when I show others
my PK is
CustomerID_PK (Never Ever just ID)
GSIN_ID_PK

My Foreign Key would then be
CustomerID_FK
GSIN_ID_FK
some people name the PK and FK the same and I find that very confusing
4. Tables should end in S like you have. I also always name the PK the same as the table.
You have Attachments which I would call tblAttachments. My PK would be something like AttachmentID_PK. You call it File which makes it a little harder to know immediately what table it comes from.
5. Remove spaces in form names and consider using a prefix like frmContacDetails. I use frm and subFrm to identify if it is used as a subform.
6. In contacts table you have GSIN. I do not think a contact relates to a single GSIN. Not sure what the represents.
7. The contact table seems to be a mix of Company information and POC information. This may need to get broken up if a company can have multiple POCs. Then in the company table you have information unique to the company (web page, address, company phone). In the POC table you could have information about the people (home phone, first name, last name) and a foreign key to the company. So you then can have more than 1 person pointing to a single company. I assume the Vendor is the company and not the listed person
Hi there,

Thank you for your advice and input! Sorry I have been slow with a reply. Based on the information you have given me, I have decided to start from scratch and have tried to incorporate all the advice you've given me on making sure I have proper naming conventions and table structures. I've also change the way I am doing my Private and Foreign Keys to match the way you do them, this makes a lot of sense.

I also like your idea of separating the Vendor detail information and the POC information, as I didn't think about multiple POCs or easily adding or removing POCs if needed. I am not sure how to create the relationship between those tables yet or how to properly display them on a form.

GSINS (Goods/Services Identification Numbering System):

As far as the GSINs table goes, this is purely for internal identification and categorization purposes . GSINs are already determined for us and this table just lists the current ones. The Goal here is to allow the end user to identify any goods/services they have purchased from a vendor and be able to categorize it with the correct the GSIN (by typing or perhaps a dropdown list or search).

So as an example Let use GSIN 5180 (Hand Tools - Sets, Kits and Outfits):

Say an employee (end user) here buys a Philips screwdriver from Home Depot (Vendor), I'd like them to be able to add this to the vendor details in the "GINs/Goods/Services" tab. They would select the GSIN information and then be able to manually type in "Screwdriver" in a Field. They should be able to add as many items as they need, each item gets its own line or record. The "Goods/Services Supplied" field would be unique to that specific Vendor record, but the GSIN info is just referenced from the list of available GSINs....(I'm totally not explaining this well I know).
Also there would be many vendors that might supply multiple types of commodities

GSINCode: GSINLongDescription: Goods/Services Supplied:
5180 Hand Tools - Sets, Kits, and Outfits Screwdriver

I want to make this searchable so that if someone new here in our office wants to buy a screwdriver but doesn't really know what vendors supplied screwdrivers they could search and find the vendor(s). I know there is most likely many flaws with this (typos, input formatting) especially if multiple people are using the database, but I'm willing to accept that.

We will not be keeping track of purchase orders or anything like that, this will only be for reference.

Also I would like to be able to save multiple files to the vendor records. I don't want to save the actual file in the database to avoid bloating and other issues, but just the reference to where the files are actually stored. So say we buy something with a Safety Data Sheet or a product manual, I'd like to save that to the vendor record so the end users can access the file from there. I have found a "Demo" of this and have added it to the database, it is functional but I would like to have this as a sub form perhaps and unique to each vendor record. I am thinking this might be done possibly with some sort of filtering so that only the attachments for that specific vendor show? Just brain storming.

Would you be willing to take a look at what I have done to see if I'm on the correct track now? I have not created any relationships as of yet just really focused on tables and naming conventions.
 

Attachments

  • Vendor Database v2.accdb
    2.3 MB · Views: 418
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:02
Joined
May 21, 2018
Messages
8,463
I will take a look later.
Also I would like to be able to save multiple files to the vendor records. I don't want to save the actual file in the database to avoid bloating and other issues, but just the reference to where the files are actually stored. So say we buy something with a Safety Data Sheet or a product manual, I'd like to save that to the vendor record so the end users can access the file from there.
One thing IMO you do not want to ever save a full path to the document. Only save the file name. Normally I have a table called tblSettings. In this table I save one record with a bunch of fields for application settings. Here I would have a field called
DocumentPath
That is where I store the path to my documents, and dump all documents in that path. At a later date if the path changes then just update the table. Then the full path is a concatenation of the defaultpath and the file name.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:02
Joined
May 21, 2018
Messages
8,463
Here is the relation
relation.jpg

You relate PK to FK. You can see the 1 and the infinity. Because 1 Vendor can have many POCs. The 1 is on the PK side the many is on the FK side.

To add a child table to a form you do this with a subform. The subform then both filters and adds the FK. When you create a new record in the subform it automatically fills in the FK with the proper parent PK value. When you change the record in the main form it filters to the correct children for the parent record. This is an ugly subform, but it is fast because you can just drag it on to the main form. You can do the subform as a tabular form or single form view as well.

subform.jpg


When you create a relation it asks if you want relational integrity. In this case likely yes. You do not want a POC that is not associated to a vendor. If you set this to true then you basically cannot create an unassigned POC. It also asks if you want to allow cascade deletes. This means if you delete the Vendor it will automatically delete the vendor POCs. You probably do not want those POCs anymore if you no longer are tracking that vendor. Not all data should have referential integrity. Assume you have a list of Employees and you assign them a computer. When that employee leaves you want to have an unassigned computer, you would not want to remove it from the DB.
 

Attachments

  • Vendor Database v2.1.accdb
    2.7 MB · Views: 433
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:02
Joined
May 21, 2018
Messages
8,463
As for goods and services. This could happen a couple of ways. This will depend on how common the goods are across multiple vendors. Assume vendors have unique goods (only one vendor carries screw drivers
Then your table would be

TblVendorGoodsServices
-VendorGS_ID_PK
-VendorID_FK
-GSIN_ID_FK
-Fields for that specific item (GSName, GSDescription, Cost, Manufacturer, any thing unique to that type of item)

Your form is easy. You select a GSIN_ID_PK from a combo (you likely want cascading combos to narrow your search), and fill in your item information. You can pull in the other linked GSIN fields for display purposes in your query, but only store the GSIN_ID_FK.

On the other hand assume all vendors supply a lot of the same items. You would not want to enter that item information multiple times.
In that case your table structure would be different.
TblGoodsServices
GS_ItemID_PK
GS_Name
GS_Description
- other fields
GSIN_ID_FK

Then in you TblVendorGoods
-VendorGS_ID_PK
-VendorID_FK
-GS_ItemID_FK

In the latter case Multiple vendors can reference the same GS Item. In this case you pick a GS from a list to assign to a vendor, and that item will already be associated to a GSIN.

There are pros and cons of both approaches, and really will depend on what your data is like. However in the latter more work is required. It requires a second table and you have to pick from that table or add to that table in order to add to your vendor table. This is more database art than science since there is no right or wrong answer.
 

OverSight

New member
Local time
Today, 10:02
Joined
Jul 29, 2021
Messages
18
Here is the relation
View attachment 93354
You relate PK to FK. You can see the 1 and the infinity. Because 1 Vendor can have many POCs. The 1 is on the PK side the many is on the FK side.

To add a child table to a form you do this with a subform. The subform then both filters and adds the FK. When you create a new record in the subform it automatically fills in the FK with the proper parent PK value. When you change the record in the main form it filters to the correct children for the parent record. This is an ugly subform, but it is fast because you can just drag it on to the main form. You can do the subform as a tabular form or single form view as well.

View attachment 93355

When you create a relation it asks if you want relational integrity. In this case likely yes. You do not want a POC that is not associated to a vendor. If you set this to true then you basically cannot create an unassigned POC. It also asks if you want to allow cascade deletes. This means if you delete the Vendor it will automatically delete the vendor POCs. You probably do not want those POCs anymore if you no longer are tracking that vendor. Not all data should have referential integrity. Assume you have a list of Employees and you assign them a computer. When that employee leaves you want to have an unassigned computer, you would not want to remove it from the DB.
Thank you very much for taking the time to explaining this and assisting with the subform! I am learning so much! This makes sense now.
 

OverSight

New member
Local time
Today, 10:02
Joined
Jul 29, 2021
Messages
18
I will take a look at this later tonight to see if I can figure it out. Thank you again for helping me with this.
 

OverSight

New member
Local time
Today, 10:02
Joined
Jul 29, 2021
Messages
18
Hi there, so I took a look and attempted to create a quick form to see if I could figure out the basics. I decided to go with the first method you suggested, one vendor carries specific goods/services. For me I'm not too concerned about the end user manually typing in data multiple times. Here is what I've done:

1. Created a table structure.
tblVendorGoodsServices
- VendorGS_ID_PK
- Vendor_ID_FK
- GSIN_ID_FK
- GS_Supplied


2. Created Relationships.
- tblVendors (Vendors_ID_PK) to tblVendorGoodsServices (Vendor_ID_FK) as a one to many.
- tblGSINs (GSIN_ID_PK) to tblVendorGoodsServices (GSIN_ID_FK) as a one to many.

3. Created a form called frmGSINSupply_Subform with cascading combo Boxes and one input field.
- cboGSINCode -cboGSINDesc -GS_Supplied
from tblGSINs from tblGSINs from tblVendorGoodsServices

When I input data into the "GS_Supplied" field and save or close I get the following error:
You cannot add or change a record because a related record is required in table 'tblGSINs'

I'm not sure if I should be creating a record in tblGSINs as this is really just a reference table. So perhaps I've created the wrong relationships? Perhaps I am forced to have another table to pass things trough? I've attached version 2.2 for reference.
 

Attachments

  • Vendor Database v2.2.accdb
    1.6 MB · Views: 265

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:02
Joined
May 21, 2018
Messages
8,463
One thing that could have made this a little clearer is that a GSIN is a very good natural key. It is unique, non changing, and simple. You could have used those as the PK. I rarely use natural keys (instead use auto numbers) but this is a case where it makes sense. It is not bad but may be confusing to some people. You have a unique ID to represent another unique ID. But not a problem, just something to think about. 1005 represents small arms. Now you added a key 1 to represent 1005 Small Arms.

If you select a GSIN PK you get all the data associated with that GSIN. You do not have to repeat that information in a query or form. On your form you were repeating the GSIN number and description instead of simply pulling it from a query. You just have to save the PK and then use a query to show the related info. For example if I associate a vendor to a GSIN I can create the below query, if I simply save the keys in a table.
tblVendorGoodsServices tblVendorGoodsServices



Vendor_ID_FKGSIN_ID_FKGS_Supplied
1​
1​
M-16 A2
1​
40​
5.56 NATO Ball
1​
31​
ACOG
1​
225​
Coca Cola
1​
603​
Air Jordans
With the two keys stored you can pull in anything from those tables. Example

Query1 Query1


VendorNumberVendorNameGSINCodeGSINShortDescriptionGSINLongDescriptionGS_Supplied
123456789Travis Cleary IT Consulting
1005​
Small Arms & PartsGuns, through 30 mm incl Accessories & PartsM-16 A2
123456789Travis Cleary IT Consulting
1305​
Ammunition thru 30mmAmmunition, through 30mm5.56 NATO Ball
123456789Travis Cleary IT Consulting
1240​
Optical SightingOptical Sighting and Ranging EquipmentACOG
123456789Travis Cleary IT Consulting
3550​
Vending/Coin OperateVending and Coin Operated MachinesCoca Cola
123456789Travis Cleary IT Consulting
8430​
Footwear, Men'sFootwear, Men'sAir Jordans

So, in the Sub form you need to store the vendor Id which is done automatically by the sub form control. You store the GSIN PK by selecting it via a combobox. This is IMO the most important trick in access forms. You build a combo box that shows the information you want but store a key. Easier to see then explain. You will use this technique all the time in Access forms.

Vendor.jpg

The trick above it that the combo is showing a description, but storing a GSIN_ID_PK. Look at these properties
Bound Column: 1
Number Columns: 2
Column Widths: 0; 3
and look at the combo row source.

Bottom line for each entry the table stores a Vendor ID, GSIN ID, and a typed in Good/Service item
 

Attachments

  • Vendor Database v2.2.accdb
    1.7 MB · Views: 418
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:02
Joined
May 21, 2018
Messages
8,463
Your GSIN combobox has 843 items to choose from. A list this large may be complicated to use with simple combo, you may want another feature to aid in finding what you need. I provided a demo using a popup Find as you type form. You pop open the form then type to search. Then you hit ok and it fills in the selected value. There are other techniques. I have a class module to do all the work. I just call the class. In this example I know I am something with "Fire" in the name.

FAYT.jpg
 

Attachments

  • Vendor Database v2.3 MajP.accdb
    2.3 MB · Views: 415

OverSight

New member
Local time
Today, 10:02
Joined
Jul 29, 2021
Messages
18
Your GSIN combobox has 843 items to choose from. A list this large may be complicated to use with simple combo, you may want another feature to aid in finding what you need. I provided a demo using a popup Find as you type form. You pop open the form then type to search. Then you hit ok and it fills in the selected value. There are other techniques. I have a class module to do all the work. I just call the class. In this example I know I am something with "Fire" in the name.

View attachment 93400
Wow! Yes, this works very well! This demo will be way more useful to the end users as there are so many that they may not know the actual number or description but this will allow them to search for most any kind of information!! Thank you very much for this! I have a separate question, but let me know if I should post it as a different topic....Is there a way to make it so the end users can't see the back end with all the tables?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:02
Joined
May 21, 2018
Messages
8,463
If you captured that hierarchy in your table then you could have cascading comboboxes to help find something as well.

Combo 1 would be category have C,G,R,S
If you picked G the Code Group combo would have values like (10 Weapons, 11 Nuclear, ....)
If you picked 10 Weapons the next GSIN combo would show things in the 1000 series.
 

Users who are viewing this thread

Top Bottom