User Input as part of Module Parameter

[Camões]

I'm new to Access so I'll try to explain the best way I can:



So I have this Query where a pop-up appears for the user to input the Year and the query displays info for all months for that year. And it's working as intended

SELECT Estagios.Nome, 
(SELECT COUNT(idInterno) FROM Alocados WHERE Mes=1 AND Ano=[Insira Ano:] AND idEstagio=Estagios.ID) AS M01, 
(SELECT COUNT(idInterno) FROM Alocados WHERE Mes=2 AND Ano=[Insira Ano:] AND idEstagio=Estagios.ID) AS M02, 
(SELECT COUNT(idInterno) FROM Alocados WHERE Mes=3 AND Ano=[Insira Ano:] AND idEstagio=Estagios.ID) AS M03, 
(SELECT COUNT(idInterno) FROM Alocados WHERE Mes=4 AND Ano=[Insira Ano:] AND idEstagio=Estagios.ID) AS M04, 
(SELECT COUNT(idInterno) FROM Alocados WHERE Mes=5 AND Ano=[Insira Ano:] AND idEstagio=Estagios.ID) AS M05, 
(SELECT COUNT(idInterno) FROM Alocados WHERE Mes=6 AND Ano=[Insira Ano:] AND idEstagio=Estagios.ID) AS M06, 
(SELECT COUNT(idInterno) FROM Alocados WHERE Mes=7 AND Ano=[Insira Ano:] AND idEstagio=Estagios.ID) AS M07, 
(SELECT COUNT(idInterno) FROM Alocados WHERE Mes=8 AND Ano=[Insira Ano:] AND idEstagio=Estagios.ID) AS M08, 
(SELECT COUNT(idInterno) FROM Alocados WHERE Mes=9 AND Ano=[Insira Ano:] AND idEstagio=Estagios.ID) AS M09, 
(SELECT COUNT(idInterno) FROM Alocados WHERE Mes=10 AND Ano=[Insira Ano:] AND idEstagio=Estagios.ID) AS M10, 
(SELECT COUNT(idInterno) FROM Alocados WHERE Mes=11 AND Ano=[Insira Ano:] AND idEstagio=Estagios.ID) AS M11, 
(SELECT COUNT(idInterno) FROM Alocados WHERE Mes=12 AND Ano=[Insira Ano:] AND idEstagio=Estagios.ID) AS M12, 
Nz((SELECT COUNT(idInterno) FROM Alocados WHERE idEstagio=Estagios.ID AND Ano=[Insira Ano:] GROUP BY idEstagio),0) AS Total
FROM Estagios;

But with the next Query I use a Module named "ConcatRelated" that puts in a Cell a list of names separated by ",":

SELECT AA.ID, AA.Nome, 
ConcatRelated("Internos_Nome","qAlocados","IdEstagio = " & AA.ID & " AND Ano=2020 AND Mes=1","Internos_Nome") AS M01, 
ConcatRelated("Internos_Nome","qAlocados","IdEstagio = " & AA.ID & " AND Ano=2020 AND Mes=2","Internos_Nome") AS M02, 
ConcatRelated("Internos_Nome","qAlocados","IdEstagio = " & AA.ID & " AND Ano=2020 AND Mes=3","Internos_Nome") AS M03, 
ConcatRelated("Internos_Nome","qAlocados","IdEstagio = " & AA.ID & " AND Ano=2020 AND Mes=4","Internos_Nome") AS M04, 
ConcatRelated("Internos_Nome","qAlocados","IdEstagio = " & AA.ID & " AND Ano=2020 AND Mes=5","Internos_Nome") AS M05, 
ConcatRelated("Internos_Nome","qAlocados","IdEstagio = " & AA.ID & " AND Ano=2020 AND Mes=6","Internos_Nome") AS M06, 
ConcatRelated("Internos_Nome","qAlocados","IdEstagio = " & AA.ID & " AND Ano=2020 AND Mes=7","Internos_Nome") AS M07, 
ConcatRelated("Internos_Nome","qAlocados","IdEstagio = " & AA.ID & " AND Ano=2020 AND Mes=8","Internos_Nome") AS M08, 
ConcatRelated("Internos_Nome","qAlocados","IdEstagio = " & AA.ID & " AND Ano=2020 AND Mes=9","Internos_Nome") AS M09, 
ConcatRelated("Internos_Nome","qAlocados","IdEstagio = " & AA.ID & " AND Ano=2020 AND Mes=10","Internos_Nome") AS M10, 
ConcatRelated("Internos_Nome","qAlocados","IdEstagio = " & AA.ID & " AND Ano=2020 AND Mes=11","Internos_Nome") AS M11, 
ConcatRelated("Internos_Nome","qAlocados","IdEstagio = " & AA.ID & " AND Ano=2020 AND Mes=12","Internos_Nome") AS M12
FROM Estagios AS AA;

ConcatRelated being:

