VBA Question sending reports

  • Thread starter Thread starter Krzysztof via AccessMonster.com
  • Start date Start date
K

Krzysztof via AccessMonster.com

Good day to all!

I hope someone can help

I have a query, and from that query i have 2 reports: one is comprehensive,
and the other has a filter, that is supplied by a drop down in a form. i
made only two reports so i didn't have a seperate report for each sales rep.
i need to take that filtered report and email it. my problem is i cannot
seem to get the report in a filtered state to send it in a HTML format. the
email part is easy. Also, having a seperate report for each rep makes this
easy as well. has anyone done something like this?

Very Much TIA

~K
 
Thanks a bunch,

just one question - i can see the logic, but i am having trouble getting my
sql code in. it is about 8 lines long, and the VBA window does not like it.
that's my problem. my question - if i use this bit of code, will it
permanently change my query or just for this instance?

TIA
~K

Alex said:
Hi,
make your report based on a query, before sending report build query SQL,
filtered by sales rep and then send report

currentDB.querydefs("MyReportQuery").SQL="Select * from Table where
salesrep='Krzysztof'"
Good day to all!
Click to expand...
[quoted text clipped - 15 lines]
~K
Click to expand...
Click to expand...
 
permanently
for such queries I use a following trick:
I make a MyReportQueryTemplate Query, with SQL like:
Select * from Table where salesrep=[SalesRep]

then code will look like:

currentDB.querydefs("MyReportQuery").SQL=Replace(currentDB.querydefs("MyReportQueryTemplate").SQL,"[SalesRep]","'Krzysztof'")

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


jurczak via AccessMonster.com said:
Thanks a bunch,

just one question - i can see the logic, but i am having trouble getting
my
sql code in. it is about 8 lines long, and the VBA window does not like
it.
that's my problem. my question - if i use this bit of code, will it
permanently change my query or just for this instance?

TIA
~K

Alex said:
Hi,
make your report based on a query, before sending report build query SQL,
filtered by sales rep and then send report

currentDB.querydefs("MyReportQuery").SQL="Select * from Table where
salesrep='Krzysztof'"
Good day to all!
Click to expand...
[quoted text clipped - 15 lines]
~K
Click to expand...
Click to expand...
Click to expand...
 
Alex,

Thank you for your help!

another thing: because my sql statement is long, i am creating a variable
for it. seems simple enough. but my it will not let me set it up as a
string, it uses any quotes or parentheses and it throws out the syntax for
the variable statement. how can i get it to look at the entire statement as
a whole?

Alex said:
permanently
for such queries I use a following trick:
I make a MyReportQueryTemplate Query, with SQL like:
Select * from Table where salesrep=[SalesRep]

then code will look like:

currentDB.querydefs("MyReportQuery").SQL=Replace(currentDB.querydefs("MyReportQueryTemplate").SQL,"[SalesRep]","'Krzysztof'")
Thanks a bunch,
Click to expand...
[quoted text clipped - 20 lines]
Click to expand...
 
Sql = "first part of my SQL"
Sql = Sql & "next part of my SQL"
Sql = Sql & "next part of my SQL"
Etc
Etc
Etc

Hope this helps

Krzysztof via AccessMonster.com said:
Alex,

Thank you for your help!

another thing: because my sql statement is long, i am creating a variable
for it. seems simple enough. but my it will not let me set it up as a
string, it uses any quotes or parentheses and it throws out the syntax for
the variable statement. how can i get it to look at the entire statement as
a whole?

Alex said:
permanently
for such queries I use a following trick:
I make a MyReportQueryTemplate Query, with SQL like:
Select * from Table where salesrep=[SalesRep]

then code will look like:

currentDB.querydefs("MyReportQuery").SQL=Replace(currentDB.querydefs("MyReportQueryTemplate").SQL,"[SalesRep]","'Krzysztof'")
Thanks a bunch,
Click to expand...
[quoted text clipped - 20 lines]
~K
Click to expand...
Click to expand...
Click to expand...
 
Another thought:

After your variable

MsgBox SQL


This will throw up what you have done so you can check it

Krzysztof via AccessMonster.com said:
Alex,

Thank you for your help!

another thing: because my sql statement is long, i am creating a variable
for it. seems simple enough. but my it will not let me set it up as a
string, it uses any quotes or parentheses and it throws out the syntax for
the variable statement. how can i get it to look at the entire statement as
a whole?

Alex said:
permanently
for such queries I use a following trick:
I make a MyReportQueryTemplate Query, with SQL like:
Select * from Table where salesrep=[SalesRep]

then code will look like:

currentDB.querydefs("MyReportQuery").SQL=Replace(currentDB.querydefs("MyReportQueryTemplate").SQL,"[SalesRep]","'Krzysztof'")
Thanks a bunch,
Click to expand...
[quoted text clipped - 20 lines]
~K
Click to expand...
Click to expand...
Click to expand...
 
Sql = "first part of my SQL"
Sql = Sql & "next part of my SQL"
Sql = Sql & "next part of my SQL"
Etc
Etc
Etc
Click to expand...
I created a class to do this sort of stuff for me:

dim c as New CSQLCommand
c.Mode = "Jet" ' or ADO etc

c.Add "SELECT OneField,"
c.Add " TwoField"
c.Add "FROM MyTable"
c.Add "WHERE ThreeField = " & c.SQLDate(someDateValue)
c.Add " AND FourField = " & c.SQLQuote(someTextValue)

Set rs=db.OpenRecordset(c.SQL, dbOpenSnapshot, dbForwardOnly)



Hope that helps


Tim F
 
Back
Top