Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, I am going to show you how to build a help desk trouble ticket system in Microsoft Access.

Today's question comes from Bryce in Independence, Missouri, one of my platinum members. Bryce says, I run a tech support call center help desk. Right now, we're just using email to track everything and it's a mess. I bet it is. Will you show me how to put together a database to track tickets so you can get a list of what's open, what's closed, what still needs to be billed, and so on? It would also be nice if we could sort them by priority. If this guy's whole system is down, he should get first priority. If that guy's speakers are making a funny noise, he can wait. That kind of stuff.

Well sure Bryce, let's go ahead and run through this real quick.

First up, a couple of prerequisites. You should know relationships because we're going to have to make some different relationships between multiple tables. Go watch my relationships video first if you haven't already. It's free. It's on my website right there or on my YouTube channel. I'll put links down below in the description down below the video. Go click on those.

You should also know how to make continuous forms and relational combo boxes. That's where you use a combo box to pick a value from a different table.

I also recommend you go watch my invoicing video. It's free again. A lot of the same concepts that we're going to cover now also work with invoicing. Whether you're doing an order form and the order details and relating that to a customer, or whether you're tracking vehicles, or whether you're tracking tickets and ticket detail items, it's all pretty much the same stuff.

Working with Access is like playing with Legos, like I always say. I'm just going to show you how to put different parts together, and you can arrange them any way that you want. So we'll do pretty much the same thing we did in the invoicing video here. I'm going to add a couple of extra things for the people who have already watched invoicing. But go watch that first if you can and then come back to this one.

So here I am in my TechHelp free template. This is a free database template. You can grab it off my website. If you watched the invoicing video, then you've got a copy of this already. So I'm going to use this to modify because a lot of the groundwork is already done with databases already built. We have tables and forms you can use.

Let's add the tables that we need for our ticketing. People always say, what tables do I need to put together? Tables are important, and like I always say in my Access Beginner 1 course, you should lay out your tables first on paper. But these are pretty straightforward.

So let's go to table design. We're going to do our ticketing table first, the overall ticket. We have the ticket ID. That's our autonumber, our primary key. We have a customer ID, which is a number of type long integer. That's our foreign key, so we can relate this back to a customer.

We've got created date, the date that it was created. I'll use a default value of =Date. If you want to the minute, to the second, put Now in there instead. Date is fine. Maybe a due date, which is optional. If it's just get to it whenever, then leave due date blank. If this guy has to have a system fixed by Wednesday, it's due then, so put a due date in there.

Now, we're going to make a couple of helper tables. We're going to do priority and status. So I'm going to put a priority ID in here. That'll also be a number. That'll be the priority: low, medium, high, for example. That way we can sort them by priority, get the urgent ones done first.

Then we'll also do a status ID. We'll make a separate status table. That'll be a number as well. Status will be things like active, escalated, canceled, completed, invoiced, that kind of stuff, waiting on the client.

I used to do text before back in the 90s. I don't know how many times I had to wait on the client. You put something in there, tell them what to do, say, try this and come back. Of course, this was before the internet was big. So it would always be, I'll go try that and I'll call you back. I put waiting on client there. If it sat waiting on client for two or three days, I assumed it was good.

Then of course we have a description, which will be short text, and then notes, which will be long text. An overall description and then some big notes, whatever you want to put down here.

Save this as TicketT, the master table, the parent table.

Now we're going to make a TicketDetailT just like orders have orders and order details. The order table is the overall stuff about the whole order: the customer, the date it was placed, that kind of stuff. The order details are the line items. He bought a mouse, he bought a keyboard. So our ticket detail will be all the different things that you did for this ticket.

You might, for example, have a phone call today, on-site visit tomorrow, and then when it's all done, you bill the whole ticket. At least that's how we used to do it back in the day.

So let's close this and create another table, table design. We're going to have a ticket detail ID. That's our autonumber and this will relate back to a ticket. There's our foreign key: primary key, foreign key.

Now we've got the service date time. If you want to put a date and time in there, I'll default this to Now. That's to the minute. If you're on the phone with them, you type in, OK, he just called, boom, the time goes in there as well: date and time. Whereas the overall ticket, the date it was opened is fine.

Again, a description. Short text is fine. If you want to put notes for each item, that's up to you. You can put notes in here too.

Then the hours - how many hours are you going to bill for this? This will be a number and I'm going to set this to type double. Of all these different types, I pretty much almost always use long integer or double. Long integer for numbers that don't need a floating point, double for things that do. Those are my two go-to ones. Yes, there's byte, integer, single, and decimal.

Don't worry about replication ID, but just stick to long integer and double. For 99 percent of databases, that's all you need. You don't have to worry about single bytes unless you're doing a gigantic database with tons and tons of data and every byte counts. With today's hard drives, just trust me: long integer and double. Double if it's a floating point.

Default value: I'm going to make that one by default. Let's say by default, everything gets billed in 15 minute increments. So if you don't put anything in there, it's .25, .25 of an hour. See how that works? If it's going to be changed, put it in there. Every phone call is 15 minutes by default unless they change it. Or you can even change it to just a minute if that's what you want to do.

Now, you also have to be careful because "hour" by itself is a reserved word. So on second thought, I'm going to make this "billable hours." You might be on the phone with them for half an hour but only bill for 10 minutes. That's up to you, if you want to get fired, or if it's a good customer.

So save this. This will be my TicketDetailT, the ticket detail table.

Now I like to put some sample data in.

Let's take a look at my customer table. We have customers 1, 2, 3. I'm customer 1, Jim Kirk is customer 2. Just so I know what I'm dealing with.

Tickets. Customer 1 called on today, 1/9/22. No due date. For the priority, we're going to make the priority table in a minute. We're going to go with low, medium, high, and urgent. Let's say this one is medium priority, so we'll go 2.

