Runtime error 3061, holding my VBA back...

[LOUISBUHAGIAR54]

I have a code which runs as follows. Basically it opens up a Report containg invoices. The user is prompted to give a year so that only those invoices for that year come up for reporting.

Private Sub Report_Open(Cancel As Integer)

Dim StrSQL As String

StrSQL = " SELECT Particulars.[First Name], Particulars.[Second Name], Particulars.[idcard no], invoicelog.Invoice_no, invoicelog.invoiceamount, invoicelog.paid, invoicelog.[date of issue], Year([date of issue]) AS Expr1" _
& " FROM Particulars INNER JOIN invoicelog ON Particulars.[idcard no] = invoicelog.resident_id" _
& " WHERE (((invoicelog.paid) = Yes) And ((Year([date of issue])) = [Please enter year under study]))" _
& " ORDER BY Particulars.[Second Name];"

Dim sena As Variant
Dim mydb As Database
Dim rst1 As DAO.Recordset
Set mydb = CurrentDb()
Set rst1 = mydb.OpenRecordset(StrSQL)
If rst1.RecordCount = 0 Then Exit Sub
rst1.MoveFirst
sena = rst1![date of issue]
Me.Label20.Caption = "Paid invoices for year " & Year(sena)
Me.recordsource = StrSQL
End Sub



However when the code reaches

Set rst1 = mydb.OpenRecordset(StrSQL)

it stops and displays error 3061 saying
'Too few parameters. Expected 1"

Can someone please explain this error and how it can be corrected ?

Many thanks for your assistance.


LouisB.

 

[vbaInet]

The Recordset property is not applicable to a report.

If you want to filter the records in your report, use the following method (substituting OpenForm for OpenReport):

http://baldyweb.com/wherecondition.htm

 

[LOUISBUHAGIAR54]

I have made some finding out and found that the code probably breaks down like this as the SQL statement contains a parameter field requiring user entry.

The whole point of this code was to pick all invoices of a certain year and report on them. In addition the code has to find out the year of study and change a label on the report accordingly.

For any readers that may find a similar difficulty I changed to code to the following and this is now working well. I managed to bypass certain parts of the code that were giving trouble.

The code is the following;

Private Sub Report_Open(Cancel As Integer)
Dim StrSQL As String
StrSQL = " SELECT Particulars.[First Name], Particulars.[Second Name], Particulars.[idcard no], invoicelog.Invoice_no, invoicelog.invoiceamount, invoicelog.paid, invoicelog.[date of issue], Year([date of issue]) AS YYear" _
& " FROM Particulars INNER JOIN invoicelog ON Particulars.[idcard no] = invoicelog.resident_id" _
& " WHERE ((invoicelog.paid) = Yes)" _
& " ORDER BY Particulars.[Second Name];"

Dim xisena As Integer
xisena = InputBox("Please indicate the year to audit!", "Year to audit.", , 8000, 6000)
Me.Label20.Caption = "Paid invoices for year " & xisena
Me.RecordSource = StrSQL
Me.Filter = "[YYear] = " & xisena
Me.FilterOn = True
End Sub

 

[vbaInet]

I have made some finding out and found that the code probably breaks down like this as the SQL statement contains a parameter field requiring user entry.

No! Like I said before, a report doesn't return a recordset that's why it breaks. It only works in ADP's.

Seriously, what is the point of building the SQL in code when you can simply have it set already in the Record Source and pass any parameters via the WHERE clause of the OpenReport method?

 

[LOUISBUHAGIAR54]

Many thanks for your reply.

I have learnt the following from this issue.

If I remove the part of the SQL statement where the parameter value needs to be added, the code works and the report opens. So I do not understand why you say that the report does not return a recordset.

What are ADP's ?

I appreciate your bit about putting the SQL statement in the record source of the report and passing parameters via the WHERE clause of the OpenReport method, but I would like also to get the year of the entries of the invoices so that I can alter a Label on the report accordingly.

As I said the code above is working as I planned. If you have any better ways to do it sincerely I would like to learn how.

Many thanks again for your help.

LouisB

 

[vbaInet]

Oh my bad. I thought you were returning the recordset from the report, something like this:

set rst = Me.Recordset

I obviously didn't pay attention to your code so my apologies.

The InputBox is a separate piece of code from your open report routine. You can get all the required parameters before calling the OpenReport command.

 

[LOUISBUHAGIAR54]

I do appreciate that you are an expert in access and I have benefitted from some points in this post.

Many thanks for your reply.

LouisB