Help with Export to Excel

[theDBguy]

Hi. Glad to hear you got your answer already. I was just trying to help with the original error. I didn't completely test your code past the spot causing the error. Good luck with your project.

 

[Gasman]

Thank you both theDBguy & Gasman for so much insight.

just wanted to know that theDBguy last updated db was allowing me to export the excel without any error. However, it exports as-is the entire records from the queries/tables to excel except the query which has the date parameter set.

thanks

My confusion was that I *thought* referencing the form controls as criteria in the query (or using global/tempvars variables) would do the job, but it seems using as recordset is not the case.

I still do not understand what setting the parameter value to Eval(parameter.name) is meant to do. :banghead:

 

[lookforsmt]

Thanks theDBGuy & Gasman for your time.
Dear Gasman, i dont really have the answer to your query. I have decided to put date prompt to all my queries inorder to avoid the error.

Just one last thing. can i apply the same logic for deleting the data from the respective tables based on date criteria. (business start date & business end date)

 

[theDBguy]

My confusion was that I *thought* referencing the form controls as criteria in the query (or using global/tempvars variables) would do the job, but it seems using as recordset is not the case.

I still do not understand what setting the parameter value to Eval(parameter.name) is meant to do. :banghead:

Hi Gasman. Let's back up a bit to make sure we're on the same page. If you can use TempVars, then try creating the following query:

SELECT TempVars!TestVar AS TEST;

Save this query as "qryTest" and then run it, to make sure it works.
Then, in the Immediate Window, enter the following code:

Set rs = CurrentDb.OpenRecordset("qryTest")

What happened? Did you get the 3061 error?

Now, copy Leigh's function into a Standard Module and enter the following in the Immediate Window:

Set rs = fDAOGenericRst("qryTest")

Did you get the error again?
Thanks!

 

[Gasman]

No, no error

I wrote a small sub and the value of TempVars!Test is NULL ?

BTW, what reference do I need for the ADO code please?

 

[theDBguy]

No, no error

I wrote a small sub and the value of TempVars!Test is NULL ?

BTW, what reference do I need for the ADO code please?

Hi Gasman. Yes, Null is fine. All I was trying to demonstrate there was that first you got an error, then you don't, simply by using Leigh's function. If you want to try the ADO version, you'll need to set a reference to Microsoft ActiveX Data Objects x.x Library. Coincidentally, I figured out a way to make Leigh's function work with parameter queries too. I just don't know why he didn't make it that way. I might ask him about it.

 

[Gasman]

Ah, OK, I misunderstood.

I could not see why aparameters would not be used if they were there as Null would,not produce any data in most cases.?

I did wonder about having globals by the same name as the parameters, whether that would populate the parameters.

At least I played a little more with querydesf, which is something I had not used much previously.

Thank you for the reference. I was trying to use the recordset library

Edit. I *thought* I had already posted this, but could not see it, so wrote it again.

 

[theDBguy]

Ah, OK, I misunderstood.

I could not see why aparameters would not be used if they were there as Null would,not produce any data in most cases.?

I did wonder about having globals by the same name as the parameters, whether that would populate the parameters.

At least I played a little more with querydesf, which is something I had not used much previously.

Thank you for the reference. I was trying to use the recordset library

Edit. I *thought* I had already posted this, but could not see it, so wrote it again.

Hi Gasman. Glad to hear we're on the same page now. Manipulating the QueryDef object is basically what Leigh's function was doing, which you learned to do on your own. I always recommend his function whenever a user asks about the 3061 (too few parameters) error, because then they don't have to mess with QueryDefs. Cheers!

 

[lookforsmt]

Hi!

i am bit confused, Do i need to add anything more to the code.

Also, can these be applied with the same logic to delete the data from the respective tables based on date criteria. (business start date & business end date)

 

[theDBguy]

Hi!

i am bit confused, Do i need to add anything more to the code.

Also, can these be applied with the same logic to delete the data from the respective tables based on date criteria. (business start date & business end date)

Hi. I'll let Gasman answer that question, since I mainly concentrated on your original problem with getting the 3061 error message. Otherwise, I will ask you to repost a copy of your db, so I can see how you implemented any of the solutions offered here to advise you on how to proceed from there. However, I can tell you now that the same logic you're using now can also be used to delete the data based on a date range. Cheers!

 

[Gasman]

As theDBguy states, Yes, and if you just use the parameter index, it does not matter what the parameters are called.
Stay consistent though with their order, startdate then enddate etc.

 

[lookforsmt]

Thank you both for your time and advice.
I will try and implement that.

i have already marked this thread as solved. Thank you once again

 

[theDBguy]

Thank you both for your time and advice.
I will try and implement that.

i have already marked this thread as solved. Thank you once again

Hi. You're welcome. Gasman and I were happy to assist. Good luck with your project.

 

[lookforsmt]

H!! Guys

i tried using this to delete the data from the table and it gave me below error

Run-time error "3219": Invalid Operation

data to delete was through delete query with start & end date prompt
any suggestions, Thanks

 

[isladogs]

Several possibilities including...

1. Does the table have a relationship to another table where this table has the parent key? If so you need to delete the child records before you can delete these parent records. However if you have referential integrity imposed with cascade delete that's not the answer

2. If the delete query contains two or more linked tables, try setting the query to unique records = yes
In SQL, that's DELETE DISTINCTROW ....

 

[theDBguy]

H!! Guys

i tried using this to delete the data from the table and it gave me below error

data to delete was through delete query with start & end date prompt
any suggestions, Thanks

Hi. Not sure which one you tried, but Leigh also has an equivalent function for action queries.

 

[lookforsmt]

Thank you for your response

My table is at the moment all by itself, there is no relation with any other table.
I have update db post 5 for reference

 

[isladogs]

The delete query worked for me.
I used start date =21/11/2019 & end date =23/11/2019. 28 records deleted with 7 left in place.

FYI you can slightly shorten the query SQL as

DELETE *
FROM tbl_Master
WHERE Entry_Date Between [Start Date] And [End Date];

EDIT
I hadn't been following this thread. Your error occurs in the form in the line Set rsSrc= qdf.OpenRecordset.
I haven't got time to look at the form code now but its that you need to modify, not the delete query

 

[lookforsmt]

HI! isladogs,

i too tried but it did not work of me.
i am still getting runtime error 3219, Invalid operation

 

[isladogs]

It was nearly 2 weeks ago but I do know it worked for me at the time