Function asks for parameter

[accessma]

Can someone tell me why this function is asking for a parameter?

Option Compare Database
Option Explicit

Function MultipleValueCriteria(pform As Form, _
                               pcontrol As ListBox, pfield As String)
                               
'Launch rptSummaryReport using
'OR criteria built on field
'passed by pfield; report must be
'passed via the forms Tag property.
Dim var As Variant
Dim strCriteria As String
If pcontrol.ItemsSelected.Count = 0 Then
   MsgBox "Please select at least 1 Department!", _
           vbOKOnly, "Error"
   Exit Function
'Build SQL statement using selected Departments
Else
    'Criteria expression uses literal string
    'values.  If using numeric or date values,
    'update delimeter component.
    For Each var In pcontrol.ItemsSelected
        strCriteria = strCriteria & _
        pfield & " = '" & _
        pcontrol.ItemData(var) _
        & "' Or "
    Next var
End If

strCriteria = Left(strCriteria, _
 Len(strCriteria) - 4)
 Debug.Print strCriteria
 'Open filtered report and close form.
 DoCmd.OpenReport pform.Tag, _
 acViewPreview, , strCriteria
 DoCmd.Close acForm, pform.Name
 Set pcontrol = Nothing
 Set pform = Nothing
                               
End Function

 

[missinglinq]

Only queries ask for parameters, and in looking at the code, my guess would be that the query involved here would be the one underlying your report that is called here:

DoCmd.OpenReport pform.Tag, acViewPreview, , strCriteria

Queries pop parameter requests when they haven't been feed a parameter thet need or if they can't find one of their fields.

 

[accessma]

No the report name comes from the Tag property in the form.

 

[missinglinq]

What does that have to do with the query underlying the report? Read my post again. The only object in Access that requests parameters arequeries. The only thing in your function that would reference a query is your report. There's something missing in this query.

 

[boblarson]

And to build on Linq's statement/questions - what is the EXACT message you are receiving? That will give you a hint as to where to look in that query.

 

[accessma]

OK new plan. In the attached WB I have a form in the FE called "Summary Report Multi Selection Criteria" For the life of me I cannot get the syntax correct. Its probably easy but having never done this before its a struggle. Any help, tips, pointers appreciated. Thanks

 

[boblarson]

Your problem is that your where clause is not building properly. You need single quotes around each of the string items. So, I changed it to this to get it to work (but you'll have to test which data type to put in the right delimiter:

    With Me.lstDept
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                'Build up the filter from the bound column (hidden).
                strWhere = strWhere &[color=red] "'"[/color] & .ItemData(varItem) &[color=red] "'"[/color] & ","
                'Build up the description from the text in the visible column. See note 2.
                strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
            End If
        Next
    End With

 

[accessma]

Everything is type "text". Will your changes work for that?

 

[boblarson]

If it is text then yes, you would need the single quotes like I put in. If it could be variable then you would need to figure a way to determine the type and put in the appropriate delimiter (' for text, # for dates, and blank for numbers).

 

[accessma]

Thanks got it working just like I want it to. One other thing and I'll be all done. How would I let the user enter a date range and do a filter on the department selections or employee selections in just that date range, or can you do that?

 

[boblarson]

Look at my sample on my website and it will show you, including how they can do a start date, an end date, or both (along with your other where clause info):

http://downloads.btabdevelopment.com/Samples/combos/FormSampleFromMultipleCriteria.zip

 

[accessma]

Hmmmm.....Clicked the link, got page cannot be displayed.

 

[boblarson]

I just clicked the link and the file dialog to download the file just came up. Maybe you have a security setting somewhere that is doing it, or maybe it just was a glitch. See what happens when you click again.

 

[accessma]

Tried it several times. Can you post it here?

 

[boblarson]

I'll post it here, but there is some setting that is keeping you from downloading it because it works fine from all my computers.

 

[accessma]

Thanks. A couple of questions:

Do you have to have all the code in the same sub or function for this to work. Also, cant get the code which I copied from your example and changed the names to clear the selections in my list boxes. Is there a setting in the properties you have to set?