Do you find the Support WebCast transcripts helpful?
Let us know!

August 3, 2000

Note This document is based on the original spoken WebCast transcript. It has been edited for clarity.

Heidi Moeller: Hello, and welcome to the Microsoft^® Support WebCast program. We'd like to thank all of you for joining us today. Our topic will be "Microsoft Access Combo Box Survival Guide," and our presenters will be Dale Dixon and Steven Parsons. I'm Heidi Moeller, and I'll be your host for today's session.

We'll start this session with Dale and Steve's presentation, and follow that up with a question and answer period when the presentation is finished. We only answer questions submitted for the Support WebCast during the live event.

I'd like to now just take a brief moment to introduce Dale and Steve. Dale Dixon joined Microsoft as a Support Professional in October of 1995. He's been involved with developing multimedia for several projects and is now involved in developing a training-based Web site for the OCS unit. Since starting at Microsoft, Dale has focused on Microsoft Access support and training. He currently works for Product Support Services, delivering customer support solutions.

Steven Parsons joined Microsoft as a Support Professional back in April of 1994. His primary focus has been on content development for Microsoft Knowledge Base. During Steven's time at Microsoft, he's been involved in MS-DOS^® support, Windows^® support, FoxPro^® support, and currently, Microsoft Access support. Thank you both so much for joining us today. Let's go ahead and get started.

Dale Dixon: Hi. This presentation is focused on using Microsoft Access applications. I'll start the presentation with a focus on the following areas: a combo box description, uses of combo boxes, combo box properties, and combo box creation.

Steven Parsons: I'll present the latter part of the broadcast with a focus on other items like how to find or move to records selected using a combo box, how to synchronize combo boxes, and other cool tips.

Dale: Thanks, Steve. Let's take a look at slide 3, "Different Controls." One of the areas I've always been interested in addressing is how controls are used in Microsoft Access, specifically, on forms. Although we're able to use many controls like the text boxes, check boxes, option buttons, tab controls, command buttons, and list boxes, that you see here, the most intriguing control, to me, is the combo box. This is probably because the combo box is a control that has a dual purpose, as the name implies. The combo box is closely related to the list box control, but has a few differences, and we'll discuss those in a moment.

On the next slide, I'll describe a combo box. It's a combination of a text field and a list box. You can enter a value directly into the text area of the combo box or you can click the directional arrow on the right portion of the combo box to display the list. You can also display a single column or multiple columns of data in a combo box.

Combo boxes are quite useful. As I mentioned earlier, a combo box is similar to a list box. A list box will display the list of data, but you must view the entire list, which could take up some valuable space on your form, as you see on this slide, whereas a combo box is much more conservative with this space. Also, a list box does not allow you to enter data, but a combo box will allow you to enter data and view the list. Combo boxes are great controls to assist in finding records based on user selections, as Steve will talk about in a moment.

Combo boxes, as you can see on slide 5, have properties, as most controls do, which can be set to different values. For this portion of the presentation, we'll present some of the properties, which appear on the Data tab and the Format tab. Let's first take a look at the Data tab for the Control Source, Row Source Type, Row Source, and the Bound Column properties.

Notice the Control Source property. With it, you can specify what data appears in a control. You can also display and edit data bound to a field, in a table, a query, or a SQL statement. You can also display the result of an expression. For instance, the syntax forms!customers!addresspart.controlsource="city" will set the Control Source property for a text box named "addresspart" to a field named "city."

In that same Data tab, we also find the Row Source Type and Row Source properties. With both properties together, you can tell Microsoft Access how to provide data to a combo box. For example, to display rows of data in a combo box from a query named Customer List, set the combo box's Row Source Type property to Table/Query and this Row Source property to the query named Customer List.

