Populating a combo box in VBA from Recordset losing information in transfer

Trying to populate a combo box in vba from a recordset. When we do this not all of the records are transfered. Specifically we have a RS of amazon categories and for instance out of 1000 records we might get 400-500 populated. The loop finishes and goes through each record but the box doesnt record them all. We think it might be a size limit because they are string values but another combo box with ebay categories populates without a hitch. When we reduce the character value from each record we eventually get all to post. Below are the codes from both combo boxes. Any help would be appreciated.

Problem combo box:
If IsNull(cmbAmazonCat2) Or cmbAmazonCat2 = "" Then
        Exit Sub
    End If
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim itemDescription As String
Dim str As String
 strSQL = "SELECT Distinct tbl_amazon_categories.path3 FROM tbl_amazon_categories where path2 = '" & CleanupforSQL(cmbAmazonCat2) & "' and isleaf = -1 and (path3 is not null or path3 <> '" & "" & "')"

Set cn = New ADODB.Connection

cn.Open MySQLConnectString()
    With cn
        .CommandTimeout = 0
        .CursorLocation = adUseClient
    End With
'cn.Execute (strSQL)
Dim i As Integer
On Error GoTo Handler
If Me.cmbAmazonCat3.ListCount > 0 Then
    i = Me.cmbAmazonCat3.ListCount - 1
    Do While i >= 0
        Me.cmbAmazonCat3.RemoveItem (i)
        i = i - 1
    Loop
End If


rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic, adCmdText
If Not rs.EOF Then

i = 0
    Do While Not rs.EOF
    i = i + 1
 
       
   str = Chr(34) & rs(0) & Chr(34)

             cmbAmazonCat3.AddItem Item:=str

        rs.MoveNext
    Loop

End If
MsgBox "Total Records = " & i & vbNewLine & "Number items on combobox = " & cmbAmazonCat3.ListCount

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

Handler:
'MsgBox "Error at cmbamazoncat3" & vbNewLine & "err message " & Err.Description
Resume Next


Ebay combo box:
If ignoreEntry Or IsNull(cmbCat2) Or cmbCat2 = "" Then
    Exit Sub
End If
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim itemDescription As String
       
If InStr(1, cmbMainCat.Column(0), "'") Or InStr(1, cmbCat2.Column(0), "'") Then
    'MsgBox "has apostorphes"
    strSQL = "SELECT * from tbl_ebay_categories where categorylevel1 = '" & Replace(Replace(cmbMainCat.Column(0), ",", "\,"), "'", "\'") & "' and categorylevel2 = '" & Replace(Replace(cmbCat2.Column(0), ",", "\,"), "'", "\'") & "'"
Else
   ' MsgBox "does not have apostorphes"
    strSQL = "SELECT * from tbl_ebay_categories where categorylevel1 = '" & Replace(Replace(cmbMainCat.Column(0), ",", "\,"), "'", "\'") & "' and categorylevel2 = '" & Replace(Replace(cmbCat2.Column(0), ",", "\,"), "'", "\'") & "'"
End If

Set cn = New ADODB.Connection

cn.Open MySQLConnectString()
    With cn
        .CommandTimeout = 0
        .CursorLocation = adUseClient
    End With
'cn.Execute (strSQL)
Dim i As Integer
If Me.cmbCat3.ListCount > 0 Then
    i = Me.cmbCat3.ListCount - 1
    Do While i >= 0
        Me.cmbCat3.RemoveItem (i)
        i = i - 1
    Loop
End If
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic, adCmdText
Dim myStr As String
If Not rs.EOF Then
 
    Do While Not rs.EOF
   
            'myStr = rs("categorylevel1") & ":" & rs("categorylevel2")
            myStr = rs("categorylevel2")
            If Not IsNull(rs("categorylevel3")) And rs("categorylevel3") <> "" Then
            myStr = myStr & ":" & rs("categorylevel3")
            ' myStr = rs("categorylevel3")
            End If
            If Not IsNull(rs("categorylevel4")) And rs("categorylevel4") <> "" Then
            myStr = myStr & ":" & rs("categorylevel4")
            End If
            If Not IsNull(rs("categorylevel5")) And rs("categorylevel5") <> "" Then
            myStr = myStr & ":" & rs("categorylevel5")
            End If
            If Not IsNull(rs("categorylevel6")) And rs("categorylevel6") <> "" Then
            myStr = myStr & ":" & rs("categorylevel6")
            End If
            If Not IsNull(rs("categorylevel7")) And rs("categorylevel7") <> "" Then
            myStr = myStr & ":" & rs("categorylevel7")
            End If
            If myStr <> "" Then
                myStr = rs(0) & ":" & myStr
                myStr = Chr(34) & myStr & Chr(34)
                cmbCat3.AddItem Item:=myStr
           End If
        rs.MoveNext
       
    Loop
   
End If

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
ProgrammingIsFunAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can set a Combo's Recordsource as a recordset directly, which might give you better results:

If IsNull(cmbAmazonCat2) Or cmbAmazonCat2 = "" Then
        Exit Sub
    End If
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim itemDescription As String
Dim str As String
 strSQL = "SELECT Distinct tbl_amazon_categories.path3 FROM tbl_amazon_categories where path2 = '" & CleanupforSQL(cmbAmazonCat2) & "' and isleaf = -1 and (path3 is not null or path3 <> '" & "" & "')"

Set cn = New ADODB.Connection
cn.Open MySQLConnectString()
    With cn
        .CommandTimeout = 0
        .CursorLocation = adUseClient
    End With

rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic, adCmdText
If Not rs.EOF Then
  YourCombo.Recordset = rs
End If

You may have to use this syntax:

SET YourCombo.Recordset = rs

Can't recall off the top of my head ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ProgrammingIsFunAuthor Commented:
Thank you for your response. We found a work around to the issue by populating the combo box in a different step other than enter. Not sure why this caused a problem. Your solution is a good one.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.