Too few parameters (Access 2k, win 2k)

Topic

New Reply

Dear All

I’m trying to get my database to do a little automatic emailing of data, why just store it if you can spread it around??

Any way I’m getting an error message on the code I’m using: –

Error message = Run Time error ‘3062’ Too few parameters. Expected 2.

My Access help returns a grey screen……

Here’s the code, up to the line after the one the de###### highlights. I’ve checked and triple checked the spelling of the query I’m refering to.

Private Sub SaveRecBtn_Click()
‘On Error GoTo Err_saverecbtn_Click (removed to activate the de###### rather than just the error message)

DoCmd.SetWarnings False

Dim DbS As DAO.Database
Dim EmailAddys As DAO.Recordset
Dim VendorEmailAddys As DAO.Recordset
Dim strEmailAddys As String
Dim strVendorEmailAddys As String
Dim Response As String
‘Dim RejectNote As Report

Set DbS = CurrentDb
Set EmailAddys = DbS.OpenRecordset(“InternalEmailAddyQry”) This is the line the de###### highlights when the code stops.
Set VendorEmailAddys = DbS.OpenRecordset(“EmailContactDataQry”)

I’ve tried the instruction after the DbS.OpenRecordset with the brackets, withot the brackets, with the speech marks etc, even tried a few variations on the ‘template’ supplied by VBA, but to no avail.

It’s going to be something simple I’ve missed, always is, but I can’t find it from any of the available resources I have. Sorry

Thanks for the help

Ian

Reply | Quote

Replies

Does your query expect a parameter? That error message usually indicates the query engine didn’t get the expected number of parameters – one in a long while it’s something else, but that’s the most common situation.

Reply | Quote

Does your query expect a parameter? That error message usually indicates the query engine didn’t get the expected number of parameters – one in a long while it’s something else, but that’s the most common situation.

Reply | Quote

Is your query a parameter query, for example with criteria of the form [Enter Year] or [Forms]![frmMyForm]![txtYear]? DAO can’t handle those, but there are ways to to pass the parameters.

Reply | Quote

Hans

Doh, Doh, Doh!!!!! Yes, it is a parameter query, worse still you’ve explained this to me before and I’d forgotten even asking the question, it was a different question, but the answer you gave will sort out my problem.

Note to self, think laterally more often…..

Thanks

Ian

Reply | Quote

OK, I’ve followed the advice that was offered about Parameter Queries, I now have the following code: –

Private Sub SaveRecBtn_Click()
‘On Error GoTo Err_saverecbtn_Click

DoCmd.SetWarnings False

Dim DbS As DAO.Database
Dim EmailAddys As DAO.Recordset
Dim VendorEmailAddys As DAO.Recordset
Dim strEmailAddys As String
Dim strVendorEmailAddys As String
Dim Response As String
Dim RejectNote As Report
Dim strEmailQry As String

Set DbS = CurrentDb
Set EmailAddys = DbS.OpenRecordset(“InternalEmailAddyQry”)
EmailAddys.Parameters(“[Forms]![PrimaryDataTableInputFrm]![LineNumber]”) = Me.LineNumber This line is highlighted by the de######.
EmailAddys.Parameters(“[Forms]![PrimaryDataTableInputFrm]![OrdNumber]”) = Me.OrdNumber
Set VendorEmailAddys = DbS.OpenRecordset(“EmailContactDataQry”)

I get the message “Method or Data Member not found” at the commented line. I’m guessing this is a missing reference, if so what reference should I add, I’ve already got the DAO 3.6 reference in my list.

Alternately, what have I missed in the code? I assume I’m OK with the Me.LineNumber as I’ve tried the full [forms]![formname]![itemname] string and that didn’t help.

I also tried going the SQL route, but I think the query is a bit complex for using in VBA, it just turned red and the de###### played up over every line of it. The query reads data from two tables with a right join to get the relevant bits of information.

Thanks

Ian

Reply | Quote

You should use a QueryDef object to open the recordset. In the declaration part, add:

Dim qdf As DAO.QueryDef

And in the body of the code:

Set qdf = dbs.QueryDefs(“InternalEmailAddyQry”)
‘ First, set the parameters
qdf.Parameters(“[Forms]![PrimaryDataTableInputFrm]![LineNumber]”) = Me.LineNumber This line is highlighted by the de######.
qdf.Parameters(“[Forms]![PrimaryDataTableInputFrm]![OrdNumber]”) = Me.OrdNumber
‘ And only then open the recordset
Set EmailAddys = qdf.OpenRecordset

For this to work, you must have declared the parameters explicitly in the design of the InternalEmailAddyQry query:
– Select Query | Parameters.
– Enter [Forms]![PrimaryDataTableInputFrm]![LineNumber] under Parameter, and select the appropriate Data Type.
– Similar for [Forms]![PrimaryDataTableInputFrm]![OrdNumber]

Reply | Quote

You should use a QueryDef object to open the recordset. In the declaration part, add:

Dim qdf As DAO.QueryDef

And in the body of the code:

Set qdf = dbs.QueryDefs(“InternalEmailAddyQry”)
‘ First, set the parameters
qdf.Parameters(“[Forms]![PrimaryDataTableInputFrm]![LineNumber]”) = Me.LineNumber This line is highlighted by the de######.
qdf.Parameters(“[Forms]![PrimaryDataTableInputFrm]![OrdNumber]”) = Me.OrdNumber
‘ And only then open the recordset
Set EmailAddys = qdf.OpenRecordset

For this to work, you must have declared the parameters explicitly in the design of the InternalEmailAddyQry query:
– Select Query | Parameters.
– Enter [Forms]![PrimaryDataTableInputFrm]![LineNumber] under Parameter, and select the appropriate Data Type.
– Similar for [Forms]![PrimaryDataTableInputFrm]![OrdNumber]

Reply | Quote

OK, I’ve followed the advice that was offered about Parameter Queries, I now have the following code: –

Private Sub SaveRecBtn_Click()
‘On Error GoTo Err_saverecbtn_Click

DoCmd.SetWarnings False

Dim DbS As DAO.Database
Dim EmailAddys As DAO.Recordset
Dim VendorEmailAddys As DAO.Recordset
Dim strEmailAddys As String
Dim strVendorEmailAddys As String
Dim Response As String
Dim RejectNote As Report
Dim strEmailQry As String

Set DbS = CurrentDb
Set EmailAddys = DbS.OpenRecordset(“InternalEmailAddyQry”)
EmailAddys.Parameters(“[Forms]![PrimaryDataTableInputFrm]![LineNumber]”) = Me.LineNumber This line is highlighted by the de######.
EmailAddys.Parameters(“[Forms]![PrimaryDataTableInputFrm]![OrdNumber]”) = Me.OrdNumber
Set VendorEmailAddys = DbS.OpenRecordset(“EmailContactDataQry”)

I get the message “Method or Data Member not found” at the commented line. I’m guessing this is a missing reference, if so what reference should I add, I’ve already got the DAO 3.6 reference in my list.

Alternately, what have I missed in the code? I assume I’m OK with the Me.LineNumber as I’ve tried the full [forms]![formname]![itemname] string and that didn’t help.

I also tried going the SQL route, but I think the query is a bit complex for using in VBA, it just turned red and the de###### played up over every line of it. The query reads data from two tables with a right join to get the relevant bits of information.

Thanks

Ian

Reply | Quote

Hans

Doh, Doh, Doh!!!!! Yes, it is a parameter query, worse still you’ve explained this to me before and I’d forgotten even asking the question, it was a different question, but the answer you gave will sort out my problem.

Note to self, think laterally more often…..

Thanks

Ian

Reply | Quote