Nov
19

A Better Way to Create Lookup Fields

By Bob S

Microsoft Office Access

Image via Wikipedia

You might be tempted, when thinking about creating a Lookup field in Microsoft Access, to use the Wizard.

Don’t.

It involves too much cleanup work after you are done. For example, if you use the Lookup Wizard, you must destroy any existing relationship between the two tables. During the running of the wizard, you will see a recommendation to hide the key column. This will also cause confusion, because if you do this, the field stored and the field displayed will be two different items. And finally, if you did have a relationship between the two tables with referential integrity set up, you must go back into the relationships after completing the wizard and reset the integrity.

Why do all of this?

Why not just change the properties of the field on the Lookup tab so that the field becomes a lookup field. It is faster and easier if you know which tabs to change. The list below shows how each property on the Lookup tab should be set. Simply follow these suggestions and you will have Lookup field without the bother of correcting and resetting properties.

Command

Setting

Display Control

Select Combo Box

Row Source Type

Choose Table/Query

Row Source

Create a query that displays all the fields you would like to see in the dropdown area.

Bound column

1

Column Count

Match the number of columns in your query

Column Heads

Leave this as No

Column Widths

Set the width of each column in the drop down area and separate them with semicolons

List Rows

Set the number of items to be displayed when the dropdown button is selected

List Width

Sum the column widths in the previous property

Limit to List

If set to Yes, no new entries are allowed, and auto-complete is enabled

Leave the rest of the properties alone.

Reblog this post [with Zemanta]

Technorati Tags:

Filed Under Desktop Education, Microsoft Access, Office |

Aug
5

Can You Wrap Text in an Access List Box?

By Jane H

Recently I was asked if you can wrap text in a list box on an Access form. The short answer is no.

However, you could try putting an unbound text box near the list box. In the text box properties set the Control Source to point back to the list box property. In the image below, I’ve set the control source to =[lstCharity].[column](0). In this example, 0 is one less than the column number I want to display in the text box (My list only had one column). If your list had two columns, you would use 0 to display the first column, 1 to display the second column.

WrapText1

The results, as you can see in the following image, allows you to have a list box with long entries and still be able to read them.

WrapText

Technorati Tags:

Filed Under Microsoft Access |