Status: we're going to have active, waiting on client, escalated, and a bunch of other ones. Let's say active is 1, so put a 1 in there.

Description: What's my problem? My problem is "computer won't start." I'm going to have to cap it: "I won't start." Notes, don't worry about that, that's optional.

Ticket 2: Let's say it's for customer 3. Who's customer 3? Oh, Deanna Troi. She calls up. So 3. And we're actually typing in one that's a couple of days old, so this is from 1/7. Same priority, same status. And she has a ... can't ... your computer's ... thoughts ... yeah, I'm silly, I know.

So there are my two tickets that I have in the system. Let's put in some ticket details.

Ticket details: Now, ticket 1. "Spoke on phone. Gave tips." Told them stuff to try. This was half an hour.

Then Deanna called in, so ticket 2.

She called back again. "More chat, blah blah blah." 15 minutes.

Then I called back in, so on ticket 1. See how this works? "Problem resolved." You'll change the status, whatever.

So I like to have some sample data. There's my ticket: ticket 1, ticket 1. And then there's ticket 2, ticket 2. See how this works?

Again, if you watched my invoicing database, I go into this in a lot more detail, how this relates back together.

Save changes to that.

Now, let's put together the helper tables. Create, table design. Let's do the priority table first. We have a priority ID, autonumber, and a description. Save this as my priority table.

What are we going to have in here? We have priority: low, medium, high, and urgent. Those are our priorities. That'll be in a combo box on the ticket.

Then we'll create another table. This will be the status ID, autonumber, and a description. Save it: StatusT.

Put some data in here.

What's our status? We have status of: active - brand new open one, waiting on client, escalated (if you have to send this up the chain), and you can have a tech like your level two techs will just look at the escalated stuff first. Canceled - I don't like deleting tickets at all, ever, so I don't let people delete tickets. They can mark them canceled. That way they're still in the system. We don't want our frontline users deleting data, ever. Orders get canceled. Customers get marked inactive. You don't delete something.

I have a whole separate video on why you shouldn't delete data. I'll put a link to that video down below. You should watch it. It's pretty good.

Then after canceled, we have closed.In other words, it's done. Customers are happy. Then we got invoiced. That's the final step because you want to be able to go through all your closed stuff and then bill those customers for that time.

Now, you know everything's active, then it's closed, and then invoiced. All right. And a proper procedure. But you can mark them all this way.

So there's our status tables.

Now, one of the nice things about having my blank template here, my default template, is that I got a lot of all of this work already done. We can repurpose the order form and the order detail form for our tickets. Just use pretty much the same stuff that we already have built.

So let's go ahead and take the order form. I'm going to copy this and paste it. Control C, Control V. I'm going to call this my ticket form.

We're going to edit this guy. We're going to design view, and we're going to change this so we're working with tickets instead.

I'm going to delete the subform right now. Delete.

Let's open up the properties. We're going to change the record source to the ticket T.

Now, when you do that, you're going to notice some of the fields are okay, like description, customer ID. These are all the same. These ones are the same. This one's the same. Is pay we don't need. We don't have any yes/no values, so we can get rid of that.

But notes, description, and customer ID are all in that table. That's why I like to keep the same field names between my tables.

There's no problem with having a customer ID in multiple other tables and forms. Same with description, same with notes.

Now, order ID can become ticket ID. Just go to your control source, drop this down, ticket ID. Don't forget to change it there and up here in the name too. Ticket ID. And then change this guy. Ticket ID.

Notice the little green thingy goes away.

Order date is now, let's go with created date. Drop this guy down and go with created date right there. Copy. Paste. Created date.

We have one more date too. Let's copy this guy. Copy, paste. Slide up there. Down. And this will be the other date, which will be due date. Copy.

I forgot one too. One spot I forgot. We can put a closed date in there if you want to. It's sometimes handy for metrics.

Let's do that too. So closed. Or this is due date. Let's put a closed date in there. I'm going to save this.

I wasn't thinking you should probably also track when it's closed. Let's go back to our table. Ticket ID. Design view. And I'm going to put closed date. Date/time.

I like to keep all my like stuff together, so I'm going to put this right under due date. So I moved it. Click, drag.

I cover that in the Access Beginner 1. If you haven't watched my Access Beginner 1 class, watch it. It's free. It's four hours long. It teaches all the basics.

Back to my form. Back to my ticket form. Design view. And we need another date in here, don't we? So copy, paste. This will be now the closed date.

Copy, paste.

Now I'm going to put I'm going to save this invoice button. We're not going to do it now. I might do it in the extended cut for the members. But I'm going to make a button. We just click the button and it pops the end. It puts all this into an order. It pops in order for the person. We'll do that in the extended cut perhaps.

Description I'm going to slide over here. There's the description. And the customer can be right under that. Then notes can go there.

These are overall notes up on the ticket.

Looks pretty good so far. Slide this in. We're going to put the subform here for the ticket information.

Now, I don't like my forms looking like each other. So let's change this background color just a little bit. Let's go from green. Let's make our ticketing system yellow.

How's that look? That's too bad. I don't know if I'm happy with that. Let's go with dark blue. Yeah. Let's go with dark blue or something else dark blue? I don't know. I don't think so. Customers are what? Customers are light blue. You have it. You can go dark blue. Let's go dark blue for our ticketing system. I like to have everything kind of like, you know, everything gets its own kind of color. Like all the ordering stuff is green. All the customer stuff is light blue.

We have to change the foreground color, our labels here just a little bit. That's readable. There we go.

Not bad. Save it. Close it. Peek. Everything looks good.

I think the description needs more space though. Let's do a little rearranging. Let's move the dates out of here for a minute. Let's put description here. Slide customer ID up and description can come across that whole field. That looks better.

Then we can put our dates below it. Now let's put the dates back over here. Then the notes can go over here. You just want to make it so it's nice and friendly for your users. That looks a lot better. I like that better.

