MS Access OpenRecordset parameters and too few parameters issue (1 Viewer)

pke8jt

Registered User.
Local time
Today, 16:17
Joined
Oct 30, 2017
Messages
27
I'm certain that I have enough parameters, fields declaration to pass values to OpenRecordSet method but I am still stuck.

General explanation:

  1. Users input start date and end date on a form that filters my query 2_Total (single value) e.g. 154,21
  2. Run the VBA function that exports the query to an excel file

Problem
Too few parameters. Expected 4 error on Set rst = qry.OpenRecordset(dbOpenDynaset)

SQL
PARAMETERS [BeginDate] DateTime, [EndDate] DateTime;
SELECT Sum(dbo_SO_SalesHistory.DollarsSold) AS SumOfDollarsSold
FROM dbo_SO_SalesHistory
WHERE (((dbo_SO_SalesHistory.InvoiceDate) Between [Forms]![RUN]![textBeginOrderDate] And [Forms]![RUN]![textendorderdate]));

VBA


Option Compare Database

Option Explicit
Public Function Trans2()

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim acRng As Variant
Dim xlRow As Integer

Dim db As DAO.Database
Dim qry As QueryDef
Dim rst As Recordset
Dim prm As DAO.Parameter
Dim strSQL As String

Set db = CurrentDb
Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Open("C:\Users\J\Desktop\August 2017.xlsx")
Set xlWS = xlWB.Worksheets("Totals")

xlRow = (xlWS.Columns("K").End(xlDown).Row)

Set qry = db.QueryDefs("2_Total")

qry.Parameters("BeginDate").Value = [Forms]![Run]![textBeginOrderDate]
qry.Parameters("EndDate").Value = [Forms]![Run]![textendorderdate]

Set rst = qry.OpenRecordset(dbOpenDynaset)

Dim c As Integer
c = 11 'C is the one that stores column number, in which c=1 means column A, 11 is for column K, 12 for Column L
xlRow = xlRow + 11

Do Until rst.EOF
For Each acRng In rst.Fields
xlWS.Cells(xlRow, c).Formula = acRng
c = c + 1
Next acRng
xlRow = xlRow + 1
c = 1
rst.MoveNext
If xlRow > 25 Then GoTo rq_Exit
Loop


rq_Exit:
rst.Close
Set rst = Nothing
Set xlWS = Nothing
xlWB.Close acSaveYes
Set xlWB = Nothing
xlApp.Quit
Set xlApp = Nothing
Exit Function

End Function
 

Attachments

  • correct.JPG
    correct.JPG
    13.2 KB · Views: 511
  • FIELD.JPG
    FIELD.JPG
    20.1 KB · Views: 473

Ranman256

Well-known member
Local time
Today, 18:17
Joined
Apr 9, 2015
Messages
4,339
if you are using a crosstab query , you MUST enter parameters into the query.
but since you are using CODE instead of a query
you must assign params via code.
Code:
set qdf = currentdb.querydefs("myQuery")
qdf.parameters(0) = vStartDate
qdf.parameters(1) = vEndDate

set rst = qdf.openrecordset

(use queries instead)
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:17
Joined
Sep 21, 2011
Messages
14,048
As you are setting the paremeters from the form controls shouldn't you be checking those parameters?

Code:
WHERE dbo_SO_SalesHistory.InvoiceDate Between [BeginDate] And [EndDate];
 

pke8jt

Registered User.
Local time
Today, 16:17
Joined
Oct 30, 2017
Messages
27
Thank you for your response, Ranman.

As you stated qdf.parameters(0) & (1) in your example,

I do have "BeginDate" & "EndDate" to assign parameters via code.
qry.Parameters("BeginDate").Value = [Forms]![Run]![textBeginOrderDate]
qry.Parameters("EndDate").Value = [Forms]![Run]![textendorderdate]

Are you saying that the parameters should be numerical instead of string? Sorry If I didn't understand you quite well.
 

pke8jt

Registered User.
Local time
Today, 16:17
Joined
Oct 30, 2017
Messages
27
Thank you so much, Gasman. I was struggling with this for days and you solved it like a pro. Thank you!! Hope you have a great day!
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:17
Joined
Sep 21, 2011
Messages
14,048
You are welcome, lucky guess :D

What I would be curious of though is...
If the parameters can be set from the form controls, in your code then the form must be open, and if so, why cannot the query use the form controls just as easy.?
 

Ranman256

Well-known member
Local time
Today, 18:17
Joined
Apr 9, 2015
Messages
4,339
params fields can be numeric,
or literal field names.
 

suzeg

Registered User.
Local time
Today, 15:17
Joined
Jun 20, 2012
Messages
27
Hello
I am struggling with this code. Novice here.
I am trying to run a query from input from a form and then send emails from that query.
I am totally lost. I viewed a lot of code but just cannot get it right.

Any help would be greatly appreciated.


Code:
Dim MyDb As DAO.Database
''Dim rsEmail As DAO.Recordset
Dim rst As Recordset
Dim qdf As Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String
Dim qryName As String

 
Set MyDb = CurrentDb()
''Set rsEmail = MyDb.OpenRecordset("qryTOE-mailStudentsGrades", dbOpenSnapshot)
Set qdf = CurrentDb.QueryDefs("qryTOE-mailStudentsGrades")
qdf.Parameters(0) = [Forms]![Queries Dialog]![StartDateInput]
qdf.Parameters(1) = [Forms]![Queries Dialog]![EndDateInput]
qdf.Parameters(2) = [Forms]![Queries Dialog]![CourseIdInput]

Set rst = qdf.OpenRecordset
 
 
With rsEmail
        .MoveFirst
        Do Until rsEmail.EOF
            If IsNull(.Fields(2)) = False Then
                sToName = .Fields(1)
                sSubject = "Grade: " & .Fields(6)
                sMessageBody = "Your Grades for Course " & vbCrLf & _
                    "Grade: " & .Fields(6) & vbCrLf & _
                    "Instructor: " & .Fields(7) & vbCrLf & _
                    "Start Date: " & .Fields(4)
 
                DoCmd.SendObject acSendNoObject, , , _
                    sToName, , , sSubject, sMessageBody, False, False
            End If
            .MoveNext
        Loop
End With
 
Set MyDb = Nothing
Set rsEmail = Nothing

End Sub
 

Users who are viewing this thread

Top Bottom