There are different Row Source Types available. A value list is where the data is a list of items specified by the Row Source setting. A field list is where the data is a list of field names from a table, query, or Microsoft SQL Server™ statement, also specified by the Row Source setting. You can also use a default Row Source Type, which is a Table/Query list. This is where the data is from a table, a query, or a SQL statement, also specified by the Row Source setting.

The last property to present, but certainly not the only property available on the Data tab, is the Bound Column property. When you make a selection from a combo box, the Bound Column property tells Microsoft Access which column's values to use as the value of the control. If the control is bound to a field, the value and the columns specified by the Bound Column property are stored in the field named in the Control Source property. If the Bound Column property is set to zero, the List Index property value, rather than the column value, is stored in the current record.

On slide 6, let's take a look at the Format tab. All the properties on the Data tab and Format tab are also located on the All tab. Let's look at the Format tab, and we'll discuss the Column Count, Column Heads, Column Widths, List Rows, and List Width properties.

With the Column Count property, you can specify the number of columns displayed in the list box portion of the combo box. For example, if you set the Column Count property for a combo box on an employee's form to 3, one column can list last names, another can list first names, and a third can list employee ID numbers. With the Column Heads property, you can display a single row of column headings for your combo box. You can use the Column Widths property to specify the width of each column in a multiple-column combo box or list box. You can also use this property to hide one or more columns.

The List Rows property shows the number of rows based on vertical height. You can use the List Rows property to set the maximum number of rows to display in the list box portion of a combo box. And finally, with the List Width property, you can set the width of the list box portion of the combo box.

On slide 7, we find that there are two methods to create a combo box: either by using the wizard, or manually creating the combo box. Using the wizard is fairly straightforward. An advantage to this technique is that you don't have to understand the underlying mechanics to create a combo box. Creating a combo box is relatively simple.

To start the wizard, simply ensure that the Wizard control button in the Toolbox is depressed before you insert the combo box on your form, then simply follow the wizard's instructions. As you can see on the slide, one important point in the wizard process is the selection of options that are available. There are three: I want the combo box to look up the values in a table or query; I will type in the values that I want; and Find a record on my form based on the value I selected in my combo box. The last option is probably the most used option.

To create a combo box manually, you simply add the control to the form. However, you have to manually set the values and the properties and write the necessary code to make it operate in the fashion that you like. Just ensure that the Wizard control button is not depressed. The advantage with this method is that you have complete control from start to finish. Steve.

Steven: Thanks, Dale. As you stated earlier, finding or moving to records is important in a database. Not only does it make sense, but also, it makes finding and editing data easier and more efficient. So first, I'd like to describe four methods of moving to a specific record based on a combo box selection. The methods are listed here (and on slide 9), as follows:

1. In the AfterUpdate property of the combo box, use the FindFirst method to find a record.
2. In the AfterUpdate property of the combo box, call a macro using the ApplyFilter action.
3. Use a Form/Subform where the combo box on the main form and the data in the subform are bound by the LinkMasterFields and LinkChildField properties.
4. Base the form on a query that joins two tables, and use the AutoLookup technique to bind the combo box to the field that controls the join.

During this broadcast, each of these methods will be applied within the sample database, Northwind that ships with Microsoft Access.

The first method, using the FindFirst action, requires that you first create a form based on a table. In this case, we called the form frmComboTest, and we based the form on the Products table. On slide 10, we see the form and some its properties. This will introduce you to some of the properties of the combo box. However, there are others to consider when using the methods described. In this case, we set the form's DefaultView property to Single Form. Next, we add an unbound combo box by using the Control Wizard. Make sure that the Wizard control button in the Toolbox is pressed in before you create the combo box.

When you start the wizard, choose the third option: Find a record on my form based on the value I selected in my combo box, and then click Next. Include the Product ID and Product Name fields, and click Next again. After you click Finish, the wizard will create an event procedure similar to the code that is displayed on slide 11.

In this code, you're simply generating a recordset based on the Forms recordset, and you set the recordset's bookmark equal to the form's bookmark. At this point, you can view a frmComboTest in Form view. Notice that when you choose a product name in the combo box, you are moved to the record selected.

