Access Invoice Template for Enterprises with Ease

Access invoice template for enterprises

welcome to another video in the access database application for our business at this point we're going to create the actual form for invoice remember in our last tutorial video we were creating the tables that were going to go behind this scene so in this in this form we're going to create the place where we put our customers names and then all the things that they order so here we are in our database program we're going to create a form but we're going to do it a little bit different than we've done the previous forms I'm going to select our invoice table to start with and choose create look for the button called form wizard form wizard is going to allow us to add fields from several different tables we're going to combine things from our invoice table and our invoice details and our products table let's start with the invoice table this form will show the invoice ID it will show the date of the sale and it will also show the customer ID now next let's do another check we're going to select the invoice details table on our form we want to show the product ID and we're also going to show the quantity that we're buying lastly let's go to the table called the products table we want to show the product name and we also want to see the retail price and all of these things come from various tables and they will all show up on one single form so this is the magic of access it lets you combine several tables into one report this next screen shows what our invoice form will look like at the heading we're going to include the invoice detail the date and the customer information in the sub form or the small detail area we're going to include all the things for each line item let's click next there's two different ways to show your sub form I prefer the data sheet view and then finally let's click the finish button we get to have a customer create a purchase item so I'm going to click brand new down here blank record this is like if somebody comes up to the cashier and says hey I'd like to buy a bunch of stuff first thing we're going to do is Select maybe the customer name so let's see Cherry Pitts is our first customer the date of sale let's pick a date today is the 25th now what is she going to buy well she's going to purchase a product so let's pick one from the list she's going to buy an iPad how many does she need well let's put in quantity let's put in three now we would expect to see that over here we would have well we have the retail price but the subtotal so this form isn't quite finished we need to add some more things to it so let's go and save this and then let's go into the design view we haven't seen the design view yet but let's take a look at what it can do we're going to move some things around so that way we can see them better so for our invoice details I'm going to click right on the little gray dot in the corner and move it over to the side here and let's stretch the width here so we get some more space and scroll down a bit more now right after retail price I want to do a subtotal so I'm going to have to move the form footer down a bit to create some space now I want to add a calculated field in here so I'm going to go up into this row of items here under form design tools choosing design and then I pick this here called the text box and I'm going to click right here so this is a blank text box it needs some information first of all let's give it a title so the label on this will be called the subtotal so we'll know how much this person's going to have to pay for this item now this is a calculated field all the other fields have a database field name in them and this one says Unbound in other words there's no information to be put into there we're going to have to program this one over here on the right side there's a section called control Source there are two ways to fill in the control Source one is you simply select a field name it's not what we want we want to go to the last item where there are three dots and click here this is how we calculate a field we're going to create an expression to create a calculated control for example we can add two Fields together and check to see if one is less than five something similar to that so I'm going to start with an equal sign and then from these expression categories in the list I'm going to select the quantity so I'm going to double click quantity and you notice it adds brackets and says quantity I'm going to press shift 8 to get the multiply sign I want to do quantity times the retail price so double click on retail price so the price times the quantity should give us the subtotal it shows us here what we're calculating last thing is let's go and check to see if it works so let's go to form View and let's scroll over the subtotal shows up here so we can see that the uh amount that we're buying 11 Xboxes are worth $240 each or 2640 let's format these numbers now so that they show currency so let's go back into the design view click on this square and let's go to where it says format the format is going to be currency let's try that again let's save it open up this form and now I can see the subtotal shows the money now the column headers are a little bit too wide in some places and too narrow and others so we can adjust that as well let's go into the layout view we can make the column comes narrower so let's see how that looks now so let's go to form View and now we can see a fairly decent layout so let's try again let's go down to the very bottom where it says records let's create a new record let's say we're going to purchase something tomorrow let's give it a customer name Jim Smith and now we're going to enter some things he wants a CPU fan he wants 10 of those the calculation is automatically updated so we'll have him buy Windows 10 just one copy that's $120 so as we add new items we find that as he purchases them the subtotal is calculated okay so now we have subtotals it would be nice to have a single total at the bottom of the page so that's what we'll do next [Music]