report based on dynmaic query

[dott]

I have a report made which is all laid out how i wish it to be. But i want to change the query of the report to something new based on a users input in a form. I know how to create the new query, but i do not know how to apply that new query to my old report. Is this possible?

 

[Hayley Baxter]

Yes you can do this. I have posted quite a few sample of dynamic queries on this forum. Have a read through the following threads, there are some sample dbs in these too.

Thread 1

Thread 2

If you're still stuggling let me know

Hay

 

[dott]

Is checking to see if the query exists important? Even if it does exist shouldnt it just over write it anyway?

 

[Hayley Baxter]

If the query already exists it will then be deleted to allow the new query to be created.

 

[dott]

I realize that but does it matter if it exists?
will it just overwrite it or will i run into problems?

 

[dott]

anyway regardless of that i am getting an error:
object variable or With block variable not set


here is the lines of code, ssql is a select statement and it is set i just took it out because it was so long

Dim ssql As String
Dim stDocName As String
Dim mydb As Database
Dim myquerydef As querydef

Set myquerydef = mydb.CreateQueryDef("qdef_dy", ssql)
stDocName = "repCBL"
DoCmd.OpenReport stDocName, acPreview, "qdef_dy"

it highlights the Set line as the error when i lick debug

i dont know if its important but when i declared the myquerydef variable above it did not capitalize the data type 'querydef' as 'QueryDef'. i found this odd as it usually changes the types to match capitalization

 

[Hayley Baxter]

it will run into problems because you need to first check if it exists so that if it does then delete, without checking if it exists access will throw an error because you are trying to create a query with the same name (it doesn't overwrite it)

 

[dott]

Ok thanks hayley,
any idea whats going on in my above post?

 

[Hayley Baxter]

Check that you have the Microsoft DAO 3.6 Object Library checked as it will not work properly without this, check that you have not forgotten to copy the module over to your program. That's the two most common problems so have a look at those first.

 

[dott]

guess thats the problem. i have 3.51

oh well

 

[Hayley Baxter]

Just go into Tools, references and check the Microsoft DAO 3.6 Object Library and it should work.