Streamline Your Vehicle Invoice Template for Administration

Vehicle invoice template for Administration

welcome to another techel video brought to you by access learning zone.com I am your instructor Richard Rost in today's video going to show you how to set up a database for tracking the maintenance on your vehicles cars trucks whatever actually this database would work fine for tracking maintenance on any kind of equipment I'll show you how to set up the vehicle tables all the related information the service maintenance table and we'll do make and model we'll make that a cascading combo box so you pick the make and then you'll see just the model models for that make of car today's question comes from Marlon from akan Ohio one of my Platinum members Marlin says I'm trying to build a database in Microsoft Access to track my company's Auto Fleet and the associated maintenance for each vehicle how do you recommend I get started I'm having a hard time picturing the tables and relationships I love what you did with yesterday's videos about the cascading comb boxes could I do the same thing for car make and model of course Marlon if you're having a hard time setting up the database sometimes sometimes putting together a map of the tables and the fields for each table and the relationships between them sometimes that's the most difficult part of getting a database started I know sometimes I make it look easy in my videos because I've been doing this for 30 years but sometimes if I'm building a complicated database I'll even sit down ahead of time paper and pencil or I'll usually use Excel and I'll put together you know what tables I want what Fields I want in each table and so on Watch My Access beginner one class in beginner one I take a lot of time going through how to set up your tables in the fields I spend a good amount of time on planning your database figuring out what tables you need what Fields go in each table and then of course planning out your forms and stuff so go watch beginner one first if you haven't watch that already so for vehicle maintenance let's start with Excel let's just map out what we want the database to look like all right I need a vehicle table a vehicle T all right what fields are going to go inside this table we got the vehicle ID right that's my Auto number uh a customer ID if you want to link this to a customer if these are all vehicles just for your Fleet then you might not want a customer ID or maybe a driver ID however you want to link it to a person okay maybe a description of the vehicle okay the vehicle year don't use just the word year remember year is a reserved word okay then we've got the make now the make is going to be from another table because you want to do where you pull the make in from a list of makes like Ford Tesla Chevy whatever and then the models will show up only for that particular make so this will be from another table so the make ID and model ID those will both be IDs we'll make another table for those all right color we'll make that where you can pick from a list and type it in I'll show you how to do that too that's really easy and then license plate number maybe maybe a VIN right notes maybe a picture whatever else you want about this particular vehicle all right and I'll do something like this right let me uh me format this we'll make we'll make the vehicle table blue okay now make and model I'll bold these because those go to a different table that goes to a different table too that's the primary key all right customer ID we already got a customer table set up in my techelp template which we'll use all right what about the m right we'll make a make t okay and this table's pretty straightforward right we just got a make ID and then maybe we'll call it the make name all right that's our second table okay we'll make that one green all right then we got the Model Model T get it haha not the Model T car all right now the model will have a model ID right it's going to have a make ID that's my foreign key that goes back to the make table and then a model name see how this works all right we'll make this table I don't know yellow all right and just lay out your tables this way what else do we need we'll need a service table to track our service right our our our maintenance items as they happen all right so a service T all right this will have a service ID that'll be the instance of service right a vehicle ID so we can tie it back to the vehicle okay then a description maybe what was done oil change whatever all right a service date maybe the mileage of the vehicle at the time the service was done the cost if you're taking it to a shop and then and other notes right this will be our service table and we'll make this I don't know orange see see how this all relate together right now this guy right make ID relates to this sometimes I'll get fancy and do something like this we'll make this blue right watch we'll go which blue did I use that one no that's not the right color well here we do this one watch go like this and then maybe over here like this and then up no I can't do it right here we go like that and then format paint over that guy I get I'll get funny sometimes like this see but that's just me you can do whatever you want see all right so that's just how I like to lay out my tables if I got a complicated database so let's take this let's translate this over into access all right so set this aside print it out put it on your other screen whatever you got to do okay here I am in my techelp free template this is a free download from my website it's a good place to get started from so let's create those tables that we just set up over in the Excel spreadsheet right create table design all right we'll set up our vehicle table first so vehicle vehicle ID that's our Auto number right the customer ID I'm going to link these in this database to customers all right you don't have to if you don't want to description of the vehicle short text vehicle year I'm going to make this a number all right and I'm going to set the default value equal to the year of today's date so it's 2021 right now so the default that'll come in here will be 2021 okay and if you want to prevent people from typing in something like you want all your like let's say uh 2015s you don't want them just type in 15 right because you might get a a 1985 if you got older like classic cars and stuff so I'm going to make the validation rule be greater than or equal to what do you think the earliest year you're going to have I'll say 1950 all right greater than equal to 1950 and less than or equal to the year of today's date plus one right you can't type in anything more than 2022 sometimes at the end of 202 you can get the 2022 models right but you're not going to have a 2024 okay and then you can put in your validation text right uh must be between 1950 and next year okay see how that works that's validation rules that's neat stuff okay let's do the make ID that'll be a numberof type long integer that's my foreign key right it's going to point over to the make table model ID again another number of type long integer the color now the color I'm going to put in a short text because I I'm going to give the user a list of options to pick from like red yellow blue black gray the standard stuff but sometimes you might get like I I used to on a Jeep and it was called gunmetal gray so you might want to just you know you might want to type that in or just be vague and type in Gray whatever you want that's why you build a database yourself right so you can do whatever you want all right how about the license plate number I like it to leave it license plate I don't like calling things number that aren't always numbers like the next one Vin vehicle identification number it's not a number there's text in it I don't know who started calling it a vehicle identification number with letters in it that always drives me nuts uh and then of course notes that'll be long text formerly known as memo let's save this as the vehic vehicle I can never spell vehicle vehicle T right v h i c okay vehicle gotcha primary key yes that'll turn our Auto number into the primary key right there there we go let's close that let's set up our other supporting tables let's make the make t make ID Auto number make name short text save it make t all right we could put some we could put some sample data in this one whoops come here get get over here okay I'll just put a few in Audi and then Ford and Chevy and Jeep and you can put whatever other ones you want here okay okay so right so right let's do the Model T create table design model ID Auto number make ID that's a number that's our forign key and then the model name save Model T primary key yes now here's what we're going to do we're going to open this up and we're going to put some sample data in but I want to see the make te as well right so I can see what these are okay and we'll make a form sub form for this so you won't have to do this this is just for some sample data up front Okay Okay so we've got uh we'll start with Audi Audi A3 and maybe a Q5 okay then two a Ford Mustang and then maybe an F-150 and a ranger they still make those I think they do and how about an explorer right okay and then we'll go to three we got a Camaro and a Malibu and then a four we got a grand Cher that's what I used to drive okay so there's our make and model list all right now I'm going to make a table for colors but I'm going to break my rule all right I'm not going to make an ID field as the primary key I'm just going to put the color in short text that's it I'm going to index it no duplicates too I'll explain why in a little bit but this is just going to be color name short text and I'm going to click on the primary key field save it color T that's it all right the primary key is just so it can't have any duplicates the basically the reason why is because it's just going to be a simple list of colors I don't want it duplicated I might use this in multiple places I hate doing value list boxes where you put the list of items in the combo box on the form because then if you want to modify it you got to modify it in multiple places if you're using that on different forms if you put it in a table or even a real simple table okay we can utilize that same list if I want to add a color delete a color it it takes effect everywhere plus when I go to make a combo box out of it I don't got to worry about that ID getting in there because I also want to be able to save that text as text in the vehicle table right but I want the user to also be able to just type in whatever they want so it's much easier to do it this way watch you'll see all right so just put a list of colors in here all right we got red black blue white gray silver all right whatever that's it all right and it's it's index so you can't put two Reds in see It'll yell you okay there are some I know I say I put an auto number in every table and that's 99.9 % true sometimes I make an exception especially if it's for a little simple helper table like this not very complicated it's usually one field of some stuff that I might use in multiple places okay let's set up the vehicle form next so create actually let's go down here we've got if you're using my template file I've got a single and a continuous form that I use for new stuff down here let's use the single form I'm going to copy paste crl C control V this will be my vehicle F my vehicle form all right where'd you go right down here design view all right I'm going to open up the properties double click here slide you over you're a bit too big record Source let's bind this to the vehicle table now I can add existing Fields just bring all of them in click on the first one shift click on the last one click and drag drop them all over here all right now I just keep these guys around for format painter watch click format painter click see I like to have the ID in gray that tells the users you can't change that and then this one double click right click click click click click click click click click that's mostly just to copy the format all right now I can get rid of these two guys we can slide all you guys up like that and I like to select them all right click size to grid and then maybe we'll squeeze them all together a little bit more like this just a tiny bit get them all nice and next to each other right okay now let's make some combo box let's save this close this okay now the customer ID is going to be a combo box where I can pick the customer now I've already got one built it happens to be on my order form if you went through and built this database with me right go to the go to customers go to orders I'm going to steal this combo box right there where I could pick a customer right right click design view copy I can close this close that click here and then paste now I got a customer combo box right there see now I can get rid of this the fields are named the same so I can slide it right in there that's why I like making interchangeable parts see and there's customer okay description is the description of the vehicle let's make this a little bit bigger so we can fit stff on here okay vehicle year is fine now make and model are going to be combo boxes make is the easy one that comes from the make te by itself so I'm going to delete that guy go to form design find combo boxes now this is why I wanted you to watch the cascading combo boxes video before this one cuz I'm going to make cascading combo boxes now if you haven't watched that video yet go watch it and then come back all right go on get out of here go watch it pause this video come back to it open up a new browser window tab whatever okay all right now since you're back I assume you've watched it all right so I want the values from another table or query make t bring these both over sort by make name that's what it's going to look like the key column is hidden that's good next store that value in the make ID all right we're picking a make and we're storing it in the make ID what label do you want make all right there we go put you in your place right there little format painter action all right maybe make this bigger like that okay let's see what we got so far I'm going to slide this bottom up like that let's close this guy down save changes open it up all right we got the customer description you know uh Daddy's ride whatever the vehicle year defaults to this year and then the make okay now we're on Model Model is going to be based on what's in this make so we're going to make a query all right that picks this guy okay remember we did this in the last video so query design we're going to bring in Model T we want the model ID the model name and the make ID but we don't want to see the make ID but it's got to be equal to forms vehicle F and and oh we didn't give the combo a name oh let's back up back up cancel cancel just move this out the way here I forgot to name my combo box this is one of my pet peeves I wish that it would ask me in the Wizard for a name for this combo box and I'm leaving this in the video sometimes I back up and I rerecord this segment but I'm leaving this in the video because you guys will do this too so I want you to see that I do it almost every time I forget to give this combo box a name I don't want it I don't want to call it combo 14 right that's silly so it's going to be my make combo that's important okay cuz now in my queries I don't have to call it combo 14 what's combo 14 I don't know all right so let's go back over to the query that we started right equals forms vehicle F make combo there it is now if I run this real quick oh do I have anything in here no this is in design view all right so come back over here design view let's close this reopen it again okay now I got Chevy in this box so when I run this I see the Chevys okay that's exactly what we did in the cascading combo Box video all right save this this will be my model Q let's call it model current Q the current model all right that is shown for the current uh make that's on the vehicle form all right now I can make the model combo box design view delete you all right get a combo drop it down here same thing next we're going to base this one off of queries model current Q bring these both over next sort them by a model name next now if you remember from the last video this query can't get any values because this guy is in design view so we have to just simply shrink that column up to nothing model name is fine storing the ID in model ID next label model and while we're in here let's not forget to give this guy a nice name double click model combo format paint and that should be good and if you remember there's one more thing we have to do which we covered in the cascading combo boxes right go to make ID event after update dot dot dot slide over here the code Builder comes up remember one line of code model combo not mobel model combo. requery remember after the make changes we have to requery the model list that also has to go in the on current event for the form again this is why I told you it's important to go watch that video model combo. requery right we need that list to requery when we move from record to record and when I change the make all right save changes open it up Chevy come kamato how come nobody caught that you guys got to raise your hand and tell me when I type something in wrong Camaro okay now that should update because that is a related table there we go Chevy Camaro if I change this to Audi I can see the list of Audi's see if I go to a new record put something else in here I'll put will rker in there and he's driving uh 1945 oh can't do that remember our validation rule okay fine a 2020 what's he driving Jeep Grand Cherokee all right okay looking good so far okay let's do color now color is that field where I want to give the user the ability to type something in that they want right like uh like sunshine yellow but I also want them to be able to pick from a list of options so design view let's get rid of color and let's add a combo box but it'll be a different combo box ready watch here we go okay drop that in there all right get the values from a table of query that's fine where are we getting them from color T that's fine color name all right sort it by color that's good now there's only one column in here if I had a color ID no matter what you do the wizard is always going to put the ID in there and that's not what I want I don't want to store the color ID in the table because I want to give the user the ability to type in whatever they want and that defeats the purpose all right so this will work just fine it'll still give them this list to pick from okay store that value in color next what label color okay and we'll do a little slide that over slide you over here and some format painter okay all right now get down there now one thing you have to do well two things you have to do give this a nice combo color combo and under data make sure limit to list is set to No it should be all right because it sees there's no ID in there these guys accept a yes because you have to pick from that list limit to list no means the user can freely type in whatever they want that's one of the major benefits of a combo box if you do it this way is it can be freely edited text like a text box or you got to pick from a list like a list box that's why they call it a combo box It's a combination of a list box and a text box but you didn't know that or did you did you know that if you took my other classes you'd know that all right so what does this look like now save it close it let's open it back up again and where the other one go there's Sunshine yellow that I typed in before but you can drop this down right and you can pick still pick red but I can also type in you know Fireball fuchia is that highball fuchsia whoops oh I hit tab tab order right this is the last control that I added on the form so it's going to be last in the tab order don't forget go to design view tab order Auto order see that'll order them all nice up and down like that and then save and while we're in here let's do a little formatting let's highlight everybody format to the left save it sometimes it gets them sometimes it doesn't yeah see sometimes sometimes I I selected everybody but sometimes these number Fields still don't go over like that I think it's a bug and access I've complained about it before too no one seems to care what I think there we go that see that vehicle ID still didn't go to the left I selected it you watched me right I don't know why it does that but it does there sometimes you got to do them individually which is a real pain okay so now we got that we got the color black license plate I don't know uh Vin whatever that should be a bigger field right that could be pretty long actually let's slide these over like this because we're going to put our service maintenance items over here okay save what you got close it I'm going to put a button on the main menu for it copy paste right right down here vehicle ah I got my caps lock on vehicle maintenance right click click build event do command open form vehicle F yeah I know I'm pronouncing it weird vehicle I know it's vehicle my son when he was like three years old used to pronounce it like that vehicle okay in the uh in the cascading combo boxes video I made a form to edit the make and the model basically make is a is a parent form models a sub form inside of make and I made the list item edits so so you can edit this list and it pops up that other form so go watch that video again if you want to learn how to do that I'm not going to make separate forms for making model okay next it's time for our service maintenance items we're going to make a sub form to go right here so every time I open up a vehicle I can see all of the service that's been done to it all right but first we need to set up our table for that we didn't make the service table yet so create table design all right go back to your spreadsheet if you want to see the list service ID that's our primary key Auto number a vehicle I did it again vehicle ID number right that links it back to the vehicle a description of the service that was done right oil change rotate tires whatever and by the way this this will work you don't have to do vehicles with this you could easily change these fields around and do just you know equipment maintenance if you're doing you know the the machines in your shop or if you're you know if you run the AV club at your high school they still have those they still have AV clubs and you're supposed to you know perform maintenance on the over overhead projectors I'm old okay uh the service date and let's default that to today's date you could put now in there if you want uh to the minute um to the second actually mileage if you want the mileage reading that'll be a number cost currency and then notes long text all right save that vehicle no that vehicle service so I got mail too service T sorry it's late in the day and I've got my uh I got my my plenty of caffeine goofy streak going on right now so I'm having fun all right let's close this let's make a form for this now next let's set up the service form so continuous form right here copy paste this will be my service F okay we're going to right click design view this is my continuous form let's go into here properties record Source will be the service table okay now we can go into add existing Fields I'm going to grab we don't need either one of the IDS since this is going to be a subform the sub form object when I put it in here we'll handle the relationships and I really don't care to see the service ID on this one so I just want description down through notes so click shift click and then click and drag and drop right there all right we can get rid of these guys we can get rid of these I'm going to slide the description over here actually let's put the date first let's put the service date first then the description of what was done then the mileage that doesn't have to be that big and the cost and the notes can actually go down in the footer you could take notes and put them in the form footer and then every time you click to a different record the notes down here will update for that record if you haven't seen my sub form video go watch it that all right so there's notes down there and I like to do with my notes I Didn't Do It on the other form let's go do this real quick actually let's delete this one watch I like to make my notes this one too um if you look at my customer form customer form see this thing here actually there's a better one in the order form let's grab let's grab this one right click design I like to give my notes Fields this yellow sticky note piece of paper look copy right and and if you keep your Fields all named the same thing paste you can just paste it in there and everybody that uses your database will know hey this yellow sticky note piece of paper looking thing is for notes right there's a big notes field right there save that come over here click paste there's a notes field see keep cons stay consistent in your database doesn't matter what you do just keep everything looking the same all right let's change our labels it's like those of you who watched my other videos if I want the user to be able to double click on something right I make it like a light blue that way they know with with a little bit of training training is important duh right they they know that if they double click on a blue field it does something it could be a search function it could open up another form okay so there we go everybody looks good save it close it now I'm going to open up the vehicle form design view and drop that other form right in here as a sub form ready here's the service form click drag drop boom okay get rid of that label that comes in with it slide this over to the left maybe bring this down a little bit like that all right how's that looking so far right okay good maybe maybe let's put a label up top let's put a a label up here that says maybe uh maintenance maintenance history okay save it close it vehicle maintenance all right here's my vehicles here's my maintenance I don't think we did the tab order in here D we let's go do tab order real quick it's the little things I think about let's highlight everybody format align left go to design tab order Auto yep flip those around because I want to be able to tab tab tab through these right save it close it open it back up again okay Daddy's ride oil change at uh 3,000 miles cost was 35 bucks all right maybe uh then we got a tire rotation at 5,000 and it was 80 bucks whatever want to put a total down here so you can see the total cost to maintain each vehicle right form footer totals if you don't know how to do form footer totals go watch my video on guess what it's called form footer totals I'll put a link down in the link section let me see what's down there yep I got it down there for you already all right copy this copy paste slide it right under here come here sit all right open up the properties go to all all right instead of cost it's going to be equals the sum of cost and I want to call it sum cost like that maybe bold it too format bold all right save it close it open it up now you can see the total that each vehicle cost you to maintain next guy right will rker he did uh I don't know what he got the break job all right at uh 6,000 miles cost him 325 whatever see and now you got a complete history for each vehicle and all their maintenance items and if you got to put extra notes down here you know um mechanic mentioned might need new uh I don't know wiper blades whatever and you can see as I move from record to record right that stays in that record okay and you can do all kinds of things you can attach an image to this don't attach an image save it like I show in my images video where you save it in a folder if you want to save like your receipts okay you could save them in here or you could save a picture of the vehicle if you want to okay or if you're the one that does the maintenance maybe take a picture of it you know you do you do auto detailing take a picture of it I love it when my uh I have a lawn service cuz I live in Florida I'm not cutting my own lawn I'm sorry it's too hot in August but you know uh every time he cuts the lawn he takes a picture of it and sends it you know email just like uh one of those food delivery services where they take a picture when they leave your your food by your door right so you can't calm open so I never got it want to make a button on the customer form to go right to that customer's Vehicles let's go to the customer form let's make it like this let's move these buttons up here all right it'll work just like the other ones watch copy paste all right Vehicles rightclick build event I'm going to copy I'm even going to copy one of these the work's already been done for you watch this copy paste we just got to change the form name right vehicle F open the vehicle form where the customer ID equals the current customer that's it that's all you got to do see how easy this programming stuff is once you got someone like me to show you the little bits and pieces you have to do it's not hard I'm telling you it's not hard this is how my developer classes are too I I start you off nice and slow oh time for an ad real quick ad real quick ad then I'll get back to cool stuff developer lessons you want to learn how to develop you want to learn VBA your access databases go to a whole another level once you learn how to write VBA code and of course I got lots and lots of expert lessons below those if you don't want to get into programming I got 32 levels of expert classes tooo come check them out okay I got to sneak ads in once in a while just little teeny tiny ads any video it's over 20 minutes you can watch a 15-second ad sorry I'm just being silly anyways go to the customer list I want John loopy cards cars Right double click here see it's a blue field right I cover this in the in the video where I build this guy the blank form open it up and now hit Vehicles boom there's Jean Luke there's Daddy's ride see and you can see it's filtered will Rec's car isn't on here see how ice said is right or you can just go to the vehicle maintenance button and you can see all of them yes you can make another list form like I got the customer list right here right you could do the same kind of list form for your vehicle maintenance and show a list of all the vehicles double click on that and open up this it's all however you want to build it I'm just I can't show everything in every video so if you want to learn how I did this go watch the blank template video and you'll learn how to make this kind of a form which is basically the the same thing we did here all right with a little double click event to open up the specific vehicle do you want to learn more in the Extended Cut for members 35 minutes long we'll set up a specific service types table so you have to pick from a list of the approved service types right oil change tire rotation and so on now having a list of approved service types like this allows us to say okay an oil change has to be done every 3 months when was the last time you had one done we have it in our maintenance history now we can generate a list of future maintenance items right what has to be done and when we can calculate our costs what are we going to have to pay in the next let's say 30 days for maintenance right then I'll show you how to switch between both sub forms see that here's our maintenance history and then we come over here and click and switch the future maintenance right what's coming up here's the DAT it was last done when is it next due what's our estimated cost and how much of that are we going to have to pay in the next 30 days see that we can switch back over here to maintenance history that's a cool trick I love that trick and no this is not a tab control we're changing out sub forms all of that is covered in the Extended Cut for members silver members and up get access to all of my Extended Cut videos Gold members can download these databases how do you become a member click the join but 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 we'll get access to all of the Extended Cut techel 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 techel 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 fulllength 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 techel 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 3 hours long you can find it on my website or on my YouTube channel and if you like level one level two is just $1 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 techel videos or to subscribe to my channel thanks for watching this video from access learning zone.com