Unable to update combo box using list box selection - error 2115

[mcompagno]

I am trying to set the value of a combo box based on the selection of a list box. I have created a very simple generic form to try to achieve this goal, but without success. The form has a list box bound to a query, a button, and a combo box bound to another query. The queries for the list box and the combo box are independent. I can set the value of the combo box when the form loads (sub Form_Load) and when I click on the button (sub Command2_Click), but when I make a selection in the list box (sub List5_AfterUpdate) I get Runtime error 2115: The Macro of function set to the BeforeUpdate or ValidationRule property for this field is preventing MS Access from saving the date in the field. I do not have any validation rules set and this is a complete listing of my code.

Private Sub Command2_Click()
    Me.Combo0.SetFocus
    Me.Combo0.ListIndex = 2
End Sub
 
Private Sub Form_Load()
    Me.Combo0.SetFocus
    Me.Combo0.ListIndex = 0
End Sub
 
Private Sub List5_AfterUpdate()
    Me.Combo0.SetFocus
    Me.Combo0.ListIndex = 3
End Sub
 

[jimatqsi]

How many list members/rows are in the combo box? Does it change or are the contents of the combo box static?

What validation do you have in the properties of the various objects? Is there anything the BeforeUpdate setting of any of the objects?

Jim

[sierra7]

Hi
If I understand your problem, you are trying to do some kind of 'cascade' filtering.i.e. the selection you make in your ListBox is to be used as a filter for the query in the ComboBox.

In this case I would write the selection made in the ListBox (in an After_Update event) to an unbound (invisible/hidden) TextBox on your form and then change the query in your ComboBox so that it uses the TextBox as a criteria in the matching field/column.

The List_Box After_Update event would have to Requery the ComboBox for the new criteria to become effective.

If I have misunderstood, then forget my ramblings. If I am on the right track but you don't understand, then post the details of your two queries and I'll suggest some code.
S7

[mcompagno]

jimatqsi - thanks for the response. The combo box is based on a query, so yes the contents can change. The table is very small with only 4 rows currently. There is no validation on any field and nothing in the BeforeUpdate setting of any object. The only events defined are those listed - form load, button click and afterupdate.

[mcompagno]

sierra7 - I appreciate your response. Yes, you are correct in the general statement of what I am trying to accomplish. However, Your suggestion does not really solve my problem (from my understanding of it). While I want the combobox to have a specific value displayed (based on the selection in the list box), I still want the user to be able to select any of the other values from the query. Therefore I cannot filter the combobox based on the list box selection - I only want to set the index value of the combobox.

It seems that the cause of the problem is based on the fact that the code is placed in an afterupdate event (the identical code works in the formload event and button click event), but why that is a problem is where I get stuck.

[sierra7]

mcompagno, i'm still struggling to understand what you are trying to acheive.

I agree you get error 2115 if you try and set the ListIndex of a ComboBox from the AfterUpdate event of the ListBox (but is ok from a Command button)

Private Sub List3_AfterUpdate()
Dim i As Variant
'#### this code gives error 2115
i = Me!List3.ListIndex
Me!Combo1.SetFocus  'removing this line gives error 7777
Me!Combo1.ListIndex = i
End Sub

If yoy remove the SetFocus line then error 7777 is given "You've used the ListIndex property incorrectly"
If you want to set a combo box to the value of a list box then this will do the job;

Private Sub List3_AfterUpdate()
Me.Combo1 = Me.List3
End Sub

But that is a pretty pointless exercise so I guess you are trying to do something more subtle.

If the query in your ListBox returns more than one column, you can set the list box to the second column with;

Private Sub List3_AfterUpdate()
Me.Combo1 = Me.List3.Column(1)
End Sub

The ComboBox itself can have more than one column but you can only match to the Bound column.
If you can give us the column headings it might become clearer what you are trying to acheive.
S7

[mcompagno]

Sierra7 - Thank you very much for your time on this. Basically I am trying to duplicate a capability I use in a commercial Access application. On a form the user selects a customized report from a list box. The form includes several options for modifying the filters for the report, including selections in comboboxes, option groups and text boxes. When the user selects the report in the list box, all of the options then display the values that are "saved" for that report. The user can then modify the selections as desired. I have been able to accomplish my goals for an option group and a text box, but I can't make the combobox work.

