none
Access Combo box is stuck RRS feed

  • Question

  • I'd like to update a combo box via vba. I believe this code should work, is there anything I've missed?

    Sub testing2()
    Forms!frm_request.Initial_Request.LimitToList = False
    Forms!frm_request.Initial_Request.AllowValueListEdits = True
    Forms!frm_request.Initial_Request.RowSource = ""
    Forms!frm_request.Initial_Request.RowSource = """Listitem1"";""Listitem2"""
    Forms!frm_request.Initial_Request.LimitToList = True
    Forms!frm_request.Initial_Request.AllowValueListEdits = False
    
    End Sub

    Access always seems to be troublesome with combo box rowsource changes, which personally I think there are bugs Microsoft has never fixed.

    Friday, August 28, 2015 3:20 PM

Answers

  • Please, if you can provide further assistance. The code I've submitted does not do what I need, and no one has provided code that does work.

    Hi. Still not sure I understand what you're trying to do but try setting the property "Inherit Value List" to "No" and see if that makes any difference. Hope that helps...
    • Marked as answer by w85 Friday, August 28, 2015 7:53 PM
    Friday, August 28, 2015 7:42 PM

All replies

  • Hi. I didn't see you specify the RowSourceType. Maybe something like:

    Forms!frm_request.Initial_Request.RowSourceType = "Value List"

    Hope that helps...

    Friday, August 28, 2015 3:27 PM
  • Sub testing2() Forms!frm_request.Initial_Request.LimitToList = False Forms!frm_request.Initial_Request.AllowValueListEdits = True

    Forms!frm_request.Initial_Request.RowSourceType = "Value List" Forms!frm_request.Initial_Request.RowSource = "" Forms!frm_request.Initial_Request.RowSource = """Listitem1"";""Listitem2""" Forms!frm_request.Initial_Request.LimitToList = True Forms!frm_request.Initial_Request.AllowValueListEdits = False End Sub

    The updated code does nothing. Combo box still has the old values.
    Friday, August 28, 2015 3:34 PM
  • How exactly are you executing this code?
    Friday, August 28, 2015 3:41 PM
  • If you are changing the row source dynamically, then I would also ensure that you have set the value of the combo box to null.

    Forms!frm_request.Initial_Request = Null
    Forms!frm_request.Initial_Request.RowSourceType = "Value List"
    Forms!frm_request.Initial_Request.RowSource = """Listitem1"";""Listitem2"""
    Forms!frm_request.Initial_Request.LimitToList = True
    Forms
    !frm_request.Initial_Request.AllowValueListEdits = False

    Removed - Not Necessary
    Forms!frm_request.Initial_Request.RowSource = ""
    Forms!frm_request.Initial_Request.LimitToList = False
    Forms
    !frm_request.Initial_Request.AllowValueListEdits = True

    Keep in mind that if this code is being run in the same form as the control you are manipulating, then you need only reference the form as Me, i.e. Me.Initial_Request.

    Also, it's always a good idea to name your controls with an identifier to indicate the type of control you are working with.

    Examples:

    Combo Box = cbx or cbo | cbxFieldName
    List Box = lbx | lbxOptionList
    Text Box = txt | txtFieldName
    Check Box = chk | chkFieldName
    Option Button = opt | optOption1
    Option Group = grp | grpFieldName
    Rectangle = box | boxControlGroup1
    Tab Control = tab | tabMyControl
    Command Button = cmd | cmdOK
    Toggle Button = tgl | tglOption
    etc...


    Friday, August 28, 2015 3:44 PM
  • The form is open. I run the code in the VBA window.

    Eventually I plan to have it executed via an event trigger. It needs to work first.

    Friday, August 28, 2015 3:44 PM
  • The form is open. I run the code in the VBA window.

    Eventually I plan to have it executed via an event trigger. It needs to work first.


    Hi. It's possible, since you're running this procedure outside of the form, that you may have to refresh/repaint the from too. Try that, or just try the code in the form itself or a copy of it.
    Friday, August 28, 2015 3:55 PM
  • Actually, just looked deeper at the form.

    I already have similar code embedded in the form that isn't working!! And I wondered why users hadn't complained yet about this glaring lack of functionality.

    So the code was working until recently. I'll probably get the "well its corrupted" answer to this one. This is a big inconvenience, to recall, I remember getting combo boxes with corrupt rowsources back years ago in Access 2007. I can't believe they still haven't fixed this.

    So whats the best option here? If I rebuild a non-corrupt combo box, its likely to become corrupted again. Maybe I make the combo box have a query rowsource, and add yet another table to my database? I think the buggy part is the string for the rowsource. If its a query, maybe this stays static and I'll have less issues?

    Any other thoughts? I just need this to work and not randomly break in the future.



    • Edited by w85 Friday, August 28, 2015 4:08 PM
    Friday, August 28, 2015 4:06 PM
  • Hi. Unfortunately, corruption can happen at any time for any reason. It's not just a matter of buggy software. It can be cause by intermittent network traffic as well. One good thing about using a split configuration is that you can just simply replace the FE when it doesn't work because your data is safely tucked away in the BE. Good luck with your project.
    Friday, August 28, 2015 4:17 PM
  • I personally have no idea what you may have experienced with a "corrupted combo box."  I've never heard of such a thing, nor have I ever had a problem with the control in over 20 years of working with Access. 

    Because you are trying to set the row source of a combo box using a value list as you have it, you're more likely to run into problems resulting from typographical errors.

    Additionally, as mentioned above, it isn't necessary to initialize the combo box with empty or alternate values just before setting those certain properties to the actual desired values.  As a caveat, you should initialize your combo box value to null before changing the row source, simply because the new row source most likely will not have the same values after the row source is updated and a currently displayed value may become invalid.

    Lastly, it isn't necessary to enclose each text item in quotes followed by the semi-colon.  You can simply set the value to a string of text items separated by the semi-colon, all enclosed in one set of quotes as shown below:

    Me.cbxMyTextBox.RowSource = "John;Bill;Roger"




    Friday, August 28, 2015 4:26 PM
  • If you've double-checked your code and you find that it is still not working as expected, you could try decompiling your front-end. Sometimes a corrupt project is the source of unexplained behavior like this.

    To do this, start the Access .exe from the command line using the /decompile switch. Then open your front-end application, open any module in the VBA editor, compile the project, and save.  Then exit Access.

    Reopen your front-end in the normal way and see if things are improved.

    Friday, August 28, 2015 4:30 PM
  • In your heading, you state "Access Combo box is stuck."  What exactly do you mean?  What problem are you encountering, or what are the symptoms of the problem?  As far as I can tell from your actual post, all you're seeking is advice on how to update your combo box... 

    Though Access does have a number of bugs and has since the beginning, combo boxes have never given me a problem, nor have I heard of any problems with them that weren't caused by user/developer error, including trying to exceed the limitations of a combo box.

    So again, please tell us what you are experiencing and what we can do to help you correct your issue.

    Thanks,

    Friday, August 28, 2015 4:40 PM
  • Like others, I haven't seen any problems with combo boxes until 2010, and that issue in not related to setting rowsource.

    Exactly what do you expect to happen after your code is run? If you are expecting the first item to appear, it won't. You would need to set the control's value to that.

    Exactly what is happening? Or NOT happening.

    What are the settings for ColumnCount, ColumnWidths, BoundColumn properties?

    Have you tried with a new combo box? - just in case there is some property setting that we all missed.

    Friday, August 28, 2015 5:28 PM
  • Ok, here's the behavior I expect vs what I get.

    In pic 1 I have row source = "abc";"def":

    Now, I run code to update the row source (to other random letters: "xyz";"lmn"), and I see it change in the property box:

    Then, I'd expect the that the change was good and it will stick. But when I click the little grey ... box for row source, magically the old values reappear. I do not expect this outcome:

    Note: none of my code is compiled. Similarly, If I run the code in form view, instead of design view, no changes happen.

    • Edited by w85 Friday, August 28, 2015 7:14 PM
    Friday, August 28, 2015 7:10 PM
  • When you update the row source of the combo box using VBA, the change is not saved with the form.  So when you close the form and reopen it, the original values will be intact. 

    Editing the list manually using the editor will change the property value and when you attempt to close the form, you will be prompted to save your changes.

    If you want to update the row source and save the new value, you will have use VBA to open the form in design view/hidden, then update the value, save and close the form.
    Friday, August 28, 2015 7:14 PM
  • Its okay that the change is not saved. I need the result of another drop down on the form to trigger this change, so that can only happen if the form is open.
    Friday, August 28, 2015 7:20 PM
  • So what you are talking about is cascading combo boxes.  The successive combo box gets its row source set following a value selection in another.  No problem.  Just realize that your changes won't be saved when the form is closed.

    It sounds like your question has been answered.  Please be sure to mark it as such or let us know if you need any further assistance.

    Thanks,

    Friday, August 28, 2015 7:27 PM
  • Ok, I see what is going on. I just tried it in Access 2007. Apparently when a value list rowsource is changed via code, the designer doesn't like it. In 2007, the ellipsis button actually disappears. I rarely use value lists and never popup edit dialog.

    However, it is the dialog that doesn't work properly. The drop down values do change, both in design time and runtime. So, you can still accomplish your changes in runtime without a problem.
    I am quite sure Microsoft is aware of the problem and will fix it right about the time that Hell freezes over.

    Friday, August 28, 2015 7:37 PM
  • Please, if you can provide further assistance. The code I've submitted does not do what I need, and no one has provided code that does work.
    Friday, August 28, 2015 7:38 PM
  • Please, if you can provide further assistance. The code I've submitted does not do what I need, and no one has provided code that does work.

    Hi. Still not sure I understand what you're trying to do but try setting the property "Inherit Value List" to "No" and see if that makes any difference. Hope that helps...
    • Marked as answer by w85 Friday, August 28, 2015 7:53 PM
    Friday, August 28, 2015 7:42 PM
  • Ok, that fixed it. My original coding even took over and resumed working again.

    I'm not familiar with that property.

    Friday, August 28, 2015 7:51 PM
  • Ok, that fixed it. My original coding even took over and resumed working again.

    I'm not familiar with that property.


    It's very rarely used but glad to hear it helped in your case. Cheers!
    Friday, August 28, 2015 7:53 PM
  • Interestingly, I have an earlier copy of the database. Inherit Value List = Yes for this same control, and the code works properly. Every other property is the same. This reinforces my belief that Microsoft has issues with the combo box row source, or that it is particularly prone to some type of corruption.

    Thanks to everyone for helping me get my combo box working again!!

    Friday, August 28, 2015 8:05 PM
  • Interestingly, I have an earlier copy of the database. Inherit Value List = Yes for this same control, and the code works properly. Every other property is the same. This reinforces my belief that Microsoft has issues with the combo box row source, or that it is particularly prone to some type of corruption.

    Thanks to everyone for helping me get my combo box working again!!


    What version of Access are you working with?  I'm using 2010 to test your problem and Inherit is set to yes.  I'm not having any problems making changes to the row source either from within the form or in a standard module using VBA.
    Saturday, August 29, 2015 12:18 AM
  • I'm working with Access 2013. This is exactly what I'm saying about rowsource being buggy. I marked the inherit value list = no as the answer, because making that change fixed it. But I don't think having it marked Yes is ordinarily a limitation. In fact, I opened up the an earlier copy of the db I had made and not only could I change the rowsource via VBA, Inherit Value List was also set to Yes. So I don't really think the value for Inherit Value List was the problem, but changing the value in it somehow fixed the issue of not being able to update the rowsource. Would be great to submit this one into Microsoft somewhere if anyone knows how to do that.
    Monday, August 31, 2015 3:36 PM
  • I'm working with Access 2013. This is exactly what I'm saying about rowsource being buggy. I marked the inherit value list = no as the answer, because making that change fixed it. But I don't think having it marked Yes is ordinarily a limitation. In fact, I opened up the an earlier copy of the db I had made and not only could I change the rowsource via VBA, Inherit Value List was also set to Yes. So I don't really think the value for Inherit Value List was the problem, but changing the value in it somehow fixed the issue of not being able to update the rowsource. Would be great to submit this one into Microsoft somewhere if anyone knows how to do that.

    Hi. I would contend that there must be a "slight" difference between the database with the problem and a previous copy that doesn't exhibit the same behavior because changing the setting did make a difference. This is something I just recently learned myself at: Combobox Gremlins. Just my 2 cents...
    Monday, August 31, 2015 3:54 PM
  • Yeah, when I was trying to change the rowsource manually I was getting the same results as in your Combobox Gremlins example. I maintain that Microsoft should fix this.
    Tuesday, September 1, 2015 1:15 PM
  • Yeah, when I was trying to change the rowsource manually I was getting the same results as in your Combobox Gremlins example. I maintain that Microsoft should fix this.

    In the meantime, at least we know what to do to work around the problem until it gets fixed. Cheers!
    Tuesday, September 1, 2015 2:56 PM
  • spun my wheel on this for like 20 minutes before I determined it had to be a vicious bug... glad I looked it up, never even noticed the "Inherit Value List" Property before... thanks!

    Anthony Griggs

    Monday, October 2, 2017 5:04 PM
  • Hi,

    I have been an Access developer since the week it came out in the early 90s (crazy, I know..).  I am working in Access 2010 now, and got stuck on this problem -- I was updating rowsource in code and it wasn't "taking".

    Note that I have code that updates the rowsource in this particular combo box in 2 places.  Without getting into all of the details, place (A) was after update of a field just before it, and place (B) was when displaying something for first time after selecting a particular person.

    In Place (A), the rowsource was refreshing correctly

    In Place (B), it was not

    I tried the above suggestion of changing InheritValueList but that didn't work.

    After banging my head up against this for half a day, I have finally gotten it working.

    Details:
    - The  fields are "Category" and "Title".  The rowsource of the "Title" combo box should refresh after update of Category
    - Both fields are on a TAB control within the FOOTER section of my form
    - At the time my code was run in scenario (B), the footer section was not visible.   The order of operation of my code changed the rowsource of the title field BEFORE changing the footer section to visible
    - when I changed my code around to SHOW the footer BEFORE changing the row source, then things started working!

    I do think this is an Access bug where certain properties cannot be set (but do not give errors) if the form section is hidden at the time the code is run.  I've hit stuff like this in other places as well.

    No idea if this will help anyone, but I figured it couldn't hurt to share.

    Alyssa Siegel

    • Proposed as answer by Alyssa Siegel Wednesday, November 8, 2017 8:27 PM
    Wednesday, November 8, 2017 8:17 PM