Editing contents of a table by showing word example instead of letter in combo box b

[KateThatcham]

Hi - I'm quite new to VBA, using Access 2007. Apologies if this is a stupid question !!

I have created a form for users to view the details from a table, so they can edit fields in that table - e.g. where the optical mark reader has misread a field on the paperwork. What would be easiest for the user would be if the DLookup gave the explanation of the field rather than just the code letter, but I need the table to always have just the letter stored.

Is this possible?

For example, the table has letters a-n representing different answers for the date the property was built.

I use a combo box with two columns to show the letter and the explanation e.g.: a;before 1900;b;1900-1920 etc.

If I make the 1st column to be width zero, it only shows the explanation in the drop down menu, but the DLookup query doesn't seem to work as the field is blank. The letter only shows if I have column widths for both letter and explanation in the combo box.

I also need any change to be stored as the associated letter in the table.

I have been using a Value list as the row source. There are 20 fields which could be edited.

Any advice gratefully received !! Thank you.

[jforbes]

I tried to find and Article or Post here on Bytes that explains the Basics of ComboBoxes, but I failed to find one.

So... Comboboxes can be a little confusing until you get used to them.

Probably the first thing that you need to wrap your head around is RowSource and ControlSource:

• RowSource - Works in conjunction with RowSourceType to provide the ComboBox's drop down list; the values the User can select from.
• ControlSource - Is the Field on the Forms RecordSource that the ComboBox displays and Edits. This is the Field that is saved in a Table. Filling this in Binds the ComboBox Control to the Field. Without a value here, it is considered an Unbound Control.

Once you've got down that there are two different places the ComboBox gets information from and one of those places is where it stores the selected value, you can turn your attention to what is displayed in the Drop Down List of the ComboBox. The next few fields to get acquainted with are ColumnCount, BoundColumn, ColumnWidths:

• ColumnCount - Lets the ComboBox know how many columns to display to the User. The ComboBox's RowSource may return five columns from a select query, but if the value here is two, only two columns will be displayed to the User.
• ColumnWidths - is a list of widths for the columns in the RowSource. Typically, there are a number of entries equal to the ColumnCount, so the ComboBox has a Width for each Column it is to display to the User. Additionally, if the width for a column is Zero, the column will be hidden from the User.
• BoundColumn - This is the Property that links RowSource and ControlSource together. It tells the ComboBox which column in the RowSource to stuff into the ControlSource when the User selects a value.

I think once you get this down, you will be able to get Access to do what you want.

[twinnyfo]

Kate,

Also, in addition to J's sound advice about Combo Boxes, it appears that your DB may not be properly normalized (which would also help to alleviate many of your problems. Please refer to the Database Normalization thread that we send folks to, to review some basic principles that may assist you with this.