Run Time Error 6, overflow

Question

Hello,

I inserted a Microsft code example to multi-select on a listbox.  Everything works fine if the listbox based of a valued list.  However, if I use a table/query for the listbox I get the following error "Run Time Error '6', Over"  When I click debug vba highlights this line: iListItemsCount = iListItemsCount + 1

Here is the complete code:

Private Sub Form_Current()

    Dim oItem As Variant

    Dim bFound As Boolean

    Dim sTemp As String

    Dim sValue As String

    Dim sChar As String

    Dim iCount As Integer

    Dim iListItemsCount As Integer

    If Me.Active = -1 Then

       Detail.BackColor = 16777215      'Active Tutor

    Else

       Detail.BackColor = vbRed  'Not Active Tutor

    End If

    sTemp = Nz(Me!mySelections.Value, " ")

    iListItemsCount = 0

    bFound = False

    iCount = 0

   Call clearListBox

    For iCount = 1 To Len(sTemp) + 1

    sChar = Mid(sTemp, iCount, 1)

        If StrComp(sChar, ",") = 0 Or iCount = Len(sTemp) + 1 Then

            bFound = False

            Do

                If StrComp(Trim(Me!NamesList.ItemData(iListItemsCount)), Trim(sValue)) = 0 Then

                    Me!NamesList.Selected(iListItemsCount) = True

                    bFound = True

                End If

               iListItemsCount = iListItemsCount + 1            

            Loop Until bFound = True Or iListItemsCount = Me!NamesList.ListCount

            sValue = ""

        Else

            sValue = sValue & sChar

        End If

    Next iCount

End Sub

Answer 1

Hi thx607,

How large of number are you putting into your “iListItemsCount” variable? If it is outside the range of an integer, you most likely would then receive this error. I believe the range would be -32,768 to 32,767.  You could try changing your variable definition from an Integer to a Long and see if that fixes your problem. With that being said, if this does fix your problem and this count represents the number of items you are actually putting in a list box control, you may want to think of a way to limit the number of items you are presenting to your user. We normally wouldn’t recommend putting thousands of records into list box or combo box controls do to various reasons such as performance and in addition to this users tend to find it difficult finding what they are looking for when having to scroll through that many options.

Overflow error - http://msdn.microsoft.com/en-us/library/hzsytfc8(VS.80).aspx

The Integer, Long, and Byte Data Types - http://msdn.microsoft.com/en-us/library/aa164754(office.10).aspx

Hope that helps.

Best Regards,

Nathan Ost

Microsoft Online Community Support

---

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 2

I know this is an old thread but I am having the same problem, the error only occurs on my system when there is no value for the list box.

In my example I have created a query that concatenates several columns (only small values short text) this query is based on a relational table (secondary table) and is linked to the master table with a primary and foreign key and then this is based in the query criteria as [forms]![mainform]![IDmain] ... this all works fine and pulls the correct record into the query. When the secondary relational table does not have any records added to it from the master table as none may be needed when the query is executed (this may be a done for search by a front end user to see if they have any records) the error as described above happens.

If the secondary table does have values linked to the master table then the query executes fine.

I have also found that on occasions if the information in the secondary table is changed or updated when you again run this list query you get the error again. This is a strange one as I have tested this and on occasions there are no problems then for no apparent reason it does cause the error.

I used the code below directly taken form http://support.microsoft.com/kb/827423

List Box

---

Name             :  NamesList

Row Source Type  :  Table/Query

Row Source       :  SELECT FirstName FROM Employees

Multi Select     :  Extended

Width            :  3.5"

Height           :  0.75"

Text Box

---

Name    :  mySelections

Width   :  3.5"

Height  :  0.25"

Command Button

---

Name     :  testmultiselect

Caption  :  Display Selected Items

Width    :  1.375"

Height   :  0.3"

Command Button

---

Name     :  ClrList

Caption  :  Clear List

Width    :  1.375"

Height   :  0.3"

Option Compare Database
Option Explicit

Private Sub Form_Current()
    Dim oItem As Variant
    Dim bFound As Boolean
    Dim sTemp As String
    Dim sValue As String
    Dim sChar As String
    Dim iCount As Integer
    Dim iListItemsCount As Integer
    
    sTemp = Nz(Me!mySelections.Value, " ")
    iListItemsCount = 0
    bFound = False
    iCount = 0

    Call clearListBox
        
    For iCount = 1 To Len(sTemp) + 1
    sChar = Mid(sTemp, iCount, 1)
        If StrComp(sChar, ",") = 0 Or iCount = Len(sTemp) + 1 Then
            bFound = False
            Do
                If StrComp(Trim(Me!NamesList.ItemData(iListItemsCount)), Trim(sValue)) = 0 Then
                    Me!NamesList.Selected(iListItemsCount) = True
                    bFound = True
                End If
                iListItemsCount = iListItemsCount + 1
            Loop Until bFound = True Or iListItemsCount = Me!NamesList.ListCount
            sValue = ""
        Else
            sValue = sValue & sChar
        End If
    Next iCount
End Sub
    
Private Sub clearListBox()
    Dim iCount As Integer
        
    For iCount = 0 To Me!NamesList.ListCount
        Me!NamesList.Selected(iCount) = False
    Next iCount
End Sub

Private Sub testmultiselect_Click()
    Dim oItem As Variant
    Dim sTemp As String
    Dim iCount As Integer
    
    iCount = 0
            
    If Me!NamesList.ItemsSelected.Count <> 0 Then
        For Each oItem In Me!NamesList.ItemsSelected
            If iCount = 0 Then
                sTemp = sTemp & Me!NamesList.ItemData(oItem)
                iCount = iCount + 1
            Else
                sTemp = sTemp & "," & Me!NamesList.ItemData(oItem)
                iCount = iCount + 1
            End If
        Next oItem
    Else
        MsgBox "Nothing was selected from the list", vbInformation
        Exit Sub  'Nothing was selected
    End If
    
    Me!mySelections.Value = sTemp
End Sub
    
Private Sub clrList_Click()
    Call clearListBox
    Me!mySelections.Value = Null
End Sub

As a footnote the clear list button does not seem to action anything?

Answer 3

Hello,

I know it's been a few years, but did you ever come up with a solution to this problem? I am having it now.

Thanks.

Gina

Answer 4

Hello,

I know it's been a few years, but did you ever come up with a solution to this problem? I am having it now.

Thanks.

Gina

Gina, I do not remember the problem but I looked back at what I ended up with and it looks like I created two Embedded Event Macros.  I don't know a better way to show you the macro, sorry they are screenshots of the macro viewer.  It also may help to follow the link provided by Nathan O., Im sure thats how I came to this solution.

AfterUpdate on the Combo box

On Got Focus

Answer 5

Thanks for taking the time to reply David! I will try this and see if it works.

Have a great day!

Gina