using a listbox value with In() within a query 1

[SBM1745]

I have a form, with a list box. The user selcts various items from the list. I want to print a report based on the users selections. So far I have this:

Private Sub OpenSchedule_Click()
On Error GoTo Err_OpenSchedule_Click

txtSeeValue.Value = ""
Dim intCurrentRow As Integer

For intCurrentRow = 0 To ListSelect.ListCount - 1
        If ListSelect.Selected(intCurrentRow) Then
            txtSeeValue.Value = txtSeeValue.Value & ListSelect.Column(0, intCurrentRow) & ","
        End If
Next intCurrentRow
txtSeeValue.Value = Left(txtSeeValue.Value, Len(txtSeeValue.Value) - 1)

Exit_OpenSchedule_Click:
    Exit Sub

Err_OpenSchedule_Click:
    MsgBox Err.Description
    Resume Exit_OpenSchedule_Click
    
End Sub

So, txtSeeValue now contains a list (example: 12,13,14) that I'd like to place into my criteria, something like: In ([Forms]![frmByChris_PrintMultSchedules]![txtSeeValue]) for the query that the report uses. This does not work as the expression [Forms]![frmByChris_PrintMultSchedules]![txtSeeValue] returns a string value so basically In() reads like this when it is processed In("12,13,14"); therefore, searching for that string and not the various elements.

One idea I have is to create a function that returns what I need, but I am worried that any list type thatis returned will be evaluated by In() as a single element.

-Chris
Starlight B. M.

 

[PHV]

Why not building on the fly the 4th argument of the DoCmd.OpenReport method ?
strCriteria = "[Name of field] In (" & Forms!frmByChris_PrintMultSchedules!txtSeeValue & ")"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SBM1745]

OH! I think that would work great! I didn't know how to run a criteria command like that on the fly. Thanks!

-Chris
Starlight B. M.

 

[SBM1745]

I added this to OpenSchedule_Click()

Dim strCriteria As String
strCriteria = "[ScheduleID] In (" & txtSeeValue & ")"
DoCmd.OpenReport "rptByChris_PrintMultipleSchedules", acViewPreview, , strCriteria

That works perfectly! Thanks! No book I've looked through ever mentioned any of these methods! Can you suggest any reading material?

-Chris
Starlight B. M.