Listbox Display is Showing Wrong 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 "cboResourceType" and a listbox called "lboResourceName", 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 "cboResourceType" combo box displays the ResourceType just fine. However, the
"lboResourceName" listbox is inconsistent in what it displays.

When the listbox is initially shown, everything looks fine. The correct words for the resource name are visible. And if I click on the dropdown arrow for the listbox, I see the correct list of valid resource names to choose from. But if I attempt to change the ResourceName by clicking on a different choice, the corresponding resource id (the numeric bound column for the listbox) is then shown in the listbox display, and is then actually stored in the ResourceName field in the EventResourcesDetails table. Meanwhile, the ResourceID field does not change in the table itself. So as an example, I end up with an EventResourcesDetails table record with a ResourceID of 3 (the old ID), and a ResourceName of 4 (the new ID). What I want to happen is that the ResourceID changes from 3 to 4, and the ResourceName changes to the appropriate wording.

Here's some info on the listbox:

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

How can I always see the valid ResourceName in the listbox display, instead of the ResourceID field (and also have the data stored correctly in the table)?

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

- Anita

 

[hermanlaksko]

Hi Anita
It looks like you want to store the ResourceName but your Bound Column is 1 (ResourceID)
So if you change your ControlSource to ResourceID and keep the rest as is it should work.
Herman

 

[aregan1]

Hi again Herman -

After your help with my last thread earlier this week, I realized that although the listbox was functioning correctly, I could no longer enter new data (because I had changed the subform's recordsource to a query using 2 tables instead of 1). And despite trying changes involving the recordset type (dynaset), the only way I could restore the ability to enter new data was to add the ResourceName field to the EventResourcesDetails table. So even though I don't like it, I'm now carrying both the ResourceID and ResourceName fields in the EventResourcesDetails table. And I therefore need to update both fields when a change is made to the listbox lboResourceName.

When I changed the controlsource to ResourceID as you suggested, a few things changed. The first change is that the text showing in the listbox in all rows of the datasheet is now the numeric ResourceID instead of the ResourceName (not what I want). The dropdown arrow still shows me all the valid ResourceNames, and when I click on one, if still puts the corresponding ResourceID in the display area (as it did before -- also not what I want). But when the record is saved, it is now correctly saving or updating the Resource ID, but it is not changing or putting anything in the ResourceName field. (Old records that are changed now contain the new ResourceID along with the old ResourceName. New records that are added have the new ResourceID and a blank ResourceName.)

So how can I always save both the ResourceID and ResourceName fields? And how can I always display the ResourceName field instead of ResourceID?

Thanks again for your help...

- Anita

 

[hermanlaksko]

Hi Anita
There should be no problem in haveing a recordset on a form consisting of more than one tbl as long as this recordset is updateable!
Also you are confusing me a bit as you keep reffering to a listbox, but if you have a dropdown arrow it must be a combobox yes/no ?.
Anyway, the way I see it you have now a correct list/combo box that will save the ResourceID this should be the relation btwn your listbox and the form.
There should be not need to save both the ID and the ResourceName as these 2 are related and this would create redundant data in your base ie. the same data in 2 tbls so you shold save only the ID in your subform/tbl and show only the ResourceName in that subform..... this should be in place now.
You show the ResourceName by selecteing, as you now have done, the ResourceID and ResourceName and setting columncount =2 and columnwidth=0(ResourceID/hidden), 2 (ResourceName/shown)
Maby I am missing something but it shold be working now ;-D
All the best
Herman

 

[aregan1]

Hi Herman -

The control that I have a problem with is indeed a listbox. But I'm viewing in in datasheet view within my subform, and in datasheet view the listbox has a dropdown arrow, similar to a combobox.

What I'm going to do is change my recordsource back to a query using 2 tables. This will eliminate the need for me to carry the ResourceName field in more than 1 place (which I didn't like doing, either). And if I have the same problem that I did before (data entry was no longer allowed), I'll post that as a thread.

By the way, there are multiple pages in VB help dealing with the problem of updating data that is based on a query. It specifically says that "if a query is based on a one-to-many relationship (which mine was), you might not be able to edit the data in the query's results." That seemed to be the case for me, but I'll retry it, and let you know.

Thanks...

- Anita

 

[aregan1]

Hi Herman -

I changed my recordsource back to a query using 2 tables, and once I changed the recordset type from Dynaset to Dynaset(Inconsistent Updates), I was able to add and edit data again.

However, I'm still having trouble with my listbox, but it's a little different than before. I'm going to start a new thread with this new problem, and post it in the Access VBA forum. I hope to hear from you again! And thanks...

-Anita

 

[hermanlaksko]

I am glad that we got you so far and solved, if not all, then most of your problem. ;-)

Herman