Troubleshoot controls

#Error? or #Name? may appear in a control for a number of reasons. To correct the problem, do the following:

• Make sure that the field specified in the control's ControlSource property hasn't been removed from the underlying table or other record source.
• Check the spelling of the field name in the control's ControlSource property.
• If you specified an expression in the control's ControlSource property, make sure that there is an equal sign preceding the expression.
• Make sure that there are brackets around references in expressions to control or field names that include spaces. For example, to subtract a Shipped Date field from a Required Date field, enter the following expression: =[Required Date]-[Shipped Date].
• If you are using one of the built-in functions, make sure that you're using the right number of arguments, that the arguments are in the right order, and that you haven't left out any necessary punctuation. To determine the correct syntax to use, see the Help topic for the function you're using.
• If you are referring to another control, make sure that you're using the correct syntax.
• Make sure that there isn't a circular reference to a control. For example, if you specify MyControl in the Name property of a control, and then type =[A]+[B]+[MyControl] in the ControlSource property box for the control, Microsoft Access can't process the expression.
• Make sure that the Access file doesn't contain a missing reference to a project or library by opening a module in Design view, and clicking References on the Tools menu. Clear the check box next to any missing references, and then set the references you want.
• If the control is in a data access page and contains an expression that uses a function, make sure that the function is available in a data access page.

You use the Column property to refer to a column in a list box or combo box. The Column property might produce unexpected results for any of the following reasons:

• You haven't supplied an index number to the Column property. The index number identifies the column you're referring to. For example, Column(0) refers to the first column in a list box or combo box.
• You've specified an index number for the wrong column. Remember that index numbers for the Column property start at zero. Column(0) refers to the first column, Column(1) refers to the second column, and so on.
• You're performing a numeric calculation directly on the value returned by the Column property. Because the Column property returns a text value, you must convert it to a numeric value before performing a numeric calculation. For example, the following expression uses the CCur function to convert the value returned by the Column property to a currency value before multiplying it by the value of the Quantity field:

  [Quantity] * CCur([PriceListBox].Column(1))

If you are working in a form or report, you might need to bind it to a record source.

If you are working in a data access page, you might need to connect it to a database.

Microsoft Access uses a single ampersand in a label or button to define an access key. Therefore, if you want to use an ampersand (&) in a label on a form or report, you must type two ampersands. For example, if you want the text "Products & Suppliers" to appear in a label, type Products && Suppliers.

If you bind a control to a field, or if you change the underlying field for a bound control, Microsoft Access doesn't set the property settings for the control to the corresponding property settings for the underlying field. In forms and reports in an Access database, ValidationRule, ValidationText, and DefaultValue property settings in the underlying field will be applied, however, because these properties are enforced for the field, not just the control.

To select multiple controls in a data access page, you must have Microsoft Internet Explorer 5.5 or later installed.

If the property sheet is displayed and you click a control that's already selected, the property sheet will go blank. If you don't need to type a value in the control, press ESC to redisplay the property sheet. Otherwise, type a value and press ENTER.

If the property setting for a particular property is the same for all selected controls, that setting appears in the property sheet; otherwise, the property box for that property is blank. If you have multiple controls selected, and you change a setting for a property in the property box, that change affects all selected controls.

If you select multiple controls but don't see the properties you expect in the property sheet, you may have selected a control that you didn't mean to select. For example, you may have included a line in a group of text boxes. Properties appear in the property sheet only if they apply to all of the selected controls. To remove a control from a group selection, hold down the SHIFT key and click the control.

The property settings for a bound control might not match corresponding settings in the field in the underlying table that the control is bound to. If the settings are different, the form or report settings typically override those in the table, but they apply only to the current form or report. It's generally a good idea to set the Format, DecimalPlaces, InputMask, ValidationRule, ValidationText, and DefaultValue properties in the underlying table or query rather than in the control. This way, you can be sure that you have consistent settings for the field whenever you add it to a form or report.

You must select more than one text box or other control at a time in order to create a control group.

• Select two or more controls by dragging a rectangle over the controls or by holding down the SHIFT key while you click each control. Then on the Format menu, click Group.