In our next method, we use a form called frmComboBox2, that's also based on the Products table, and we'll keep the form's DefaultView property set to Single Form. But this time, we will use a filter. We will set the Filter property of this new form to the following syntax: [the name of the field we want to filter - Forms]![frmComboBox2]![the name of our combo box].

Now we need to add an unbound combo box, and we'll call it cboLookup. We can place this anywhere on the form, but preferably in the form header or footer. We'll set the combo box properties as we see here on slide 12. We'll set the Name property to cboLookup. We'll set the Row Source Type to Table/Query. We'll set the Row Source to the name of our table, in this case, Products. We'll set the Column Count to 2. We'll set the Column Widths to 0"; 2". We'll set the Bound Column property to 1. And then we'll set the After Update event to the name of a macro, which we are about to create.

When we create this macro, we're going to call it mcrLocateProduct. So we set the After Update event to that name. On slide 13, we see the macro settings that we're going to use. We're going to use two actions: SetValue and Requery. SetValue is going to set the FilterOn property of our form to True, and then will requery the form. So when we open our new form and select a product name from cboLookup combo box, the filter is set to that value and the form displays the matching record.

In the third method, we'll use a Form/Subform, with the combo box in the main form, and the data in the subform, bound by the LinkMasterFields and LinkChildFields properties. We see this on our next slide displaying the LinkChildFields, which is set to ProductID on the subform and LinkMasterFields property is set to the name of our combo box on the main form, cboLookup.

First, we create a new form, not based on any table or query. We add a combo box and we set the controls properties, as we displayed on slide 15. The Name property is set to cboLookup and the Row Source property set to Products.

On the next slide, we see that a text box bound to ProductID can be added to the main form, but this is not absolutely necessary. If we use this approach, however, then we need to go back to our LinkMasterFields property and change it from the name of our combo box, cboLookup, to the name of this field, ProductID.

Next, we create a second form based on the product's table, to use as our subform. We set this form's DefaultView property to Single Form as well. Include at least the ProductID field on the new form. However, I recommend that additional fields be added, so that we can verify that we're actually moving to the matching record.

On slide 17, we show our subform that we will add to the main form. We save the form and then we use it to create a subform control on the first form by dragging it from the Database window to the Detail section of the first form. So on slide 18 we see the design view of our main form. In the Form Header section, we see our combo box called cboLookup. In the Detail section, we see the subform that we just created.

When we view the main form, notice that by changing the value in cboLookup, Access ensures that the records in the subform match the combo box. Again, this is done by setting the LinkMasterFields and LinkChildFields properties of the main form.

In our fourth method, we base the form on a query that joins two tables and use the AutoLookup technique to bind the combo box to the field that controls the join. So first, we're going to create a table called tblProductSelect, and we see this on slide 20. We're going to set the field's Data Type to Number, the Field Name to ProductID, and the Field Size to Long Integer. This field doesn't have to be a primary key field, but it can be. We definitely do not want to add new records to this table.

Next, we're going to create a query called qryProductSelect. It's going to be based on a join between the Product ID fields in the tblProductSelect table and the Products table. The next slide shows our query in design view. On the left side is tblProductSelect table, with our single field called ProductID. On the right of that is our Products table, with ProductID and other fields. The key here is to make sure that we add the ProductID field from tblProductSelect to the QBE grid of our query. As you see here on this slide, we have ProductID, and in the Table row it says tblProductSelect. The other fields are coming from the Products table: ProductName, SupplierID, and so on.

We create a form, and we're going to base the form on this new query that we just saved, qryProductSelect. We're going to all the fields to the form. When we view the form, as seen here on slide 22, we see that Product ID appears as a text box, so we want to put the form back into the design view, and we're going to morph that text box to a combo box control. After all, the idea here is to find a record based on a combo box.