We have room out here for the ticketing.

Save that. Let's see what it looks like now.

Good, good, good. Maybe bold this.

Much better. Beautiful.

Let's do the details. So let's close this. I know we got more fields to put on here. I'm getting there. Thanks for raising your hand and reminding me.

Let's do the ticket details. So I'm going to borrow my order detail F. Copy. Paste. Ticket detail F.

Let's open you up to the same kind of thing. Design view. Let's start with our colors. Change this background color first, with that and that. I'll select these guys and make them white.

This one has the ticket detail ID. We got to change where we're going to have data from here. Let's change this to the ticket fill T right there.

A lot of these forms, a lot of these fields change now. So we've got the ticket detail ID, which is this thing here. That's going to be the order detail ID. It's going to go to the ticket detail ID. Change that.

And again, remember from the other class, we don't need to see the ticket detail ID. We just need to have it on the form.

This is the ticket ID. That's our foreign key. We'll change that.

Notes is the same.

Now up here, we've got the service date/time and the description. So let's do the time first over here. We'll change this one to service date/time. Copy, paste. This doesn't need to be quite that big. Let's slide it to about there.

We'll call this date/time.

Change our background coloring. Go to that.

Next up is the description field. I like this in here. We can get rid of that because we don't need extended price. This will be hours, and I'll slide that out there.

So this will be description, and then hour. That will be description. Copy, paste. I'll have to line that. Description.

I often find it's easier to just repurpose all other forms from scratch all the time.

This one will be the hours. Where do you go? Billable hours right there. Copy, paste. The label can just say hours.

Now you are going to be the sum of the billable hours. So this will be the sum of billable hours. I'll just copy that much of it there. Put it here.

Save it. Let's close it and take a peek. See what we got.

Looks good. Oh, that alternating background color. I forgot about that. Design view. There. Alternate background color. No color.

One more pet peeve that I have is this stupid thing. I think that, and I've suggested this to Microsoft, they need to move this little box here, the date picker. Instead of blocking the next field, move it over the date field. That's what it should do.

For this, I don't think we need to be able to pick a calendar date. This is going to be something automatically filled in. But I'm going to turn it off. Show date picker: Never.

Save it. Close it. Open it up. Looks good. In fact, I'm going to make this a tad wider because if we get a longer date in here, it won't fit.

So design view. Let's shrink this down just a wee bit and make this just a wee bit bigger. Save it. Close it. Take a peek.

Looks good. Let's left align the date. Format: Left. Save. Close. Looks good.

We've got all of the fields for this one in here. We've got the description. How's our tab order? Tab order is fine. We've got notes down below if we need bigger notes. We've got the total on here, but this should not be formatted as currency it was currency in the last table. So that's an easy fix.

Click, and click, and we'll just get rid of that format. Delete. Let's go decimal places: 2.

Save it. Close it. Open it back up again.

Looks good.

If you want to force it to be in two decimal places, do this: go design view, pick both of these, go for the format, and go 0.00. That'll make sure you've got that in there as a fix, like that. It's the same thing as fix: 0.00.

Let's put this inside of this. We'll drop it right inside of there.

So design view. Close you up. Click, drag, drop. That's all you got to do to put a subform inside of that one.

If you don't know much about subforms, I do have a whole separate video. I just finished recording it. I had an older tip video on subforms. That was, it got me by. It was old. It was like 2009 old. So I just redid that as a TechHelp a little while ago. I'll put a link to the subforms video in the list of links down below too. You can go watch that if you want to. You can learn more about subforms. I probably should have had them in the prerequisites, but I'm pretty sure that the subforms video is a prerequisite of the invoicing video.

That's why I don't always list all of the prerequisites because by the time you watch invoicing, you've got to watch its prerequisite. So I know. Yeah. It was like a million other things before it.

Let's widen this out just so it's all uniform like that. We'll shrink you a little bit. I'm going to save this invoicing button for the members later. We got some more fields to put up here too, don't we?

So let's save this. Let's close it. Let's take another peek at it.

Looks pretty good. I'm liking it so far. There's ticket one. There's ticket two. See how the relationship is made automatically by that subform? I don't like the subform wizard. I never use it.

But notice the link master and child fields change to ticket ID. When you drop in that subform, it saw that there was a ticket ID in both of these fields/forms.

Excuse me. And I forgot to change you too. Close.

Got a couple of combo boxes just to make. Now the fields are missing our priority and status. So let's do this here.I'm going to take the notes and slide them over here. You want to take big notes about the entire ticket like that. Nothing wrong with that. Maybe we can get about that big. Now I got more room in here for more combo blocks. Let's move the description down and slide the states up like that. And now I got more room in here for more combo blocks.

This is all about aesthetics. Like I said, I have a whole other video on form design aesthetics too. I cover all kinds of stuff you can do to make your forms look cool. We got two combo boxes coming. Find a combo box guide. I do like the combo box wizard. The combo box wizard is a good wizard.

So, I'm going to look up the values from a table or query. Again, this is the relational combo box video that you should have watched. Let's do priority first: Priority T. Bring over both fields. Let's sort by Priority ID. I want them in the order that I put them in the table. 1, 2, 3, 4. Next. Alright, they are going to show up in this order now: low, medium, high, origin. See here, you do not want these alphabetical.

Let's leave it like this. I was going to say you might want to even show the key, but now let's leave the key hidden. Alright, that looks good. Next, store that in Priority. Alright, and then the label: Priority. Okay, there we go. Slide you out. Move you under there and change that color. Think nice and fast.

Alright, one more. Form it. Design. Combo box. We need to do the status the same way. Alright, same thing. Next. Status T. Next. Bring them both over. This one we're going to swap by description. Next. Yeah, we'll do it like that because they can happen in any order, really. Next. And then store that in the Status ID. That's a Status ID of the ticket table. Status. Slide over there. Slide you under there.

