Overflow Error

S

Stockwell43

Hello,

On my form I have a multi-select List box. This is the code I am using from
Microsofts website:

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!AddInfo.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

I got 12 records in the database and I got run time error '6' Overflow and
it higlighted this sentence of the code in yellow:
iListItemsCount = iListItemsCount + 1

Does this mean I am limited to so many selections? I have never used a list
box before.

Thanks!!
 
D

Douglas J. Steele

For some reason, it's complaining that iListItems is getting too large for
an integer to hold, which is odd given your Loop Until condition.

Try the following instead:

For iListItemsCount = 0 To (Me!NameList.ListCount - 1)
If StrComp(Trim(Me!NamesList.ItemData(iListItemsCount)), Trim(sValue)) =
0 Then
Me!NamesList.Selected(iListItemsCount) = True
bFound = True
Exit For
End If
Next iListItems
 
S

Stockwell43

I was reading something on the net saying to change the Interger to Long but
when I did that, my form froze. Just in case, is there another way of making
multiple selections? I am getting very frustrated with this and probably will
not use list boxes no more.
 
D

Douglas J. Steele

Doesn't seem relevant: it's for Visual Basic 5.0, not VBA.

What does your entire routine look like now?
 
S

Stockwell43

The List box is unbound and and the name is "NamesList" which has a row
source that is a table for additonal information needed.

On a click of a button, everything selected goes to a textbox named
"Addinfo" and the code behind the button is:

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!AddInfo.Value = sTemp

Dim cntr As Long
Dim lngListCnt As Long
lngListCnt = Me.NamesList.ListCount - 1
For cntr = 0 To lngListCnt
Me.NamesList.Selected(cntr) = False
Next cntr

End Sub

This is the code from yesterday that is in the forms Current Event:

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!AddInfo.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

This is another piece of code the instruction from Microsoft said to add:

Private Function DocList() As String

Dim strDocs As String
With Me.NamesList
For Each varItem In Me.NamesList.ItemsSelected
strDocs = strDocs & .ItemData(varItem) & ", "
Next varItem
End With
DocList = Left(strDocs, Len(strWhere) - 1)
End Function

'So to use it, append it to the text as

strMsgText = strMsgText & DocList
End Function

I do notice that after I make my selection and click the button to display
what was selected, the code you and Mr B gave me to deselct after display
works fine but, when I move to another record and go back to the original
record I left the selections are still selected and maybe that's what is
causing the problem (just a guess).

I noticed some people have the same issue and are told to change the integer
to long and when I do that my form will freeze when I do something in it.
This has to be a simple fix as everything works perfectly except for this one
error but I can't let this go into production as is unless it can be fixed OR
I can come up with another way to similate a multi-select list box without
using a list box. Should the list box be bound?

I got all of the instruction from a microsoft website for multi-select list
boxes and followed it to a tee. I believe it also said something about the
northwind database as well. Maybe I should take a look and see how it is set
up in there.

I would really appreciate any help you can give me to fix this whether is be
the list box or alternative as I am close to being finished outside of this
problem.

Thank you for staying with me on this Doug, your help is most appreciated!
 
D

Douglas J. Steele

I'm getting very confused!

I'd suggested that you replace this code:

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

with

For iCount = 1 To Len(sTemp) + 1
sChar = Mid(sTemp, iCount, 1)
If StrComp(sChar, ",") = 0 Or iCount = Len(sTemp) + 1 Then
bFound = False
For iListItemsCount = 0 To (Me!NameList.ListCount - 1)
If StrComp(Trim(Me!NamesList.ItemData(iListItemsCount)),
Trim(sValue)) = 0 Then
Me!NamesList.Selected(iListItemsCount) = True
bFound = True
Exit For
End If
Next iListItems
sValue = ""
Else
sValue = sValue & sChar
End If
Next iCount

and you wrote back that it highlights the line

Next iListItems

in yellow. However, I don't see that line of code in what you've posted!

Now, you say that the row source of the list box is a table. Originally you
said that you've got 12 records in the database. Did you mean 12 records in
the table that's the row source of the list box? If the table that makes up
the row source of the list box has more than 32,767 rows in it, then yes,
you do need to change the declaration from Integer to Long.

As to your comment "when I move to another record and go back to the
original record I left the selections are still selected", I'm not quite
sure what you mean. Let's assume that you were on record A and the 3rd row
in the list box was selected. You then moved to record B. Did the selected
row in the list box change? Now you move back to record A. Is the list box
still showing what was selected on record B, or is it changed back to the
3rd row (which is what was selected when you went to record A)? Remember
that the Form_Current procedure is run everytime you change from one record
to another, and your code automatically clears the list box and then selects
at most one entry in the list box.
 
