none
MS Access - Error - The expression On Click you entered as the event property setting produced the following error : The setting for this property is too long RRS feed

  • Question

  • Hello,

    I have a form that have 5 listboxes,multiple checkboxes,textboxes,buttons and a subform. It's a sort of search form that searches records and then export them.It has a lot of VBA code too. When i selected all the listboxes records i get the error mention above.I have look over the internet but nothing is working. Any idea how to make it correct? It's because of VBA /Properties?

    Thank you
    Monday, August 28, 2017 10:49 AM

Answers

  • Hello,

    Please the following gif of my test using your file and i think there is no work around if it reaches the maximum.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Wsm93 Monday, September 4, 2017 12:45 PM
    Wednesday, August 30, 2017 9:14 AM
    Moderator

All replies

  • You will have to debug the code. If you click a button to run the export then open the form in design view. select the button and press F7. That should open the code window to the button's Click event. Put a break point on that line. Then open the form in form view and select all the items as you did before and click the button. The code will stop running at the break.

    press F8 to step through the code one line at a time. to see which line is causing the problem. It would be helpful to us if you posted the code in the click event so we can get a look at what is happening.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, August 28, 2017 3:04 PM
  • Private Function FilterSubform() Dim varItem As Variant Dim strFilter As String Dim strItemList As String Dim ctrl As Control strFilter = "" Set ctrl = Me.Country If ctrl.ItemsSelected.Count > 0 Then For Each varItem In ctrl.ItemsSelected strItemList = strItemList & ",""" & ctrl.ItemData(varItem) & """" Next varItem strItemList = Mid(strItemList, 2) strFilter = strFilter & "Country In(" & strItemList & ") AND " End If Set ctrl = Me.Years strItemList = "" If ctrl.ItemsSelected.Count > 0 Then For Each varItem In ctrl.ItemsSelected strItemList = strItemList & "," & ctrl.ItemData(varItem) Next varItem strItemList = Mid(strItemList, 2) strFilter = strFilter & "Years In(" & strItemList & ") AND " End If Set ctrl = Me.Product ' if a product is selected add to strFilter variable strItemList = "" If ctrl.ItemsSelected.Count > 0 Then For Each varItem In ctrl.ItemsSelected strItemList = strItemList & "," & ctrl.ItemData(varItem) Next varItem strItemList = Mid(strItemList, 2) strFilter = strFilter & "Product In(" & strItemList & ") AND " End If Set ctrl = Me.PType strItemList = "" If ctrl.ItemsSelected.Count > 0 Then For Each varItem In ctrl.ItemsSelected strItemList = strItemList & ",""" & ctrl.ItemData(varItem) & """" Next varItem strItemList = Mid(strItemList, 2) strFilter = strFilter & "PType In(" & strItemList & ") AND " End If Set ctrl = Me.DataType strItemList = "" If ctrl.ItemsSelected.Count > 0 Then For Each varItem In ctrl.ItemsSelected strItemList = strItemList & ",""" & ctrl.ItemData(varItem) & """" Next varItem strItemList = Mid(strItemList, 2) strFilter = strFilter & "DataType In(" & strItemList & ") AND " End If If Me!chkShow.Value = True And Me!chkCurrent.Value = True Then strFilter = strFilter & "(Show is not null) AND " ElseIf Me!chkShow.Value = True Then strFilter = strFilter & "Show = (0) AND " ElseIf Me!chkCurrent.Value = True Then strFilter = strFilter & "Show = (1) AND " End If If Not IsNull(Me!TImestampFrom.Value + Me!TimestampTo.Value) Then strFilter = strFilter & "Fix(Timestamp) Between #" & Format(Me!TImestampFrom.Value, "mm\/dd\/yyyy") & "# And #" & Format(Me!TimestampTo.Value, "mm\/dd\/yyyy") & "# AND " End If If Len(strFilter) > 0 Then strFilter = Mid(strFilter, 1, Len(strFilter) - 5) Me.[sfrmMulti].Form.Filter = strFilter Me.[sfrmMulti].Form.FilterOn = True Else Me.[sfrmMulti].Form.FilterOn = False End If Debug.Print End Function

    This is the code. I think there is some line of code that is causing this error but i am not able to under what i causing this limitation.
    Monday, August 28, 2017 6:42 PM
  • You have not defined what varItem is to ACCESS so it does not know how to process:

    For Each varItem In ctrl.ItemsSelected

    I can't debug your code for you but try using:

    For Each ctrl In ctrl.ItemsSelected

    strItemList = strItemList & ",""" & ctrl.ItemData(varItm) & """"

    Next ctrl
    But you still need to tell ACCESS what varItem is:

    Set varItem = ?

    Monday, August 28, 2017 11:33 PM
  • Hello,

    Which line would be highlighted when you get the error?

    Would it work if you select few records?

    What is the strFilter if you select all listboxex records?


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 29, 2017 2:30 AM
    Moderator
  • Hi, 

    No line is highlighted when i get this error.

    Yes it's working if i select 3 list box , when i select 5 list boxes all values then it's where it show error, i think the string which i wrote has some limitation which edited would work but i don't know how to make it work.

    strFilter is the string that is in lisboxes.

    Tuesday, August 29, 2017 6:00 AM
  • How can i upload a sample DB can you let me know so that it would be helpful.
    Tuesday, August 29, 2017 6:12 AM
  • Please output the string when you select 5 list boxes all values and please share it here.

    According to the error message, i think the filter string may be wrong.

    You could share your db via OneDrive and paste the link here. Please refer to Share OneDrive files and folders


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, August 29, 2017 6:16 AM
    Moderator
  • I also think there is something wrong with the filter string. Attach is the link to sample DB. https://1drv.ms/u/s!Aon_FQmk7bYmiSyztu8kqdi9pfw4
    Tuesday, August 29, 2017 6:44 AM
  • varitem is defined above, Dim varItem as Variant.
    Tuesday, August 29, 2017 6:47 AM
  • I think your "function" isn't a function. A function returns a value to a variable. What we have here is a sub.

    On all events where you have =FilterSubform(), you should call the sub.

    In the on_change events of Archived and Current I made this change. As you wil see there is still an error because the filter value is "Show is not null)" (the ")" should not be there).

    See: https://1drv.ms/u/s!As9V3OD4Hg0uhPc709VQH1nOWPQtCA


    Groeten, Peter http://access.xps350.com/

    Tuesday, August 29, 2017 7:47 AM
  • Hello,

    According to my test result, there is a length limitation for filter string. The maximun is 2048.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 29, 2017 8:51 AM
    Moderator
  • @Celeste, but it's not reaching 2048 character , string is less than 2048.
    • Edited by Wsm93 Tuesday, August 29, 2017 10:14 AM
    Tuesday, August 29, 2017 10:01 AM
  • Hello,

    Please select New ~ New234, the length is 2017. And then add several chars

    E.g.

    strFilter = strFilter & "And Country In(""New255"",""N1254"")"

    The string would be 2049 characters, then you could get the error.

    If you remove one char, it works fine. 

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 29, 2017 10:08 AM
    Moderator
  • @xps350, i don't understand due to which changes it result in this erroe but you can see in original version that there is no other error except one mention above. Also t's a function as it is declared as Private Function.
    Tuesday, August 29, 2017 10:13 AM
  • @Celeste, in my original db there are round about 1500 character. Also any work around for string limit?
    • Edited by Wsm93 Tuesday, August 29, 2017 10:37 AM
    Tuesday, August 29, 2017 10:21 AM
  • Hello,

    Please the following gif of my test using your file and i think there is no work around if it reaches the maximum.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Wsm93 Monday, September 4, 2017 12:45 PM
    Wednesday, August 30, 2017 9:14 AM
    Moderator