Download Your Free MS Access Invoice Template for Product Management

Ms access invoice template free download for Product Management

welcome to another free video tutorial brought to you by access learning zone comm I am your instructor Richard Ross in today's class I'm going to show you how to build an access customer database from scratch we're going to build a basic customer table and a customer form if you'd like to download the database file associated with this video you can visit my website there's a link right there I'll also put the link in the video description below so you can click on it if you want to learn how to do this from scratch and learn how to do it the right way I suggest you build this database with me following along with the video however I know some people like to just download the database and look through it they learn that way so whatever works for you you can download a free copy of the template right there from my website let's begin by opening up Microsoft Access now this lesson will work just fine whether you're using Access 2007 or up so 2010 13 16 19 all those versions this will work just fine now the first thing we have to do is set up our blank database a brand new database and there's a couple of different options that I like the set in my new databases and I'm going to show you what those are right now so let's begin by picking blank database give it a good name I'm going to call this my customer database just customer dot accdb that's just a file extension for Microsoft Access and you may or may not see that depending on your window settings if you don't have file extensions turned on you won't see that just put in customer it's going to save this database in your Documents folder - see users richard documents you can see right there you can save it wherever you want by clicking on this browse button but that's ok for now I'm going to go ahead and click on create now if you're new to access normally access runs in this tabbed view or every table and form an object that you open goes in little tabs across the top I don't like that I like the old style that Access used to use windows inside of the main access window so we're going to make one quick change to our basic database before we continue I'm going to close down this thing here that says table 1 hit that X that closes that and now we're going to click on file and then come down to options then I want you to click on current database right here and down here where it says document window options click on overlapping windows I don't like the tabbed document interface I like overlapping windows that's really just a personal preference but I've been using access since the early 90s and that's just the way I like to build my databases I like windows I don't like tabs it's just easier to use ok now come down here in the bottom right corner click OK and now axis says you must close and reopen the current database for the specified option to take effect that's okay click on that close down your database and then open access again or you can go directly to your documents folder and open it from there I'll just open it from here since it's right there on my desktop and you should see your customer database right here up on the top if you want you can pin this by hitting the pin button here and now it'll say it's underpinned which means it'll always stay on top of your list go ahead and click on it now that'll open it up and once your database opens up the first thing you might see is this security warning it says some active content has been disabled that's just saying that access wants to make sure that this database is safe that you're opening so I'm going to click on enable content because yes access databases can be programmed to do some malicious things if you get a database from someone else in email or whatever be very careful if you open it up so don't click on that unless you're absolutely positive that this is a safe database all right so now we've got our basic database shell built this is an empty database with no objects in it and if I slide this bar over here you can see it says all access objects this is where our objects are going to go our tables or queries our forms our reports and I teach what all of those different things are in my full access classes but for today we're just going to work with a table and a form basically a table is what stores your data all the information that you put in your database is stored in one or more tables today we're going to make a customer table forms are used to work with that information in a pretty interface on the screen that's what a form is you can lay it out the way that you want and you'll see how to do it in just a few minutes here forms are not to be confused with reports reports are used for printing things on paper that's what reports are for so let's begin by creating our customer table now I'm going to click on the create option here in the menu and this menu bar pops open you may always see this menu bar open for beginner users I usually recommend that you set this so it stays open all the time for expert users you can turn that off and the way you turn it on and off is just double-click on any menu item up here for example double click on create and then it opens up the menu and stays open so for beginner users I like the tum just leave this open so you always see the options now to create a table there's many different ways to do it you can create the table using the table mode I like table design this is how I like to create a table I want to set up the fields first in my table and then go through and put the data in you can set it up a different way like an Excel spreadsheet where you start typing data I like to do it this way we're going to specify what our fields are fields or different types of data for example first name last name and things like that so I'm going to begin by typing in customer ID I like to begin every table with an ID field an ID field is basically going to be a number that access will assign to each customer in this table you don't have to worry about it but later on it let's access relate this information to other tables and I cover this in my full lessons but for now we're going to pick a data type all right I'm going to pick Auto number which is right down here an auto number basically starts at one and just counts up every time you add another record it gets a new number alright I'm gonna hit tab this is the description field it's optional you can type something in here if you want to we're going to skip that for now we don't really need that alright tab down here first name like that I don't like to use spaces in my field names later on when we get into programming and VBA it becomes very difficult to work with field names that have spaces in them so I like to go first capital first capital n for name alright tab tab and that's a short text field short text this short text in this long text short text can be anywhere from from 1 to 255 characters which should be plenty for a first name field and I show you in my later classes how you can set that number to optimize the size of your database you can bring that down to like 50 not too many first names are longer than 50 characters all right let's go last name you can put whatever different fields you want in here for your business you can put in here a company name you can put in here a family name whatever you want to put in there I'm just going to stick with the simple customers and say that each customer is a person let's put an address in so address again short Texas fine city state I'm in the USA so I'm going to go zip code just zip you can put in postal code or whatever you want to put in there in fact if you do international business with Canada England Australia whatever you can put in you know postal code or whatever to keep a generic I'm just going to keep it zip to keep it simple for today you can add a country field if you want all right phone number if you want to work with some different data types you can put in your notes all right let's drop this down and put in here long text that's a notes field long text can be pretty much as long as you want to yeah there's an upper limit but you don't worry about it you'll never hit it let's say your business does a lot of promotions and you want to be able to send out an appropriate number of whatever the promotion is movie tickets or whatever so let's say you want to know the family size for each of your customers all right family size and from the list of options over here I'm going to pick number now there's number there's large number that a lot of different kinds of numbers we're just going to go with number four now I talked about the different kinds of numbers in my full classes you might want to know how long this person has been a customer so you can send out some kind of a gift after their first anniversary or whatever so I'm gonna put in here customer cents and that will be a date time if you look at the list of options here there's a date/time field and yes there is a way to default that value to today but again I'll talk about that in a different class and yes there are tons and tons of options and access and I cover them all in my full classes it's not that I don't want to cover them now it's just that I'm trying to keep this lesson short so if I covered all those options now this would be a six hour lesson so we're just going to cover the basics real fast if you want to learn more about this stuff send me an e-mail I'll tell you what lesson it's covered in how about a credit limit all right how much credit are you going to extend this customer as a currency value currency sometimes you have old customers that for any reason stop becoming customers maybe they're deceased or maybe they moved out of the area so I'm going to put is active in here right and that's going to be a yes or no value you want to stop sending mailers and wasting postage on someone who's no longer a customer but you don't want to delete their customer record because you might still need them in your database for accounting purposes for example now there's a bunch of other field types that we're not going to talk about today this oh well the object you can put pictures of your customers right in their record there's hyperlinks you can use to put a link to their website attachments you can add files like a word document with their resume calculated fields there's all kinds of different stuff you can do these are the basics these are the fields that you're usually going to work within your tables Auto numbers short text long text number date time currency yes/no those are the fields you're going to work with 99% of the time so I'm going to hit ctrl s on my keyboard to save this table and I call this my customer t this is just my personal preference it's my own naming convention I like to end all of my tables with the letter T my query is with Q my forms with F and my reports with R that's because later on when we get into more advanced stuff sometimes you need to know whether it's a table or a query that you're working with like in your VBA code and then you got to go back and figure it out this way I know right away customer T is a table so it okay now it says there's no primary key defined it said although a primary key isn't required to timely recommend it it table must have a primary key for you to define a relationship between this table and other tables in the database do you want to create a primary key now what this is saying is access wants you to have one field in your table that is a unique identifier guess what we created one right there it's called customer ID it's an auto number we just didn't define it as a primary key just say yes and you'll see access puts this little key marker over there on the left hand side that's the key for this table in other words it's got to always be unique for each record no two can be the same but since we're using an auto number access will automatically take care of that for us and we don't have worry about it but later on when we get more advanced and we set up relationships between tables like customers and orders you need to know what that customer ID is to link it to their order but that's in a later class ok so now that I've got my table defined I'm going to go ahead and close it upper right corner right there close that table down and now you'll see it right over here in the list of all access objects under tables double-click on it now and it will open up in data entry mode let's go ahead and put a couple of records in so my cursor right now is sitting on customer ID and it says new in there that's ready and waiting for you to enter a blank new record now we can't type anything in that field it's an automatic number so hit tab put in a first-name I put myself in Richard tab cost tab my address I'll just put 101 Main Street because I'm not telling y'all where I live City Cape Coral Florida my zip code is 3 3 9 9 3 now country I'm going to leave blank what I usually do is since my business is us-based if the country field is blank it's assumed to be US because I'd say about 80% of my customers are in the US so I just hit tab phone number so I'm one six five five five one two one two no you're not getting my phone number either notes you can put whatever in here that you want a swell guy okay family size I'll put in there just one for me customer cents I'll put in just one one it'll default to the current year credit limit five thousand dollars and is active is yes I'll check that box on with a spacebar or you can click on it with the mouse tab and then I'm down to the next row to put in a second record alright one more person I'll put in James Kirk five five five Park Avenue Riverside Iowa yes I'm a Star Trek nerd a zip code five to three to seven and yes I had to Google is up to it from Riverside Iowa phone number let's see one seven oh one now I can't do that eight eight eight seven seven seven one seven oh one captain family-sized will go one customer sends to one credit limit $4,000 is active yes and so on and you can continue adding as many customers as you want again there's lots of tricks that we can cover in data entry there's ways you can default certain values for example if you want to make is active the default choice yes there's a way you can do that I cover all these in my beginner classes but for now we're going to close down this table and now we're going to build a custom reform now the customer table is okay and you can work with this there's stuff you can do in here you can you can add data in here you can make edits you can do sorting if you drop these little boxes down you can do filtering you can work with the customer table if you really want to but the real power of Microsoft Access comes between building nice forms we're going to build a form so we can lay this information out in a nice pretty way especially this is handy if other people who maybe aren't as computer savvy as you are going to be working with your database you don't really want newbies interacting with your tables that's why you want to build forms and in my later classes I show you how you can actually lock the database so all they can access is what you give them access to they can't go in and play with your tables they have to go in through your forms so let's build a customer form I'm going to click on create now there's many different ways to create forms there's this tool right here where you just click on form and it builds a form for you that's what a form basically looks like right there's different labels over here and text boxes where you can put your data in but you know what I'm not very happy with any of the results that the automatic form builders give you so let's close this and I want to save this Save Changes no there's also this thing called the form wizard which is right over here alright the form wizard lets you build a form by asking some questions for example you might not want all of the fields on the form alright you might just want a few fields so you can pick them and move them over here I'll move them all over hit next there's different layouts you can pick for your form write tabular data sheet justified I like calamari hit next do you want to open the forum and viewer enter information or modify the forums design well let's just go right to view information so I let's finish and this is what it builds for you but again I'm not really 100% happy with this layout it doesn't really look exactly the way I want now I could just modify this or I could show you how to build a forum from scratch which is what I want to do if you're in a hurry sure go ahead and throw a forum together with this guy but if you want to build a nice looking forum this is what this is the way I like to do it so let's go over here and hit close now it created a forum for us right over here on their forums called customer T just click on that and delete it let's get rid of that guy all right so we're here to learn how to build a forum the right way so let's go to create and then forum design and when you create a forum this way you get a big empty blank form with a white background all right I'm just going to grab the bottom and drag it up there so that's that's about all the form we need we don't need these big gigantic forms at access tries to create for us this should be nice and simple and compact now the first thing we have to do is tell this form where it's getting its data from it needs to know what table or later on we can pull from queries or multiple tables even but for now we'll just focus on one table we need to tell this form what table it's getting its data from so what we're going to do is this little square right here where these two ruler bars meet see that little square right there double click on that and that opens up this thing called the property sheet alright the property sheet has all kinds of different properties for this particular form all right the format of the form where its data comes from different events that's more advanced we'll talk about that later and there's some other weird things like pop-up and modal but click on the data tab and then right here you'll see a thing called a record source the record source is the table or query that this form is going to get its data from so drop this little box down and you'll see there's only one table in the database customer T go ahead and click on that and that's all we need this property sheet for now we can close it now the form knows that it's getting its data from the customer table now we're going to add controls like text boxes and labels and checkboxes and things like that to the form so we can view the data now up here under form design tools under design you'll see this toolbox there's all kinds of different controls that we can add to our form we can do these manually if we want to all right there is the text box and label and all kinds of different stuff now I cover all of these things in my full classes but for this part of the class I'm going to show you a shortcut come over here to the right and click on this box that says add existing fields click on that there's nothing wrong with using this guy this is called the field list and with the field list we can very easily put fields from the table into our form and all you have to do is click on it and drag it over watch this I'm going to click and drag and drop it right about there boom there's my customer ID field alright first name click and drag and drop it underneath the textbox portion like right there see that and if you want you can click and drag these things up into the corner here like so so you just click and drag make this a little bit wider by clicking on the side over there so you can be sized that same last name click drag and drop and I'll slide it right up there see little dots in the background that's called the grid access wants you to line your objects up nice and square on that grid all right address I'm going to slide a dress right up under here like that okay maybe widen that out a little bit if you want to bring multiple fields over at a time click on city hold the shift key down on your keyboard and then click on the last one like that city state zip and country click drag drop them all over there like that now I'm going to do that swatch I'm going to bring City right there state is usually a two-digit abbreviation alright so I'm going to click on this label this label is just going to say state on the form it's going to just tell the user what to put in there I'm going to delete this watch this delete I'm going to delete the zip and I'll leave country alone state I'm going to slide right up here like that and maybe shrink that up cuz that's just two digits usually okay and the same thing with zip code slide that right there and drop it and then shrink it up maybe cities a little bit too let's see that makes it a little bit bigger so you can move these things around nice and easily okay just like that now to tell the user what's going on I'm just going to modify this label right open the label up click in here and go city state is it just like that and you can adjust these labels so they look a little more friendly right first space name last space name and so on alright country slide it right there let's bring in the phone number right underneath here alright put the phone number there you could do phone fax cell whatever you want in fact in one of my other tutorials I show you how you can make an unlimited number of phone numbers for each contact which involves a second related table again that's a different lesson but yes you can definitely do it same thing with multiple addresses all right notes I'm going to come back to that's going to be a big note box family size can go up under here that's a short number so I'll just leave that box short customers cents right there it's very difficult to get it on the spot on the first drag alright credit limit okay and then is active when I bring this over look at that at the check box okay now notice I said I'd come back to so I'm going to slide this box out of the way just for a second here and I'm going to put notes over here on the right side and look at that it comes in as a big text box okay now we're done with the field list I'm going to close this field list and here's my notes now notes I'm going to put up here in this area over here watch this I'm going to click on the label move your mouse over that upper left corner see how it's a big box there click on that and drag it up top over the notes the note label and the note text box can be separate from each other so you can move these around but those tails they'll still stay together same but it's just you can move the place whenever it wherever you want and they'll stay the same relative distance to each other okay so right there slide notes up like that because you want more room for your notes right now I'm going to take all this stuff and I'm going to move this over here like the okay we'll slide credit limit over a touch maybe put is active up top up here right is active like that we'll check box I can bring this bottom up now because we don't need that and then this right side we can slide it in see how nice and concise this form is unlike those big giant ones that access tries to make alright maybe even click and drag a box around all of these and then slide them over to the left just a hair like that say I like to save as much space as I can not to make the forms tiny but then you can fit more stuff on the screen too the last thing I'm going to do is give this form a splash of background color alright so I'm going to click anywhere on the background notice this detail band the detail section gets highlighted come up top click on format' and then find this guy right there that's the background color I'm just going to pick something like a light blue alright see that you can pick any of these objects and change their colors who like customer ID we can't change that value so I have to gray it out so I'm going to click this and pick one of the grey colors here that just kind of visually tells the user you can't change that value alright our form is ready to save I want to click ctrl s to save it and this is going to be my customer F my customer form ok close it down and then we're going to reopen it yeah you can switch between the different views you can switch from design view back over to form view where you can edit data right there but I like to close things down and then reopen them why it's a habit that I formed when I got a little more advanced when I start doing programming because some events fire when the form opens and it's just nice to close them down and then reopen then you get a fresh start with the form double-click on this customer F and look at that there's my customer form all you have to do now is type in data just like you would before this shows you the first record all right you can tab through the fields you can make any changes that you want in here let's say for example you want to put Street in there just click on it and type here's your notes field your is active box if you want to go to the next record click on this little button right there right want to add a new record click on this little button right there to add a new blank record and you can come in here and type in and Sue Jones and so on let's say you don't have sue Jones's address tab tab tab tab tab she declined to give her phone number right tab come over here type in family size if you don't know all this information don't put it in all right didn't get customer info that's all active though and then when you're done close there you go so there's our real simple basic template we've got a customer table and a customer form and that is in a nutshell the essence of a Microsoft Access database a table to store information and a form to work with that information on the screen thank you I hope you've enjoyed this video I hope you learn something today you can find lots more templates like this one on my website there's the address right there also I'll put a link in the video description below so you can click on it I've got lots of other templates available on my website including this one this is a customer list form where we have a list of all of our customers like this and we can simply double click on one of them and it opens up that customer form I've got other templates for making a main menu a start up form a contacts form mailing labels and order entry form all kinds of different templates on my website so make sure you visit today also if you're new to access I've got a free three hour introduction to Microsoft Access I call it beginner level one you can find it on my website absolutely free it's also on my youtube channel there's a link right there again there will be a link in the description below so you can click on it absolutely free no strings attached today I just tried to cover stuff quickly but this level one lesson goes over all the basics and in good details so if you've never used access before or if you're new watch this class and if you like level one you can get level two which is another whole hour-long lesson with some more advanced stuff in it there's a link right there you get it for just one dollar alright so make sure you thumbs up drop me a comment subscribe to my channel make sure you check out my blog there's my Facebook Twitter and YouTube accounts if you have any comments or questions feel free to contact me there's a personal email address right there Emma chronic gmail comm or drop it in the comments section below and thank you once again for learning with access learning zone