Creating an Access Form with Subforms

in this video we will discuss how you can create a main form in Access that holds a subform with the details of whatever is in the main form in the main form let's say we have suppliers and suppliers suppliers with product so this supplier has these products and we calculate at the bottom the total value in stock how many units do we have in stock times two units fine and sum them all so when I go to another supplier we get different products so the subform is hooked up to the main form because one supplier has many products let's see how you would make such a subform I'm deleting what we have so we really start from scratch I want you to know that the database that I'm using is very simple the relationships show you you have only two tables it's important to realize that they all have an ID as a unique identifier which is an auto number in these cases so this is the primary key and in the table products that supplier ID is the foreign key one supplier here has many suppliers there and of course the products have a product ID so I can identify them later home and people place orders but I leave that out of this database let's start with the main form we create with the form wizard that is usually the easiest way that saves you a lot of designing which table do you want to use table suppliers let's say we want everything from their table in the form sometimes you want to leave certain things out we make it a columnar one and I'm going to call this form frm main suppliers and finish it's a very simple form it has all the fields from that table and we are going to place here a subform what are the steps to do so usually there are three steps you have to make clearly that hooks those products up to a specific supplier then you transform that query into a four and then you play that place that form into the main form as a subform so step one is creating a query let's create a query create and we go to query design based on those two tables you don't really need in this case the table supply is where we just need two products I take everything from the product so I'm going to double click on the air sex what it's important that you have to supply your ID on the what they call the many side the foreign key supply so don't take this one so if you do that you can never add new products to a supplier so when we close this clearing and save it qry we make it for the subform sub product so I call it clearly sub product ok now we are going to change that query into a form don't forget that step so we do a form wizard again but this time based on the tail of the query sub product let's say I want everything in there usually you don't need a Product ID and the supplier ID they automatically kick in anyway but I'm going to leave them in to show you and let's make it a datasheet that is more compact that is a problem with a datasheet that I will show you very soon when you create subtotals and totals next what title do you like for your form I'm just going to make that sub product and this is what the datasheet looks like all we have to do now is place this into the main form so that we will only see four supplier 1 D 3 products and 4 supplier 2 dose etc and then a total for these records so how do we get that in a sub form open the main form in design view make your form higher and wider and go to the design step and insert a sub form or sup report in this case a sub form click on it click where you want it and draw it use an existing form please make sure that you have that form ready otherwise you are asking for a lot of trouble and then it says what is the relationship between the main form and the subform define your own its the supplier ID from the main form in the supplier ID from the sub for next gives that thing the name and this is what we got I'm taking that header out you don't need that here so just to show you what we have at this moment we have this it works already when I go to another record another supplier in this case I see different products later on I will test whether they also update so now we want a total here you want that total somewhere here but the problem is that data sheets cannot show those totals so then I have to make another subtotal box here that refers to this one let's go to the design screen and we are going to put in the subform a 4-footer place in the form footer a text box that is going to calculate all the subtotals and go to the properties of that box in the control source I place a a formula I'm blowing up the farm so you can see it better equals the sum function of the unit price per packet times the units in stock and summit so it does the total and setting the format to currency unfortunately as I said already you will never see this box so let's give the Box a name and refer to it later on in the main form and call it txt total very simple for now don't forget to save all these changes so we have to refer to that box now in the main form so now I click in the main form I'm out of the subform I place in there another text box and I bind that one to dead box in the subform so I go to its control source I click on the ellipsis the three dots in the form main suppliers in the subform there is a box that I would like to talk to text total it puts the address in there all I have to do is make it currency and do a lot more formatting but in this case I think we did enough already you will see that this company has in total 952 dollars in stock 39 times 18 13 times 10 and 10 times 12 and the next one is a similar story now the last thing we need to test can I update everything let's say we have also a product peach nectar on that same supplier don't change that that is automatically done let's say that cost $12 and we have 10 in stock at the moment I get out of that racket it automatically updates in the total box can I find that back later on when I go to the next supplier and I go back to the first supplier that peach nectar is still there this is how you make sub forms it's very fast course on it I want you to know that there isn't much more to access of course and you will find all of that on my CD your arm your access to the world find it at genesis pc comm this is the section that we just discussed but only we did a very small part of it this one is much more extensive if you match my tools and look at what else it discusses these are all the sections on that cd-rom it even goes into how to make macros how to make do some basic programming in excess and it does some great tricks with maintaining your databases importing and linking converting databases it is a great tool to work with access GNSS PC comm will show you this tool

Channel: Dr. Gerard Verschuuren

Views: 155,466

Rating: 4.8797383 out of 5

Keywords: Access Forms, Access Subforms, Insert a Subform

Id: 2M6TMwfc6i4

Length: 10min 29sec (629 seconds)

Published: Thu Dec 05 2013