form-based parameters, paste output to Excel - "too few parameters"

[Lifeseeker]

Hi there,

What I would like to do:

1) have users fill in dates in this form in Access.
2) click on a button which takes the parameters and feed into a query
3) have access extract the output of the query into an excel file.

Issues I'm getting is that when I run the code, Access keeps saying "too few parameters 2 expected".

Can anyone help? I have attached the Access and Excel file. As you can see, I'm simply trying to take the output from Access and paste it directly into "data" sheet at A1, with column headers.

Thank you

 

[pbaldy]

I suspect you're running into this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209203

 

[Lifeseeker]

I suspect you're running into this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209203

Error I have is on this line:

 Set rs = CurrentDb.OpenRecordset("Select * from qry_combine")

error code is actually "too few parameters. expected 2", not 1

How do I go about fixing this? The example I found on the website....there is a set of code used to set the value of a parameter and there is another set of code used to create a query to set the value of a parameter. I am a bit lost as to where to get started fixing.

 

[pbaldy]

Same issue, you just have 2 parameters in your query rather than the 1 in the example. Here's are the relevant lines from a db I happen to have open:

  Set qdfStatement = db.QueryDefs(strQuery)
  qdfStatement![Forms!frmMainMenu!txtDate] = Forms![frmMainMenu]![txtDate]
  qdfStatement![Forms!frmMainMenu!txtEndDate] = Forms![frmMainMenu]![txtEndDate]

  Set rs = qdfStatement.OpenRecordset

  xlSheet.range("A9").CopyFromRecordset rs

 

[Lifeseeker]

Same issue, you just have 2 parameters in your query rather than the 1 in the example. Here's are the relevant lines from a db I happen to have open:

  Set qdfStatement = db.QueryDefs(strQuery)
  qdfStatement![Forms!frmMainMenu!txtDate] = Forms![frmMainMenu]![txtDate]
  qdfStatement![Forms!frmMainMenu!txtEndDate] = Forms![frmMainMenu]![txtEndDate]

  Set rs = qdfStatement.OpenRecordset

  xlSheet.range("A9").CopyFromRecordset rs

Do I need to define what "strQuery" is in that bracket?

 

[pbaldy]

Yes, you could replace strQuery with your query name in quotes. In my case, the query could vary so that variable was set earlier in the code.

 

[Lifeseeker]

Yes, you could replace strQuery with your query name in quotes. In my case, the query could vary so that variable was set earlier in the code.

I attached a new one, but now I'm getting a new error saying "items not found in the collection" on this line...

Set qdfsample = dbsample.QueryDefs("select * from qry_combine")

 

[pbaldy]

I said the query name. Try

Set qdfsample = dbsample.QueryDefs("qry_combine")

 

[Lifeseeker]

I said the query name. Try

Set qdfsample = dbsample.QueryDefs("qry_combine")

worked!! Thank you

 

[Lifeseeker]

I said the query name. Try

Set qdfsample = dbsample.QueryDefs("qry_combine")

Just trying to understand this bit further...

Dim intcolIndex As Integer
    Dim rs As Recordset
    Dim dbsample As Database
    Dim qdfsample As QueryDef
    Set dbsample = CurrentDb()
    Set qdfsample = dbsample.QueryDefs("qry_combine")
    
    'set the value of the parameter that references the form
    qdfsample![forms!frm_parameters!txt_from] = Forms![frm_parameters]![txt_From]
    qdfsample![forms!frm_parameters!txt_thru] = Forms![frm_parameters]![txt_Thru]
    Set rs = qdfsample.OpenRecordset

the part where I am not fully understanding is the

qdfsample![forms!frm_parameters!txt_from] = Forms![frm_parameters]![txt_From]
    qdfsample![forms!frm_parameters!txt_thru] = Forms![frm_parameters]![txt_Thru]

It seems that we are passing the form-based parameters into the query? that sounds about right?

 

[pbaldy]

Yes. When run normally a query knows how to get the form values. From OpenRecordset it does not, so this method overcomes that limitation.

 

[Lifeseeker]

Yes. When run normally a query knows how to get the form values. From OpenRecordset it does not, so this method overcomes that limitation.

this is a bit abstract.

in here...

qdfsample![forms!frm_parameters!txt_from]

Why is there only one big bracket rather than individual smaller bracket like this...forms![frm_parameters]![txt_from] for the left hand side of the equation?