Wait, so we can read it. Oh, wrong one. Undo. Click. There we go.

Okay, save it.

Okay, that's it.

Okay, let's do it.

Okay, let's do it.

Okay, let's do it.

Okay, let's do it.

Save it. Alright, let's close this. Let's open it back up again. Alright, looks pretty good. Pretty good.

I'm thinking let's put default values in for these boxes too. Because if we go to a new one now, we need a default priority and a default status. So let's go back to the table. Default status is going to be... where is my status list? Status 1 will be active. Set that default value there to 1. And the default priority is going to be medium, 2. There.

And I am going to do it so that when you open up a ticket, it will start you on the description field.

Okay, so let's go into design view again and let's change the tab order. Let's auto order first. I always like to change my tab order. I got combo 19, combo 21. I do this a lot. I'll build a combo box or drop a button in and I'll forget to give it a good name. Is combo 19 a good name? No, I can't tell what that is by looking at it. But don't forget, take a tip from Alex. Alex is my number one. You got to put in here a valid name.

Now you could call this priority ID if you want to. I like to call it priority combo. The reason why is because a lot of people ask me this. I just call it priority ID. The reason why is because when you are doing programming sometimes, if it's priority combo, you know you can handle a combo differently. A lot of my tips and things I tell you to do, I do not always explain at the beginner level because you will not get it.

But when you are writing VBA code, if it's priority ID, I know that it's a number of type long integer and I have to treat it like a text box. If I see priority combo, then I know that I can access its columns in VBA. I can do all kinds of different things because it's a combo box. It's still that ID, but it's a combo box. I can mess with it differently. I do not have to stop and go, wait a minute, was that a text box or a combo box?

Alright, so this is going to be my status combo. Let me just drop in random tips. That is what these TechHelp videos are all about. Folks, just random stuff, just off the top of my head. Do not get used to it.

Alright, so now let's go back to what we were doing a minute ago. Let's go back to our tab order. Click, do tab order. There we are.

Okay, so we are going to start the tab order with the description. But I still want the rest of this stuff in the tab order. When we open up, I want to start on description. Where is description? Right there. Pull description up top.

Now after I'm here at description and I hit tab, I want to go into the subform. So that is going to be the ticket detail F. That's going to be next. I am going to type in this and then drop right in here to start typing stuff in. But if the user clicks on any one of these things to change them, I want the rest of the tab order to behave normally.

So ticket ID, customer combo is fine. Actually, let's go down across, then over the notes. So we are going to go to created date next after ticket ID, then due date. I am moving these. Then close date. All right, now I will go tab, tab, tab, come over here. Customer ID is next, customer combo, priority combo, status combo, notes over here. Alright, and then we will drop back to description.

So what I'm going to do is take the invoice button out of the tab order. We will see that in a second. I am going to make it so that it cycles on the same record instead of moving to the next record. I hate that. I do not change that as much as I should.

So let's make it so that this is not a tab stop. That is on Other. Then tab stop set to No. So as you are tabbing, tab, tab, tab, tab, tab, tab, tab, you will never get there. Likewise, for the form properties, find cycle and change that to current record. That way when you tab, tab, tab, tab, tab, tab, it will just go back to the top of the same record.

Alright, save it, close it, and let's see what I'm talking about. I open it up and it started me right in description. If I go to a new record, for example, we are going to open a blank record from the customer form. If I go to a new one and I type in blah, blah, blah, blah, blah, blah, blah, blah, I am going to tab and it's going to put me right down in here.

In fact, in here, I am just going to set it so that description and hours are the only two things that are tab stops. But let's continue. Tab, tab, tab, tab, tab, tab, tab, and these are off a little bit. Actually, they're right, but I'm going to take date time out of there. Because usually you just want to type in stuff and have this be automatic.

But if the user does click up here, it's going to go tab, tab, tab, tab, tab, tab, and back to the description. That is exactly what I wanted.

Let's change this in design view so that you are no longer a tab stop. There. Save it, close it, open you up, tab goes right into there. Same. So we are cooking with gas. We're good now.

Another thing I mentioned earlier, I don't want to allow users to delete stuff out of here. No deleting this, no deleting these. They can set hours to zero if they want to cancel something out down here. But you have to mark this as canceled.

How do I do that? Right click, design view. We are going to go to the properties for the form. Go to Data, Allow Deletions is No. In here too, you have to click once for the subform object, then click a second time for the properties for the subform. Allow Deletions there is No as well. Now they cannot delete anything from the ticketing system.

Open it up. If I click on this and go delete, no, can't do it. Sorry, no longer valid, mark it canceled. All right, same thing down in here. Click, oh, can't do it. Sorry.

Next up, we need a way to open a new ticket for the customer. So what we are going to do is go to the customer form, right click, design view. I am going to put a button down here to open up that form and go to a blank new record.

You can't do this with the button wizard. If you come in here, you can go form operations, open a form, next, pick the form you want like the ticket F, next. We can open the form and show all records or find specific data. So I could find the records for the current customer, but I want to go to a new record. There is no way to do that with this wizard.

So you can either write a macro or you can use a little bit of VBA. I like VBA. I really do not like macros. You should learn a little VBA. Now I did not make this a prerequisite, but I'm going to tell you now: pause the video if you can and go watch my intro to VBA video. It's free, absolutely free. I think about 20 minutes long, half hour, just to teach you the little basics. We are going to need just a couple of lines of VBA code, but watch this. It will give you all the background you need first.

How to do it, where to put it, how to start it. I'm going to show you right now, but yeah, I should have made this a prerequisite, but it is not really, because you do not really need to do this. This is one of those things where if you want to learn a little bit of VBA, Access becomes so much more powerful. It opens up a whole new world of stuff, all with a couple of lines of code.

