Help with Export to Excel

L

lookforsmt

Registered User.
Local time
Today, 03:07
Joined
Dec 26, 2011
Messages
672
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.

PHP: 
"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.
 

Attachments

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
 
Gasman said:
I can't open it as my version is too old.?
Click to expand...
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.
 
Hi. Did you try the link I posted? Just curious...
 
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.
 

Attachments

Last edited:
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.
 
Hi. Glad to hear you got it sorted out. Good luck with your project.
 
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.?
 
Gasman said:
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.?
Click to expand...

Hi Gasman. Just curious what is your impression of Leigh's functions?
 
theDBguy said:
Hi Gasman. Just curious what is your impression of Leigh's functions?
Click to expand...

Who is Leigh ?:confused:

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.:o
 
I've seen this code a few times when researching last night, but not sure what it is doing?

Code: 
For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
Next
 
Gasman said:
I've seen this code a few times when researching last night, but not sure what it is doing?

Code: 
For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
Next
Click to expand...
Hi. The point of Leigh's function was to replace something like this:
Code: 
Set rs = db.OpenRecordset("QueryName")
with this
Code: 
Set rs = fDAOGenericRst("QueryName")
to avoid the '3061-Too few parameters' error.
 
Gasman said:
Well it would not work as I thought it would :(

Not with Form controls being referenced or TempVars.:banghead:
Click to expand...

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
 
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.?
 
Uncle Gizmo said:
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
Click to expand...

Yes, I came upon that last night.
I have never used the .Add method, just a simple TempVars("MyTempVar") = Me.Control.Value
 
Gasman said:
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.?
Click to expand...
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:
lookforsmt said:
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.
Click to expand...
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...
 

Attachments

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.
 
Gasman said:
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.
Click to expand...
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!
 
You must log in or register to reply here.

Similar threads

B
  • Question Question
export and transpose problem
Replies
8
Views
377
Pat Hartman
P
A
Combining multiple reports into a PDF export
Replies
2
Views
395
KitaYama
K
J
Use VBA to export form data to Excel, but keep getting "We Found a Problem..." when opening the file in Excel
Replies
5
Views
359
June7
June7
D
Solved Command button on Report to export to excel
Replies
8
Views
578
Eugene-LS
Eugene-LS
R
Export a form to excel along with the combo boxes
Replies
16
Views
1,168
CJ_London
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom