Collaborate on Access Invoice Template for HR with Ease Using airSlate SignNow

Access invoice template for HR

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 monthly memberships or any kind of recurring billing to your microsoft access database today's question comes from summer from bozeman montana one of my platinum members summer says my business has a monthly membership program i'm using your tech help invoicing database which is very helpful thank you you're welcome is there any way to have the database automatically add invoices for membership dues every month right now i've got over 200 members and it's a pain to add them all by hand well yes of course summer we can definitely do that a little bit of warning though anytime you say automatically when it comes to microsoft access it's going to involve a little bit of programming now you can use macros or vba personally i prefer vba i don't like macros so make sure you go and watch my intro to vba video there's the link i'll put it down below in the link section so you can just click on it don't be scared vba is not that difficult once you learn it i'll walk you through it step by step now for the rest of you who have not used my invoicing database go watch these videos first my blank database is where i build the database template we add contact management to it then we add invoicing to it it's three different videos if you're brand new to my videos go watch those three first then go watch the form naming video this is so you can know how to get a value from a form and also set a value on a different form when we loop through the memberships we're gonna have to be able to set values on the order form so go watch all five of these if you have not yet already they're all free they're on my website they're also on my youtube channel go watch them right now go on get out of here go watch them okay you're back you watch them here we go okay here i am inside my tech help free template again it's a free download from my website and if you watch those other videos you know exactly what this is and how i built it okay so we've got our customer form and each customer can have orders and what summer has to do right now is she has to open this up go to a new one right put in here your membership okay come down here type in you know membership for july 21 whatever all right tab over tab over 9.95 and then print it right okay so we want to automate this process here so she's got a list of members what their memberships are right you could have multiple membership levels and then click one button and then him automatically add those invoices to the system okay so what's the first step well the first step is going to be to set up a membership table so we can track what customers have memberships and what the details of those memberships are so create table design this will be our membership key so we'll start off with membership id that'll be on my auto number then a customer id that's my foreign key so that's a number of type long integer that's what customer this membership is associated with then the description which i'll call membership type or you can put description or whatever you want in there whatever you want to show up on the invoice as all right that'll be short text the amount of the membership that's a currency and then the renewal date that'll be a date time value okay let's save that this will be my membership t membership table primary key yes that's the id all right that's all good let's put some sample data in it all right customer one and of course we'll pick from a list we won't have to type these numbers in all right we'll say this is the basic membership and that's 9.95 a month and the renewal date will be uh let's say seven one this one was due today is 7 28 so this one should be due okay let's put another one in let's do customer two let's do the premium membership that'll be 19.95 and that was due on seven three all right okay let's make a form for this now close that save changes yes now if you watch my blank template i got some blank forms over here that i keep so i don't have to keep recreating the wheel right reinventing the wheel so i'm going to use my continuous f as the template for this so copy paste ctrl c ctrl v this will become my membership f my membership form which is right down here now design view okay let's first thing i like to do let's just change the colors all right let's make this look unique let's go to format let's pick like uh let's go darker green with that and this down here let's go a little bit lighter green for the detail section like uh maybe that okay and of course there's that alternating color someone recently pointed out to me normally what i do with the back color i just copy this one to that one okay it's just a habit but someone recently pointed out to me in a comment if you delete that it puts zero in there which all zeros are black but if you drop that down you can also pick no color so that might actually be a better method to do this because if you change this in the future you don't have to keep changing it in here so thank you whoever that wasn't made that comment sometime i get sometimes i get stuck in habits of just copying and pasting and i forget that you can just pick no color from there okay it's the simple stuff okay let's bind this form to let's go to the data section let's bind this to my membership table which is right there right now this form is getting its data from the membership table let's add the existing fields go to add existing fields i'm going to click on the first one click on the last one that selects them all just click and drag drop them all over here in the detail section okay now i can turn this off okay and the reason why i keep these over here is for formatting purposes watch i can just slide these over here watch let's get rid of these labels delete membership id can go right there and i like my ids to be gray that's why i keep this one around so click on that format painter and paste it over that see and these guys all look pretty good so i can get rid of these now delete slide that up there next up is the customer id but i don't want to type in a customer id i want to pick a customer from a list of customers right using a combo box now again i don't want to reinvent the wheel and i've already got a customer combo box on my order form so let's save this real quick control s let's go over to my order form which is under customers and then orders right right here's a customer combo box let's just borrow this guy right click design view click on that copy ctrl c close it close that click over here click and paste and there it is there's my customer combo box very little work right delete the label delete this guy slide you right up into place see right like that okay a lot of working with access is is reusing controls reusing forms you don't have to reinvent the wheel every time you want to do something right okay membership type is next that can go right there okay the amount is right there that doesn't have to be quite that big unless you're dealing with tens of thousands of dollars and then a renewable date can go right at the end over there okay now i'm going to select all these guys right click i'm going to go size to grid make sure they're all sized on the grid shrink that up like that let's fix our labels right how many do we need one two three more so copy paste paste paste just slide these over here roughly where they go shrink that up we can rename these now this will be the customer right slide you over there this will be the membership type right these are labels so they just have to look nice for you right this is the amount and let's resize you and this is the renault date slide you over there and if you've never done work like this before working with forums go watch my access beginner level one class i cover all this in access beginner one it's free it's four hours long it's on my website i'll put a link down below in the link section renewal date okay and let's make sure these are all sized to the grid too highlight them all right click size to grid slide that up one more thing i'm going to check is my tab order i'm going to click here on the detail section right go to design tab order because see i added that customer combo right hit auto order that orders them all nicely so when you're tabbing through tab tab tab tab you don't got to worry about going jumping around right save it close it open it up and there we go there's that nice membership form maybe align this guy to the left right that's another one of my pet peeves select that format align left okay save it close it and open it back up again and it still didn't go see sometimes if you do a group of them i think this is a bug in access you have to do it individually sometimes it doesn't get with a group save it close it open it back up again there it goes so i've noticed that many times if you select a bunch of fields and you and you left the line or right line them all it doesn't always get them all all right let's add someone else on here now let's say jean-luc picard he's got the basic membership i'll copy and paste that 9.95 and his renewal date is uh 7 20 let's say and let's put someone in the future in here let's put will riker he's got the the gold membership and that's like 29.95 but he's not due until 9-1 we don't want him to be processed yet as we'll test to make sure our stuff is working okay now let's sort this based on renewal date so we'll sort that oldest and newest so the oldest guys show up on top up here and what we're going to do first is make a button down here in the footer so when i click on process renewal it will open up an order form and add that order for me using some vba code okay are you ready right click design view down here in the form footer we're going to put a command button so find buttons up here right there drop the button unfortunately there's no wizard for this so cancel and let's rename the button caption we'll call this um process current membership or renewal it's called process current renewal okay let's name the button itself double click on it command 12 will become process renewal button btn okay alex is proud of me now all right save this now let's walk through the steps oops wrong button actually let's put the membership on here it's not on our menu is it i'm just going to copy and paste one of these buttons right copy paste we'll change this to memberships all right right click build event it brings up my code builder and again if you haven't watched my intro to vba class go watch it one line of code do command dot open form membership f that's it just like the other ones that are in here let me resize this a little bit vba is not scary okay there we go all right close that save this let's reopen the main menu and there's our memberships form okay looking good so far so let's walk through the steps that i want this to take when i do write my code and it's not a bad idea to put this inside of like a notepad all right open up notepad all right here's notepad okay what do you want to do well the first thing you have to do is we're going to open up the customer form because when we go to print our invoice okay it needs the customer form if you remember from when we built the invoicing database it requires that to print out stuff correctly so the first step is going to be to open the customer form for the current customer okay whoever this customer is in this box then we're going to open up an order form and go to a blank new order all right so open order form go to blank new order all right then we have to set the order details including the membership type the money right excuse me this is the ordered the order information i said order details i forgot this form down here is actually called the order details form so what i meant is the the specifics for the order itself all right set order info which is going to be the date the description okay the customer and actually since the customer form is open the customer combo box will get its default value from there but i still like to manually set that stuff just to make sure okay then once we set this stuff we can then set the order details right set order details which is going to be the uh the type the amount right okay then we can uh at that point we can print the order okay so let's let's take our notes here set them over to the side just get them out of the way but it's nice to sometimes have that you know what steps do you want your code to take before you sit and just start writing code unless you've been doing it for 40 years like i have and i can just sit down and start writing code but if you're just getting started out it's good to write this stuff down so what do we put in this button here all right so design view right click build event brings up my code builder process renewal click all right so the first thing i'm going to do is check to make sure that whoever's order i'm on whatever membership i'm on is actually due if it's in the future just say this guy isn't due yet right so if renewal dates is greater than today's date then message box not due yet and then exits up and if all right so save that come back over here all right if i click it on here nothing should happen but if i click on this guy and hit process current renewal it'll yell at me not do yet all right so it'll stop anybody who's not due from being processed again optionally if you wanted to you could put a box down here that says process through x date maybe if you're going on vacation and you want to you know run memberships through next week so they get built ahead of time whatever you want to do so instead of using today's date you could put the date in a message box or in a text box all right but i'm not going to go that far okay so what's the what's up next so next we have to open up the customer form all right to the current customer now the current customer is in this combo box which is called customer combo so next up is do command dot open form we're going to open up the customer f we're going to use a where condition go comma comma comma where condition right here customer id equals customer combo this says the customer id on that customer form has to be equal to whatever value is in customer combo a little string concatenation there right that puts it together and the result will be customer id equals four let's say and that gets sent to the do command open form so now if i come over here and click on me and go click there's me okay see how that works all right what's next we have to open up the order form and go to a blank new order okay so what we'll do is we'll go do command dot open form order f is the form comma comma comma comma go to data mode and go ac form add all right it's different ways to open up this form you can open it up in edit mode which is the default read only which means people can only look at stuff all right or add don't worry about property settings add okay we're going to add a blank new record now that form will get the default value but i still want to set it okay so let's set the values on the order form what are the fields we have to set let's go take a peek at the order form again all right customers orders all right so the date again the date should be defaulted but let's set it anyways just to be sure okay we got the description okay and then we've got the customer so three things we have to set on the order form probably not a bad idea to also make sure is paid is zero right even though these things have default values in my code i still like to force them as well so let's take a peek and make sure we get the names order date i'm pretty sure this is just is paid yep is paid and description and this is customer combo okay so now we know what all these are we can set these in our code let's go back to our code right back here all right now this is why i wanted you to to uh to review that form name thing because here's where you have to set a value on a different form remember right now i'm sitting on the membership form so any fields that i reference at this point like customer combo are fields on the current form if you want to refer to a different form you have to use its full name so set values on order form all right forms order f and we're going to start off with uh we'll do the customer combo equals customer combo in other words set the customer combo on the order form equal to the value of the customer combo on the membership form okay you might see in some code people do this right or even me dot all right no just you don't need that just use customer combo me is assumed the only time you really need me is if you're dealing with properties like me dot caption that's a property of the form all right but if you're dealing with fields you don't need the me all right forms order f what's next order date equals today's date okay what's next forms order f description equals and let's put membership membership dues or whatever you want to call it this goes on the order form and we could go membership dues um we could say um or say let's say membership through and then the date of your renewal date right renewal date so on your membership form here right the renewal date it was due 7-1 all right so it wouldn't be well we could put through we can add a month to that right come in here right we can add a month to this using the date add function right date add what are we adding a month comma one comma renewal date all right assuming all of your memberships are monthly and this will be something we'll address in the extended cut because you might have weekly bi-weekly quarterly annual memberships right we'll do that in the extended cup for the members all right one more field is is paid right forms order f is paid equals false make sure you set that to false can you shorthand some of this yeah there's a thing called a with statement i almost never use it so if you want to i'll cover it in a future lesson send me some send me some feedback and i'll talk about i think i talk about it my developer classes i almost never use the with i think with makes the code harder to read okay so now we've got our order information set next we have to set the values in the order detail form that's next but first a real quick commercial break if you really want to get the most out of access and you're enjoying these lessons sign up for my access expert classes i've got 32 levels over 50 hours of lessons covering everything from relationships to sql order entry accounts receivable mass email lots and lots of stuff the list goes on check out my website link there's a complete course listing online i'll put a link to this down below gold members if you sign up gold members get one free expert level class every month so that's one more reason to sign up as a member okay back to class i usually put an ad at the end of the video but i know a lot of you drop out as soon as you know the lesson's over so i gotta i gotta start sneaking them in the middle of these longer lessons now sorry sign up more members i get the fewer advertisements i have to do all right now let's set the order detail stuff let's take a look at the order detail form all right let's go back to the customer form orders right what do we got to set in here the product name the quantity should always be 1 right and then the unit price so let's take a peek and make sure we got the names right product name quantity unit price okay good and let's go back to our code again and one thing i forgot up here i'm looking at my notes here make sure when you're done you throw in a forms order f dot refresh dot refresh says commit this stuff to the table remember when you're editing a record in the table right as you edit something remember the little pencil there it gets dirty that means that this change has not yet been committed to the table underneath all right so as soon as you leave that record or issue a refresh then this gets saved in the table and that's important when we're doing this stuff because we want this order to be saved to the table first before i start ordering or adding order details to it all right so now we've committed this change to the order table now we're going to come down here and do forms here now this is where it gets a little complicated we're dealing with a sub form now okay here's how you get a reference to subform and i mentioned this in the other video forms order f order detail f dot form exclamation point product name equals what is going to be equal it's going to be the membership type right there all right so we'll put membership type okay this right here is how you refer to that order detail subform i should put subform here right okay now i copied and pasted that so i just paste it right forms order f order detail f dot form exclamation point what's the next field name unit price equals uh amount i believe is the field name right amount yup and if you're not sure just type it in and see if it capitalizes remember that trick okay next is quantity and that's going to be equal to one and then when you're all done dot refresh and it'll save that information in the order detail sub form okay now we're going to update the membership record we're going to say the renewal date for the current membership equals that same thing we did earlier right up here copy paste and then me dot refresh refresh that membership record okay see what we did open up the customer form open up the order form and this really is only open so we can print okay set the order set the details update the membership record and then we're done and that's one iteration through the current member all right let's save it and test it and see if it works here we go ready let's click on the first one okay process current renewal click boom opens up me goes to a blank new record right membership through eight one that looks about right right what was the membership date over here well it already updated it eight one it was seven one remember my name basic membership one 995 and now i can print it okay or put a button on here to print directly whatever you want to do all right close this one okay that's updated go to the next one seven three process renewal click boom there you go membership through eight three jim kirk premium membership looks good all right click on the next one 720 go all right jean-luc membership through 8 20. and then finally the last one click oh he's not due yet okay see how that works so now we've got it working perfectly to add one at a time now how can i click on the button and have it automatically add all of these orders to the system without me having to do them one at a time i've already saved you a ton of work summer by just making it one click per customer right but you can also loop through these and do them all in one shot we'll cover that in the extended cut for the members want to learn more in the extended cut for the members i will show you how to automatically process all of these at once click one button process all do bam bam it'll generate the invoices for you it'll loop through all of them update the renewal date we'll also use the members only version of the order entry database so your memberships can be entered into the product table and then they'll be added to the orders that way i'll also show you how to mark these invoices to be printed they're all new when they're added to the system that way you can generate a query later you want to print them all at once and you can then go through and print all 10 1500 invoices that you have to print or email them however you handle it and we've got 35 minutes folks and i'll show you how to do daily weekly monthly quarterly annual right so your memberships don't have to necessarily just be monthly okay that's all covered in the very long extended cut for the members silver members and up get access to all the extended cut videos and gold members can download these templates how do you become a member click the join button below the video after you click the join button you'll see a list of all the different types of membership levels that are available silver members and up will get access to all the extended cut tech help videos live video and chat sessions and more gold members get access to a download folder containing 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 platinum members get all the previous perks plus access to my full beginner courses and some of my expert courses these are the full-length courses found on my website and not just for access i also teach word excel visual basic asp and lots more 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 if you like this video please give me a thumbs up and feel free to post any comments that you have i do read them all make sure you subscribe to my channel which is completely free and click the bell icon and select all to receive notifications when new videos are posted click on the show more link 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 new video click on the link to join my mailing list now if you have not yet tried my free access level one course check it out now it covers all the basics of building databases with access it's over three hours long you can find it on my website or on my youtube channel and if you like level one level two is just one dollar and it's also free for all members of my youtube channel at any level want to have your question answered in a video just like this one visit my tech help page and you can send me your question there click here to watch my free access beginner level 1 course more of my tech help videos or to subscribe to my channel thanks for watching this video from accesslearningzone.com you