|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Bang! v Dot. By Richard Rost 
5 years ago
Should I use the Bang! or Dot. Operator in Access In today's QuickTip video, I'll discuss the difference between the Bang (!) and the Dot (.) operators in Microsoft Access and when you should use each. LinksReserved Words: https://599cd.com/ReservedWords
IntroIn this video, we'll talk about the difference between the Bang (!) and Dot (.) operators in Microsoft Access, especially when referencing controls, fields, properties, and methods in your forms and VBA code. I'll explain when to use each operator, common mistakes that can lead to errors, how reserved words affect your code, and why using the correct operator helps avoid problems with your database projects.TranscriptWelcome to another TechHelp Quick Tip video brought to you by AccessLearningZone.com. I am your instructor Richard Rost.In today's video, I'm going to talk about the Bang versus the Dot operator in Microsoft Access. This is something I see all the time. People ask me why their databases don't work or what the difference is between the Bang and the Dot. I decided to put a video together because I'm sick of answering this question, so I'm going to start referring you to this video. This is a little more advanced tip. It's for people who usually do a little VBA programming, but you may see in your VBA this or this. There's me.firstname if you're referencing the first name field on the current form. For example, you're on your customer form and you have first name as a field. Me.firstname works most of the time. Then there's me.bang, the exclamation point, that's also called the Bang operator in Microsoft Access. Me.bang firstname is technically the correct way to do it. This is how I want to see my students doing it with the Bang symbol. Let's talk about the difference. The Dot operator is for properties or methods of an object. For example, me.name is the name property of the current form. So, customer f, for example. Me.caption is the caption property that goes across the top in the title bar. You can set that in the customer form, for example. There are also methods. Properties describe something about it, like its name, its caption, its width. Those are all properties. Methods are for doing stuff. DoCmd.OpenForm is a bunch of commands that fall under DoCmd. OpenForm is one of the methods. Close is another method. DoCmd.GoToControl, DoCmd.GoToRecord, those are methods. Properties of controls: first name is a control, a field, or text box. .SetFocus is a method that says go do something. It also has properties like firstname.width. Those are properties or methods. CurrentDb.OpenRecordset: this is doing something to the CurrentDb, an action. These are actions or properties. The Bang operator is to reference a member of a collection. Me is a form and it has a collection of controls, and in those controls, there is one called firstname. Technically, if you care, you could write this as me.controls.item("firstname"). There's a lot of advanced stuff I could get into, but basically, bang makes it a shorthand to say that's a member of the firstname form. First name: you can write it this way also. First name is a member of a collection of fields on the customer form, which is a member of the forms collection. That's a collection; it's a member of a collection. This is the way you'd have to write it if you're referring to it on a different form, whereas if you're using just the current form, you can use that. And yes, for those of you who are curious, I'm using PowerPoint for my slides. I love PowerPoint. If you've been using spaces in your field names, you have to refer to them with these brackets around them. I cover this in my very first Access beginner lesson. Don't use spaces in your form names or your field names. See how much more complicated this looks than that? This is how you can refer to it on the current form. This is how you refer to it on a different form, and this is how you refer to it on a different form with spaces in the name. Now, you're telling me, Rick, I've been using me.firstname forever and it works. Yes, it'll work some of the time, but not all of the time and not reliably. Why does it work? Because Access is doing you a favor. When a form loads up, Access will make all of the controls properties of that form. It's like a shorthand. They threw it in there to help people out. So, you can say me.firstname and it will work most of the time, but not all of the time, so don't get used to it. You should be doing this, not this. Why? In the first case, if your field name happens to be a reserved word in Access, the bang will work, the dot will not. So, if you happen to make a field name in your table name or caption or picture or any of the other dozens of reserved words, then me.name will not work. Me.caption will not work. Me.picture will not work. This is the biggest thing that I get people emailing me. I've got a field called caption because I'm doing a photo database or whatever and I want to put a caption for the picture in a text field, but when I say me.caption, it's not working. That's because caption is a reserved word. You're referring to the property of that form called caption. So, don't use that. Use exclamation point, and now this will work. This will refer to a field on your form called caption. Same thing with picture. There are hundreds of reserved words. I'll put a link down below to Microsoft's website. Name, for example, is one of the worst ones. Don't use name. Never use name for your fields. First name, last name, those are fine. Don't use name. Another reason they might stop working is if you change the record source of your property. This is a VBA technique. I use it all the time. You have a form and you want to change the record source, like you want to switch it from being customer to maybe manager or something else. You say me.RecordSource equals something else. As soon as you change that and that field name no longer exists, Access doesn't recreate it as a property. So, if your code references me.firstname and firstname is no longer in the record set, you'll get an error message. This is a less common reason, but it's still a valid reason why not to use me.firstname. Use me!firstname. If it doesn't find it, you'll just get nothing. You'll get no result instead of an error message. The only major benefit of using the dot is you get IntelliSense. If you type in me., you'll get that list of IntelliSense stuff that shows you all the properties and the fields and the methods in the current form. If you really want that, type in me! and then hit control space and that'll open up the little IntelliSense dropdown. I almost never use that though. Another side note is the me is completely unnecessary. I see people posting code all the time online. They send me code in questions and emails. You don't need to reference me if you're just working with the current form. Leave it off completely unless you're referencing a property, unless you want me.caption, or the width property, or something like that. Just leave it as firstname. That's all you need. You don't need me.firstname or me!firstname. You don't need me at all unless you want a property. If you want me.caption, great. If not, just say this. Otherwise, if you're referring to something on a different form, again, there's the notation. If you're referring to a subform, that's another popular question I get. This form's parent form, the name of the subform.Form!FieldName. This is a side note, but this is another email I get all the time. The takeaway from this quick tip: use this, not this, for your fields. I don't want to see any of my students doing that anymore. You send me in email, you're going to get sent to the principal's office. I happen to be the principal too, so I get the dunce hat and the paddle going. Hope you learned something. Hope this helps. Remember, bang, not dot. How do you become a member? Click the Join 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 will get access to all of the extended cut TechHelp 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 TechHelp 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 full-length 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 TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more. If you liked 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. If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It's over three hours long. You can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just one dollar, 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 TechHelp page and you can send me your question there. Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel. Thanks for watching this video from AccessLearningZone.com. QuizQ1. What is the primary difference between the Bang (!) and Dot (.) operators in Microsoft Access VBA?A. The Dot operator is used for properties or methods of an object, while the Bang operator is used to reference a member of a collection. B. Both operators can be used interchangeably without any issues. C. The Bang operator is for methods, and the Dot operator is only for properties. D. The Dot operator is used only with forms, and the Bang operator is used only with reports. Q2. When should you use the Bang operator in Access VBA? A. To reference a control or field that is a member of a collection on a form or report B. When setting properties of an object C. Only when calling methods of an object D. When referencing VBA modules Q3. Why can using the Dot operator instead of Bang lead to issues when a field name is a reserved word such as "caption" or "name"? A. Because the Dot operator references the object's property, not the intended control or field B. Because the Dot operator cannot reference any controls C. Access will always convert reserved words automatically D. The Bang operator cannot reference reserved words Q4. If you change the RecordSource of a form and a field is no longer present, what happens when using the Dot operator (e.g. me.firstname) to reference that field? A. You may get an error because the property no longer exists B. The proper field value will still be returned C. Access creates a placeholder property automatically D. The code will always return an empty string Q5. What is a major convenience of using the Dot operator according to the video? A. It provides IntelliSense, showing available properties and methods as you type B. It prevents any syntax errors in VBA C. It works with reserved words without issue D. It increases the speed of your VBA code Q6. According to the video, what should you do if you are referring to a control on the current form and not a property? A. Use the Bang operator, or even omit "Me" and use the control name directly B. Always use the Dot operator C. Only use square brackets around the control name D. Use the form's full name and Dot operator Q7. What needs to be done if you have spaces in your field or control names in Access? A. Enclose the name in brackets (e.g. [first name]) B. Replace spaces with underscores C. No special action is needed D. Use the Dot operator instead of Bang Q8. Which of the following best summarizes the recommended best practice according to the video? A. Prefer using the Bang operator for controls and fields, and the Dot operator for properties and methods B. Use only the Dot operator everywhere for consistency C. Avoid both Bang and Dot operators whenever possible D. Always use the Bang operator for methods Q9. If you want to refer to a field on a subform from the parent form, which notation should you use? A. ParentForm.SubformControl.Form!FieldName B. Subform!FieldName C. ParentForm!FieldName D. Form.FieldName Q10. Why does Access sometimes allow me.firstname to work, even though the Bang operator is technically correct? A. Access creates the controls as properties of the form as a shortcut for convenience B. The Dot and Bang operators are always interchangeable C. VBA automatically corrects operator usage D. Only newer versions of Access support this syntax Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryIn today's TechHelp Quick Tip video from Access Learning Zone, I discussed the difference between the Bang (!) and Dot (.) operator in Microsoft Access, a topic that comes up frequently when students ask why their databases do not work as expected or what makes these two operators behave differently. Since this question is so common, I decided to put together a concise explanation to help clarify things for anyone working with Access, especially those who write a bit of VBA code.First, let's talk about the typical scenarios where you encounter each operator. When you are referencing something on the current form, such as a FirstName field, you might see both me.FirstName and me!FirstName in existing code examples. Even though using the dot operator seems to work in most cases, the technically correct method is to use the bang (!) operator, as in me!FirstName. This is the method I recommend my students use. So what is the difference? The dot operator (.) is used to refer to properties or methods of an object. For example, me.Name gives you the name property of the current form, and me.Caption refers to the caption appearing in the form's title bar. Methods such as DoCmd.OpenForm, DoCmd.Close, DoCmd.GoToControl, and DoCmd.GoToRecord are all actions you can perform, and they also use the dot operator. Controls like FirstName, which can represent text boxes or fields, have both properties (like FirstName.Width) and methods (such as SetFocus), and all of these are accessed with the dot. On the other hand, the bang operator (!) is used to reference a member of a collection. For instance, a form (represented by Me) contains a Controls collection, and each element of that collection, like your FirstName textbox, is referenced with the bang operator (me!FirstName). This is essentially shorthand for me.Controls.Item("FirstName"), although you typically do not need to write out the full version. This distinction becomes especially relevant when working with forms that have fields or controls with names containing spaces. In those cases, you must use square brackets around the name. I advise you to avoid spaces in your field or object names because they make referencing more complicated than it needs to be. Now, many people say, "I have been using me.FirstName forever and it works!" Yes, that often works, but not reliably in all situations. Access tries to help you by making the controls available as properties of the form automatically when it loads, so me.FirstName often succeeds. However, it fails in certain cases and may result in errors that are difficult to trace. A major reason to avoid using the dot for controls or fields is when your field name matches a reserved word in Access. For example, if you create a field called Caption or Picture or Name, using me.Caption will reference the form's caption property, not your field. The bang operator (me!Caption) will refer to your field, even if its name is a reserved word. This is a common reason students email me with problems. I always recommend not using reserved words as field or control names, but if you already have them, use the bang operator. Another scenario where dot referencing can cause issues is if you change the RecordSource of a form dynamically in VBA. Once the field is no longer part of the record source, Access stops exposing it as a property, and trying to reference it with me.FirstName can cause errors. The bang operator simply returns nothing if the control or field is not found, rather than throwing an error. The primary advantage of using the dot operator is that it triggers IntelliSense, which lists out all available properties and methods as you type. Some developers rely on this for productivity. If you want a similar dropdown when using the bang operator, you can type me! and press Control+Space, though most advanced users find this unnecessary. It is also worth noting that you do not always need to use Me when referencing controls on the current form. For most situations, you can just use the control name directly, such as FirstName. Me is only necessary if you are referring specifically to a property or want to make it explicit for clarity. If you are referencing a control on a different form or a subform, there is a different notation to use for that scenario, something I get asked about frequently as well. The key takeaway is that you should use the bang operator for referencing fields or controls, not the dot operator. If you send me code using the dot in this context, be prepared for me to call it out. I really want to help you avoid headaches in your future projects. To wrap up, remember: bang for fields and controls, dot for properties and methods. If you are interested in becoming a member, there are several membership options available, each offering different levels of access to extended video content, live sessions, downloadable databases, full courses, and more. You can find all the details on my website. These free TechHelp videos will continue as long as you keep watching them, so please subscribe to my channel if you have not already. Subscribing is free and ensures you stay updated. Also, take a look under the video for additional resources and links. YouTube no longer sends out email notifications when new videos are posted, so if you would like to get notified by email, consider joining my mailing list. If you are new to Access, my free Access Level 1 course is an excellent place to start and covers all the basics. Level 2 is only one dollar and is also free for all channel members. If you have a question that you want me to answer in a video like this one, just go to my TechHelp page and submit your question there. You can find the complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListDifference between Bang and Dot operators in Access VBAUsing the Dot operator for properties and methods Access reserved words causing issues with Dot operator Using the Bang operator to reference collection members When to use brackets for field and control names Changing form record source and its impact on Dot referencing Impact of field name changes on property referencing IntelliSense availability with Dot and Bang operators Referencing fields without using the Me keyword Referencing controls on different forms Referencing fields on subforms |
||||||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access Should I use the Bang! or Dot. Operator in Microsoft Access Forms, Using the Bang (!) or Dot (.), Bang Vs. Dot in Forms, bang and dot, bang notation, dot notation, dot vs bang, dot v bang, syntax for referencing objects, exclamation PermaLink Bang! v Dot. in Microsoft Access |
