Solved Error 3061. Too few parameters. Expected 2

[GaP42]

My database uses an append query to add member financial records for current members when a new financial year is established - launched from a command button on a form to manage financial years. Following in the same routine are two update queries that then insert the membership fee appropriate to the member type (1 = an person, 2 = corporate member) for that financial year. However it fails with the above message when it gets to the first of these two update queries. The update queries are each dependent upon a select query that when run, retrieves and shows the appropriate fees for the financial year for the member type. The select query also uses the financial year value from the form.

The select query uses the SQL:
SELECT FinancialMbr.FinMbrID, FinancialMbr.MemberID, IIf(IsNull([MembershipFee]),IIf([MbrTypeID]=1,[Fee],[CorpFee]),[MembershipFee]) AS FeeOwed, FinancialMbr.FinYrID, Member.MbrTypeID
FROM tluFinYr INNER JOIN (Member RIGHT JOIN FinancialMbr ON Member.MemberID = FinancialMbr.MemberID) ON tluFinYr.FinYrID = FinancialMbr.FinYrID
WHERE (((FinancialMbr.FinYrID)=[Me]![FinYrID]) AND ((Member.MbrTypeID)=1));


The code for the command button is as below. It fails at line 47. Appreciate any advice/pointers to what can/should be done:

Private Sub btnFinYrMbrRec_Click()

Dim AFRFManagement As DAO.Database
Dim qdf As DAO.QueryDef
Dim dtFinYrStartDate, dtFinYrEndDate As Date
Dim lngRowsAffected As Long
' Dim intMemberTypeID As Integer
Dim intFinYrID As Integer

Set AFRFManagement = CurrentDb

    dtFinYrStartDate = Me!FinYrStartDate
    dtFinYrEndDate = Me!FinYrEndDate
    intFinYrID = Me!FinYrID

Set qdf = CurrentDb.QueryDefs("qryMbr4FinYrUpdate")
    qdf!setFinYrID = intFinYrID
    qdf!FinYrEndDate = dtFinYrEndDate
    qdf!FinYrStartDate = dtFinYrStartDate


If IsNull(intFinYrID) Then
    MsgBox "No Financial Year ID"
    
    Else
     If IsNull(dtFinYrEndDate) Then
        MsgBox "No Financial Year End Date"
    
        Else
         If IsNull(dtFinYrStartDate) Then
               MsgBox "No Financial Year Start Date"
                
            Else
             If DCount("[FinYrID]", "FinancialMbr", "[FinYrID]=" & intFinYrID) > 0 Then
                MsgBox "Records already exist for the financial year - verify if the records are valid or remove them"

                Else
                qdf.Execute
                lngRowsAffected = AFRFManagement.RecordsAffected
                Set qdf = Nothing
                
                MsgBox "Completed - Financial Records of " & lngRowsAffected & " members for the selected Financial Year Added"
'                lngRowsAffected = 0 ' Reset counter
                
                'Now Update records with applicable fees (Membership Fee - Corporate or individual)
'                intMemberTypeID = 1     ' Members who are natural persons
                AFRFManagement.Execute " UPDATE FinancialMbr SET FinancialMbr.MembershipFee = [qryMbrFeePerson]![FeeOwed]" & _
                "WHERE(((FinancialMbr.FinMbrID) = [qryMbrFeePerson]![FinMbrID]))"
                lngRowsAffected = AFRFManagement.RecordsAffected


                MsgBox "Updated fees for " & lngRowsAffected & " Individual Members within the Financial Year"
'                lngRowsAffected = 0     ' Reset Counter
                
'                intMemberTypeID = 2      ' for corporate mambers
                AFRFManagement.Execute "UPDATE FinancialMbr SET FinancialMbr.MembershipFee = [qryMbrFeeOrg]![FeeOwed]" & _
                "WHERE (((FinancialMbr.FinMbrID)=[qryMbrFeePerson]![FinMbrID]))"
                MsgBox "Update fees for " & lngRowsAffected & " Corporate Members within the Financial Year"
'                lngRowsAffected = 0     ' Reset Counter
                
            End If
        End If
    End If
End If

End Sub

 

[theDBguy]

Hi. I just looked at your topic title, so I'm not sure if this will help, but maybe take a look at it anyway. Check out this Generic Recordset function. Hope it helps...

 

[GaP42]

