Create Your Custom Microsoft Receipt Template for Inventory Effortlessly

Microsoft receipt template for Inventory

welcome to another tech help video brought to you by accesslearningzone.com i am richard ross your instructor in today's video i'm going to show you how to change an order from a quote or quotation or an estimate to an invoice to a paid receipt in your microsoft access order entry database today's question comes from ethan in orlando florida one of my gold members ethan says i've been using your invoicing database for a while now it's very helpful thank you you're welcome is there any way that i can differentiate between quotations or estimates invoices and paid receipts do i need to copy all the data between three tables no ethan in fact you can use the same ordered t table that we use in the invoicing database to track all three things quotations invoices and receipts quotations are basically you're sending this to the customer not sure if they're going to buy it or not right that's a quotation or an estimate then once they decide that they want it you change it over to an invoice so now that indicates they owe you money okay then once they've paid for it we can switch the invoice over to a receipt now there's no need to have three separate tables here all you need is two yes or no fields one to indicate whether it's a quotation or not and the other one to indicate whether it's paid or not so if it's not a quotation and it's not paid it's an invoice if it's not a quotation and it's paid it's a receipt now some people like to copy quotations over to a separate record to track the difference between the estimate and the actual invoice that's a lot more advanced i'll talk about that toward the end of the video but let me show you how to handle this first couple of prerequisites first make sure you've watched the video where i build the invoicing database it's free it's on my website i'll put a link down below and of course that is based on a previous creating the blank database template so go watch that first too all right so watch the invoicing database and the stuff before it and for this video you're gonna also have to know the if function the immediate if if i call it right go watch that video too because we're going to use this to change that label on our report okay so now you've got a copy of the tech help free template the invoicing database right and you know how to use an if function so if we go into our order table design view we already have is paid in here now we just have to add is quotation i'll just make it is quote okay this will be a yes or no value and by default i'm going to say no i would rather have my people put orders in that are accidentally invoices when they should be quotations instead of the other way around because if you default at the quotation and they put it in it's supposed to be an invoice then you're not expecting that you're supposed to be paid for that so i like to make is quote default to no so by default it's an invoice when you put it in the system i have to switch it to quotation okay that's my preference save that now in the database we've got some records already let me close that field list all right his quote should be no on all of them okay now we have our order invoice queue and this is what we use to generate the printed invoice right if we go in here to go to orders go to invoice all right this guy is based on order invoice queue so we're going to just basically put a text field in here all right calculated field a calculated text field using that if function that says if this is a quote put the word quotation there if it's not a quote but it's paid put receipt there otherwise put invoice here we're going to use a nested if function i cover nested if functions in the extended cut the extended help for the members after the if video so go watch that go sign up as a silver member and go watch that video too it's not super hard though i'll show you here how to do it all right so let's go into this right click design view all right come all the way out here to the end we're going to add a calculated query field out here now calculated query fields i also have a video on those i'll put a link down below if you watch the if function video i'm pretty sure the prerequisite for that one is calculated field so you gotta learn how to walk before you run everything's got prerequisites all the way back to the beginning even access 101 you got to know windows right so anyways people are always like well how come you got off so many videos before this one well i'm sorry that's how knowledge works right you can't learn calculus without basic arithmetic first just be fortunate that i'm pointing in the right direction with the links for the other videos okay so in this we're going to call this field top text that's the name of the field let me zoom in so you can see it better all right there's my zoom window shift f2 and in here i'm going to say if if what if is quote that's a field then put the word quotation here i like it all in caps comma otherwise what do i put in here if that's false another if function this is where your nested if comes in if all right what's the next condition if is paid then put the word receipt in here otherwise put the word invoice in here close up my parentheses all right see how this works take a look at the inside one first right if is paid this is a receipt otherwise it's not paid it's an invoice now this whole thing is the false condition for this if function if it is a quote market a quotation otherwise whatever this function receipt returns okay see how that works hit okay now we can't run this query by itself from here because it's dependent upon the open order right we learned that in the invoicing video so i'm just going to save this close that query now let's go into the order form pick up a customer go to orders gotta do a little bit of work on this guy first but before we do that come over here and open up order invoice now let's just take a peek at what we got in here this is this should be the current order right order one slide over to the end and it says invoice because is quote we don't have it on here yet is quote is false right and is paid is false so that's an invoice all right if i were to mark this paid come over here run the query again and now it should show receipt say receipt it's based on the is quotation field and the is paid field so let's put is quotation on the order form and i can't seem to click on things today there we go all right i'm going to shrink this button up and let's change this to just the word print now because it could be an invoice a receipt or a quotation all right here's is pay let's just make this say paid and i'm just going to copy this guy right copy copy paste slide it over here oh i missed it again i'm having trouble clicking today all right this is going to be quotation all right and open up the properties for this checkbox double click and we're going to change the control source to is quotation or is quote copy paste all right now save it close it open it back up again alright so put that back where it belongs it's not a quote but it's paid so if i run the query it says receipt all right good if it is a quotation which means it shouldn't be paid if i run the query now it says quotation see that now we just use this text in the receipt or in the actual printed report okay so let's find that order invoice r right click design view now this guy we really don't need anymore because this label is going to change it's going to indicate whether it's paid or not but i'm still going to leave the check box on here here's what i'm going to do i'm going to delete the label i'm going to slide the checkbox over in the corner and i'm going to make it not visible double click open it up change the visible property to no i still want it on here you still need it there if you're going to refer to it for some other values okay so we're going to leave it there hidden now this guy is a label we're going to replace this with our text box that we created in the underlying query okay remember this report is based on order invoice queue which is that query that we modified earlier so come in here add existing fields find my top text field drag that drop it right about there okay close the field list now this guy get rid of the label that it comes in with delete that make this bigger so you can see the whole thing watch what i'm going to do so i have to reformat the whole thing right i'm going to click on this guy go to the format painter and then paste over this one see i just copied the format over now i can get rid of this i don't need that label anymore goodbye slide you right in that spot there okay make sure it's big enough so the whole word the quotation is probably the biggest word make sure that fits there all right save it close it and now click the print button click there we go there's quotation all right close it turn quotation off click the print button invoice see that so i get a paid invoice and now receipt see how that works the two fields here indicate what the value of the if function is in the query which changes the text on the printed report now you may have noticed if you're if you're if you're sharp you may have noticed that between clicks of the button i was clicking down here in the detail section if i change this like unpay it all right notice this record is dirty that little pencil there means this data has not been saved to the table so if i open up the report right now it still says receipt even though i marked it unpaid why because this record wasn't saved it wasn't refreshed in the table and i opened this up which still is pulling the old data that's why i was clicking down here because technically i just left this record right this is a sub form and when you leave a record it saves to the table and now it updates okay that's confusing for our end users to have to do if someone's in here clicking on quotation and hitting print and they're going why isn't it switching to a quotation because they don't know that if they're not an access user so what you have to do is in this button you have to put a refresh command before the report opens up all right bonus material here we go we're going to modify this button usually i say this for my advanced classes all right you're getting some bonus material and in my for the extended cut for the members i usually use vba so you just go do command dot refresh but this is a button that i created using the command button wizard right up there so i'm going to show you real easily how to modify a button that the wizard created it's not hard right click build event that opens up this thing this is the macro editor normally i don't use macros but the the command button wizard in access creates button macros they're embedded macros they're called okay and you can see this macro has one command in it just the open report so we have to do is put a refresh in front of that open report okay not hard to do come down here type in refresh hit tab alright now it says run menu command refresh that's what we want but i need to click and drag it up top see what i just did there you can click and drag and move these things around you want the refresh command on top of report oh i missed it here click drag i see sometimes it's hard to do there okay make sure you got run menu command refresh on top then open report now save this save close the app that's saved inside this button now close the form save changes there yes open up the button open up the invoice the order all right hit print it's a quotation close it turn off being a quotation without clicking down here i can click notice it's still dirty but when i click the print button look at that it refreshed it first then opened up the report and now it's properly an invoice do the same thing with paid print and there you go it's a receipt okay so that's how you can control whether this is an estimate a quotation an invoice that needs to be paid or a paid order now members in the extended cut i'm going to show you how to do the same thing without using this macro and without using that if function we're going to open up the report and using vba just change what's in that box all right we'll change the caption property of that label in the build event so that's from members it's actually easier to do than setting up that query field but i try to avoid programming in the in the regular techhub classes because i know there's a lot of beginners all right something i'm holding off or holding out on you guys i want to try to show non-developers how to do stuff and access in the regular tech help classes if you are a developer that's what the extended cuts are for if you want to learn vba and programming and all that stuff we're also going to put a little paid sticker there all right so it shows up like on a slant like paid like a rubber stamp kind of thing okay and we're gonna put a little logic over here so that if it's already marked paid you can't switch it back to a quotation okay we're gonna put a little vblogic in these in these buttons here um if you want to learn a lot more about setting up invoices all right access expert nine and ten i cover a lot of this stuff in here here's a whole big order entry system building the printable invoice it's a lot more detailed than the one we build here all right expert 10 covers things like the invoice and the paid and all that okay second i got i got hours and hours of classes on invoicing believe me it's one of the more popular things people ask me how to do and i mentioned earlier in the video lots of people always ask me out they say i want to keep the estimate i want to keep the invoice but i want to make a copy of it and modify the copy for the actual order that way i've got both can you do that yes and in access developer 24 i show you how to do it but it involves copying a record with children which you can't do without programming it's very difficult to do all right and i have a couple of lessons yeah i do a little bit of thing with a reservation system first but copying an order detail copying an order with the details all right and it does involve some coding all right but this lesson does cover that and the reason it involves programming is because you have to copy the parent order and then you got to know what the order id is so you can copy the children right all the line items and make those refer to the new order so it's it's it's a little complicated you can definitely be done and i cover it in access developer 24. so in the extended cut for members we're going to use vba and some code to change the label in the invoice report we're not going to go through making the nested if function in the query i'm going to show you how to go right into the report into the build event change the the label caption directly without having to do anything else we're going to be using the report header build event then we're going to add this cool little paid graphic this little guy right here is a little stamp i'll show you how to make that or you can use any screen clipping you want if you got some cool clip art you want to use or whatever okay well sure we'll hide that or show that only only on a paid receipt will you see that then we're going to add some logic we're going to say if it's paid make it an invoice in other words make it basically make it a receipt in other words if the user clicks the is paid and it happens to be a quotation then make it so it's not a quotation automatically but also if it's marked paid you can't turn it into a quotation make sense you don't want someone taking you know a paid order and then click on the is quotation button and now it doesn't show up in your you know your paid orders okay that's all covered in the extended cut from members silver members and up get access to all the extended cut videos gold members can download the 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 1 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 one course more of my tech help videos or to subscribe to my channel thanks for watching this video from accesslearningzone.com you