Combobox - Have a seperate field shown in forum, than the 2 fields in dropdown box?

[darlingm]

I'm using Access 2007.

I created a table named TableStates which has four fields: StateID (AutoNumber), Abbreviation (Text), State (Text), Combined (Text). Yes, I'm in the US.

There is a row in TableStates for each State, such as: 1, "AL", "Alabama", "AL (Alabama").

In another table named TableClient, I have a field StateID which is a Number, and has the Lookup value defined as being from the TableStates table.

My question is regarding a form named FormClient. Right now, the form has a combobox that only uses the field Combined [example "AL (Alabama)"]. I want this to be displayed, because some of the users won't necessarily know all the state abbreviations.

I would rather have this combobox show the Abbreviation and State fields in its column form, which is done in the wizard or query builder by adding extra fields. This changes the combobox to instead show the options in more of a table format [example "AL | Alabama"] which looks nicer.

The problem I'm encountering is that once the user moves to another field in the form, the combobox is only showing the abbreviation [example "AL"], however I would prefer it shows the combined [example "AL (Alabama)"].

Is this possible? I want it to show the field "combined" from the table but not actually show the field "combined" in the combobox list -- but instead showing the fields "abbreviation" and "state" in its tabular format. I think it may have something to do with the "Bound Column" property, which I've messed around with having no luck.

 

[ajetrumpet]

however I would prefer it shows the combined [example "AL (Alabama)"].

Is this possible?

To keep your columnar form in the dropdown, but change the value displayed in the box, try this on the AfterUpdate event of the combo box:

me.ComboBoxName = me.ComboBoxName.Column(0) & 
     " (" & me.CombBoxName.Column(1) & ")"

See what that does for you. You're right about the columns. I think Access displays only the first column's value in the actual "box" portion of the control (by default), but it is holding all the values from the record that you have specified in the rowsource.

Another thing about this method: It will work to change the value of the combo, but you cannot limit the list of the box.