S

Stockwell43

That came from the copy I have. Seeing how I posted what was wrong when I
changed it to what you gave me, I thought when you asked what my entire
routine looked like, I thought you wanted to see what I started with prior to
your code. Thinking if you can see everything I was using, you may have had
another way of writing the code.

I apologize for the confusion, my error.
 
S

Stockwell43

I have 12 records in the main table. The table that feeds the list box is a
standalone table that stores 22 rows and consist of just additonal items
like:

Deed of trust, Oringinal Signature needed, Original POA etc... it is more of
a list of things is all.

On my other issue, if I enter information in record A and make three
selcetions from the list and click add record I now move to record B and the
list has no selections made which is correct. However, when I go back to
Record A the three selection I made are still selected even though I have
them displayed in the textbox. So I'm wondering why they are still selected?
When I click the button to display them in the textbox and leave the record,
should they still be selected when I go back? Again, I never worked with
Listboxes before and not to knowledgeable on the functionality.

Sorry about all this. :blush:)
 
D

Douglas J. Steele

What are you trying to do? Looking more closely at your
testmultiselect_Click procedure, you appear to be building a comma-delimited
list of selected rows in the list box and then putting that comma-delimited
list into AddInfo (is that a bound control?)

In the Form_Current procedure, you're then doing something funky with the
content of AddInfo.

Are you trying to violate database normalization rules and store multiple
values in a single field?

If you have some legitimate reason for doing so, you're going about
decomposing the comma-delimited list incorrectly in Form_Current.

Private Sub Form_Current()
Dim lCount As Long
Dim lListItemsCount As Long
Dim sTemp As String
Dim vSelected As Variant

sTemp = Nz(Me!AddInfo.Value, " ")
vSelected = Split(sTemp, ",")

Call clearListBox

For iCount = LBound(vSelected) To UBound(vSelected)
For iListItemsCount = 0 To (Me!NameList.ListCount - 1)
If StrComp(Trim(Me!NamesList.ItemData(iListItemsCount)), _
Trim(vSelected(iCount))) = 0 Then
Me!NamesList.Selected(iListItemsCount) = True
Exit For
End If
Next iListItems
Next iCount

End Sub
 
S

Stockwell43

Correct. The Addinfo is a bound control saved to the main table.

All the code I gave you came from Microsofts website. I just changed their
field name to mine (addinfo) and that was it. If the code goes against
normalization, that's micorsoft code not mine.

I am storing multiple selection in a textbox (addinfo) and have that field
set to Memo so it would act like a comments box.

All of this was trial an error (I see now mostly error). I didn't know how
to go about starting it so I found the code on Microsofts website, changed a
few things around to fit what I needed it to do and it worked fine other than
the overflow error.

Should I replace the code with what you sent? Is that what will fix the
error? I'll give it a try Doug and thanks. Sorry to have been a pain with all
this but this is how I learn. :blush:)
 
S

Stockwell43

Ok, I ran the code and it gives my a compile error: Variable not defined
It stops on iCount highlighted in blue in this line:

For iCount = LBound(vSelected) To UBound(vSelected)
 
D

Douglas J. Steele

Sorry, my typo. I forgot that I'd changed the Integers to Longs.

For lCount = LBound(vSelected) To UBound(vSelected)
For lListItemsCount = 0 To (Me!NameList.ListCount - 1)
If StrComp(Trim(Me!NamesList.ItemData(lListItemsCount)), _
Trim(vSelected(lCount))) = 0 Then
Me!NamesList.Selected(lListItemsCount) = True
Exit For
End If
Next lListItems
Next lCount

Either that, or change the declarations to

Dim iCount As Long
Dim iListItemsCount As Long
 
S

Stockwell43

I tried it both ways using the new code and using the one before but change
the declarations and it give me the same compile error "Variable not defined"
But it now highlights "Next lListItems" in blue on both ways I tried.
 
S

Stockwell43

I got it. I added Count to the end of Next lListItems and added an "s" and
the end of (Me!NameList.ListCount - 1) to make it (Me!NamesList.ListCount - 1)

Seems to work ok, I have not encountered the error. I will continue to add a
couple of hundred records to make sure the error does not appear.

Doug, I truely appreciate you staying with me on this and thank you again
for helping me along. Sorry for all the confusion. I think I'm good now.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cascading form box with multi-select and multiple column popup fai 1
hyperlinking to charts in file 1
Traverse XML Document 1
Function Problem 6
Multi-Select Listbox VBA code causing overflow in loop 1
dynamic query help 4
Convert Function to Sub 6
File locking problem 2

Top