Thank you tDBg - I have used the DAO snippet and modified the code:

Set rs = fDAOGenericRst("UPDATE FinancialMbr SET FinancialMbr.MembershipFee = [qryMbrFeePerson]![FeeOwed]" & _
                "WHERE(((FinancialMbr.FinMbrID) = [qryMbrFeePerson]![FinMbrID]))")

The error message that pops up in the generic function is :
Error 2482: The db cannot find the name 'qryMbrFeePerson' you entered in the expression
I presume I am referencing the select query incorrectly - not sure what to change.

Error pops up here in the generic code:

    For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next

 

[plog]

AFRFManagement.Execute " UPDATE FinancialMbr SET FinancialMbr.MembershipFee = [qryMbrFeePerson]![FeeOwed]" & _
                "WHERE(((FinancialMbr.FinMbrID) = [qryMbrFeePerson]![FinMbrID]))"

1. [qryMbrFeePerson]![FeeOwed] and [qryMbrFeePerson]![FinMbrID] aren't there. I assume you are trying to set it to whatever is in the FeeOwed field of qryMbrFeePerson query; but that's not how you do it. Here's how you do an update with a JOIN:

SQL UPDATE with JOIN - javatpoint

SQL update with join query with sql, tutorial, examples, insert, update, delete, select, join, database, table, join

www.javatpoint.com

2. There's no space prior to the WHERE so your field name gets jammed together with WHERE.

I suggest you build your UPDATE query SQL in a query object. Get it right there, then view the SQL and move it to your VBA.

 

[arnelgp]

Private Sub btnFinYrMbrRec_Click()

    Dim AFRFManagement As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim dtFinYrStartDate, dtFinYrEndDate As Date
    Dim lngRowsAffected As Long
    ' Dim intMemberTypeID As Integer
    Dim intFinYrID As Integer

    Set AFRFManagement = CurrentDb

    dtFinYrStartDate = Me!FinYrStartDate
    dtFinYrEndDate = Me!FinYrEndDate
    intFinYrID = Me!FinYrID

    Set qdf = AFRFManagement.QueryDefs("qryMbr4FinYrUpdate")


    If IsNull(intFinYrID) Then
        MsgBox "No Financial Year ID"
    
    Else
        If IsNull(dtFinYrEndDate) Then
            MsgBox "No Financial Year End Date"
    
        Else
            If IsNull(dtFinYrStartDate) Then
                MsgBox "No Financial Year Start Date"
                
            Else
                If DCount("[FinYrID]", "FinancialMbr", "[FinYrID]=" & intFinYrID) > 0 Then
                    MsgBox "Records already exist for the financial year - verify if the records are valid or remove them"

                Else
                    With qdf
                        .Parameters("setFinYrID").Value = intFinYrID
                        .Parameters("FinYrEndDate").Value = dtFinYrEndDate
                        .Parameters("FinYrStartDate").Value = dtFinYrStartDate
                        .Execute
                    End With
                    lngRowsAffected = AFRFManagement.RecordsAffected
                    Set qdf = Nothing
                
                    MsgBox "Completed - Financial Records of " & lngRowsAffected & " members for the selected Financial Year Added"
                    '                lngRowsAffected = 0 ' Reset counter
                
                    'Now Update records with applicable fees (Membership Fee - Corporate or individual)
                    '                intMemberTypeID = 1     ' Members who are natural persons
                    AFRFManagement.Execute " UPDATE FinancialMbr SET FinancialMbr.MembershipFee = [qryMbrFeePerson]![FeeOwed]" & _
                        "WHERE(((FinancialMbr.FinMbrID) = [qryMbrFeePerson]![FinMbrID]))"
                    lngRowsAffected = AFRFManagement.RecordsAffected


                    MsgBox "Updated fees for " & lngRowsAffected & " Individual Members within the Financial Year"
                    '                lngRowsAffected = 0     ' Reset Counter
                
                    '                intMemberTypeID = 2      ' for corporate mambers
                    AFRFManagement.Execute "UPDATE FinancialMbr SET FinancialMbr.MembershipFee = [qryMbrFeeOrg]![FeeOwed]" & _
                        "WHERE (((FinancialMbr.FinMbrID)=[qryMbrFeePerson]![FinMbrID]))"
                    MsgBox "Update fees for " & lngRowsAffected & " Corporate Members within the Financial Year"
                    '                lngRowsAffected = 0     ' Reset Counter
                
                End If
            End If
        End If
    End If