On slide 23, we see our text box has been converted to a combo box. When we now run the form, we can select the value from the combo box, and the other fields on the form will automatically update to the correct record. Let's switch gears here a little bit, and we'll talk about a different subject: synchronizing two combo boxes.

On the next slide, we show a form in the Northwind sample database. And in this form, we have two combo boxes: one called Categories and the other one called Products. The idea here is that when we select a category from the Categories combo box, the Products combo box will only show products that match that category.

After opening Northwind, we create a query based on the Products table. we set the query properties as displayed on slide 25. In this query, we have the Products table. And in the CategoryID column, we're going to reference the Categories combo box that's on our form. This is called query by form. We're now going to save the query and call it Category Combo Query.

Then we create a new form, which in this case, is not based on any table or query, with the combo boxes shown on slide 26. On the left side, we see the properties for our first combo box. The combo box is called Categories and its Row Source is the table called Categories. On the right side you see the properties for the second combo box. It's called Products, and its Row Source is based on the query that we just made, called Category Combo Query.

Now we're going to create a new macro. We're going to save this macro with the name of Refresh Products, as you see displayed here on slide 27. This macro has a single action called Requery, and notice that the Action Argument for Requery is Control Name. We are going to Requery the second combo box called Products. When we now view the Categories and Products form in form view and we select the category in the first combo box, the second combo box is updated to list only the available products for the selected category.

On slide 28, we list several tips that are frequently asked by customers who call in to Microsoft Support. In this first tip, we want to make a combo box that defaults to the first value in its list. We set the DefaultView property of that combo box to the first value in the combo box list. What happens is, every time we go to a new record, that combo box automatically inherits the first item in its list. Dale, do you want to discuss this next tip?

Dale: Sure, Steve. In certain situations I find it helpful to be able to manipulate a combo box without using a mouse. There are keystrokes that we can use to manipulate the combo box. For instance, pressing the F4 key or the ALT+DOWN ARROW will open a combo box. Or, if we have a different control, like a list box, it would do that also. The DOWN ARROW will move down one line in the combo box, and PAGE DOWN, will move down one set of values in the combo box. The UP ARROW will move up one line, and the PAGE UP will move up a set of values. If there is an entry in the combo box that we want to select, we simply press the ENTER key. Steve.

Steven: Okay. This third tip, how to automatically open a combo box, is a pretty neat thing and a lot of users want to do it. What you have to do is trigger the drop-down method of the combo box. For example, in the OnEnter event of the combo box, which is another property of the combo, you can execute a single line of code that reads similar to me!the name of my name combo box.drop-down. As soon as the user lands on that combo box, it triggers the OnEnter event, which, in turn, runs our line of code and automatically opens the drop-down list for us. Dale.

Dale: Steve, there are other areas where more advance coding techniques and methods could be applied to a combo box. However, I think we're going to have to save that for another WebCast. If you take a look at slide 29, we list some Knowledge Base articles that we'd like for you to refer to:

• Q105519: "How to Make a Combo Box Default to First Item in List"
• Q209011: "How to Start Macros, Functions, and Applications from a Combo or List Box"
• Q209595: "How to Synchronize Two Combo Boxes on a Form"
• Q209537: "Four Ways to Move to a Record from a Combo Box Selection"
• Q161007: "Use NotInList Event to Add a Record to Combo Box (95/97)"
• Q139039: "How to Include a Null Selection in a Combo Box List"

Steven: Those are really good resources, Dale. On slide 30, we list a few more from our Microsoft Press^® collection of books. Most of the books here are for the beginner user in Access. They're highly recommended if you're new to the product. The first one is Microsoft Access 2000/Visual Basic^® for Applications Fundamentals. The second one, Programming Microsoft Access 2000, is good for both the beginner and the intermediate user. The third one is Microsoft Access 2000 Step by Step. And the fourth one is Running Microsoft Access 2000.

