$Account.OrganizationName
The Access Wizard Newsletter Tips Tricks and Traps for Access Users and Developers
December 2005

Combo Boxes – 104: The Code behind the Magic

I recall a time from my childhood when my buddies and I were playing with a golf ball; rolling it around, bouncing it, playing catch – basic kid stuff. After a while we started arguing about what made the ball so hard and why it bounced so high. After a heated debate (“debate” may be a misleading term for what went on within this gang – arguing and name calling may be more accurate), we decided to end the dispute by opening the ball to see what was inside.

We got a sharp knife and started cutting away. It wasn't an easy job. When we first split the cover and looked inside, we found tightly wound rubber bands. Being curious kids, observing the rubber band layer wasn't enough, so we hacked and hacked at the dimpled cover until it was almost off. One of the kids grabbed two opposing sides of the cover and pulled and pulled, until all of a sudden the rubber bands popped out at us. We all jumped a foot in the air!

Although surprised, we were not deterred. We decided to see what was under all those rubber bands. We started unraveling and tearing off layer after layer until finally we got to the center: a small rubbery ball. Our curiosity still not satisfied, we cut the ball open and liquid oozed out.

Although our approach wasn't particularly scientific, we did learn a fair amount about the construction of a golf ball.

Last month we used a Wizard to create a combo box that finds records on a form. The advantages of using a Wizard to create this combo box are many; but one disadvantage is that the Wizard creates the code behind the scenes. Only the user who makes an extra effort to examine this code will understand what the code is doing and how the combo box works. Like kids opening a golf ball to learn more about it, we'll learn more as we explore this Wizard- created code to understand the code behind the combo box.

