473,563 Members | 2,633 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

6 New Member
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_AfterUpda te) 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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command2_Click()
  2.     Me.Combo0.SetFocus
  3.     Me.Combo0.ListIndex = 2
  4. End Sub
  5.  
  6. Private Sub Form_Load()
  7.     Me.Combo0.SetFocus
  8.     Me.Combo0.ListIndex = 0
  9. End Sub
  10.  
  11. Private Sub List5_AfterUpdate()
  12.     Me.Combo0.SetFocus
  13.     Me.Combo0.ListIndex = 3
  14. End Sub
  15.  
Nov 3 '11 #1
11 4610
jimatqsi
1,274 Recognized Expert Top Contributor
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
Nov 4 '11 #2
sierra7
446 Recognized Expert Contributor
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
Nov 4 '11 #3
mcompagno
6 New Member
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.
Nov 7 '11 #4
mcompagno
6 New Member
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.
Nov 7 '11 #5
sierra7
446 Recognized Expert Contributor
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)
Expand|Select|Wrap|Line Numbers
  1. Private Sub List3_AfterUpdate()
  2. Dim i As Variant
  3. '#### this code gives error 2115
  4. i = Me!List3.ListIndex
  5. Me!Combo1.SetFocus  'removing this line gives error 7777
  6. Me!Combo1.ListIndex = i
  7. 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;
Expand|Select|Wrap|Line Numbers
  1. Private Sub List3_AfterUpdate()
  2. Me.Combo1 = Me.List3
  3. 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;
Expand|Select|Wrap|Line Numbers
  1. Private Sub List3_AfterUpdate()
  2. Me.Combo1 = Me.List3.Column(1)
  3. 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
Nov 7 '11 #6
mcompagno
6 New Member
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].[ReportCategoryI D] 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.
Nov 8 '11 #7
sierra7
446 Recognized Expert Contributor
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 ReportCategoryI D.

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 ;
Expand|Select|Wrap|Line Numbers
  1. Me!txtRepID = Me!List3.Column(0)
  2. 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
Nov 8 '11 #8
mcompagno
6 New Member
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.
Nov 8 '11 #9
sierra7
446 Recognized Expert Contributor
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 ReportCategoryI D 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
Nov 9 '11 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

1
1158
by: Katie | last post by:
I am a rather inexperienced VBA programmer so this may be a very simple question! I need to update combo boxes using VBA to incorporate filters from other fields. I have been trying to use .RowSource, but I can't get it to work. Any advice? Could you email at above address! Katie
3
11072
by: Brian | last post by:
I have a form that has a textbox used to search records in a table. On the form I have a listbox which shows each record in the table. When you click on each row in the listbox the rest of the form updates to show the other fields in the table. The problem I am having is when I enter text to search the table the list narrows successfully,...
2
7160
by: sianan | last post by:
I am having a problem doing the following in generics. I have two list of a custom item type. I need to iterate through the first list and match each item against another list to see if there is a match. If a match is found, I need to move the matched item into another list. I want to compare the objects by value. The items in each list...
3
3835
by: Cagey | last post by:
What I'm trying for: If this selection or if click on selection (highlighted line choice/ which ever selection change) w/in query's combo dropdown list box (on Switchboard), then Open in Datasheet View, the specific underlying record's Row... showing all columns Or selected columns. For a specific row's record ONLY, Or for all Xs (same...
3
23238
by: keithsimpson3973 | last post by:
I have 6 combo boxes on a vb form. Is there a way, using vb6, to remove the list item that was selected in the first combo box from the list of the second, third fourth, fifth, and sixth combo box list? This is what I have tried with no luck cb = combobox1.listindex combobox2.list = combobox1.list combobox2.removeitem cb
2
2372
by: JweldonH | last post by:
My issue probably has a simple answer. I want to limit a populated combo box to items that are NOT in use in another table. I have a Table with a field that looks up values from another table via a combo box. As I select items from that combo box list, I would like for the list to automatically remove that selection from the combo box list so...
1
1251
by: napstar | last post by:
I have 2 combo boxes which can be used to set an employee's availability and Salary type on an employees form in access. I have sub which changes availability to “Full time”, if the salary type is “Retainer” ;but when I do this I keep getting Error 2115 : The macro or function set to the BeforeUpdate or ValidationRule property for this field is...
4
2579
by: lulu123 | last post by:
here's my problem 1) rowsource .RowSource = "select distinct , " & "from Projects " & " where =" & Me! I have the above code, and it works fine. however, i want to make the combo box in alphabetical order by . I tried to add "order by " after "me!". But it gives me error msg. Can someone tell me how can i do that. Thanks. 2) REfresh...
8
3348
by: hollinshead | last post by:
Hi there i have bit of an issue. i haver this database that is purely used for searching records under certain criteria. This criteria is chosen by the user on a form using list boxes and combo boxes. So when the criteria is chosen the user selects a click command button and then the results are displayed on another form. I have successfully...
16
19100
by: rudivs | last post by:
I would like to do data validation in the BeforeUpdate procedure, but Access gives me a runtime error when I try to do this: An example of what I am trying to do is as follows: Private Sub Option_BeforeUpdate(Cancel As Integer) If Option = A Then Answer=MsgBox("Don't you mean B?",vbYesNo) If Answer = vbYes Then
0
7658
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7579
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8101
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7631
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
1
5479
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5204
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3631
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1194
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
912
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.