Access Invoice Template for Shipping Effortlessly to Streamline Your Business Processes

Access invoice template for Shipping

welcome to another tech help video brought to you by accesslearningzone.com i am your instructor richard ross in today's video i'm going to show you how to add a discount percentage rate to your order form and invoice in microsoft access today's question comes from jerry in tacoma washington one of my gold members jerry says i've been using your invoicing template for a while now thank you for making that available you're welcome how can i add a discount percent to the order so if i'm having a 25 off sale it's easy to calculate well jerry it's easy to add a dollar amount discount right if you give someone like a 10 credit you could just type in negative 10 and make that a line item but if you're going to do a discount percentage it's a little more work but it's not that hard it's a little bit of math let me show you how to do it for the rest of you who have not watched the invoicing video go watch this now it's absolutely free it's on my website you can even download a free copy of this database if you want to there's the link i'll also put a link down below in the description below the video you can click on that go watch it go get a copy of the database and here we go okay so here's the template you got the customer form each customer can have multiple orders and what i want to do is i want to specify a discount percentage for the order as a whole and then down here we'll calculate what the discount should be and then subtract that from the subtotal to get an order total so the first thing we need is a place to store that information we're going to put that in the order table so let's go to design view the order table we'll come right down here i'm going to type in discount percent we'll make that a number and then we'll make that a double remember there's really only two types of numbers you got to worry about there's long integer which is the default type that's just counting numbers right one two three four five zero and they're negatives and if you want a fractional component like a percentage you're going to use double don't use decimal don't use single just use double or long integer format if you want you can put percent in there decimal places is up to you if you only do whole discounts like 25 you know 50 then you can set that to zero or if you might have a you know 1.5 percent all depends on what you're doing i just leave it at auto it covers pretty much every situation all right save that now let's go over to the datasheet view real quick and we've got to put some values in here for the existing record so i'm just going to put zeros in for everybody right now now if you got a bunch of orders in your table already and you don't feel like doing that manually you can use an update query to update everybody to a zero percent again if you've never done an update query before i got a video for it i'll put a link to it down below in the description okay now we're going to add that field to the order form so right click design view i'm going to make some extra room in here let's stick it below the customer so i'm going to select all this stuff here and just slide this down a little bit all right let's go to the form design tab go to add existing fields there's discount percent we're going to drop it right there i'm going to use the format painter and just paint over that guy so it looks good and we could change the label let's say discount with a percent sign because it's a little long we'll just squeeze that right there slide you over a bit more and there we go make sure that's left aligned okay good all right let's see what it looks like save it let's close it and open it back up again okay good there's my discount percent now doesn't do anything yet if i type in a five all right oh that's another thing i don't like since i just brought that field in it's the last field in the tab order and when you tab past it it's going to move to the next record i personally don't like that so i'm going to go into the form design one more time go right here where the form properties are go to other and i'm going to change cycle to current record current record just means it'll loop back to the first field on the same record instead of going to the next one so now if i come back in here save changes yes now if i come in here and i type in like a seven and hit tab it just loops back up to the top that's how i that's how i like this one okay now down below here in the footer of the sub form right here remember we got to click there twice right if i click here once now i've just selected the sub form i want to click in there a second time now i'm working with the actual subform fields okay let's make that smaller and i'm going to put a label next to this now so we know what it is i'm going to copy one of these labels click on that guy and hit paste and that'll attach that label right to that that text box this will now be the subtotal all right and i can rename that subtotal too let's call it subtotal and let's not bold it unbold okay and i'm gonna copy and paste this copy paste paste copy it twice all right we've got two copies here now the first one is going to be the actual discount in dollars all right we'll name this guy discount and what's this going to be let's go over to data and it's not going to be the sum of the extended price what's it going to be anybody pause the video if you want to try and figure it out yourself all right that's how you learn best by figuring out yourself the discount is going to be what times what see if you can figure it out okay it's going to be this subtotal times this percentage rate and then we got to make it negative right so it's going to be equals subtotal times now we have to get the value off a different form there's a couple ways you can reference it but the easiest way is forms exclamation point order f exclamation point discount percent and it's right there okay now just see what we got there save it close it open it up all right looks good ah but look at that if you click on it it's deceiving because it's only showing it to you as a currency but it's rounding you get that little fraction of a penny there right the view it rounds it off you can't really see it but if you click on it i was i was hoping i get that number all right so we gotta round that value off now so come back in here design view all right come back into discount and we're gonna i'm gonna i'm gonna shift f2 to zoom in so you can see this better we're gonna round this whole thing off so round and then open parenthesis and then at the very end comma two round that off to two decimal places and we wanna make it negative right if it's a ten percent discount it's going to be negative some value so just times negative one okay see all that and if you don't know what this is that form field notation i got a whole video that explains that better that's called form name look down in the link section below for that too and i also have a video on the round function go watch that lots of lots of videos to get you to this point so if you don't understand any of this stuff go watch those videos i'll put a whole set of links down below for you okay all right let's hit okay this avoids the superman 3 problem right with the rounding of a penny or office space depending on which one you like better okay so there we go and if you click here notice it's exactly negative 3.88 okay but now we gotta figure out the total total right we gotta take this and add that to it we're adding a negative number which is the same as subtracting right so design view this will now be the order total and this guy will now be uh equals the subtotal plus the discount all right and yes we will bold these ones let's hold those format bold and today let's see i got good names for this stuff here all discount and no not text 17 we'll call this order total all right close it open it up and there you go it's a beautiful thing there's your discount now here's a little quirk with the way access works if you change some value in here let's say you make this three cupcakes now and if you move off that record watch the order total see it updates okay but if you come up here and change this to say 10 and tab it doesn't change all right this this doesn't refresh these fields down here until you hit the refresh button up top now it updated the discount see i come in here and put this back to five and then hit refresh it updated the discount now don't be scared i'm going to teach a tiny little bit of vb remember i always say learn a little teeny tiny bit of vb and it makes your database a whole lot more powerful we can use one line of code in the right place to make that automatically refresh itself watch this design view go to that field go to events and go to after update hit the dot dot dot button all right i get the code builder if you get a window up that says what builder would you like pick the code builder and then in here we're just going to say me dot refresh that's it that's all you got to put in there that says after i update this field refresh all the calculations on the form all right save it i'm going to close that close that open it back up again now if i come in here and i put in like a 10 all right let's watch the totals tab there they go see it automatically refreshed all the calculations if you want to learn more vba go watch my free intro to vba video it's about 20 minutes long teach you all the basics you'll need to know a couple of different commands to make your databases a whole lot more powerful okay now we got to put the same thing in our invoice because right now our invoice doesn't have the discount in it all right the good news is we can use pretty much the same fields we had over here okay so right click design view all right and i'm going to try and fit these both on the screen at the same time here okay let's put the discount right over here so i'm just going to copy this guy copy and paste and it comes in over on the left i hate that and click and drag click and drag and sit here right there line these labels up a little bit okay and then down below gotta make a little room in here all right we'll do the same thing i'll grab a label copy click paste that attaches it there let's match the font size what is it uh about 16 i'm gonna say up 16. so we'll come over here we'll make this guy 16 point make it a little bit bigger let's unbold this stuff and i'm just going to copy and paste this guy copy paste paste all right this will be the subtotal this will be the discount and this will be the order total all right and let's set these guys up here too so some extended price we're going to rename that to subtotal this we're going to name discount and this we're going to name order total okay those are the names of the fields now let's put the updated calculations in here discount we're going to get from this guy let's open up his properties double click and we're going to copy that function right there so shift f2 zoom in select it all with the mouse copy it let's close that come back over to here bring up your properties and then paste that in the control source there there's my discount and then this one was easy right this one was just the equals the subtotal plus the discount all right save it let's close it close it close it sure okay all right so i like to go back in from the top all right so orders invoice and there we go that should match that looks good oh we got the stupid box around that i hate that box right click design view we'll just use uh you could format paint but we'll just go up here it's easy to do we'll go to format and we'll go to shape outline and we'll set it to transparent save it close it and one more try and there we go beautiful looks fantastic now if you want to learn more in the extended cut for the members i cover tiered discount rates what does that mean well one of my students emailed me a little while ago and said i like to give the customer a discount based on how much money they've spent with me in the past so let's say for example the customer has spent a thousand dollars then they get a 10 discount right if they spent more than five thousand dollars they get a 15 discount and so on so i'll show you how when you create the order it'll automatically calculate how many total sales they've had in the past and make that the discount percentage for the new order that's covered in the extended cut for the members and if you really want to learn more about building an order entry system in my access expert level 8 and 9 classes i cover a lot more including calculating sales tax the proper rounding of values final product and tax totals we do taxable items so you might have certain items on an order that are taxable and are not taxable like if you sell groceries and clothes for example and then in expert level nine we do multiple sales tax calculations for example each customer can have their own taxable rate or whether they're taxable or not because you might have certain customers that are tax exempt okay and you can have different rates for each order and that's all covered in access expert levels eight and nine i'll put links to these down below but check out the extended cup for the members silver members and up get access to all of my extended cut videos as well over 200 of them now and gold members can download these databases and have access to my code vault how do you become a member click on the join button below the video after you click the join button you'll see a list of all the different membership levels that are available each with its own special perks silver members and up will get access to all of my extended cut tech help videos one free beginner class each month and more gold members get access to download all the sample databases that i build in my tech help videos plus my code vault where i keep tons of different functions that i use you'll also get a higher priority if you decide to submit any tech help questions to me and you'll get one free expert class each month after you've finished the beginner series platinum members get all the previous perks plus even higher priority for techhub questions access to all of my full beginner courses for every subject and one free developer class each month after you finish the expert classes these are the full-length courses found on my website not just for access too i also teach word excel visual basic and lots more you can now become a diamond sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you're a sponsor you'll get a shout out in the video and a link to your website or product in the text below the video and on my website but don't worry these free tech help videos are going to keep coming as long as you keep watching them i'll keep making more and they'll always be free if you enjoyed this video please give me a thumbs up and post any comments you may have i do try to read and answer all of them as soon as i can make sure you subscribe to my channel which is completely free and click on the bell icon to select all to receive notifications when new videos are posted make sure you click the show more link down below the video to find additional resources and links you'll see a list of other videos additional information related to the current topic free lessons and lots more youtube no longer sends out email notifications when new videos are posted so if you'd like to get an email every time i post a video click on the link to join my mailing list even if you don't want to become a member feel free to donate to my tip jar your patronage is greatly appreciated and will help keep these free videos coming i got puppies to feed if you have not yet tried my free access level one course check it out now it covers all the basics of microsoft access it's over four hours long and i just updated it for 2021 you can find it on my website or on my youtube channel i'll include a link below that you can click on and also if you like level one level two is just one dollar yep that's all one dollar and it's free for all members of my youtube channel at any level even supporters want to have your question answered in a video just like this one visit my tech help page on my website you can send me your question there while you're on my site feel free to stop by the access forum lots of good conversations happening there be sure to follow my blog find me on twitter and of course youtube once again my name is richard ross thank you for watching this tech help video brought to you by accesslearningzone.com i hope you enjoyed this video and that you learned something today i'll see you again soon you