Alright, so here is what we are going to do. I am going to borrow one of these buttons, copy, paste, slide it down under here. Let's call this one new ticket. Let's give the button a good name, double click on it, and come in here instead of Command 32, let's call this new ticket button.

Now we are going to put some code in the button. Right click on it. Come up to Build Event. You might get a little window popping up asking what kind of builder do you want. Pick the Code Builder. I talk about that in the intro to VBA.

Right in here, we are going to put a little bit of code. I want to open up that form, but I want to open it up to add a new record. Here is how we are going to do it:

DoCmd.OpenForm "TicketF", , , , acFormAdd

I want to add a new record. That's it. That is all you have to do. DoCmd.OpenForm "TicketF", , , , acFormAdd.

Pick up from the list. You can see some of the other open form commands up here. This one opens up the contacts for this customer. This one opens up the orders for this customer. We are going to do something similar with a ticket thing in just a minute, but I want to do this first.

Alright, save this. Close this, go to the customer form, click on new ticket. Boom, there we are, new ticket. See? We are at new. All this stuff defaults. This was set as a default value in the invoicing video that we watched. This combo box. It gets a default value right here:

Forms!CustomerF!CustomerID

That's how to get a value from another open form.The customer form has to be open for this to pull that default value in.

I have another video that teaches you how to do this. I'll put it down below. It's called the form name video. It's in the links section down below.

This is why I have my regular course series. My beginner one, two, three, expert, all those, my regular courses. I teach you things in the proper order there. If you're just coming into this video and you haven't watched my previous stuff, then you might need to go watch the other one first. It gets a little confusing, but in order to do this, you have to know some things beforehand.

It's like when you go to a college class. If you're going to take calculus, you have to know arithmetic and algebra first before you can learn calculus. It's the same thing here. There are prerequisites. You have to learn some things before you can learn other things. This is a little more advanced.

That's why in my regular courses, I teach you things in the order that I think you need to learn, instead of jumping around. That's the TechHelp videos. Those are to jump around and teach different topics.

Now I have a button that I can click on. Boom. Then I'm ready to enter any new ticket. I'm going to slide this over here like that. Save it. When you move a form around and hit save (Ctrl+S), it saves its location. See, if I open it up again, boom, it opens up over here now.

Yes, I've got VBA code where you can control exactly where the form opens up on the screen. That's a lot more advanced.

Now I'm ready to say, "Called for on-site service visit, tab went to office, charge them three hours." "Called for follow up, charge them for 0.25 hours." If you want to put notes on either of these, like here, "went to office," this is the notes field that's bound to that. This is the notes that's bound to the overall ticket.

So in here, you can put "five mile drive," or whatever you'd like about that particular thing. For the call for follow up, you can put "said everything is working," etc. Over here, if you want to put directions or whatever else pertains to the entire ticket, you can do that as well. That's the difference between those two notes fields.

Next, we're going to make a form that lists all of the tickets in the system, so your people can just open it up and see, "What do we have to do?"

Now, to do that, we're going to make a query to bring everything together. Go to Create, Query Design. Shut this down. We're going to bring in the TicketT. We don't need the ticket details in this, just TicketT. And then, of course, its helper data. So we need the Priority table, the Status table, and let's bring in the Customer table too.

This is the stuff you want to see on your summary form. I want to see the ticket ID, the created date, and the due date. I want to see the priority, but I don't want the priority ID. I want the priority description, but I don't want to call it description. I want to call it priority. That's called making an alias. It's description, but it's description from the Priority table, and I'm going to call it priority here.

If you run it now, you'll see what I'm talking about. See, you just get the word "medium." Now, this is not editable. You can't change it.

One of the big things that people always do is make these big, complicated queries with all these joins, and they're like, "Why can't I change things?" Well, you can't edit that. It's brought in from another table. If you wanted that to be editable, you'd have to bring in the ID and make a combo box out of it, which I'm not going to do. This is just for display purposes.

So when you see something on the store shelf, "for display purposes only," this query is for display purposes only.

Same thing with the status now. Bring in the status description, but I don't want to call it description. I'm going to call it status and alias it as status.

So instead of PriorityT.Description and StatusT.Description or whatever, I've got that, and it looks much nicer.

What else do you want in here? The description can come in. That's the actual description of the ticket, which I'll bring to the left. I'll keep all the stuff from the same table together. I want to see the customer. I'm going to bring in first name and last name together, but I want them displayed in the same field. So over here, I'm going to say the customer is going to be first name plus a space plus last name. That should get the field, because there's only one table in here that has first name and last name.

Let's see. Yeah, it worked. If you had multiple tables in here with first name and last name, you'd have to say CustomerT.FirstName and CustomerT.LastName. Since this table is the only one that has those fields, Access guesses it correctly.

These are all the fields I need for my ticketing table. I'm going to save this as my TicketListQ, my ticket list query.

Now, I can use this to build a status form. So how do we do that? Well, it's going to be a continuous form. Let's borrow another continuous form. What do we have in here that looks good? Let's use the CustomerList form. This guy. That's a good form. We can repurpose this one.

Let's copy this guy. CustomerListF. Copy. Paste. I built that, I think, in the blank database or the contact management one, one of the prerequisites for the invoicing video. That's why we need to know the prerequisites.

This is going to be my TicketListF. Let's design it.

First off, let's work on the colors. Change the colors, people. I don't like them. We'll use the same recent colors. That one. And this background color, we'll go back to that. Change the alternate background color, which I hate, to no color. I've got to change the templates; it's no color, so I can do it now.

Let's set these fields up. Open this up. Go to the All tab here. Change the record source to the TicketListQ, so we have our helper data.

Then, just change our fields around. But first, a word from our sponsor. Who is the sponsor? Well, that's me. I'm my sponsor. Just a real quick promo.

If you haven't yet watched my free Access Level 1 video, go watch it now. It's great. It's four hours long and covers all the basics.