In this issue
  • Tip of the Month – Rename your Combo Box columns
  • The Preliminaries
  • The Code that makes things Happen
  • The Nitty-Gritty
  • Conclusion
  • Trap of the Month - Override the Spell checker

  • The Preliminaries

    Last month we created a combo box on the form below:

    In this example, the user selects a camper from the list of all possible values using the drop down list in the combo box, and the form is populated with the record corresponding to the user's choice. You can get the details on how we got this far in the November issue.

    This is really nifty stuff, but how does it all happen? Let’s closely examine the code generated by the Wizard to discover the magic.


    The Code that makes things Happen

    To examine the code that runs when the combo box is clicked:

    1. Open the form in design view
    2. Right click the combo box
    3. Choose properties
    4. Click on the Event tab

    You'll be presented with the following:

    The information under the Event tab shows an exhaustive list of the various events that a combo box can respond to. For example, based on the list above, a combo box could respond on exit, on click, or on key press. In our specific case, we're interested in the after update event. When we used the Wizard to help us build this combo box, it created a small program that runs after the user makes a selection from the combo box. That selection is the update event, and the after update event is when the form is populated with the record of the specific camper selected. To see the actual code that runs after update, click on the three dots ( . . . , the ellipses for all of you English majors out there) in the far right column.

    Clicking on the ellipses will take you to a code window containing programs that run when various things happen in your application. The content of this code window has been the subject of many a chapter in many a book, so I won't delve into the complete content of this code window in detail. Our task today, is to deconstruct the code that makes the combo box work.


    The Nitty-Gritty

    Ok after you've clicked on the ellipsis you'll see something that looks like this:

      Private Sub Combo1_AfterUpdate()
        ' Find the record that matches the control.
        Dim rs As Object
        Set rs = Me.Recordset.Clone
        rs.FindFirst "[CamperKey] = " & Me![Combo1]
        If Not rs.EOF Then Me.Bookmark =
      rs.Bookmark
    End Sub

    The combo box Wizard created this code and it runs after the user makes a selection (or update). We'll look at this line-by-line to truly understand what's going on.

      1. Private Sub Combo_1AfterUpdate()

    This line makes some declarations about the lines that follow. The text Sub in the first line of the code declares that the code between this line and the End Sub line is a subroutine, or a series of instructions that run and finish. The alternative is a function, which runs a series of commands and then returns a value to the calling routine. That's beyond what we're discussing here so I'll leave that for another day.

    The word Private indicates that this routine can be called only from this particular form. The alternative is Public, which indicates the routine can be called from any place within the application. For a form, you'll almost always want to leave the default as Private.

    The phrase Combo_1 AfterUpdate indicates that this subroutine will run after the user makes a selection from the combo box called "Combo_1".

      2. ' Find the record that matches the control.

    Notice that this line starts with an apostrophe. The apostrophe indicates a comment, or a line in the subroutine that serves as a note to the developer or user about what the routine is doing. This line is not perceived as code that will run in the subroutine. When you get to the point of writing your own code, I encourage you to comment it liberally. It will really help you understand what's going on when it comes time to debug.

      3. Dim rs as Object

    We're getting more obscure here, but it's not too difficult. Dim is short for dimension and it indicates to Access that it will be using a variable (something that can change or vary); rs is the name of the variable. The "as object" is a special type of variable- once again variable types are something I'll cover at some time in the future

    These first three lines have “set the scene”, so to speak. The next line is where things start to happen.

      4. Set rs = me.Recordset.Clone

    The Set rs indicates that the variable declared in line 3 is being set to something – and that something is me.recordset.clone. Let’s dissect this by looking at the 3 specific components between the periods.

    The me specifies the form that the code is associated with, in our case the camper detail form. The recordset indicates the set of records (think of them as rows in a query or table). The word clone is the only truly tricky part of this line. Clone in this case means a copy of the underlying recordset (group of records in the form.) The use of the clone is how you manipulate a group of records in Visual basic, the programming language on which Access is based. This is a rather complex subject that we'll also leave for a future discussion.

      5. rs.FindFirst "[CamperKey] = " & Me! [Combo1]

    This line is a bit tricky as well. The rs.FindFirst indicates that we’re examining the recordset clone we just dumped into our rs variable to find the first record that meets our criteria. This is the set of instructions we’ll to use to find the record we're interested in. Our criteria, surrounded by double quotes, is the field CamperKey (the Wizard adds the square brackets, which are optional in this case). We’re looking for the camper key that's equal to Me![Combo1]. Let's pull this apart. The Me again refers to the form. The exclamation point indicates that on the form it should find whatever follows the exclamation point, in our case Combo1, the name of our combo box. So to put that line into English, we’re looking for the first record in our copy of the records (or clone) on our form that equals what the user selected in the combo box. The combo box uses the CamperKey which is what we'll use to find our record. You'll hear even more about why we use keys rather than something like last name as we go forward.

      6. If Not rs.EOF Then Me.Bookmark = rs.Bookmark

    This is the last line of true code. If you look back at line 5, we are looking for the first record that matches the value selected by the user in the combo box. This line basically says if you don’t get to the end of the clone recordset without finding the record that matches the camper the user selected in the combo box, put a marker (Bookmark) to indicate the matching record in our copy (clone) of the recordset. That record should also be marked in the camper dataset. The form then uses this bookmark to jump to the selected record.

      7. End sub.

    This line lets Access know that this subroutine is finished. So, that's what happens if a matching record is found; but what happens if no matching record is found? The IF statement in line 6 says do this only if the condition is true. If it's not true, then nothing happens and your user will continue to see whatever was on the form before he made the selection..


    Conclusion

    Although at times the code behind an event or an object like a combo box can seem mysterious, it’s an interesting exercise to take a look at what the Wizard generates behind the scenes. As kids, we made good progress understanding the construction of a golf ball by tearing it apart. As with that case, you may be surprised how much you learn if you take the time to dig into the code. By looking at and understanding existing code you've taken a major step to writing your own!


    Trap of the Month - Override the Spell checker

    Periodically, a database field will contain a certain combination of valid keystrokes similar enough to an English word to cause the Office spelling tool to leap to your rescue and ”fix” your mistake. This phenomenon is notorious with acronyms. For example, one of my clients has a certain service they provide call IHS. When I type this string into a text box in Access, it’s invariable “fixed” to read HIS. This is the built in spell-checker at work, and in 99% of the cases, you actually want it to make corrections for you (especially if you’re as poor a typist as I am). But sometimes, as described above, you want the spelling tool to leave the string alone. To stop the spelling tool from fixing mistakes in a textbox:

    1. Open the form in design view
    2. Open the properties window of the textbox (View | Properties while in design view)
    3. Choose the Tab labeled Other and
    4. Change Allow Auto correct to False

    From that point on, the spelling tool will not make any automatic spelling corrections in the selected text box.


    Tip of the Month – Rename your Combo Box columns

    If you use a meaningful naming convention for your tables and fields (a practice I heartily recommend) you’ll likely want to show your users the field names (e.g. the name of a column) in plain English (as opposed to a name which may not make sense to them) when you build combo boxes. This gives your database more polish and it's easy to do!

    The first step is to show the column heads in your combo boxes. You do this by opening the combo box in design view, clicking on the format tab, then changing the column heads property to yes. This tells Access that you want each column to show the title of the field you're displaying. At this point if you do nothing else your users will see your field names.

    The second step is to turn your native field names into English. While still in the combo box design view, choose the properties sheet and click on data tab. Under the row source property, click on the ellipsis (. . . ) to the right. This brings up the query by example view that you normally see in a query. For each column head, click in the Field row of the grid and precede the field name with what you want your user to see, followed by a colon – just leave the rest of what was there to right of the colon.

    Now when you or your user clicks on the combo box, they will see an understandable column name---a name you’ve selected--- at the top of the column.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services



    Join our mailing list!

    Forward email

    This email was sent to jim@custom-software.biz, by jim@custom-software.biz
     
     

    Custom Software | © Copyright Custom Software All Rights Reserved | Westford | MA | 01886