You can ungroup selected text boxes or other controls only when one group at a time is selected on the form or report.

• Click any control in the group to select the group, and then on the Format menu, click Ungroup.

If you have applied conditional formatting to a control, then Microsoft Access applies the formatting depending on whether the control value or other evaluated data meets a condition that you specified.

• If you base the formatting condition on the text contained in the selected control by choosing Field Value Is in the Conditional Formatting dialog, then formatting such as bold or font color may change as the text in the control changes.
• Conditional formatting has priority over formatting that you apply directly to a control by using a Formatting toolbar button. If a specified condition is true, the formatting for that condition overrides the default formatting.

• Check for multiple conditions    If you specify multiple conditions and a value meets more than one condition, Microsoft Access applies only the formatting for the first true condition.
• See whether conditions overlap    If you specify conditions that overlap, Access applies only the format of the first true condition. For example, if you specify a condition that applies a yellow background to control values between 100 and 200 inclusive, and then you specify a second condition that applies a red background to control values below 120, the control values of 100 through 119 will have a yellow background. Try to avoid overlapping conditions.
• Check control references    If you used an expression as the formatting criteria, the control references in the formula may not be correct.

You might have placed the control outside the option group. When you add a check box, toggle button, or option button to an option group, Microsoft Access highlights the option group to show that the control will become part of the option group. If the option group isn't highlighted, you're probably placing the control too close to the edge of the option group.

Make sure that the controls in your option group are option buttons. You can use check boxes in an option group on a form, but not in an option group on a data access page.

If you use a wizard to create the list box, drop-down list box, or combo box, Microsoft Access automatically sorts the rows that make up the list by the first visible column. If you want to specify a different sort order for a control on a form or report, click the Build button next to the RowSource property box for the control, and specify a sort order in the Query Builder or SQL Statement Builder. If you want to specify a different sort order for a control on a data access page, create a query with the sort order you want, and then specify that query in the ListRowSource property of the control.

In a multiple-column combo box on a form or report, the text box portion of the combo box displays data from the first visible column in the list. You use the ColumnWidths property to determine which columns are visible. For example, if you have a combo box that is based on a query that includes the SupplierID field and the SupplierName field, in that order, then Microsoft Access displays the SupplierID field in the text box portion of the combo box unless you hide it using the ColumnWidths property.

You may have specified the wrong column in the BoundColumn (forms and reports) or the ListBoundField (data access pages) property box. The BoundColumn or ListBoundField property determines which column's data is stored in the field that is specified in the ControlSource property box. For example, if you have a combo box on a form or report that has a SupplierID column and a SupplierName column, the bound column would be the SupplierID column (the foreign key field) even if the SupplierName column is the only visible column.

You may have misspelled the field name in the ControlSource property box. You use the RowSource (forms and reports) or the ListRowSource (data access pages) property to specify the items that make up the list. When you click an item in the list, Microsoft Access needs to know where to store the value that you selected. You use the ControlSource property to specify where the values are stored.

In form Design view, you probably entered an inappropriate setting in one of the following properties: RowSource, ControlSource, BoundColumn, ColumnWidths, ColumnCount. To learn more about combo box properties, see Microsoft Access Help, or click the property box for the property and press F1.

In a form, to refer to a column other than the bound column, use the Column property. The Column property is zero-based. For example, to refer to the second column in the combo box called SuppliersCombo on the Products form, use this syntax:

Forms![Products]![SuppliersCombo].Column(1)

The LimitToList property is set to Yes. Set the LimitToList property to No so that you can type new values in the combo box. If the column displayed in the text box portion of the combo box isn't the bound column, Microsoft Access automatically sets the LimitToList property to Yes.

To automatically add a value to the list on a form, you need to write an event procedure and attach it to the combo box's OnNotInList property.

Note  You must set the LimitToList property to Yes to use the NotInList event.

There are several things you can do to make your list boxes, combo boxes, and drop-down list boxes run faster:

• Base the list box, combo box, or drop-down list box on a saved query instead of an SQL statement. On a form, if you use a wizard to create a list box or combo box, Microsoft Access automatically sets the RowSource property of the control to an SQL statement. To change the RowSource property to a saved query, click the Build button next to the RowSource property box. With the Query Builder window open, click Save on the File menu, and type a name for the query. When you close the Query Builder window, click Yes when Microsoft Access asks if you want to update the property.
• Base the drop-down list box on a saved query instead of a table. On a data access page, if you use a wizard to create a drop-down list box, Access automatically sets the ListRowSource property of the control to the table you specified in the wizard. To retrieve rows from a query instead of a table, select the query name from the ListRowSource property box.
• In the query specified in the RowSource or ListRowSource property box, include only fields that are absolutely necessary. Extra fields can decrease performance.
• Index both the first field displayed in the combo box, list box, or drop-down list box and the bound field (if the fields are different).
• In combo boxes on a form, set the AutoExpand property to No if you don't need the fill-in-as-you-type feature.
• On a form, if the AutoExpand property for a combo box is set to Yes, the first displayed field should have a Text data type instead of a Number data type. In order to find a match in the list, Microsoft Access converts a numeric value to text. If the data type is Text, Microsoft Access doesn't have to do this conversion.
• On a form, if the bound field in a lookup combo box is not the displayed field:
  • Don't use expressions for the bound field or the displayed field.
  • Don't use restrictions in the row source.
  • Use single-table (or query) row sources rather than multiple-table row sources, if possible.
• Don't create list boxes, combo boxes or drop-down list boxes based on data in a linked table if the data won't change. It's better to import the data into your database in this case.

Note  The Query Builder is not available in a data access page. Wizards are not available in a stand-alone data access page, or when you point a data access page to a database other than the one currently open. In both cases, you will have to create a list box or drop-down list box on your own.

Microsoft Access displays this message when it can't open the source file for an OLE object. Check to make sure that:

• The application needed to edit the file is installed on your computer.
• You have enough memory to run the source application. Close other applications to free up memory.
• If the application that you're trying to open is already running, make sure that it doesn't have any open dialog boxes.
• If it's a linked object, make sure that someone else doesn't already have it open.
• If it's a linked object on a form, make sure that the name of the source file hasn't changed or that the source file hasn't been moved. To see the name of the original file, click the object, and then click OLE/DDE Links on the Edit menu. The original file name appears in the Links box. If the file has been renamed or moved, click the link in the Links box, and then click Change Source to reconnect the link.

1. Open the form in Design view or the report in Design view.
2. Click the icon.
3. On the Edit menu, point to the appropriate Object command (for example, point to Worksheet Object for a Microsoft Excel object), and then click Convert.
4. In the Convert dialog box, clear the Display As Icon check box.

To keep a picture's proportions when you size it, use the PictureSizeMode property (for background pictures) or the SizeMode property (for bound object frames, unbound object frames, and image controls). If the picture is a bitmap, it's best to use the Zoom setting rather than the Stretch setting. You should also consider using a metafile rather than a bitmap since you can size metafiles without causing distortions.

If the picture you're displaying was created on a computer that has more colors than the computer that you're displaying the picture on, Microsoft Windows substitutes colors to match the original colors. This process is known as dithering. For example, you may be trying to display a picture that was created with 256 colors on a computer that only displays 16 colors (standard VGA). If your computer can display more colors, you can use a form or report's PaletteSource property to specify that Microsoft Access use the color palette that was used to create the picture.

If you're using background pictures with forms that have popup forms, it's a good idea to set the PaletteSource property of both forms to the same value. This is true if the form has a background picture and the popup form doesn't, if the popup form has a background picture and the form doesn't, or if both have background pictures. Windows can use only one palette at a time, so if you set the PaletteSource property to the same value, then you ensure that the colors you need to paint each form are available when the focus moves to the popup form or back to the form.

The amount of information that is used to represent an object is often greater than the size of the object itself. One way to work around this disproportion is to display an icon in the form or report that represents the object instead of the object itself. When you display an icon, the OLE server (the application used to create the object) sends only the information needed to display the icon. When you double-click the icon, the OLE server is launched with the data that the icon represents.