If you like all this stuff with subforms and combo boxes and reporting, I have 32 levels of Access expert classes. There are nine beginner, 32 expert, and a bunch of developer programming stuff. The expert series is what covers all the cool stuff, all the nuts and bolts, and the nitty gritty.

As I said earlier, I cover things in the right order, so you're not confused jumping around between different videos. Just follow Expert 1, then Expert 2 (relationships), Expert 3 (SQL), Expert 4 (more form controls), and so on. I teach you the things in the order you need to learn them.

Come to my website. There's the address right there. You'll find links down below in the link section. I have a membership program you can sign up for if you want. You get a 50% discount. If you want to learn more after that, you can sign up for the developer classes. I have lots of those, where I teach VBA programming. All kinds of stuff. Come to my website, AccessLearningZone.com. I know I have this little short URL, 599cd.com. That was my old one, but I kept it because it's nice and small, and it works for all my links.

Come to my website and check it out.

Let's go back to the class. I have to do a little advertising once in a while. I have puppies to feed, so when you buy a couple lessons, I can feed my dogs.

So, ticket list query. I am going to, let's see, CustomerID. We're going to make this what we're going to click on to open up the ticket, so that's going to change to the TicketID.

Copy, paste. Next up after that is the created date, then the due date, then the description (that will be bigger, of course), then the priority, the status, and then I'll need one more for the customer.

Now in a situation like this, I really hate layout view. I almost never use it, but there are situations where it's okay. I'm going to save this and close it.

Be very careful with layout view if you have VBA code in your forms. I've seen it corrupt forms before. Open up the ticket list and you can see we need to change the layout here.

Right-click, layout view. Now you can resize these things a little bit. I'm just trying to slide that to the left and it already ganged me. We'll come back in here. Be very careful. Let's see if I can... it's going to be tough to... No, okay. I don't like it. I don't like it already. I'm just going to stick with design view for resizing stuff. I don't like what it did already, so I'm going to undo that.

So, you resize. I'm going to go to design view. I have bad luck with layout view. I really don't like it. Every time I try to move stuff around, it messes up on me.

Slide the created date over. Let's see what that looks like. Save it. Layout view. This can be smaller.

That looks good. Left align it. I'm going to left align everything while I'm in here. Design view. Slide the due date over. Again, it didn't move on the grid like I like. I'm just going to make you the same size as this one. I almost got it. I have bad finger coordination when it comes to that. Beautiful.

The description is going to be nice and big. If I have it, it's wider. Priority and status will slide over here. The client can go out to the right.

Save it. Let's check layout. Not bad. That's not too bad. These could probably be a little shorter.

Let's save it. Let's go back to design view. Be very careful with layout view; it doesn't snap to the grid. Come on, Microsoft. When you resize these in layout view, they should stick to the grid.

I'm going to put that on my notes for Microsoft. Microsoft has a feedback page, by the way, now. There's Microsoft's Access feedback portal. A lot of people have suggestions there. Michael, by the way, has a great blog. I'll put a link to it down below. It's on longerSet.com. I love reading his stuff.So there, I posted my feedback. So they have it. Now we just have to change our labels around. Sometimes what I do, I have gotten lazy lately, and a lot of - well, let's not say lazier now, but efficient - we'll call it efficient. I do this with one label now a lot of times. I'll do this: I'll go to ID for the ticket, ID, and I'll just go space. All right. Create a space. Due date, or just due. Due is fine. All right.

Easier than working with multiple labels, and that priority data, and then the customer. It's a little bit of a pain if you have to move these things around and move the labels around, but just for this, it's fine. It works great. It's quicker to design it in the first place.

No, I don't want to open customer, I'm going to open ticket. All right. I think this is a macro. It's a right-click build event. Let's see, yeah, I built this early on with the first blank one. So let's just get rid of it. Goodbye. I don't like using those macros. I use them for the very, very beginner people so I don't confuse them, but we're a little bit past that. So let's go into design, drop a button down here.

Cancel, open ticket, tickle, ticklet, open ticklet - is it like a checklist? Open ticket. Now right-click, build event.

Oh, I forgot to name my button. It's command 18 click. Stop, go back. Alex is going to yell at us if we don't change the button name to open ticket button. Better. I always forget to do that. I leave it in my videos because I still, to this day, forget. I've been working with Access for almost 30 years. I still forget to do that.

All right, build event. DoCmd.Open - what am I opening? The ticketF, comma, comma, comma, where condition: where the ticket ID equals ampersand ticket ID. That says the ticket ID on the ticket form is going to be the same as the ticket ID on this form, whatever record you're on. We're going to put the same code in this, all right, which is going to be in the double-click event. I cover this in the invoicing video, I believe, with the blank or one of those invoicing video prerequisites.

All right, ticket ID double-click. Look, it's the same as this stuff up here. I'm going to actually get rid of this now. I've got an If IsNull on there, Exit Sub, so if you're on a blank one, it doesn't try to open it. So that's not a bad idea. Let's put that in here. Whoops, put that right in here: If IsNull(ticketID) - can't type today - then Exit Sub. All right, and put the same code in here, and I can actually delete this because we got rid of customer ID.

Now, I don't like having the same code in two different places. So what I'm going to do is make my own subroutine: Private Sub OpenTicket. Now watch this. I'm going to take this code, put it in here. Now in here all I have to say is OpenTicket, and I can recycle it. Watch this: copy and then paste. See, now I have OpenTicket in one spot. You don't want to maintain multiple sets of the same code in different places. If you're going to use the same code in two different places, put it in its own subroutine.

This is all stuff I cover in my Developer Level 1 class, my beginning introduction to VBA. Private Sub this, Private Sub that, save it, come back out here - see, a little teeny tiny bit of programming makes your life so much easier.

