Help with Export to Excel (1 Viewer)

[lookforsmt]

Hi! all

I have form linked to a table which has tables/queries listed in it. Its on-click event exports the data in excel format. No problem here.
The issue is when the query has parameter with date prompt the on-click event fails. Below is the error msg.

"Run-time error 3061"
Too few parameters, expected 2

VBA code error
Set rsSrc = db.OpenRecordset(ThisTable)

Instead of date prompt in query, if the date (start & end) is inputted on the export form and export code returns the queries and then exports the data to excel.

any suggestions. I am uploading my db.

 

[Gasman]

I can't open it as my version is too old.?
Easiest way would be to refer to the date form controls in any query that needs the dates.?

Another way might be to set TempVars from any form and refer to the TempVars in the query.?

HTH

 

[theDBguy]

Hi. Take a look at Leigh's Generic Recordsets. Hope it helps...

 

[vba_php]

I can't open it as my version is too old.?

same here...I get a "unrecognized database format" error. this is one thing that MS has seriously screwed up. I've posted many times about this annoyance.

 

[lookforsmt]

Thanks everyone. I have attached lower version db. Hope this helps

 

[theDBguy]

Hi. Did you try the link I posted? Just curious...

 

[Gasman]

Well it would not work as I thought it would

Not with Form controls being referenced or TempVars.:banghead:

Got it working with this

Also had to set the references, so just copy the code if you use it.

HTH

Edit: Set qdf to Nothing at the end of the code if you use it.
Also look at qry_Master for the parameters.

 

[lookforsmt]

Apologies to all, couldn't respond earlier.

HI! theDBguy, i have visited you site. amazing work, honesty, i have not taken the effort to understand how i could apply the code in my project.
long way to go for me to understand. Thanks again for sharing the link, it will help me in my future projects.

Thanks Gasman for the solution code.
The code works perfect for me. I had to put the start & end date parameter in all queries to get all exported to excel. Otherwise get an run-time error 3265. "Item not found in this collection"

Thanks once again for your help. It has saved lot of my time in cleaning the unwanted data from the excel.

I will close the thread as solved.

 

[theDBguy]

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[Gasman]

I've looked a little more into this and that apparently is the best solution if using a recordset.?
If just a normal query I believe you can just hard code form controls or tempvars as the criteria. I know I have used TempVars in the past for exactly that.

The other way is to build the sql on the fly and concatenate the form control values with the sql string.?

 

[theDBguy]

I've looked a little more into this and that apparently is the best solution if using a recordset.?
If just a normal query I believe you can just hard code form controls or tempvars as the criteria. I know I have used TempVars in the past for exactly that.

The other way is to build the sql on the fly and concatenate the form control values with the sql string.?

Hi Gasman. Just curious what is your impression of Leigh's functions?

 

[Gasman]

Hi Gasman. Just curious what is your impression of Leigh's functions?

Who is Leigh ?

Oh, I see now. TBH I just had a quick peek and left it at that.?

I initially thought a simple reference to form controls or tempvars would do the trick, but was very wrong.

 

[Gasman]

I've seen this code a few times when researching last night, but not sure what it is doing?

For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
Next

 

[theDBguy]

I've seen this code a few times when researching last night, but not sure what it is doing?

For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
Next

Hi. The point of Leigh's function was to replace something like this:

Set rs = db.OpenRecordset("QueryName")

with this

Set rs = fDAOGenericRst("QueryName")

to avoid the '3061-Too few parameters' error.

 

[Uncle Gizmo]

Well it would not work as I thought it would

Not with Form controls being referenced or TempVars.:banghead:

I don't know if it's relevant to this read because I haven't been following what's going on, but I noticed that something is not happening with Templars as you expect and I thought you might find my blog about TempVars interesting:- TempVars Value Error

 

[Gasman]

So how are the parameters meant to be supplied?, as the code above gives me an error saying it cannot find StartDate which is the first parameter in qry_master?

I've just tried the DAO function in the o/p's DB and get that error.?

 

[Gasman]

I don't know if it's relevant to this read because I haven't been following what's going on, but I noticed that something is not happening with Templars as you expect and I thought you might find my blog about TempVars interesting:- TempVars Value Error

Yes, I came upon that last night.
I have never used the .Add method, just a simple TempVars("MyTempVar") = Me.Control.Value

 

[theDBguy]

So how are the parameters meant to be supplied?, as the code above gives me an error saying it cannot find StartDate which is the first parameter in qry_master?

I've just tried the DAO function in the o/p's DB and get that error.?

Hi Gasman,


I guess I should have looked at the OP's attachment, but I was going by what the OP said in the original post, as quoted below:

Instead of date prompt in query, if the date (start & end) is inputted on the export form and export code returns the queries and then exports the data to excel.

I took it as meaning the OP originally had a parameter prompt query, which was then changed into using a Form reference instead. That's what I did to his/her file as shown in the attached modified copy of the original file, and the code doesn't give that error anymore. Hope it makes sense...

 

[Gasman]

Sorry, I cannot open that file. My version is too old.

Not to worry. I now know what to do if a recordset is required for a query that has parameters.

 

[theDBguy]

Sorry, I cannot open that file. My version is too old.

Not to worry. I now know what to do if a recordset is required for a query that has parameters.

Oh, I downloaded the wrong file. Sorry. In any case, Leigh's function is at least a way, so the developer won't have to do the QueryDef code behind all the forms causing this error. You simply replace the OpenRecordset method with fDAOGenericRst. If nothing else, it should reduce your typing efforts. Cheers!