How can a Listbox display something other than the bound column?

[aregan1]

I'm currently working on an Access subform which is displayed in datasheet view on another form. The subform contains a combobox called "ResourceType" and a listbox called "ResourceName", along with a few other fields. When the form displays existing data, there is one row of data in the subform for each record in the "EventResourcesDetails" table.

The "ResourceType" combo box displays the ResourceType just fine. However, the "ResourceName" listbox displays the numeric record key, rather than the description field that I want to see. Here's some info on the fields:

1) ResourceType
RowSourceType: Table/Query
RowSource: SELECT [ResourceTypes].[ResourceTypeID], [ResourceTypes].[ResourceType] FROM ResourceTypes ORDER BY [ResourceTypes].[ResourceType];
ControlSource: ResourceTypeID
ColumnCount: 2
ColumnWidths: 0",2"
Bound Column: 1

2) ResourceName
RowSourceType: Table/Query
RowSource: SELECT [Resources].[ResourceID], [Resources].[ResourceName] FROM Resources
WHERE [Resources.ResourceTypeID]=[forms]![MediaResources]![MediaResourcesSubform]!ResourceType
ORDER BY [Resources].[ResourceName];
ControlSource: ResourceID
ColumnCount: 2
ColumnWidths: 0",2"
Bound Column: 1

The ResourceName field is displaying the numeric ResourceID rather than the ResourceName (ex. "5", not "VCR&quot. If I click on the dropdown arrow for the listbox, I see the valid list of ResourceNames that apply to the selected ResourceType (as I should). But if I click on one of these names, the corresponding ResourceID is then displayed in the row.

How can I always see the valid ResourceName in the listbox display, instead of the ResourceID field?

Thanks for your help. (I'm fairly new to VBA, so please keep explanations simple...)

- Anita

 

[hermanlaksko]

Hi Anita
As I understand you, there is 2 comboboxes that works fine and 1 field (RecourceName) that does not work?.
If so the field (RecourceName) is linked to ResourceID and not to ResourceName. Have a look at this and let me know.
Herman

 

[aregan1]

Hi Herman -

I actually have 1 combobox that works fine (called ResourceType), and one listbox (called ResourceName) that isn't working the way I would like. The ResourceName listbox is displaying the bound column (a numeric key called ResourceID) in the visible listbox text in each datasheet row. I would like it to display the associated description (called ResourceName, just like the name of the listbox itself) in the listbox text area.

When you ask what the field ResourceName is linked to, I imagine you're asking what the control source is? If so, the listbox called ResourceName has a control source of ResourceID. I thought it is necessary to specify the key to a table as the control source. I also thought that the control source must refer to the same field as the bound column. Is that not true? Thanks for your help...

- Anita

 

[aregan1]

Hi again Herman -

I thought I would try to change the control source for the ResourceName listbox from ResourceID to ResourceName. However, when I click on "..." in the control source property, it displays all the fields in the EventResourcesDetails table (which is the record source for the subform), and ResourceName is not in that table! That table carries the "key" of ResourceID only, and the only place where the actual ResourceName is carried is in the "lookup table" called "Resources", which has only 2 fields -- ResourceID and ResourceName.

So it looks like I can't change the control source from "ResourceID" to "ResourceName"...

- Anita

 

[aregan1]

Hi Herman -

With your input, I've answered my own questions!

I had to change the record source for the entire subform, from just using the table "EventResourcesDetails" to now using a query that combines all the fields from this table with the "ResourceName" field from the Resources table.

Then, when I tried to set the control source for the "ResourceName" listbox, it gave me the field "ResourceName" as an option. I chose this, and now my listboxes display the ResourceName instead of the numeric ResourceID!

Your response got me thinking in the right direction. Thanks again!

- Anita

 

[hermanlaksko]

Hi Anita
Excelent !
Herman