Open up this ticket here. Can't hear the computer thoughts. Double-click, boom, there's your ticket. Close that one, open this guy up, boom, there it is. Maybe I like to slide this one down just a little bit so you can tell it's kind of subordinate to this one.

Open up ticket one, double-click, boom, boom, same. One more thing to do - actually, two more things to do. Let's do a little conditional formatting in here. Let's open this one up. Actually, let's make a requery button too.

There are a few things to keep in consideration as you're working on this. This ticket, right, priority gets changed to high. I should have a fit now when you close this. This doesn't - I probably did update. Okay, it doesn't always update. In this particular case, it did, but if you come down here, for example, open this one up, change this to urgent, close it - it happens to be changing in this case, but not always. I want a requery button down here.

I show in other classes how you can, when you close one form, requery another one, but we'll just do a requery button. Copy, paste this button. Let's change this to requery. Go change the name, give it a good name: requery button. Right-click, build event, and it's just Me.Requery, that's it.

Me.Requery, that way if you make major changes and things aren't updating, you can just requery instead of having to close it and reopen it. In fact, let's put a button on our main menu: copy, paste, button, button, button everywhere. Ticket list. Right-click, build event. DoCmd.OpenForm "TicketListF". Oops, that single quote.

Close the main menu, reopen it again. Ticket list, boom, open up this one, boom, there you go. Change this to low, close it. All right, it's updated, that's good. If not, hit the requery button, it'll requery this listing.

You might not want the user to be able to add new ones from here. You might want to have them go into the customers record first. So you can come in here, in design view, open this up, change the data: Allow Additions: No, Allow Deletions: No for sure, Allow Edits: No. Save that, close it, open it up. Now you can't add new ones, you can't make any changes in here. You have to go into the ticket.

You can make it so you can double-click on the customer to open up the customer record. That's easy to do. Design view. I like to make it so that any field you double-click on does something, so I like to make it like this blue color, for example. Use the format painter, format paint, paint over that. Like that. Now, we don't have the customer ID on here. Customer ID wasn't brought in here. It's not in the query, so let's go back to the query.

This is our TicketListQ, design view. Let's add the customer ID. Save that. Now we can put the customer ID in here. Right-click, design view. We might be able to get away without doing it, but to be safe, let's add it: customer ID. We can drop it right down here on the bottom.

Because we need it to open up the customer form, and I'm going to make this a hidden form, or a hidden field, so I think my hidden fields are red, so in design view I can see that it's a hidden field, and I double-click on it, set visible: No. Save that.

Now the double-click event on this guy, open it up. Actually, it's text17, isn't it? Yeah, we didn't change the names of these boxes, did we? You guys have to raise your hands in the back of the class and yell at me to change the names of these boxes. It doesn't matter - well, it does, because you don't want to be calling it in code later on and realize it's the wrong field name. Copy, paste. I changed all the control sources but not the names.

So, this event on double-click, right there, code builder. DoCmd.OpenForm "CustomerF", where the customer ID equals the customer ID. Open the customer form where the customer ID on the customer form equals the customer ID on this form. That's why I had to have it. Had to have it on the form so I can pull it, I can reference its value.

And now I can do this: I can open up the ticket list and say, oh, let's go see Deandreoy. Double-click. Boom, customer. Want a new ticket for our customer? Boom, new ticket.

Now, we can filter this to say, let's see all Richard Rost tickets by right-clicking and going equals Richard Rost. Or we can put a button over here on - whoops, wrong one - we can put a button on the customer form to view his tickets.

Here we go. You ready? Copy, paste. View tickets. How do we do that? Right-click, build event, Command32. See, I always forget this. I'm going to leave it in the videos until I get better at doing it. So open it up. I'm going to annoy all of you because I can never remember to name my buttons. ViewTicketsButton.

Right-click, build event. Try this again. Here we go. DoCmd.OpenForm "TicketListF", where the customer ID - that's why I also wanted to put it on there - equals the customer ID on this form. I see a lot of people do this: they go Me.CustomerID. They saw it online somewhere. Yeah, you can. There's nothing technically wrong with that, but you don't need it.

Why am I putting it on there? Me is assumed, and customer ID is a field on the current form, so why bother? The only thing you really need Me for is properties. Me.stuff - usually it's properties: Me.Caption, that's the caption of this form. Usually controls you want to reference with that, Me.FirstName, for example, is something like that. That's a whole different video. I got a Bang vs. Dot video. I'll put a link to it. Double it if you really want to watch it. Yeah, the exclamation point is called bang.

But anyway, let's close this. Close this, close this. Ticket list. Go to a customer or go to a ticket. All right, this looks good. Let's go to a customer. Actually, let's start from the customer list. Open up the customer. View their tickets. Boom. Isn't that pretty? There you go. There's the basics of it.

Well, I put a little conditional formatting in here. Let's go back to the main ticket list. You can see the stuff. Ticket, you might want to sort this list by priority, by the way.

So, this is based on a query. Let's go to the query: TicketListQ, design view. Now we're going to sort by the PriorityID. What do the priorities look like? So it's urgent as 4, low as 1. So let's sort this by PriorityID in reverse order.

So we're bringing PriorityID now, and I'm going to bring the sort fields up to the front. Sort this in descending order. You could just put the sort in the form if you want to, but if the form is based on the query, it'll get that sort. See, urgent, medium, low.

Then we'll put a little conditional formatting in here. We can use priority to do the conditional formatting. That's fine. It's just if you change the word, then it gets tricky, so it's better to do the conditional formatting on the ID.

So, we need to bring the ID in here now too. So I'm going to copy this guy. It's going to be hidden. Copy, paste. Open you up, and we're going to change this to the PriorityID. Now, you're probably saying to yourself, "How am I going to do the priority conditional formatting if the priority is down here?" Well, we're going to use a weird kind of conditional formatting called expression-based.

