Access Invoice Template for Research and Development with airSlate SignNow

Access invoice template for Research and Development

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 time and billing to your microsoft access invoicing database today's question comes from fletcher in madison wisconsin one of my platinum members fletcher says i'm using your tech help free template for invoicing can you add something where i can keep track of how much time i've spent working for my client so i know how much to bill them for at the end of the month well sure fletcher a lot of people like to keep time and billing in their database separate from the invoicing that way every time you do something for a customer whether it's a phone call or in my case like development work or training online or some research you can put that in your let's call it work table and then at the end of the month you can build them for all the stuff that's in the work table that you haven't built them for yet let me show you how i'd set it up so this is the tech help free template that flashy is using you can download this for free off my website if you'd like to i'll put a link down below in the link section so let's start by creating a work table where we'll put all of the actual work items in here so we've got the work id auto number we've got the customer id so we know who to bill it to that'll be a number of type long integer a description of course what kind of work was done whether it's research or whatever i like to throw notes in there now rather than start time and end time i like to put when it comes to billing i like to put a start date time and then billable hours so for example i know i started it at let's say 9 a.m on a monday okay and when i'm done let's say i've worked for three hours maybe i only want to build for two two and a half whatever so i could put in the start date time so i know when i started so later on when i'm done i can look at it and be like okay i just spent six hours on this but maybe you don't want to bill them for the whole six hours maybe you took a lunch break in the middle or whatever so you know exactly when you started and instead of putting an end date in an end time in and letting access calculate that because if you want to change the billable hours then you've got to go in there and figure okay i got to back this up to 430 then no just put the start date in there and how many hours you want to build them for that's how i like to do time in billing and in the more advanced classes in the extended cut for example i'm going to show you how to do a little time clock so you can click on start and then stop and it'll actually keep track of that for you but for now this is just fine right start date time we'll make that a date time field and the default value will be equals now whenever you start it right put the current system clock time in there then we'll do billable hours so when you're done you'll type that in now i'm going to make that a number i'm going to make that a double okay and the default value i'll put in there is one hour all right maybe you got a minimum building of one hour and then after that you build a half hour increment so you can set it to 1.5 to 2.5 and so on now some people build different rates for different things like you might bill a phone call at one rate research at a different rate you know online training at a different rate development at a different rate okay so put your hourly rate in here and if you want that'll be currency and if you want default that let's say you normally build 100 an hour put 100 down there but you could change it or if you're billing for different people different different staff maybe you're a law firm and your lawyers build a different rate than your paralegals do all right so you could change that as well for each client and then of course you want to keep track of has been invoiced right yes or no have you actually invoiced for this line item yet okay so let's save this this will be our work t our work table i like to throw some sample data in here real quick so customer id one that's me all right maybe a phone call no notes for this one the start date time defaults to right now that gets thrown in there automatically let's put one billable hour in there at 100 bucks and it has not been invoiced yet okay same customer a little bit later on some research okay maybe i did half an hour of research at 75 dollars an hour okay all right easy enough now we're gonna make a form to put all this into but first let's make a query that'll do a little calculation for us so create query design now if you've never done calculated query fields go see that video first i got a video on that i'll put a link down below go watch that if you've never done calculated query fields before also we're going to be using the if function the immediate if i call it the if function all right go watch that video too okay so now that you're an expert in both of those things bring in the work table then we can close that bring in all the fields from the work table and i want to sort this by the start date time descending whoops not in the criteria field i want to put that in the sword field right so when i open up my form the ones on top i want to see the most recent items okay on the very top of the list now i don't want duplicates in my query if i run this right now i'll get a duplicate right and it says field zero so to avoid duplicates we just simply hide it like that and now if i run it you don't see the extra copy of that field let's save this real quick while i'm thinking about it as my work queue my work query now what else do i want to put in here well i've got the billable hours and i know the hourly rate let's put in here the line total which is the billable hours times the hourly rate right so now i can see the total for each item right one hour at 100 an hour right we don't have to worry about formatting that we'll format it in the form we'll make it look all pretty in the form okay what's next in here well i want to know how much i have not built yet for this customer okay so we're going to say unbilled is going to be if let me zoom in so you can see this better there we go on build if not has been invoiced comma line total comma zero what does that say well if this hasn't been invoiced that means it's unbilled right make this value the line total otherwise make it zero because it's been built all right that's pretty cool huh let's take a look at that see now if i mark one of these as has been invoiced that goes to zero so i can easily see at the bottom of the column how much i haven't built this customer for all right pretty snazzy all right let's save that now let's go build a form now if you haven't watched the video in which i build this the invoicing video alright go watch this video first because in that video and in the one before it where i build the blank template all right i create this continuous form it's a blank continuous form so we don't have to keep reinventing the wheel so let's modify this continuous form actually let's copy and paste it copy paste let's that's ctrl c ctrl v for those of you don't know your keyboard shortcuts right let's call this my work f my work form all right right down here now let's design this guy design view first thing we do is bind it to the work query so come into all drop this down and find the work queue now this form is bound to that query we just built so we can still edit the data but we also get our calculated fields okay very important let's change up the colors a little bit it has to do with time and billing so i'm going to make it green for money let's go to format let's drop this down let's go kind of that like that green let's go a little darker though let's go into the palette here and let's drag this down to oh i don't know about there let's see yeah that looks good all right now i'll click down here click the same color go into here click the same color let's make it a little bit brighter though let's go up to about that green yeah it looks good and we'll copy the back color over the alternating back color so those are the same i hate that alternating back color okay so now i keep these in here just for fun but we don't need those let's get rid of those let's go to design and add existing fields now we don't need the work id in here all right the the auto number is meaningless for this form we don't need to see it all right the customer id i'd like to bring in a combo box for the customer we'll do that in just a second let's bring in description notes i'm going to put down here actually i'll bring i'll bring customer up here so i don't forget to put the combo box up top i always do okay let's bring in what else we got the start date time that's going to go up here first the billable hours we need obviously the hourly rate whether or not it's been invoiced and then our line total and our unbuilt we'll bring those over here on the right and this is a big jumbled mess isn't it look at that okay so i'm going to get rid of all of these labels yeah i know there's a faster way to do all this but i'm kind of old school let's get rid of that okay so i'm going to go start date time over here on the left then my description i just there's a certain there's a certain satisfaction i get from laying this out by hand i don't know what all right billable hours will go here that doesn't have to be quite that big hourly rate will go next to it okay now because of that now i have my line total and my unbilled amount and let's slide that over like that now these two guys you can't edit those so i like to tell the user visually let's make those gray let's go look about that great right there okay and here's my invoiced way over here on the end all right let's adjust the tab order so that these guys are all in line tab order auto all right and if you want to you can even take these two out of the tab order right click properties and then go to other and then tab stop say no that way when they're tabbing along tab tab tab tab doesn't even stop there all right because you can't change those anyways right all right i'm going to delete that label for the notes and you know what i'm going to do i'm going to steal some fields from other places watch this the customer id is a combo box right and it's bound to a field called customer id i happen to already have one of those on my order form right and since you watch these videos you know how i did all this stuff right so design view i'm just going to copy that combo box copy i'm going to slide back over here and paste it in now i can delete that text box see that so i already have a combo box built right for the customer let's slide this down let's move these labels down a little bit and let's slide the customer over here like this okay so there's the customer okay may make that a little bit bigger let's make this white format and wrong one drop that down pick white i like to pick the standard colors by the way i don't usually use theme colors all right i'm gonna do the same thing with that notes field i've already got a pretty notes field delete right here on my customer form i like this notes field the yellow look with the sticky paper shadow in the background right copy you copy and paste and since the fields are named the same i can get i can get away with this right it's notes on this form it's notes on the other form this is where you put the notes about what this particular building is for okay all right now let's make these labels pretty right start date time slide this one over description and now we need more of them right for the other ones so copy paste paste paste paste paste all right we'll just slide these over here like this yeah i know there's faster ways to do this but like i said already i like doing it by hand okay so we've got this is the billable hours so let's go hours hrs like that remember these are just the labels these are what the person sees so we can put whatever we want in these hourly rate okay let's put the line total here we'll put unbuild next to it and you know what for unbilled we really don't even need to see this here i want actually i want to have unbilled down below here but we'll move that in just a second let's slide that over and we'll put over here inv for invoiced okay all right slide you right there let's see what we got close that open it up looks good all right little bit of formatting to do here still let's right click design view let's format this guy what do i want to see in here let's do m m d d y y h h and n like that yeah it'll be in 24 hour time but you know what i'm comfortable with it and that's what i want to see looks good let's left the line everybody in here i like i know people want numbers and currency values to the right i like everything left aligned that's just me it's my preference i almost never write a line stuff i think it looks a whole lot better okay i never bill in cents so i can make the rate in here at least i can make that let's make that uh under format let's go dollar sign zero just like that without any decimal points after it let's see what we get all right looks good now these might be formatted with that you might want to build in in infractions right so right click properties and we'll make the format for these currency that just gives us the two digit decimal values let's see well i keep going back to the customer form you know while i'm thinking about it let's do this let's go to the customer form let's put a button on here to open up the work form all right so i'm going to slide this guy over a little bit and let's copy one of these buttons copy paste and why are you not copying and pasting copy paste my keyboard has been doing some weird stuff lately copy paste there it goes okay it finally took a second sometimes my video recording software does weird things when i'm copying and pasting let's change this caption to work and let's give it a good name so alex doesn't yell at us let's go to work button here work btn for the name of the button now you could use the command button wizard and drop a button down here but i'm going to do it my way right click build event that'll open up the vb code window you might get a message up saying um which builder would you like the code builder if you haven't watched my intro to vba class go watch that vba is not hard don't let it scare you do command dot open form what form were you opening up the work f that's it now you can come in here and say comma comma comma where the customer id equals the customer id and the current form that'll open up just the work items for this particular customer see how easy that is don't let vba scare you it's not hard and once you know how to do that it's actually faster than using the wizards it is and you get better results alright so now save your changes yes now if i come in here open up work i get just the work for this particular customer now i personally hate these little pop-up date pickers i i don't like them they they float over stuff i turn them off usually especially since the start date time right here it's going to default when we want to create a new one it defaults to the current date and time so if you want to change you can click in here and change it but i usually turn off those date pickers so i'm going to format and show date picker i'm going to say never that's just my preference again you can do whatever you want if you like to use those things all right there we go all right research phone call what's this one going to be you know oh i don't know courtroom time i don't know all right three hours and for that i built 150. okay see there you go let's throw some form footer totals down here again i got videos on form footer totals go watch those if you've never done a form footer total before right click design view i'm going to copy the total and the unbilled copy paste them down here in the footer see i did that copy them and then paste them down here now for line total this is going to be equals the sum of the line total and i'm going to make its name sum line total and i'm going to do the same thing for the unbilled all right equals the sum of unbilled now you have to have that in the query for you to be able to do a form for a total on it down there okay so that's that that's that that's that okay let's make this guys i knew my first forgetting somebody some unbilled save that all right and now if you go into work you can see how much you have to still build this guy for now if you click on one of those here remember you got to move off the record for it to update the form footer totals all right you could put a meet at refresh in there if you want to all right again a little tiny bit of coding i teach all this stuff in my developer classes all right but the the problem is is that the the calculations don't update until the record is refreshed which means you have to move off of the record to another line item but if you want to make it force it to refresh whenever you change any of these things all you gotta do is go into that individual control go to events go to after update and then in here say meet out refresh that's all you gotta do see it's not hard vba oops i hit the maximize button by accident it's it's hard if you're like me and you're clumsy okay but now if i come in here as soon as i click that button it refreshes the calculations see do that down the bottom look at that see one 137 four 587 you can do the same thing with these guys too you can change this to two you can have it immediately update down below i do that a lot for all of my um my order entry stuff right after update event right there me that refresh that way it doesn't confuse people right same thing with the hourly rate if you change that meet at refresh let me not refresh i always put those in there because it's confusing right you get someone who doesn't know access you're used to like excel for example if you're used to treating this like a spreadsheet all right if you type as soon as you type in one and tab off of it to the next one you want to see your totals update well access doesn't work that way access only updates calculations in the form footer when the record is saved and the record is not committed if i if i come here and do that right see that little pencil that means this record is dirty dirty means it hasn't been saved to the table yet if i had escape right now nothing happens it doesn't save it all right if i come over here and put a six in there and hit escape it doesn't save it okay so if i hit six now and hit tab now it updates because i got the me dot refresh in there see it's these little teeny tiny enhancements that learning vba can take your database it can take you from a beginner access user to an advanced access user with very little work all right just knowing a couple little things to enhance your databases it's all the stuff i teach in my developer lessons okay now i know right here at the end of the month i can open this up and say okay you got 975 dollars that i haven't built you for all right so i can mark these invoiced and i can come over here orders right i can add a new order all right billing and then you put in here like billing for march 2021 right and 975 or whatever you whatever they owe you all right you want to put the totals down here what you build them for all that stuff okay now close that out now you might want to have it so when you come into here you only see the stuff that is not billed okay so how do we do that well i put a little check box right here that says show all normally when you click on this you want to see the stuff that you haven't invoiced for yet okay so watch this design view i'm going to put a little check box in here right there we're going to call this guy show all right show all means i want to see everything versus the norm will be just show me the stuff that's not invoiced yet so let's make this guy called show all and the default value will be no now tiny little bit of work in here right right click build event right here i'm going to say if show all then do that show me all of the items for this particular customer otherwise i only want to see the items for this customer that have not been built yet all right got no a little bit of sql to do this i'm going to copy that paste it down here all right here's the where condition if you've never done any sql before go watch my sql videos again i'll put some links down below for you all right so the where condition right here customer id equals whatever the customer id is in the current record three for example and and i can spell anthony and has been invoiced equals false that's it see even sql isn't that scary once you know what you're doing right save it close it close it customer work ah look at that i'm not seeing anything right because it's only showing me this stuff that hasn't been invoiced yet unless i click on show all then it'll show me everybody okay see that so it's time to go add some more stuff come in here all right let's see i started today at uh i can probably make this a little bit bigger here let's make this bigger let's slide this out just a little bit so we can see the whole thing in there you know i'll put the am and pm back on not a lot of people like 24 hour time so let's go into here format let's put the am pm on the end there why not right okay all right looks good let's say i started today at 9 00 a.m we did some research uh six hours of it at 75 dollars an hour okay and that's it for we're done for the day go away okay pop it up come back tomorrow open up mr ross go back to his work say that and yes you could make a um you can make a button on your main menu if you want to to click on it to see everybody who has to be billed okay right certainly the sky's the limit once you understand how to do all this stuff right but now we know how to go into the customer form right open up the work form here so we can see what we have what we've done let's say the next day we come in here we did uh you know meeting we met for an hour and a half uh i built 130 an hour for that and there we go now at the end of the month i can quickly see down here how much i have to build this customer for and there you go there's your basic time and billing of course lots more to come in the extended cut want to learn more about time and billing in the extended cut for members i will show you how to add a start and stop timer right on the work form when you click start the little timer will start up and it will count up seconds and this will allow you to automatically track how much time you spend if you take a break you go get lunch whatever you click the stop button you can come back later hit start again start the timer up i'll show you how to lock all the controls on the form so you can't manually edit them while the timer is running that's important sometimes then i will also show you how to round up the hours billable to 15 minute increments so if you've worked for an hour and five minutes we'll round it up to an hour and 15 minutes that's all covered in the extended cut for members silver members and up get access to all of my extended cut videos gold members can download this database i also have a time and billing seminar available on my website in addition to what's covered in the extended cut i go over lots more including billing all of your customers that have unbilled hours with one click it'll automatically generate invoices for them and then you can batch print them or email them all out that's all covered in my time and billing seminar also available as just a template i'll put a link down below for you 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 of 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 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