Here is the rowsource for the list box: SELECT [qryReports].[ReportID], [qryReports].[ReportName] FROM qryReports ORDER BY [ReportID];
The bound column is column 1 (ReportID), but the display is column 2 (ReportName).

Here is the rowsource for the combobox: SELECT [qryReportType].[ReportTypeID], [qryReportType].[ReportTypeName], [qryReportType].[ReportCategoryID] FROM qryReportType ORDER BY [ReportTypeID];

The bound column is column 1 (ReportTypeID) and the display column is column 2 (ReportTypeName).

BTW - there are not any relationships established for the database, even though I enforce relationships in the code.

After the user selects the report in the list box, I query the table to get the associated record. Using the ReportTypeID I then want to display in the combobox the correct ReportTypeName. While the code I used for this example simply uses an integer for the listindex, my application code gets the listindex from the record associated with the list box selection.

[sierra7]

Hi again,
I think we are getting somewhere!
So we make a selection in the ListBox by picking a ReportName, but the ReportID is also available from the first column (which is Column(0))

This is then used to 'set' the ComboBox. I am assuming that there are multiple records in the recordset of the ComboBox that will 'match' this ReportID but from your description I do not know whether it is ReportTypeID or ReportCategoryID.

For the moment I will assume it is ReportTypeID because it is the bound column. I would add a TextBox to the form called txtRepID, then modify the query for the ComboBox by adding [txtRepID] as the Criteria in the design grid, under ReportTypeID. Keep the square brackets around txtRepID so Access will recognise it as a field and not a literal text string.

Next, in the AfterUpdate event of the ListBox, which I am calling List3 ;

Me!txtRepID = Me!List3.Column(0)
Me!Combo1.Requery

The ComboBox will now only display values that are relevant to the selection in the ListBox.
The table on which qryReportType is based must contain ALL the options for ALL the Report Types, even though some of these may seem to be duplications!
If this is not the case then I can't see why you would use a ComboBox....or why it should be related to the output of the ListBox
S7

[mcompagno]

Sierra7 - again, I very much appreciate your inputs. Unfortunately your solution does not quite achieve my goal. I sincerely apologize for not being able to express it more clearly.

Basically the records in List5 simply contain the filter selections (I have 5 defined) for a report as saved by the user. For example, I have a Boolean field, and the user can select Yes/No/All using an option group and then save the report. When that report is selected, the value (i.e. Yes) is displayed as selected. The user can then run the report with that selection, or change the selection to No and then run the report with the new selection. This provides the user with the ease of having customized reports (to repeat periodically) and the flexibility to easily modify those reports.

The combobox is simply another attribute for the report, which is the report type (I have 4 defined). When the user selects the saved report in the listbox, the report record contains the report type name, which I use to select the record for the combobox. The combobox is not being filtered, only set to display the saved value.

The buttonclick event accomplishes exactly what I want to have happen. I simply want that behavior after selecting an item from the list box instead of having to click on a button.

[sierra7]

Mcompagno, thank you for you very detailed description but I still fail to see the problem. (I never was good at football; - scoring goals!)
It seems that neither ReportTypeID nor ReportCategoryID in the ComboBox matches ReportID in the ListBox?
You need some sort of relation (connection) between the ListBox and the ComboBox. i.e. something you select in you ListBox must identify with the bound field of the ComboBox, if it is to make the ComboBox respond.
If you think ListIndex will do the job, then convert the ComboBox to a second ListBox.
Staying with the ComboBox a bit longer, you could add a third column to you ListBox query, that holds the value of ReportTypeID for each value of ReportName. Then refer to this as Column(2) in the After_Update event.
If the ListBox query can not be modified to include the bound field of the ComboBox, then maybe you could hard-code a CASE statement in the After_Update event. After that I am running out of ideas. Sorry!
S7

[mcompagno]

Success at last!
I had the answer all along in another part of my code.
Instead of using listindex I used itemdata:
Me.combo0= Me.combo0.ItemData(1)
This does the same thing as the setting the listindex from the point of view of the user, but does not throw the error.

[sierra7]

Glad you got it to work
S7