Recordset Too few parameters expected 1

[Gina Maylone]

I'm trying to open a recordset to loop through and apply payments. I'm getting the too few parameter error at the bold red line below. The query runs fine on its own. Thanks in advance.

Private Sub Command52_Click()
    DoCmd.SetWarnings False
 
    Dim strsql As String
    Dim BD As Variant
    Dim BL As Variant
    Dim CR As Variant
    Dim db As Variant
    Dim tb As Variant
    Dim P As Variant
    Dim mbrid As Integer
    Dim dp As Date
    Dim cn As Variant
    Dim dbs As Database
    Dim rst As Recordset
    Dim rssql As String
 
    dp = DatePaid
    mbrid = Me.MemberID
    BD = Me.baldue
    BL = Me.balleft
    CR = Me.CRAmount
    db = Me.DBAmount
    tb = Me.totalbal
    P = Me.payment
    cn = Me.CheckNum
    rssql = "SELECT [dbamount]-Nz([cramount],0) AS baldue, Accounts.DBAmount, Accounts.CRAmount, Accounts.AsmtType, Accounts.Details, Accounts.LotNumber, Accounts.EnteredBy, Accounts.DatePaid, Accounts.CheckNumber, Accounts.Note " & vbCrLf & _
"FROM Accounts " & vbCrLf & _
"WHERE (((Accounts.MemberID)=[Forms]![AccountsandPayments]![MemberID])) " & vbCrLf & _
"ORDER BY Accounts.DateAssessed;"
 
 
    'Add to payments
    strsql = "INSERT INTO AsmtPayments (PaymentAmount, MemberID, PaymentDate,checknum )"
    strsql = strsql & " VALUES (" & P & ", " & mbrid & "," & cn & ", #" & dp & "#);"
    CurrentDb.Execute strsql, dbFailOnError
 
 
    DoCmd.SetWarnings True
    Requery
 
 
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(rssql, dbOpenDynaset) 

etc...etc...

[CJ_London]

you don't need the

& vbCrLf &

when building a sql string

but I think the reason for your error is this

"WHERE (((Accounts.MemberID)=[Forms]![AccountsandPayments]![MemberID])) "

should be

"WHERE (((Accounts.MemberID)=" & [Forms]![AccountsandPayments]![MemberID] & ")) "

[isladogs]

You have the values and field names in a different order in strsql statement so Access can't use them

[Gina Maylone]

Thanks Ridders, I caught that and rearranged as necessary. :-)

[Gina Maylone]

I am now getting a syntax error. "Syntax error in insert into statement." When I debug, the values are correct.

Private Sub Command52_Click()
    DoCmd.SetWarnings False
    Dim strsql As String
    Dim BD As Variant
    Dim BL As Variant
    Dim CR As Variant
    Dim db As Variant
    Dim tb As Variant
    Dim P As Variant
    Dim mbrid As Integer
    Dim dp As Date
    Dim pd As Date
    Dim cn As Variant
    Dim dbs As Database
    Dim rst As Recordset
    Dim rssql As String
    
    
    dp = DatePaid
    mbrid = Me.MemberID
    BD = Me.baldue
    BL = Me.balleft
    CR = Me.CRAmount
    db = Me.DBAmount
    tb = Me.totalbal
    P = Me.payment
    cn = Me.CheckNum
    
    
'set the recordset sql
rssql = "SELECT Accounts.DBAmount, Accounts.CRAmount, Accounts.DateAssessed, Accounts.AsmtType, Accounts.Details, Accounts.LotNumber, Accounts.EnteredBy, Accounts.DatePaid, Accounts.CheckNumber " & vbCrLf & _
"FROM Accounts " & vbCrLf & _
"WHERE (((Accounts.MemberID)=" & [Forms]![AccountsandPayments]![MemberID] & ")) "
Debug.Print rssql

    'Add to payments
    strsql = "INSERT INTO AsmtPayments (PaymentAmount, MemberID, PaymentDate, checknum )"
    strsql = strsql & "  VALUES (" & P & ", " & mbrid & ", #" & dp & "#," & cn & ""
    
CurrentDb.Execute strsql, dbFailOnError (Errors here)
    DoCmd.SetWarnings True
    Requery

etc etc

Correct Debug Results:
P = 94.07
mbrid = 1
dp = 4/6/2018
cn = 57

Additional information: The payment, date paid and check number are collected from unbound fields on the subform. Don't know if this matters or not. If I attempt to run an append query using the data in the unbound fields I get greek characters "긔葺䁗" in payment amount but it does append correctly to the asmt payments table.

I know I've been looking at this for too long. TIA.

[Gina Maylone]

Not sure what I did, messed around with quotes and stuff, and no longer getting an error.

[Micron]

you are missing the ending bracket
strsql = strsql & " VALUES (" & P & ", " & mbrid & ", #" & dp & "#," & cn & "" )

Tip for the future: output sql to immediate window before executing. That way, all variables are 'converted'. Copy/paste to new query and switch to datasheet view (so that if it's all good, and it's an action query, the query doesn't actually run). If there are any syntax issues, most of the time Access will highlight them when you attempt to switch views.

[Gina Maylone]

Thanks Micron! I'll give it a shot!

[Micron]

Enjoy! I should have said all variables are converted IF reference correctly. Goes for control references too.