Combo boxes are a fantastic way to show meaningful information
while storing something cryptic to the human eye.
A combo box is a combination of a list box and a text box.
Hi, this is crystal.
A list box displays rows and one or more columns,
the source of which can be from tables that are not in the form record source.
A text box allows a user to type.
Combo boxes can be used on forms and reports.
This database is to record Payments that Customers
make on Orders of Products.
Products are custom-built, and there is a separate order for each.
In the Customers table, CustomerID is an AutoNumber field
and is also the Primary Key.
In related tables like Payments, CustomerID is a Long Integer
with no Default Value.
This form collects payment information from customers.
The Record Source for this form is the Payments table.
The problem is that CustomerID is a number,
which is not very helpful for entering data.
So how can a list of customers be displayed so choosing the right one is easy?
One answer is to turn the textbox into a combo box
that gets its list from the Customers table.
In the design view of the form,
right-click on the CustomerID control.
From the shortcut menu, choose
Change To > Combo Box.
If the Property Sheet is not showing,
click the Property Sheet icon on the DESIGN ribbon tab,
or right-click and choose Properties from the shortcut menu,
or press Alt-Enter.
With the combo box selected,
click in the Row Source property on the Data tab of the property sheet.
The Row Source is where the list will come from.
Go to the query builder by clicking the 3 dots to the right,
or press Ctrl-F2.
The Show Table dialog box appears.
Add the Customers table by double-clicking it,
or selecting it and choosing Add.
Then Close the Show Table dialog box.
Since the combo box needs to store CustomerID,
double-click it in the field list
so it appears on the grid in the first column.
The second column will be CalcCustomer,
which is a calculated field with the formula:
CustMain & (", " + CustFirst)
You can sort by the calculated field.
I prefer, however, to sort by the fields that go into the calculation,
so I will add CustMain and CustFirst to the grid.
CustMain is the company name, or last name of a human.
CustFirst is the first name of a human.
Under each of these columns,
I choose Ascending order, and uncheck the Show box.
Look at the datasheet view and resize the customer column
to see how wide it should be.
Looks like about 2 inches should do it.
Close the query builder, and Save it.
On the Format tab of the property sheet,
change the Column Count to 2.
Set the Column Widths to 0;2"
The width of the first column is set to 0 so it will be hidden.
Column widths are delimited with semi-colon (;)
If your units are not inches, adjust accordingly.
Two inches is about 5 cm.
The List Width will be 2.2",
which is the sum of the column widths
+ 0.2 inches to allow for a scrollbar,
or 5.5 cm.
Save the form and look at the form view.
Customer is now a combo box
that stores CustomerID and shows the Customer name.
The Row Source for a combo box is an SQL statement,
a table name, or a query name.
When the builder is used, the SQL statement
to retrieve data can be saved.
Because the Row Source can come from multiple tables and queries,
we will add a column to also show the total amount of Orders for each customer.
Go to the query builder for the combo box row source.
Open the Show Table dialog box and add the Orders table.
Put CalcTotal on the grid.
This is a calculated field with the following equation:
Price+ IIf(IsNull(AmtTax),0,AmtTax) +IIf(IsNull(AmtAdj),0,AmtAdj)
I've said the words, but you need to type the fieldnames.
Because a Customer can have multiple orders,
click the Totals button on the ribbon
to add a Total row to the grid.
The default value is Group By,
which is what we want for fields from the Customers table.
Under the CalcTotal field, change Group By to Sum.
Because there will be more than one column displayed,
it will be good to show column headers,
which means we should change the field names
to be more friendly.
On the grid, in the Field cell,
change CalcCustomer to Customer: CalcCustomer,
and CalcTotal to SumOrders: CalcTotal
Anything before the colon (:) will be the column name.
This looks good.
I close and save the SQL statement for the Row Source.
Now that there is another column,
properties need to be changed.
Set the Column Count to 3
Column Widths to 0;2";1"
List Width to 3.2"
and Column Heads to Yes.
Now the combo box displays the sum of orders for each customer!
The final touch will be to show the amount of total orders for a customer
without having to drop the combo box.
Create a textbox control.
Delete the associated label control.
Change the Name of the control to CustomerID_SumOrders so it is logical.
The Control Source property on the Data tab will be
=CustomerID.column(2)
Column indexes start at 0 (zero), not 1,
so index 2 is really the third column.
Set the Tab Stop property on the Other tab to No
so it is not a stop
as the user presses TAB to advance through controls on the form.
When a value is Yes on the property sheet,
you can double-click it to change it to No.
To make this data look different than that which must be entered,
I set the Fore Color property to Blue.
This can be done on the property sheet,
or by picking the color from the Font Color tool
on the HOME or FORMAT ribbon tab.
Looking at the data,
I see that it is not displayed as Currency.
I set the Format property to Currency,
but still it shows as it did.
That is because columns of a combo box are text, not numbers.
To get the Currency format to apply,
the Control Source property can include conversion to a number.
The formula in the Control Source can be wrapped with CCur.
CCur is Convert to CURrency but it doesn't work for Null.
Therefore, before CCur is done, if a value is missing,
NZ, or Null-to-Zero, can be used.
The final equation is
=CCur( Nz( CustomerID.column(2),0 )) -- , 0 is optional for Nz
What I also did, that apparently got cut from the video,
was change the Shape Outline to Transparent.
This is saved in the Border Style property.
Then I set the paragraph alignment to Left
so there is not extra space before the number.
I save the form and look at the form view.
As I scroll through the records,
the customer name is displayed in the combo box,
and their total orders are displayed to the right.
In this lesson, you learned how to change a textbox into a combobox
so that meaningful information is displayed
while a numeric foreign key is stored.
Thanks for joining me.
Through sharing, we will all get better.