Split returned values from Function

[MrsMope]

Hi All,
Ok first this is what I want to do, I want to fill a combobox on a form based on the user selection from a different combobox.

The combobox I want to fill will contain a sales ID and a salesperson name.
I would like to use my existing function to fill this combobox the function is as follows:

Public Function FillRecordset(SQL) As String
Set cnnICInquiry = CurrentProject.Connection
'Function used to create a string statement for the combo box rowsource
On Error GoTo err_FilLRecordset

rstTemp.Open SQL, cnnICInquiry, adOpenKeyset, adLockOptimistic

With rstTemp
  'clear list
    strList = ""
    If .RecordCount > 0 Then
        For intCtr = 0 To .Fields.Count - 1
            'strList = strList & .Fields(intCtr).Name & ";"
        Next intCtr
        Do Until .EOF
            For intCtr = 0 To .Fields.Count - 1
                strList = strList & .Fields(intCtr).Value & ";"
             
            Next intCtr
            .MoveNext
        Loop
        'If the list is longer than combo limitations, then display label showing error
        If Len(strList) > 2047 Then
            Form_frmMain.lblList.Visible = True
            .Close
            Exit Function
        Else
        End If
    Else
    'IF there are not roles or tiers associated with salesperson/customer for time frame
    ' let end user know then reset the frames
        MsgBox ("There are no associated IC payments for the selection made")
        Form_frmMain.fraICRoles.Value = 1
        Form_frmMain.fraTierPercentage.Value = 1
        Form_frmMain.lstICRole.Value = ""
        Form_frmMain.cboTier.Value = ""
        Form_frmMain.lstICRole.Visible = False
        Form_frmMain.cboTier.Visible = False
        Form_frmMain.cboFromDate.SetFocus
    End If
    .Close
    FillRecordset = strList
End With
exit_FillRecordset:
Exit Function
err_FilLRecordset:
    MsgBox Err.Description
    Resume exit_FillRecordset
    
End Function

As you can see I will get my values but they are returned in a string like :

112345; Joe Smith; 112346;Jane Doe

How can I take what I have returning and put it into two columns in a combobox to look like:

 Column 1   Column 2
       112345     Joe Smith
       112346     Jane Doe

 

[BoxHead]

On the combo box, the Row Source Type has to be Value List, the Column COunt has to be 2 and you may have to include quotation marks arround the Sales Persons' names.

HTH


"True organizational power lies in the paradigm shift from
Efficient Data Management to Effective Data Mastery."

 

[Zion7]

Exactly as boxhead said, but I don't believe you need quotes, around the string values (as your original code shows).

MrsMope, here's a small suggestion to streamline your code a bit. I like to use the GetString(), when obtaining a rowsource, from a recordset...

Public Function FillRecordset(SQL) As String
Set cnnICInquiry = CurrentProject.Connection
'Function used to create a string statement for the combo box rowsource
On Error GoTo err_FilLRecordset

rstTemp.Open SQL, cnnICInquiry, adOpenKeyset, adLockOptimistic

With rstTemp
'clear list
strList = ""
If Not .EOF Then
strList = .GetString(vbClipstring,";",";")
'If the list is longer than combo limitations, then display label showing error
If Len(strList) > 2047 Then
Form_frmMain.lblList.Visible = True
GoTo exit_FillRecordset:
End If
Else
'IF there are not roles or tiers associated with salesperson/customer for time frame
' let end user know then reset the frames
MsgBox ("There are no associated IC payments for the selection made")
strList = ""
Form_frmMain.fraICRoles.Value = 1
Form_frmMain.fraTierPercentage.Value = 1
Form_frmMain.lstICRole.Value = ""
Form_frmMain.cboTier.Value = ""
Form_frmMain.lstICRole.Visible = False
Form_frmMain.cboTier.Visible = False
Form_frmMain.cboFromDate.SetFocus
End If
FillRecordset = strList
End With
exit_FillRecordset:
.Close: Set rstTemp = Nothing

Exit Function
err_FilLRecordset:
MsgBox Err.Description
Resume exit_FillRecordset

End Function

 

[TRYP]

or just use VB's split() function to turn your string into an array...



just an idea
-tryp