[Q]Using "instr"

I've finished my customer database program EXCEPT one last thing.

I got to add products to database field from listbox by putting product names in string variable (ie. strCustom = APPLE LW SELECT360 TONER (OEM), BROTHER MFC2800 PRINTER (Reman.))

but then I want to reverse it back, so when user wants to edit the product list from the listbox, he/she can pick individual ones.

from APPLE LW SELECT360 TONER (OEM), BROTHER MFC2800 PRINTER (Reman.)

to

APPLE LW SELECT360 TONER (OEM)
BROTHER MFC2800 PRINTER (Reman.)

VB Code:

Dim strTempCustom As String
        Dim strTempCustom2 As String
        Dim pos As Integer
        
        rsCustomer.FindFirst "CompanyName= " & Chr(34) & cdTempCustom.strCompany & Chr(34)
        
        strTempCustom = rsCustomer!Products
        
        pos = 1
            
            Do While InStr(pos, strTempCustom, ",")
                pos = InStr(pos, strTempCustom, ",")
                lstProduct.AddItem Mid(strTempCustom, pos + 1, InStr(pos, strTempCustom, ",") - pos)
            
            Loop

my friend me gave me this code, but it just loops infinitely.

I'm not used to using Instr function..

any help would be appreciated, thank you

found a few thing i would do differently
this should help you get on track...
but you need to modify strTempCustom, or you will always find it, and hence get in an infinite loop.

not sure how long strTempCustom is, but if its only a few items, you could just strip out the left side up to the comma, and use mid$ to get all the text to the right of the 2nd comma.

just my 2 cents

VB Code:

Dim strTempCustom2 As String
            Dim pos As Integer
            Dim pos2 As Long
            Dim tmpLine As String
 
            rsCustomer.FindFirst "CompanyName= " & Chr(34) & cdTempCustom.strCompany & Chr(34)
            
            strTempCustom = rsCustomer!Products
            
            pos = 1
                
        Do While InStr(strTempCustom, ",") > 1
                   
            pos = InStr(strTempCustom, ",") ' first comma
            pos2 = InStr(pos + 2, strTempCustom, ",") - pos  'len till 2nd comma
            tmpLine = Mid$(strTempCustom, pos + 1, pos2) 'text between commas
               
 
            lstProduct.AddItem tmpLine 'add temp text instead of trying to parse
 
 Debug.Print pos, strTempCustom
                
                Loop

it's easier to use split in this situation:

VB Code:

Dim strTempCustom As String, strParts() As String
    Dim N As Integer
    
    rsCustomer.FindFirst "CompanyName= " & Chr(34) & cdTempCustom.strCompany & Chr(34)
    strTempCustom = rsCustomer!Products
    
    strParts = Split(strTempCustom, ",")
    For N = 0 To UBound(strParts)
        lstProduct.AddItem Trim$(strParts(N))
    Next N

Although bush has said a better alternative, I would just like to point out that in the original code

VB Code:

Do While InStr(pos, strTempCustom, ",")
                pos = InStr(pos, strTempCustom, ",")

Would probably be better as

VB Code:

pos=InStr(strTempCustom, ",") +1
Do 
'other code here
pos=InStr(pos,strTempCustom, ",") +1
loop while pos<>1

Less calls to instr, and very little difference in cose size :-)