Problems with Listbox Controlsource and Display

[aregan1]

I'm currently working on an Access subform which is displayed in datasheet view on another form. The subform contains a combobox called "cboResourceType" and a listbox called "lboResourceName" (which is dependant on cboResourceType).

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

1) Subform RecordSource:
SELECT EventResourcesDetails.*, [Resources].[ResourceName] FROM EventResourcesDetails, Resources WHERE ((([EventResourcesDetails].[EventID])=[forms]![MediaResources]![EventID]) And ([EventResourcesDetails].[ResourceID]=[Resources].[ResourceID]));
Redcordset Type: Dynaset (Inconsistent Updates)

2) lboResourceName
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 (there is a dropdown arrow, since the listbox is in datasheet view), 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.

I tried changing the controlsource from ResourceID to ResourceName. Then, when the subform first appears with data in it, all the listbox displays in each row appear to be fine, with the correct wording instead of the numeric id. But if I click on the dropdown arrow and choose a different option, the numeric ResourceID then appears in the display. And even worse, the numeric ResourceID then gets saved in the ResourceName field of the Resources table, instead of being saved in the ResourceID field of the EventResourcesDetails table! I definitely don't want to be updating the Resources table in this form! My updates are supposed to happen to the EventResourcesDetails table.

How can I always see the valid ResourceName in the listbox display (instead of the ResourceID field), and then save the new ResourceID in the EventResourcesDetails table?

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

- Anita

 

[KenReay]

Hi

Select the lsit box, in the properties window, see the property .ColumnWidths, set it to 0;value

where value is a number appropriate to the width you want to dispaly in inches or centimetres depending on which measurement unit you are using.

The above will effectively hide the first column (the id) so you will see the description, but the id will still be the .value of the listbox.

Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reay@talk21.com

 

[aregan1]

Hi Ken -

I already had the columnwidths set to 0",2", which only controls what appears in the dropdown box when you click on the dropdown arrow. The dropdown box shows the correct wording, like I want.

What isn't showing up correctly is the listbox display area. When I click on the dropdown wording, the corresponding numeric ResourceID shows up in the listbox display area. And depending on if the controlsource is set to ResourceID or ResourceName, it actually stores the ResourceID incorrectly.

Please re-read my original explanation, and I think you'll understand what's happening. I'd appreciate any additional input...

- Anita

 

[LittleSmudge]

Beware : - Multi Post Timewaster alert

thread702-353797

 

[aregan1]

Just so I know for next time...

Is it considered bad form to post a question in more than one forum? I assumed that the same people might not belong to both forums where I posted this question...

- Anita

 

[LittleSmudge]

It is ALWAYS bad form to post the same question in more than one forum.

It is often the case that a question applies to more than one forum.

The solution is to post ONCE then in the other forum(s) post LINKS back to the original.

In that way you get answers in ONE place only and other people do not waste their time and effort researching and answering your problem only to find that it has been adequately answered 20 minutes earlier in another forum.


G LS

 

[aregan1]

Thanks for the info...

- Anita