A Tabbed Access Form with Subforms and Sub-Sub-Forms

what we are going to do today creating forms with subforms um I cannot go into all the details if you don't feel very comfortable with access you may still run into trouble so you you probably need more than what I'm discussing today what I I want you to know is that access is not an easy program so I made a CD ROM with more than 1500 slides that goes into all the details you need to know in order to feel comfortable with Genesis PC far thousands of people have already benefited from this CD and they have learned among other things how to make forms with subforms but for sub forms you use we need queries so you have to know how to how you can make queries and how you connect them this is my simple database that I am working with today and all these tables have a one to many relationship one to many one to many on the one side is always a primary key in my case that is an auto number so this is an auto number that is an auto number this is an auto number that is an auto number and this one is an auto number the many sites are always the foreign keys so this is a foreign key that is a foreign key this is a foreign key and these two are foreign keys and I'm showing you what we are going to do first I put all those sub forms in one form so this is the main form this is one sub form for the Department of that specific employee this is a sub form with all the time sheets for that employee and this is a subform with all orders that that employee placed let's say in the cafeteria and this sub form has an other sub form inside that says what was in that particular order three items in this case um then I decided not to do it this way but use a tapped form the tabed form has in this case four tabs so it tells me here where the who the employee is and I can go to the next one and then it shows me in which department that person is then I show the time sheets for that specific employee barara Brown in this case until I go to another one and the same story about orders this is the order sub form and it has an orders detail sub sh form so how do you make that things the secret is usually that you make queries for the sub forms and then you make those sub forms so you need to know what to put in the queries this is the orders sub form query you take from it the order ID from the orders table the item ID from the orders detail table the employee ID from the orders table then I made an order Sub sub form query it has a different setup it has the employee ID from the orders table the order ID from the details table and the item ID from the details table item the quantity the price and then I did a a subtotal close the query and I have another query that is the subform query for the time sheets employee ID from the time sheets table on the many side week date work hours vacation hours that all speaks for itself a calculation here okay then which is a very important step you create those sub forms first the order subform itself is not a data sheet if you make that a data sheet you cannot put a Sub sub form in there so this one was a a regular singular form one and now we are going to create a tabed form something like this I'm going to do that from scratch so you will go through all the steps one by one create in this case I want a blank form in order to create that tapped form it's a blank form you are going to put on that blank form design a tapped tool a tapped control select it click in the range where you want it then I'm going to say to this total form with a tab control on it what this is going to be the main one on page one so we are going to add the table employees to it go to the design screen go to the prop properties of that form you can find that here at the spot where the two rulers meet right click there the properties and put a record Source behind all of this whether you are in data or under all the record sour is always the first one drop down employees table so from now on all the fields from the employees table are behind this form we are going to put them on here so I go to design again and I would like to add existing Fields employee ID shift pay type and I move them all in here so here when I go I have 16 employees when I go to the next employee I get another person and another person another person now I would like on page two I would like to find the department of that employee the department is in the employees Department sub for form so let's put that form in here so we go to the design view again we go to page two design and we add a sub form or a sub report click on that button click in this on the page where you want it and drag it and it's going to ask do you want to use an existing table or don't go for that always go for an existing form if you can so that is going to be the department sub form next and in this screen you always Define your connection what is the connection between the main form that's the employees and this one that is the department ID Department ID of that employee and the department ID of the Departments table next you don't care about this thing I'm going to delete that anyway finish so I I delete that label on top there so now we have the Departments for that employee in accounting go to the design screen again then we would like all the time sheets for that person so I need a new page here right click on page two insert a page again we want on there the employees time sheet sub form correct so so we are going to create a sub form again design sub form click where you want it and draw drag it this time use an existing form the employees time sheet sub form next Define your own this time it's the employee ID you want a time sheet for that specific employee on the main form and you want on the time sheets that I hooked up to that same employee ID next don't worry about that label we are going to delete it anyway so now I have the time sheets for a specific employee I I want you to know that I can also add time sheets to people so if I click in here you could make that a default value I put today's date in there that person work 40 hours no vacation hours so at the moment I um I click somewhere else then the total pay goes up to 4,000 and when I go to two I go back to one there is that 4,000 again so that record was beautifully saved for the right employee I want one more tab insert a page and there we are going to insert the ORD sub form that order sub form has already the order Sub subform in there how did we add that sub subform to that one we use the same trick as I showed you with all these tabs you insert a sub form but those forms have different queries behind them one has the sub form query and one has the sub form query so do the same thing again use an existing form the order sub form for that has already the order sub form in there next don't forget to link them together employee ID employee ID next finish and delete that thing see what we got here so these are the orders for that employee now I would like to see that this is the employee Etc so I want to change those captions and then I would like to say say here who is the employee we are talking about correct so we go to design again click on page one click on that label and get the properties page one we are going to change into employees or employee page two we are going to change into Department then we are going to add here who is that employee so we are going to insert a text box click there and click again it's unbounded so in the control Source we are going to say equals the field first name that's how I call that field space m per space double quote space double quotes I am inserting a literal space space m perent space and then the field last name okay so we have in there this formula okay so now when I check in the regular view you will see this is CH I should blow that up but I don't so if I go to another employee then that name will change so if I want a new order notice that I have three scroll bars now this one regulates which employee that one regulates which or order and that one regulates which order detail so this person Barbara Bush has two orders so far now I would like here to the the right employee ID to pop up so I'm going to set a default there it automatically takes a new order number but that part has not been implemented yet so we go to that employee ID and we say we would like under under data a default value and the default value should be from database based on the form which forms loaded form and form one why form one because I haven't saved anything yet okay so let's keep it called form one what do you want from form one you want the employee ID from form one you want that to be the default value so at the moment I click okay that is the default value so now when I go here and I go again to who did I have I think I had Barbara Bush her orders she has only one order so far I go to the next order you see there is her employee number number three I type a date in there control semicolon gives you today's date I give her an order let's say she she would like a pizza everything should automatically update when I click somewhere else it gives me a total order of 1050 2 * 525 as I told you before if this went too fast or too many questions that you want to be solved your answer can be found at Genesis PC

Channel: Dr. Gerard Verschuuren

Views: 163,322

Keywords: Access Forms, Tabbed Forms in Access, Sub-Sub-Forms

Id: 8BZoR7JaKsY

Length: 14min 14sec (854 seconds)

Published: Tue May 13 2014