Access Invoice Template for Financial Services to Streamline Your Billing Process

Access invoice template for Financial Services

[Music] welcome to our youtube channel today under the microsoft access tutorial i am going to teach you i am going to explain you how to create invoice template using microsoft access so this is a simple invoice template and you can easily create this template by using microsoft access [Music] please follow my video step to create this inverse template and i will attach my sample invoice template with this video then you can download by following my link so i will add i'll add the i'll add download link to video description to create this invoice template i am going to create a new database so my so here i am going to give database name for my noise template file name is sample say file name is simple invoice and and here i'm going to add location for my database yes now press create now i created my database and i renamed then i created my database i named my database as simple invoice for to create this database first of all i want to design tables here i expect to create four tables then my first table is for invoice main table so in short form i name my table as inv main right this is my first [Music] table under this table right list and is a good design view then i want to design my table structure so here my first column is invoice number for invoice number i use inv number and it is you can keep it as auto number then invoice number is a description then my second column is invoice date therefore inv date then the data type is date invoice date is a description then the third column is [Music] supplier name [Music] data type is text and supplier name is the description [Music] then the other column is supply address data type is more because address is lengthy one may be a lengthy one then address is the supply address that supplier contact is the other column [Music] and the data type is they are the text contact number then supply email data type is text text supplier email so these columns come comes to you know invoice main table invoice main table then finally supplier account number supply account number is the final column of my invoice main table apply countdown yes now i created my first main table of my invoice table invoice database sample inverse database so i want to save my table press ctrl s or press data sheet view yes this is my invoice main table now first one table is okay then the second table is go to create and select table and press ctrl s to save your table so the second table is invoice subtable so my short name is inv sub sub press ok for inverse tab sub table under the design view so what are the columns i go i'm one of the columns i'm going to add so here also for invoice sub also there is a column for invoice number inv number deleted type is just a number right then the next column of my noise subtable is product id product id first column is of my invoice tab table first column is noise number and second column is product id and its data type is text or you can keep it as a number doesn't matter then the third column is product name and [Music] fourth column is unit price then its data type is currency [Music] product name and unit price so these are the invoice subtable columns then i want to add a column for quantity so here qty for quantity and it is it's i its data type is number because it is quantity number then the final one final column means sub value this is for line item values it means price multiplied by quantity just say sub val then the data type is currency yes so these are the columns of my invoice tab table so i can add descriptions like this invoice number product id then product name then you need price quantity we can say purchased quantity and this is sub value line item value it means unit price range price multiplied by quantity sub value equals to unit price multiplied by quantity like that so these are the columns i want to add for my invoice tab table then i want to remove this primary key because yeah because i want to create a one-to-many relationship with invoice main table and invoice sub table therefore i want to if i have to create i have remove primary key from the invoice table invoice number column yes now two tables okay now there are there are two tables to design there are more two more table design there are two tables design now this is my invoice up table then again [Music] create a new table and save the nameless tbl product tbl product press ok and go design view so here under the dbs product id is the primary key data type is number and we can say a product name [Music] then you need price currency is the data type so my dba product table contains three columns their product id product name and unit price now save the table so it is like that and the fourth table is dba supplier this table is used to enter the supply details so we can take the supplier name and addresses address by using this table to your invoice template go to create and click on the table then save the table first here tbl supplier is the table name so here we can add supply id supply id so i apply identification number then supply a name then supply address so supply address data type is memo then supply contact then supply email then supply account number yes so these columns we need to create supplier table so here is id means supply id and it is a primary key then supply name then supplier address supplier contact supply these are the columns i want to use for my supplier table then click on this icon now table is saved so this is the supplier table so now i have created now i created all four tables which are necessary to create inverse sample invoice template so for supplier table i can enter some details [Music] [Music] [Music] [Music] yes i entered sample data to my supplier table i entered sample data to my supplier table then close the supplier table and open the tbl product so here i am going to add sample data for my table product just assume that this business cell government items then product id for shirt is 100 and the unit price is thousand five hundred and two hundred is a product called for fox and it is 2500 in price and 300 for draft time and its product price is thousand seven hundred fifty and just say skirts spread our friendly product in price is thousand yes i filled dbl product table with the sample data so now i added sample data to tbl supply or supplier table and table product and the product table because these sample informations are useful to test our database so therefore at the very first beginning i entered sample data to my tbr product and tbs supplier now now already i have created four tables invoice main invoice sub tbl product tbl supplier so invoice main table invoice subtable table product table and the table supplier table so these are the four tables i'm going to use to create my [Music] uh simple invoice template [Music] [Music] then the next step is to create relationship to create relationship among tables what should i do you have to click on the database tools tab so once you click on the database tools tab you can see a button called relationships so you have to click on it so once you have click on the latency button then you can drag the desired tables to this window now i want to create a relationship in between dbl main and table sub so my resistive type is one too many so i have assigned primary key for my invoice main table in which number and i have not had i have not placed primary key for my sub table then to create relationship want to main relationship what shall i do i should drag invoice number from invoice main table to the invoice subtable invoice number so like this so once you have read like that then you have a pop-up then you have a dialog box like this so in that dialog box you can edit your relationship so here there's a check box called enforce referential integrity so we have to check this referential integrity checkbox and here the relationship is displayed so my relations type is one to many now it is correct now create the relationship among these two tables you have to click create so once you have create once you have successfully create relationship among these two tables you have you can see a link like this so this is [Music] the this is a one too many relationship this is type if it's one to many and here the special thing is that you you can't create a one-to-many relationship if you assign primary key for invoice number in invoice up table there you can and primary key so if you add a primary key then you can't create one too many relationship so if you want to create a want to relationship here you should not assign primary key for your invoice subtable invoice number now if you want to edit your relationship what you should do you should right click on this line and click edit relationship so once you have click on the edit you can see same window like that so then there you can edit relationship ing to need on the other hand if you want to remove the relationship what you should do is once you have right click on this line then you have a menu like this then there you can see option called delete so if you press the delete then it will ask you will you will be asked are you sure you want to permanently delete the selected relationship from your video if you press yes then permanently your relationship will delete yes yes now i have created one-to-many relationship among invoice main table and invoice subtable so that is the second step now relationship now the relationship part is okay on the other hand i want to tell some one thing to create a relationship with these two tables the data type of the two tables link columns should be equal for example here i am here for invoice number under the invoice main table i have given number data type is number my invoice main table invoice domain table inverse number column data type is number and also invoice subtable invoice tab table you can see a column called invoice number so the other data type also number so the data type of two tables must be equal to create a relationship like this so it is something you should remember when you are creating or when you create relationships among tables now the relationship part is okay and just save your relationship and clause now click on the the invoice main table so once you [Music] enter the data to your invoice main table and sub table then the plus mark will appear here then you can access your invoice subtable so that kind of plasma comes because of that one-to-many relationship so i'll see you later now relationship is created successfully and the four designs and the four tables have created correctly now [Music] what is the next step what is the next step so next step is to create form based on these tables next step is to create the forms based on this table [Music] to create form so here i am going to take a blank form i am going to take a plank from i am going to add a blank form then click on the create ribbon then click on the read tab and select blank form then press ctrl s here you can save your form so here my name for my form is fra main yes now i created my form then here to design my form edit form just right click on the empty space and then select design view so once you have clicked the design view you have uh from this for your form opens in design view so here then again right click and select form header footer then you can see your form once you click on the header folder by right clicking by right clicking you can select form header and what is once you click form header and put form header here the form footer and this is the detail section right now here i want to add a label like this invoice [Music] template invoice template then you can increase the font size here and on color and alignments again bald label yes if you want you can change the color here in form header section color can be changed by right clicking and select this option fill slash back color so there i would like to uh keep my back coloring let's see yes this color you can change your color like that you can change the foam header color record into your desire ing to uh ing to your wish yes now [Music] foreign form [Music] is going to be designed now the main form is we are going to design the main form of our invoice template database [Applause] then then again click this this place yeah so once you click on this place and right click then select properties once you select properties then form property box home property sheet appear from here right hand side then there under the property sheet there's a tab called data so under this uh data tab okay under this data tab you can select the table which is used to which is relevant to foreign so fra main is linked with invoice main table yes now i add the control so now i selected the if invoice main table as record source of my fra main form yes now it is working now now you can see we have successfully added record source for my main frame main form [Music] then under the design view then go to designer then you can add labels and text boxes ing to your needs so here i am going to add a this one frame cancel yes then you can resize your form ing to your design so what are the text boxes i should use for my fra main so click on this text boxes click on this text click on this text box and keep it on the detail section of your main form [Music] right now this text box just say fontiz 30 and the font color is black so this text box is to view invoice number invoice number invoice number invoice number yes then here you can rename your text box name here so it is very easy if you rename your text box then later when you use vba part when you are when you use if you want to use vba part then if you name your text boxes properly then it is very easy then select this text box and so under the other tab of your property sheet then i'm going to rename this textbook as txt inv number invoice textbox number txt inv number so that's the name of my text box so the ini so this text box will display the invoice number so it is [Music] it is to show the invoice number of your invoice main table fine and and also [Music] you can add invoice number by using this text box then then copy and paste the same text box and this is for invoice date just say that then again go to the other tab so my name of this textbook is txt date and the control to change the control source click on the data tab and select invoice date yes don't forget to save within the process now other textbook next text box is two is for i think guys for supplier name just say [Music] for supply name here i can add and what we call combo box for supply name i can add this comb box because by by a combo box i can select supplier name add a combo box for supply name then under the so once you add the combo box then you have a menu like this when you have a dialog box like this there you yeah please select uh first one i want to i want the combox to get the values from another table or query then click next select the tbl supplier then you have to select supply idn supply name or you can add supplier name [Music] oh both so here i would like to add supply id supply names click next next hide the key column so unchecks your checkbox then next when you select a row in the combo box you can store value that value from that raw in your database or you can use the value later to perform an action choose a field that uniquely identifies the row which column in the chrome box contains the value you want to store or use in your database so i want to store supply name therefore click next then next then click finish then place your combo box supply name combo box in a proper place this is supplier name [Music] [Music] do [Music] supply name which is something like that so the control source is so it's name supply name and you can rename your combobox name cbo name cbs name review his name is a view supply name right then i think i have added another text boxes for other table columns and this one is for supply address just say txt a double dre address then there are control sources select the control source like that s address we can adjust the table later then this is for supplier contact supplier contact [Music] so contact and the next text box is for supply email i say the control source then rename the label to email right the final one final one is for supply account number now under the data tab select the suppliers account number and rename the table as account number right then you have to rename your text box as txt txt acc and account number [Music] then txt email then txt contact contact yes now the noise main there in the invoice main form interfaces like this you can adjust the interface ing to your needs so this can be done by selecting layout view so layout view you can change the [Music] form a layout right [Music] do [Music] [Music] yes now click this click the form view now this is how my form main form like looks like okay so before now i design main form for my noise template then the next one next thing save this one and close the invoice close the main form then i want to create a sub form for my sub table then go to create tab again select blank form then ctrl s to save the form form sub form right click and press design view then here also i want to add a home header right click select form header form header then this is my for sub form and this is this this form is act as a child form so this form is act as a sub form of sub before then here also i want to set the control source so uh click here this corner square and right click and select properties so my sub form [Music] control source property is like this select the data tab then go to record source then click on this drop down down arrow and select inv or in subtable yes then again go to design view now i am going to add text boxes for my sub form here the first one i think first one is product id for product id i am going to [Music] use combo box so once you add the combo box on the detailed section then you have then you can see combo box wizard they are select abl product and click next then select product id and product name yes these are the two columns i need for my combo box then next next yes next then finish so that you have to adjust your combo box ing to your knees then for this combo box also i won't say the control source so select the data tap and [Music] select a combo box edit combo box and its control source is product id and i want to change the font color and font size right then i need additional one two three three column three three text boxes i need additional three six uh text boxes so here you can change the combo box name cbo p id right product right then go to design tab and select text box [Music] yeah this textbook is display txt item name txt name item txt product name p name right then go click on the other sorry data tap and select the product name font size is 12 and font color is black [Music] now product id and product name okay now copy and paste product name rename textbooks and change it to unit price and then click the other type and name rene the textbox name txt price right then you can place this text post here and resize with exposure ing to the ing to requirement then copy this text box and paste this is for quantity txt qty so it is the name of my quantity text box and click data and change the control source to quantity right then the final one is for sub value control sources software then the text box name is just a txt sub well txt software and place your text box in the correct position just now we created most of the subform then drag this like that then here you have to do [Music] um then here what you should do is you have to go to the all select the sub form and click on the all tab so under the all tab you can see [Music] under the some form all tab all property sheet that's the tab called all so there you can see default view so by default single form design is selected so here you have to select continuous form we have select continuous form yes we select the continuous form then press ctrl s save your platform so this is the subform we have created yes now [Music] now open your fra main table and right click select design view now then we are going to add our sub form here we are going to add our sub form here so you can do this by clicking this one you see subform report icon [Music] so once you click on the subform icon from design view you can see here sub form wizard you can see sub form user it automatically popups so under this upfront wizard use existing table look where use an existing form so here use existing form as a sub form okay now you have to select you you have to choose use an existing form option so by default are be selected then click next then next then finish now we have added the sub created subform to our main form okay now i think it is clear to you right now go to the forum view here you can see our subform so you can modify your invoice table subform part here you can add frame for your sub form yes your sub form is like this then you can do more just for adjustments by clicking by right clicking and selecting a layout view you can do other adjustments where necessary like this and you can change this up position like this so to do this you have to select layout view [Music] then ctrl s then close main form then again go to subform click click check select this up form and right click and select design view so here you can add labels so here you can see this one is for product id product id [Music] and this one is for product name and this label is for unit price and this one is for quantity this is for value then select all textbooks assigned you can change the color and font size [Music] [Music] oh then ctrl s is for save your database and how to save your database by making these adjustments then click close then again go to free main see what has happened now your labels the edit labels is shown like that now we have created main form and subform and we added subform to our main form we added subform to main form so now it is look like this you see once we select record automatically it moves to new line because this is happened because we select continuous form option under the default view so that is why it looks like this okay to reduce the gap you can reduce the gap here [Music] reduce the gap by right click and go to design you select these forms from subbing right click and select the design view then here adjust this one reduce the space and save then close then again go to the frame main then select record yeah now the gap is minimized our gap is there now the basic structure or the basic design of our invoice tape plate is almost done now the form and table parts and the relationship part are okay now we have a small coding part for our combo boxes and we have to small vba coding part [Music] then we can create invoice report after this after completing these mine adjustments then we can create invoice report after that okay i added new combo box to my invoice template here i added a new combo box it is for customer id and actually it is unbound text box so when i select this custom id what happens it fills these text boxes so actually it's a little bit little bit easy and that is why i created new combo box unbound combo box so let's see what happens when i select customer id from this unbound combo box go to form view now here i am going to select [Music] number one samantha era sound press customize is number one so once i click on this select this one what happened it fills these lines so this step uh details are taken from specially customer name and the address address and contact details are taken from custom table right now inverse number is auto generated and you can add the date like this so this is how it goes then to do this i used vlookup function so right click on the customer id com i mean right click the right click on this combo box and go to build event so i added this function vba function will be record to accomplish this this task so here yeah this is the comb box ah i mean after updating i added vb recording and you can see this is the form text box and this is the supplier tables supplier name column and this is table supply table and this is the id and this is the form combo box so this is how i use the lookup function to generate data records based on the customer id yes then i use same method to get the product details also this is the frame sub invoices up form and i use uh lookup function to get the product details also it is like this there i used [Music] cbo product id after update event then edit these codes [Music] it is quite similar to first function i discussed then then just assume someone let's assume someone payroll right he's going to buy buy a shirt and the quantity is 2 and the row value is 3 000 and also is by going to buy half ton quantities to say one yes then the total value is four thousand so this is how my noise templates feels based on the 11 records then once you fill this once you fill the template like this then you have to press the add button so once you press that button these records go to the relevant table tables then my next aimee was to create report for this invoice template so before creating report i had to create in my query so my query name is qry invoice so i am going to show you how my query look like so just click on this query invoice and select design view so this is my query and i take and i took details from main table and sub table so then [Music] there are some columns based on the main table and some columns are based on the sub table special product details are relates to sub table so this is how my query looks like yes so once i create my query my next my next aim was to create report then this is my report right click and design view so this is my report and this is the report header and it includes company details company contact details and contact numbers and this is the page header so the page header contains the main table details and this is the detail section of a report so this section includes the sub product detail details i mean this is relates with the sub table so again i use some function at the inside the report footer so then i get the summation of sub value sub value column like this so this is the function then i designed my table like this so then i design my report like this so it includes all the necessary details based on the relevant tables then then i selected query invoice and query invoice as record source my repo so for my report so this is the qr invoice query and this is the select this is the record source yes now the report designing part and the query designing part is okay so what i want to do next is next i want to uh generate report based on the combo box value so once i select the invoice number it should load or it should preview my report 11 report automatically so to do the to do this i added unbound combobox and i linked unbound combobox with invoice table inverse table invoice column i selected the unbound combo box with invoice main table and its invoice column so then i added a small coding for uh save your report after update event so this is the coding i used to generate report based on the combobox value so this is the report name and this is the preview mod of my report so this is the vba function i used to open the report automatically and it is included under cbo report after update event right [Music] then again to get that specific records from the what we called this combo box i had to include small criteria to my qry noise so i'll show that just right click on the qri invoice and go design view so this is the this is the code i included under the criteria section of the uh invoice column so this one this function is like this so here i gave the cbo report combo box name and this is the link to combobox forms forum frm invoice then if rm invoice includes cbo report so this is the path this is the criteria i use to generate report automatically just close the query so i'll show you how it works now now now we have three invoices already entered we have already entered three invoices 13 14 15 so just i want to generate invoice number 15 so if i want to generate invoice number 15 what should i do i just want to select the number like this so once i select the number it automatically load the relevant invoice just like this so it includes all the uh details and correct invoice number the invoice is open under the preview report preview so just say i want to open uh number 30 invoice so here you have to select the number 13 from the drop down menu or from the combo box you want to select the number 13 invoice number 13 noise so it opens the 11 invoice this is the noise number and invoice date these are the company contact details and this is supply details and these are the details possessed by this customer again i am going to open num 14 invest number 14 so once you select the invoice number 14 it opens inverse number 14 like this so it opens in a preview mod so this is how i design my invoice template using microsoft access actually this is very simple invoice i think some i think you may helpful this video and you may be able to get some knowledge addition knowledge and i think you will be able to develop advanced invoice template by expand by starting this video then don't forget to subscribe rover youtube channel for more videos please stay with us so if you need any clarification regarding this process invoice creating or creating invoice template process if you are in question please put a comment under the comment section so i will reply as soon as possible and also i will include the sample template [Music] i will put download link to get the sample file sample access file so i think it is helpful to you then again don't forget to subscribe my channel for our youtube channel then let's meet with another video bye