In other words, you put a little expression in here instead of basing it on the value that's in this field. I have a whole separate video on this too. I'll put a link to that video down below as well, but here's how you do it in a nutshell.

Go to conditional formatting, click on your field, go to conditional formatting, do rule. Instead of "field value is," which is based on that field, I don't want to have it equal to the word urgent. That's what's in that field.I want the number because I might change the text later on the table and it will break my conditional formatting. But that value will always stay the same.

So, go to Expression Is. And it's basically priority ID equals four. Then we will let go. Let's say red with white lettering and we'll make it bold. But be careful. Watch what happens.

Hit OK. It looks good. Expression is, priority ID equals four. That looks fine. Looks good. Save it. Close it. Take a look.

It's not working. Why? Well, conditional formatting always messes with you. It's just one more thing for it to do. So let's put the word priority ID inside quotes. We don't want that. We want this to be inside brackets.

Now, I said you don't have to use brackets if you don't use spaces in your field names. 90 percent of the time that's correct. With conditional formatting, it likes to mess with you.

Now priority ID is the field, equals four. Save it. Close it. And there you go. There's your urgent.

You can do medium and high and all the other ones. I'll just do one for another one, for high. Let's come back in here. Format, conditional formatting. Do rule. Expression is, priority ID equals three.

What do you want high to be? Let's do light red like that and bold. OK, looks good. My eyes will do the other ones too.

Expression is, priority ID. We'll skip medium, which will be just regular white. Then we'll go down to low, which will be one. We'll make this one just gray. Now let's do white background. Let's do gray text. That's low. OK.

And they will be applied in the order shown. So if you want this one to be evaluated first, whatever. That usually only happens if you have inequalities, like greater than 100. OK. Save it. Close it. Open it back up again.

There you go. Urgent, medium, low. Let's see if this medium one changes. Let's open it up. Let's change it to high. Close it up. Now it's formatting as high. That's kind of cool.

You can do the same thing if you want to give due dates in here. If you want to hide the stuff that's not active, whatever, you can come in here. Let's say that one of these is closed. You can put a filter on. Just right click. Equals active to show me the active stuff.

You want to sort it by date. You want to sort it by the customer. Whatever you want to do. The sky is the limit.

In fact, in the extended cut with the members, I'll put a couple of little filter boxes up here to make it easier to do that kind of stuff.

There you go. There's your basic ticketing system. It took us about an hour to build. Not that bad. Tickets. Ticket list. Open up a ticket. Come in here. Type in what you got. Type in more stuff. Billing for another half an hour.

When it's all done, you click the invoice button. It invoices it and marks it closed or marks it invoiced. Which we will do in the extended cut too.

So, we have been working on this for about an hour now. We've got something pretty respectable. I got a lot more I'm going to work on. It will be in the extended cut for the members.

If you want to learn more about this kind of stuff, come to my website. If you want to learn more, I got a lot more in the extended cut. We're going to make printable tickets. This guy over here. You can print it out. You can give it to the tech. You have to go on the road. You can put the address on here if you want to. I didn't, but you can. All the stuff that's been done previously, we've got it.

Then we're going to assign it to employees. So, you can put in here a list of employees and who is each one of these tickets assigned to. The person can then filter on his name. Show me all my tickets.

Then we're going to do a filter by status box. You can drop this down and you can pick show me all the active ones. That will be the default when you open it. Show me just the active ones. Show me the closed. Show me the escalated ones. Show me the ones that are invoiced.

We're going to add an all option. This right here is worth the price of admission, folks. This all. That is a crazy little trick.

We can put that in the table and have it actually show up so we can display all of these. Then we're going to have open to billing hours show up in here. In the customer list, you can see this customer has one open ticket, this one has two tickets. We have to bill for a total of five and a half hours.

All of this will be in the extended cut for the members. It's 60 minutes long. A lot of my extended cuts are pretty long. Some of them are 50 minutes. Most of them are like half an hour. This one is an hour.

Silver members and up get access to all of the extended cut videos. Gold members can download the database that I build in the video.

Sometimes I do this with my extended cuts. I had more than an hour for the regular extended cuts, so I made a bonus extended extended cut for the gold members. I have silver, gold, and platinum, another half an hour.

What I do is automatic billing. In other words, it takes all of the tickets that are in the system for all of the customers. You click one button and it generates all of those invoices automatically. Click the do billing button, it creates an order for each customer that has tickets in the system. Add them all up. Line items are ticket one, ticket two, ticket three. Because you are invoicing. Another 30 minutes for gold members.

So, come on. Sign up. Become a member. You get access to all this stuff. All my extended cut videos. Gold members, in addition to the videos, can also download these database templates. So, join up now.

Oh, and one more thing. I know a lot of you have said to me that you want some of these templates that I build, but you do not want to sign up. You do not want to make a monthly commitment to become a member. That is fine.

You will also find this in the templates section of my website. So, just go to the website. Click on templates. It's right across the top. You will find it there too. You can buy this as just a standalone template instead of making a commitment to become a member.

But you can also sign up for membership for one month and cancel. There is no minimum month. You do not have to be a member for a year. You can just sign up for one month. Download whatever you want. Watch whatever videos you want for that month. Then just make sure you cancel before your month. That is it.

Thanks for watching.

How do you become a member? Click on the Join button below the video. After you click the Join button, you will see a list of all the different membership levels that are available, each with its own special perks.

Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more.

Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. You will also get a higher priority if you decide to submit any TechHelp questions to me, and you will get one free expert class each month after you finish the beginner series.

Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you finish the expert classes. These are the full length courses found on my website, not just for Access too. I also teach Word, Excel, Visual Basic, and lots more.

You can now become a Diamond sponsor and have your name or company name listed on a sponsors page. It will be shown in each video as long as you are a sponsor. You will get a shout-out in the video and a link to your website or product in the text below the video and on my website.

But do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more and they will always be free.