Option Compare Database


Public Function ConcatRelated(strField As String, _
    strTable As String, _
    Optional strWhere As String, _
    Optional strOrderBy As String, _
    Optional strSeparator = ", ") As Variant
On Error GoTo Err_Handler
    'Purpose:   Generate a concatenated string of related records.
    'Return:    String variant, or Null if no matches.
    'Arguments: strField = name of field to get results from and concatenate.
    '           strTable = name of a table or query.
    '           strWhere = WHERE clause to choose the right values.
    '           strOrderBy = ORDER BY clause, for sorting the values.
    '           strSeparator = characters to use between the concatenated values.
    'Notes:     1. Use square brackets around field/table names with spaces or odd characters.
    '           2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
    '           3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
    '           4. Returning more than 255 characters to a recordset triggers this Access bug:
    '               http://allenbrowne.com/bug-16.html
    Dim rs As DAO.Recordset         'Related records
    Dim rsMV As DAO.Recordset       'Multi-valued field recordset
    Dim strSql As String            'SQL statement
    Dim strOut As String            'Output string to concatenate to.
    Dim lngLen As Long              'Length of string.
    Dim bIsMultiValue As Boolean    'Flag if strField is a multi-valued field.
    
    'Initialize to Null
    ConcatRelated = Null
    
    'Build SQL string, and get the records.
    strSql = "SELECT " & strField & " FROM " & strTable
    If strWhere <> vbNullString Then
        strSql = strSql & " WHERE " & strWhere
    End If
    If strOrderBy <> vbNullString Then
        strSql = strSql & " ORDER BY " & strOrderBy
    End If
    Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)
    'Determine if the requested field is multi-valued (Type is above 100.)
    bIsMultiValue = (rs(0).Type > 100)
    
    'Loop through the matching records
    Do While Not rs.EOF
        If bIsMultiValue Then
            'For multi-valued field, loop through the values
            Set rsMV = rs(0).Value
            Do While Not rsMV.EOF
                If Not IsNull(rsMV(0)) Then
                    strOut = strOut & rsMV(0) & strSeparator
                End If
                rsMV.MoveNext
            Loop
            Set rsMV = Nothing
        ElseIf Not IsNull(rs(0)) Then
            strOut = strOut & rs(0) & strSeparator
        End If
        rs.MoveNext
    Loop
    rs.Close
    
    'Return the string without the trailing separator.
    lngLen = Len(strOut) - Len(strSeparator)
    If lngLen > 0 Then
        ConcatRelated = Left(strOut, lngLen)
    End If


Exit_Handler:
    'Clean up
    Set rsMV = Nothing
    Set rs = Nothing
    Exit Function


Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()"
    Resume Exit_Handler
End Function

The problem arrises when I susbtitute "Ano=2020" on the second Query for "Ano=[Insert Year:]" that results in a Error PopUp saying:
Error 3061 : Too few parameters. Expected 1.

I think that the problem is that a user input can't be on a parameter for a module, so now I need to know the best option:
Is there any way to change the ConcatRelated Module to work with the change above, do I need to make subqueries and pass them as the parameter.

[davegri]

Instead of "Ano=2020", just use "2020"

[CJ_London]

you can try


ConcatRelated("Internos_Nome","qAlocados","IdEstag io = " & AA.ID & " AND Ano=" & [Insert Year:] & " AND Mes=1","Internos_Nome") AS M01,

[Micron]

I think the function requires a where clause without the "where" so just a number shouldn't work, and I don't think the user wants a prompt for every query execution, but I could be wrong.
If those answers don't help, what's the data type of the year - string, date or number? AFAIK, the sql interpreter (query side of Access) isn't so fussy about recognizable data types and it does make assumptions. However, you don't have that luxury when running sql from code, so I'm thinking that 2020 may need delimiters.

EDIT - I just realized this is outside of the quotes for a portion of the WHERE clause - " & AA.ID & "
Now I think that is what the DAO recordset is not able to interpret and is asking you for a parameter. If that's the case, you'll probably have to modify the function code to declare and define a query def object and declare and define any parameters.

[Camões]

you can try


ConcatRelated("Internos_Nome","qAlocados","IdEstag io = " & AA.ID & " AND Ano=" & [Insert Year:] & " AND Mes=1","Internos_Nome") AS M01,

Thank you, ended up doing something similar:

SELECT AA.ID, AA.Nome, [Insira Ano:] AS [Year], 
ConcatRelated("Internos_Nome","qAlocados","IdEstagio = " & AA.ID & " AND Ano=" & Year & " AND Mes=1","Internos_Nome") AS M01,

with the use of the extra column I can create a TextBox in the report and define it as : =[Report]![ReportName]![Insira Ano:]; to display the Year in the report.

[CJ_London]

that's fine - just be aware that Year is a reserved word. Using it as a field or control name can cause inexplicable errors. At the very least, surround with square brackets.