Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-30-2017, 07:31 AM   #1
pke8jt
Newly Registered User
 
Join Date: Oct 2017
Location: colorado
Posts: 27
Thanks: 17
Thanked 0 Times in 0 Posts
pke8jt is on a distinguished road
Smile MS Access OpenRecordset parameters and too few parameters issue

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
Attached Images
File Type: jpg correct.JPG (13.2 KB, 84 views)
File Type: jpg FIELD.JPG (20.1 KB, 77 views)

pke8jt is offline   Reply With Quote
Old 10-30-2017, 07:53 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 2,995
Thanks: 0
Thanked 657 Times in 642 Posts
Ranman256 will become famous soon enough
Re: MS Access OpenRecordset parameters and too few parameters issue

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)
Ranman256 is offline   Reply With Quote
Old 10-30-2017, 07:56 AM   #3
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,134
Thanks: 268
Thanked 317 Times in 302 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: MS Access OpenRecordset parameters and too few parameters issue

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];

__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
pke8jt (10-30-2017)
Old 10-30-2017, 07:57 AM   #4
pke8jt
Newly Registered User
 
Join Date: Oct 2017
Location: colorado
Posts: 27
Thanks: 17
Thanked 0 Times in 0 Posts
pke8jt is on a distinguished road
Re: MS Access OpenRecordset parameters and too few parameters issue

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 is offline   Reply With Quote
Old 10-30-2017, 08:02 AM   #5
pke8jt
Newly Registered User
 
Join Date: Oct 2017
Location: colorado
Posts: 27
Thanks: 17
Thanked 0 Times in 0 Posts
pke8jt is on a distinguished road
Re: MS Access OpenRecordset parameters and too few parameters issue

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!
pke8jt is offline   Reply With Quote
Old 10-30-2017, 08:30 AM   #6
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,134
Thanks: 268
Thanked 317 Times in 302 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: MS Access OpenRecordset parameters and too few parameters issue

You are welcome, lucky guess

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.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 10-30-2017, 09:53 AM   #7
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 2,995
Thanks: 0
Thanked 657 Times in 642 Posts
Ranman256 will become famous soon enough
Re: MS Access OpenRecordset parameters and too few parameters issue

params fields can be numeric,
or literal field names.

Ranman256 is offline   Reply With Quote
Old 11-06-2017, 09:46 AM   #8
suzeg
Newly Registered User
 
Join Date: Jun 2012
Posts: 27
Thanks: 8
Thanked 0 Times in 0 Posts
suzeg is on a distinguished road
Re: MS Access OpenRecordset parameters and too few parameters issue

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

suzeg is offline   Reply With Quote
Reply

Tags
access , openrecordset , query , sql , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
db.Openrecordset(query) - too few parameters evermore Modules & VBA 13 02-26-2009 03:06 PM
How to set query parameters for use in OpenRecordset Jaco K Queries 1 09-13-2006 07:58 AM
Passing parameters to an Openrecordset freemind Queries 2 01-26-2005 11:32 PM
Passing Parameters to an openrecordset freemind Queries 2 01-26-2005 11:31 PM
Too few parameters in openrecordset line Roni Sutton Modules & VBA 1 08-31-2000 10:07 AM




All times are GMT -8. The time now is 08:09 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World