Combo Box Data Source And Formatting

Consider a simple form for entering data into a product ordering template. The form has combo boxes for customer name and product name. It also has text boxes for price quoted, quantity and a calculated field for extended price. The data source for this form is the Orders table.

If you are used to using textboxes that link to the underlying data source, you think only of the Control Source. When the form displays, the Control Source dictates what displays in the text box and when you edit the text box the Control Source dictates where the data goes - the same field in the same record of the same table (or other data source).

For combo boxes you may be tempted to base the form on a multi-table query that contains things like customer name and customer ID; product name and product ID. But this can cause your data source to become a non-updateable recordset  and it is entirely unnecessary. The combo boxes on your form should have different data sources than the form itself.

Unlike a textbox which reads from and writes to the same Control Source, a combo box reads from one place and writes to another. The combo box reads from it's Row Source and writes to its Control Source. This makes sense when you consider that you'll have just one selection but many options.

Are You Ready For Office 365?

Office 365 is

• Email and calendaring powered by Microsoft Exchange
• Office Web Apps - the office productivity apps you already know, tailored for the web
• Websites powered by Microsoft SharePoint both for internal-facing intranet and customer-facing website
• Instant Messaging and Online Meetings powered by Microsoft Lync
• All with a financially-backed 99.9% uptime guarantee

Sign up for a free trial:

Name
Company
Email Address

Your information will not be shared with any other party. You must provide a valid email in order to recieve your login information and starting information kit.

Watch this video  to see how Office 365 can help your business.

Choose the first option; use a table or query.

In our case, we'll use the Customers table for the combo box, even though we'll be pushing our data into the Orders table through the form.

Include the CustomerID (since that's what we'll push into the Orders table) and the Customer Name (since that's what our users will want to see.)

Note that the key column has been hidden since the number will be meaningless to the user. We'll see later that this means that the column width for our first column has been set to zero inches (0").

Now the wizard know where we're reading data from, it asks us where we want to write it to. The above box indicates that we'll take the CustomerID from the Customers table and write it into the CustomerID field in the Orders table.

We're finished with our combo box and we kept the data source for our form very simple. But if you just hate wizards? What if you already made your combo boxes and you just need to tweak them a little? What are the properties of the combo box that the wizard sets?

In a different order from the wizard (matching these up to wizard steps is left as an exercise for the reader):

Control Source: the field in the form's data source the data gets written to

Row Source: the name of a table or query or the SQL statement that populates the possible choices in the combo box

Bound Column: If you have three fields displayed, which one of them gets written into the Control Source. Here, we are writing column 1 (Products.ProductID) into the Control Source (Orders.ProductID)

Column Count: Explicitly state how many fields are returned by your Row Source

Column Widths: You can explicitly state the width for all columns, but you can (and I usually do) leave the last one blank. This tells Access to fill up whatever space is left with the last column. For a typical combo box with just two columns where the first is hidden, it looks like 0". If my combo box is 2" wide, I really should write 0";2" just to be explicit.

Good luck!