Input Masks Lookups and Validation Rules

hi this is Letty I want to talk to you about three things you can do to help a table be better well I'm talking about our input mass to ensure that data goes in looking the same a lookup field which helps a person to choose from a selection of choices and a validation rule which ensures that data goes in properly let's look at the student table in design view and while I'm talking about navigating to design view when I'm in design view this icon takes me to datasheet view when I'm in datasheet view this icon takes me to design view get used to toggling back and forth like this it's the most efficient way to move around in your database so in design view when I click on a field I can see the properties for that field down below let's look at the properties for this first name field it's a text field that means that it's going to be a field that holds text it's been limited to a field size of 15 characters it's got a caption a caption means that if the field name doesn't look pretty on a form or on a report just the way this doesn't cause if there's no space between first and name that you can make the name look prettier on those items it's not the real name the real name for this field is field name with no space but it'll look better when you use it and there's not much else going on here I would like to point out that there are properties that you can not use at all and that's fine now here's the gender field it's been set to one digit now the reason it's been set to one digit is to help ensure that people don't enter bad data anything that you do that helps you to make sure that people who are using your database enter data correctly is called data validation and data validation comes in many forms and so the three things we're talking about today are part of that setting a field size for a gender field - one helps ensure data validation and another thing that could help is something called a validation rule if I want to ensure that people only enter the letter M for male or letter F for female I can also create that rule here my syntax has to be perfect I'm going to type it in M space or space F space now when I click away access is going to correct my syntax to be perfect M surrounded by double quotes the word or and then F surrounded by double quotes now this means that I have made sure that nobody will enter anything but an M or an F or it just won't accept it now what if I may have somebody entering data in my table it doesn't really know how to use access very well or is confused about why they can't enter boy or girl or male or female as full words the validation text is to go along with your validation rule and it just helps explain what's going on what you type here doesn't have to be perfect syntax it just has to help the user so I'm going to type please enter either so here notice when I click away it just stays exactly what I typed in it's just explaining the validation rule that's in use I'm going to save my table and go out to datasheet view and scroll to the gender field so you can see the gender field is in use right here here's somebody put in an M and that works perfectly what if I try to put in a t there's no such gender as T it gives me an error message with my validation text please enter in M R and F so you can see how that works I'm gonna press escape and I don't need to change that okay back to design view let's talk about doing a lookup I could do lots of different kinds of lookups but let's add a new field to demonstrate it I'm going to say that with every student that registers at my college I'm gonna have a way a free gift and a student has to choose what kind of free gift they want so I'm gonna type in a field to say free gift I don't want to keep track of what a student chose for their free hit and for this I'm going to give a selection of choices and to do that I'm going to choose a lookup wizard now when a lookup wizard finishes it actually finishes as a text field but in the meantime we're going to tell it what we want the first step of the wizard is to say do I want to go look at another table and see what's available so for instance if I had a gifts table and I had three different records in that that students could choose from I'd want this first choice but in this case I want the second choice I'm just going to type in the values of the choices that I want and I'm going to choose next so here's where I'm going to type them in so I'm going to say they can choose a t-shirt and I'm going to press tab to get to the next cell they can choose a mug tab or they can choose a coffee card let's like go to the coffee stand and get some free coffee now I'm going to choose the next step of this wizard the there's not many steps the last one is to say how do I want the field to be named and of course I'm going to stick with free gift and I'm going to choose finish so as you can see here it just looks like a regular text field but if I go down and look at the properties there's a lookup tab where I can see my choices t-shirt mug and coffee and when I go out to datasheet view and I go to that column of data when I click in here can you see that the down arrow actually allows me to choose some of those choices another thing that happens when you've got a lookup established is that if I just start typing in tea it enters the data for me and it means that if I'm entering data by hand it's very fast if I enter C it wants to fill in coffee card the down arrow will just lock in that choice now you notice that I didn't have to put anything in these columns a lookup doesn't enforce it and in fact a lookup doesn't say you can't put in something else so let's say a student says none I can type in none and it will accept that even though it's not in my lookup list a lookup does not restrict all entry it just gives you choices and helps you spell things better if I wanted to query my later and find out how many students chose to get a coffee card well I can ensure that somebody didn't just enter coffee like whoops whap sup sup Sam coffee and space it would be harder to enter coffee without card if you see what I mean it could be done because I haven't restricted it to that but it really is much easier to make sure everybody's entry goes in the same okay so that's lookups and validation rules let's go back to data and let's talk about an input mask when you have a phone number you almost always want to use an input mask and I'm going to go back to the general tab to make sure that the phone number goes in with parentheses with the number of digits space and then number of digits again this syntax would be very hard to remember so fortunately access gives you a wizard that helps you create an input mask especially for commonly used things so for instance if I decide I want to keep track of a student cell phone that's a telephone number - it is going to end up being text comps so I don't need to change that it has to be text because there are going to be things stored in this field that are not numbers for instance we want parentheses and hyphens stored and you can't store anything that's not a number unless it's a number in a number field um so you have to use a text field for that okay so here's where you put in an input mask and notice when I click on this property and click the build button that appears over on the far right this is this little square that has the three dots called a build button this is what allows me to get at the input mask dialog box or wizard it already is suggesting that I might want a phone wizard and I do I'm going to choose next and I'm going to choose neck again I don't need to choose anything here I'm quite happy with placeholders and everything as it is and next again but here is a stage of a wizard on an input mask that is a very important one by default it wants to store the numbers you type into a phone number without the symbols I suggest that you always choose with the symbols because that way later if you're creating a mail merge directory or something the phone numbers are going to look nice they're gonna have those parentheses and the space and the - this doesn't look nice for a phone number and you don't want it to be like that when you ever use your data somewhere else okay so always go through the wizard choosing to store the information with the wizard symbols and choose next and we're finished that puts the syntax in here for you you don't even have to think about whether or not you need a zero or a hash sign or an exclamation point I suggest always using those wizards to help you get your syntax correct well let's go check out our input mask I'm going to go out to datasheet view and I'm going to scroll over to my new cell phone field now whenever I put a cell and input mouse into a field and I want to try it out what I usually do is go and click in the field that's just to the left of it and then press the tab key that puts me right at the beginning of the input mask field on the left side and now I can try four to five five five five one two one two it works perfectly it looks really good let me show you what happens if I just click in the field can you see how clicking in it just puts me somewhere in it and then I need to get to that beginning so that I can try it out it's just easier to click in here whoops I'm gonna escape it click in here press the tab key and then star okay I'm going to escape because I don't really want to store that I just wanted to show you those three things those three things are part of data validation making sure that data goes in correctly the first time so that it's useful when you're querying it or printing it out

Channel: LWITTraining

Views: 42,008

Rating: 4.7303371 out of 5

Keywords:

Id: 2mO1tUi3IQM

Length: 10min 55sec (655 seconds)

Published: Fri Apr 26 2013