NEED HELP:Main form that pulls in information for a subform

Question

Good Afternoon! I hope someone out there can help me with a database issue. It's been a few years since I have built a database, and I know what I am wanting to do is possible, I just can't wrap my brain around how to do it. I am trying to build a database that when I enter in information in a form, it will give me the vendor name as the main form and a subform that has the past years and price information. However, I can't find a way to link two databases together without it telling me that there is duplicate information. For example, I have a vendor who I've purchased from for the last 10 years, I want to be able to look at the form and see that vendor, and all of the years and their costs listed. Does anyone know what I'm doing wrong. I've tried to link ISSN#'s but not all ISSN numbers are strickly number, some have letters, therefore I have to use a short text for that field and not a number, so that doesn't work. Then I tried auto numbers and nothing would match up. I know I'm missing something, but I don't know what. Any help would be wonderful!!

Answer 1

This sounds quite "typical".

Ok, so first up. We assume you build a "main" form. That form will be based on your main table.

Get it working? You can in general just click on that table, and then in the ribbon in "create", just click on form.

You of course want to keep the form "smaller" and maybe even remove some fields, since the idea is to THEN drop in a sub form.

So, build the main form. It will be sourced on your main table.

next up, building the sub form.

You note you have another table (but, you seen to hint another database????).

We assume that you have this 2nd table in the same database. (it does not have to be, but let’s assume so).

So, close the first form.

Now select that 2nd table, and this time under create choose multiple items. I suppose you could also consider a datasheet, but multiple items does have better formatting.

So, now build and create this form (it will eventually become a sub form), but get it all nice laid out. This of course is a continues form.

Once you get that form laid out, and showing multiple rows?

Save and close it.

Now open your main form in design mode, and drag + drop in the new sub form from the nav pane into your form (likely down near the bottom part).

At this point you have the main form, and sub form.

So, we deal with this:

>>I've tried to link ISSN#'s but not all ISSN numbers are strickly number, some have letters,

Well, if some have text, then you need the column in both tables to be of type text. I don't see this as much as a problem. So, just make sure the column types between the two tables are of the same type.

And NOTE careful:

The two forms you built (main form, and the sub form) were created separate and they are EACH based on their respective base tables. No need for ANY sql or queries here.  (none, zero, nada!).

To link the main form to "filter" or only display the record for this one top main record?

In design mode, click on the sub form (top part - you do NOT want to enter into sub form).

You want to set the link master and child settings.

This setting:

It is the link master (main form) and the link child (sub form) setting that you can set to "link" up on the common column between the two tables.

You now get a chance to choose the columns between the two forms (tables) that will link up the data for you.

If the column types don't match, then you can't link the two tables. But you stated that you DO require a common column of SSN or some such.

You want to ensure the columns between the two tables are of the same data type. (but then again, how could the data match if they were not).

So, the most important tip?

You do not base nor use any sql queries for the forms.

Both forms are to be directly based on their respective single table.

Regards,

Albert D. Kallal (Access MVP 2003-2017)

Edmonton, Alberta Canada

Answer 2

First, you should have 2 tables with fields as follows to build a stable database using the relational model:

TblVendor:

VendorID Data Type: AutoNumber indexed: Yes (No Duplicates) Primary Key

VendorName Data Type Short Text

Other vendor fields as needed (e-mail, contact person, address, phone etc)

TblTransactions:

TransID Data Type: AutoNumber indexed: Yes (No Duplicates) Primary Key

VendorID Data Type: Number indexed: Yes (Duplicates OK) used as a Foreign Key to hold TblVendor VendorID numbers for each transaction.

TransDate

TransCost

other transaction fields as needed

Once you have a true relational model for data, you can then set the sub-forms Link Master Fields and Link Child Fields property to VendorID. This will be the common field that both the form and sub-form use in their respective Record Source properties to link common data. Make sure VendorID are fields in both the main form and sub-form (they can be invisible if you wish). The sub-forms VendorID default property should be set to =Forms![Main Form Name]![VendorID] so each time a new transaction record is added, the subform Record Source (a query using TblTransactions) VendorID field is automatically completed.

But first, you must create a relational model so each table has a relationship with at least one other table.

Answer 3

Thank you for your reply. I actually was able to create what I wanted. I knew that I was missing something simple and low and behold I was. Thank you again for your help!