End Sub

 

[GaP42]

Thanks plog

Using the DAO Recordset suggestion from tDBg, and the review of the SQL as you suggested (thanks for picking up the space (just an amateur here)), it is now

                Set rs = fDAOGenericRst(" UPDATE FinancialMbr " & _
                " SET FinancialMbr.MembershipFee = qryMbrFeePerson![FeeOwed] " & _
                " FROM FinancialMbr FM " & _
                " INNER Join qryMbrFeePerson Q1 " & _
                " ON FM.FinMbrID = Q1.FinMbrID " & _
                " WHERE Q1.MbrTypeID = 1 ")

However trying the SQL in the MS ACCESS query design ie: UPDATE FinancialMbr SET FinancialMbr.MembershipFee = qryMbrFeePerson.FeeOwed FROM FinancialMbr FM INNER Join qryMbrFeePerson Q1 ON FM.FinMbrID = Q1.FinMbrID WHERE Q1.MbrTypeID = 1
I get a syntax error - missing operator in query expression 'qryMbrFeePerson.FeeOwed FROM FinancialMbr FM INNER Join qryMbrFeePerson Q1 ON FM.FinMbrID

And the DAO function now reports an error at For Each prm In qdf.Parameters
Runtime Error 91 Object Variable or With Block variable not set.

 

[GaP42]

With qdf
.Parameters("setFinYrID").Value = intFinYrID
.Parameters("FinYrEndDate").Value = dtFinYrEndDate
.Parameters("FinYrStartDate").Value = dtFinYrStartDate
.Execute
End With
[/CODE]

Thanks Arnelgp
While setting of the parameters for the update query are set here, it is the setting of a parameter in the select query that is called seems to be the problem - ie in the 2 queries: qryMbrFeePerson and qry MbrFeeOrg which have the structure:

SELECT FinancialMbr.FinMbrID, FinancialMbr.MemberID, IIf(IsNull([MembershipFee]),IIf([MbrTypeID]=1,[Fee],[CorpFee]),[MembershipFee]) AS FeeOwed, FinancialMbr.FinYrID, Member.MbrTypeID
FROM tluFinYr INNER JOIN (Member RIGHT JOIN FinancialMbr ON Member.MemberID = FinancialMbr.MemberID) ON tluFinYr.FinYrID = FinancialMbr.FinYrID
WHERE (((FinancialMbr.FinYrID)=[Me]![FinYrID]) AND ((Member.MbrTypeID)=1));

[Me]![FinYrID] is a value on the current form from which the update query is launched, but this a subquery - does that cause a problem? If so how do I pass this value down to the subquery?

Thanks

 

[arnelgp]

you cant use Me in the query, use Forms!Formname!FinYrID.

 

[GaP42]

Thanks arnelgp for the correction to the select query....
I still have the problem referred to above about the runtime error 91 reported from the DAO code snippet from tDBg - not sure whether to revert or continue with it at this stage.

 

[arnelgp]

you can always upload your db for quick analysis.

 

[GaP42]

Here is a cut down copy containing the form, queries, tables with sample data and the module/function called by the code underlying the command button to create the financial records for the beginning of a financial year. Re operation: the financialmbr table holds records for a number of financial years. I have removed 2021/22 records so these are ready to generate and are generated when the button is clicked on the only form provided in the app. The error 91 is generated on the first update query - by Module1. Delete the records for FinYrID = 4 in the financialmbr table to regenerate these records again.

 

[arnelgp]

i did not use query, qryMbr4FinYrUpdate.
instead i use "manually query" (see code).
added query qryUpdatePersonFee to update the fee for "person" member.
added query qryUpdateOrgFee to update the fee for organization.

see all code in the form, i did not use the code in the Module.
always zip your attachment so it is fast to download.

 

[GaP42]

i did not use query, qryMbr4FinYrUpdate.
instead i use "manually query" (see code).
added query qryUpdatePersonFee to update the fee for "person" member.
added query qryUpdateOrgFee to update the fee for organization.

see all code in the form, i did not use the code in the Module.
always zip your attachment so it is fast to download.

Thank you ... looks good .. solves my problem, and thanks to the others who contributed. A little tweaking to do.