That concludes this portion of our WebCast. We'll now turn the WebCast back over to Heidi for any questions.

Heidi: Excellent. Thank you very much to both of you. It is time to move on to the Q&A portion of this Support WebCast. A couple of comments before we do move on. If you would like to locate information on all upcoming Support WebCasts as well as get information and the content from all of our past Support WebCasts, please go to http://support.microsoft.com/webcasts.

For every Support WebCast, we have on-demand streaming media, PowerPoint^® slides, and within three weeks of the live session, full session transcripts. So we do have lots of options available for you if you missed part of the session or you'd just like to have a hardcopy of those slides. I know that sometimes some of the details of those slides are little bit difficult to see from within your browser.

Now on to the Q&A session. The first question is: How can I assign dynamic values to my combo box?

Steven: The answer to this question is quite involved, and as Heidi alluded to a moment ago, it's going to be beyond the scope of what this WebCast is meant for. But just let me briefly say that to do such a thing would involve the use of user-defined code. We would call functions from some of the properties that Dale talked about earlier, such as the Record Source Type property and the Record Source property.

Heidi: Okay. Excellent. The next question is: If my combo box has two or more columns, how can I retrieve the values that appear in the other columns?

Steven: This is going to involve the use of a little bit of code too, but not much. What you're going to do is reference the Column property of the combo box. You won't find this property listed in the property window, but it is, in fact, a property of the combo box.

If you have a situation where the combo box is made up of two or more columns, keep in mind, as Dale said at the beginning of the WebCast, we can only bind one column to the combo box. How do we get the information from the other columns? We use the Column property. If you want to find out more about the Column property, I suggest you look in the online help of Access.

Heidi: Okay. Excellent. I want to encourage all of you who attended (or listened to the on-demand content) to send us your feedback on this session specifically, any other topics you'd like to see in the future, and any comments or feedback you have about the program overall. We want to make sure that we're hitting the mark with this program, and giving you beneficial content. So we highly value any feedback you have for us.

Moving on to the next question: How can I filter my form based on the current combo box selection?

Dale: You can filter your form using any of the methods that Steve talked about earlier. And let me also recommend that reviewing Knowledge Base article Q209537, "Four Ways to Move to a Record from a Combo Box Selection," would also assist in filtering your form based on your current combo box selection.

Heidi: The next question is: Can I trigger a second combo box to drop down with values based on selection in the first combo box?

Steven: Yes, you can. In this case, you would have to put an event procedure, like the After Update event of the first combo box, that would move the focus to the second combo box. You would do that using the SetFocus method. And then after you've set focus in that second combo box, it would have its own OnEnter event that would trigger the drop-down and automatically open the box.

Heidi: Okay. Excellent. The next question is: What happens if I change my bound column?

Dale: As we discussed earlier, the Bound Column property tells Microsoft Access which column's value to use as the value of the control. If you change the bound column, you're actually changing which column to use to apply a value to that particular control.

Heidi: The next question is: Is it possible to change a combo box to a different control?

Steven: You may remember that I used the term "morph" earlier in the WebCast. That's the term we used to change a control from one type to another type, so yes, we can take a text box and we can morph that control to a combo box or to a list box. We can also go in the opposite direction, and take a list box or combo box and morph that back to a text box.

Heidi: Okay. With that question answered, we have cleared the queue of the all the questions that were submitted during today's Support WebCast. I want to thank all of you for joining us today. Again, a reminder, if you're interested in any of the archived content, we will have the on-demand streaming media available within about eight hours of the live session. It's a great way to get the content if you missed part of this session.

The PowerPoint slides are also posted, and transcripts will be posted in three weeks. Once again, I want to encourage you to submit your feedback to us using the alias feedback@microsoft.com. If you use that alias, be sure to include "Support WebCast" in the subject line. Thank you so much for joining us today. We hope you have a great day and have time to join us again the near future. Thank you, and good-bye.