ReportToPDF

[johnlute]

Good day! I'm trying to employ Stephen Lebans' ReportToPDF design.
It's a nifty thing but I'm a bit stuck. I need to throw a where clause
into the following click event:

Private Sub cmdReportToPDF_Click()
Dim i As Integer
Dim strForm As String

For i = 1 To CurrentProject.AllForms.Count
If CurrentProject.AllForms(i - 1).IsLoaded Then
strForm = CurrentProject.AllForms(i - 1).Name
If strForm <> "Marzetti Main Menu" Then
DoCmd.Close acForm, strForm, acSaveNo
End If
End If
Next i

Dim blRet As Boolean
blRet = ConvertReportToPDF(Me.cbSelectReport, vbNullString, _
Me.cbSelectReport.Value & ".pdf", False, True, 150, "", "", 0, 0, 0)

End Sub

The where clause is:
Dim strWhere As String
strWhere = "[txtProfileID] = """ & _
Forms![Marzetti Main Menu].Form![txtProfileID] & """"
DoCmd.OpenReport Me!cbSelectReport, acNormal _
, , strWhere

I just can't get the where clause into the code without an error being
created. I think I may be trying to do the impossible...? Is it
possible to filter this with a where clause? I've adjusted a test
report to filter accordingly however the database is designed so that
the report can be run from other forms therefore if I "have" to go
this route I'll need to eliminate that feature and force all reports
to be opened from the Main Menu. I don't want to do that!

Your help is greatly appreciated!

 

[Bob Quintal]

m:

Good day! I'm trying to employ Stephen Lebans' ReportToPDF design.
It's a nifty thing but I'm a bit stuck. I need to throw a where
clause into the following click event:

Private Sub cmdReportToPDF_Click()
Dim i As Integer
Dim strForm As String

For i = 1 To CurrentProject.AllForms.Count
If CurrentProject.AllForms(i - 1).IsLoaded Then
strForm = CurrentProject.AllForms(i - 1).Name
If strForm <> "Marzetti Main Menu" Then
DoCmd.Close acForm, strForm, acSaveNo
End If
End If
Next i

Dim blRet As Boolean
blRet = ConvertReportToPDF(Me.cbSelectReport, vbNullString, _
Me.cbSelectReport.Value & ".pdf", False, True, 150, "", "", 0, 0,
0)

End Sub

The where clause is:
Dim strWhere As String
strWhere = "[txtProfileID] = """ & _
Forms![Marzetti Main Menu].Form![txtProfileID] & """"
DoCmd.OpenReport Me!cbSelectReport, acNormal _
, , strWhere

I just can't get the where clause into the code without an error
being created. I think I may be trying to do the impossible...? Is
it possible to filter this with a where clause? I've adjusted a
test report to filter accordingly however the database is designed
so that the report can be run from other forms therefore if I
"have" to go this route I'll need to eliminate that feature and
force all reports to be opened from the Main Menu. I don't want to
do that!

Your help is greatly appreciated!

The trick to use mr Lebans' code to generate a filtered report is to
open the report filtered using docmd.openreport
and while it is opened call the report to pdf function using the
name of the open report.

docmd.OpenReport me.cbSelectReport, _
acViewPreview, ,stWhere, acHidden
blRet = ConvertReportToPDF(Me.cbSelectReport, vbNullString, _
Me.cbSelectReport.Value & ".pdf", False, True, 150, "", "", 0, 0, 0)
docmd.close acReport me.cbSelectReport

Thanks again to whoever taught me that.

 

[Albert D. Kallal]

The where clause is:

Dim strWhere As String
strWhere = "[txtProfileID] = """ & _
Forms![Marzetti Main Menu]![txtProfileID] & """"

Does the above look ok when you add:

msgbox strWhere


DoCmd.OpenReport Me.cbSelectReport, acNormal _
, , strWhere

You should be using a dot in the above...not a bang.

The trick in using stephans pdf system with a where clause to open the
report FIRST in PREVIEW mode.

DoCmd.OpenReport Me.cbSelectReport, acViewPreview, , strWhere
...call pdf create...
docmd.Close acReport,me.cboSelectReport

I just can't get the where clause into the code without an error being
created.

Well, the where clase reall has no relationsihp to the pdf system. I mean,
if you can get the where clause working for launching a report, you're never
gonna get to work for the PDF system are you?

So, get the simply report opening in preview mode with a where clause
working.

You don't mention what version of access you're using, in 2007 pdf support
is native to the system, and you can explore reports directly to pdf without
any add-ins...

 

[johnlute]

Thanks, Bob and Albert!!! I'm using 2003 and with your guidance came
up with this:

Dim i As Integer
Dim strForm As String

For i = 1 To CurrentProject.AllForms.Count
If CurrentProject.AllForms(i - 1).IsLoaded Then
strForm = CurrentProject.AllForms(i - 1).Name
If strForm <> "Marzetti Main Menu" Then
DoCmd.Close acForm, strForm, acSaveNo
End If
End If
Next i

Dim stDocName As String
Dim strWhere As String
strWhere = "[txtProfileID] = """ & _
Forms![Marzetti Main Menu].Form![txtProfileID] & """"
DoCmd.OpenReport Me.cbSelectReport, _
acViewPreview, , strWhere, acHidden
Dim blRet As Boolean
blRet = ConvertReportToPDF(Me.cbSelectReport, vbNullString, _
Me.cbSelectReport.Value & ".pdf", False, True, 150, "", "", 0, 0, 0)
DoCmd.Close acReport, Me.cbSelectReport _

End Sub

It seems to work just fine now however is there anything you see that
may not be correct? I'm not very good at codes and it's something of a
miracle that I arrived at this.

Also, I noticed that if a selection is NOT made in cbSelectReport then
the code runs and generates a report arbitrarily. What can I throw in
there to assure that a selection is made? cbSelectReport is a listbox
(improperly named "cb" I know!) and I'm unsure - throw an error MsgBox
into the BeforeUpdate event of the button...?

 

[Albert D. Kallal]

Thanks, Bob and Albert!!! I'm using 2003 and with your guidance came
up with this:

Great to see you moving forward (My apologies for that last post that seemed
very messy in terms of spelling, I actually bumped the send key, and was
typing it on a laptop).

Also, I noticed that if a selection is NOT made in cbSelectReport then
the code runs and generates a report arbitrarily. What can I throw in
there to assure that a selection is made? cbSelectReport is a listbox
(improperly named "cb" I know!) and I'm unsure - throw an error MsgBox
into the BeforeUpdate event of the button...?

You can in the start of the code routine go:

if nz(Me.cbSelectReport.Value,"") = "" then
msgbox "No report selected"
exit sub
end if

....your code continues here....

If the short display of the report displaying and closing is an annoyance
for your users, you could set the visibility of the report to false right
after it loads. However, it sounds like you have this working now, so that's
really the most important issue for the time being.

 

[johnlute]

Great to see you moving forward (My apologies for that last post that seemed

very messy in terms of spelling, I actually bumped the send key, and was
typing it on a laptop).

No problem! I appreciate the help very much!

You can in the start of the code routine go:

 if nz(Me.cbSelectReport.Value,"") = "" then
   msgbox "No report selected"
   exit sub
 end if

...your code continues here....

I gave that a try but it blew right through it and printed anyway. I
also tried:
If IsNull(Me.cbSelectReport = True) Then
MsgBox "No document is selected!"
Exit Sub
End If

....but that didn't work, either.

Any thoughts? Thanks!

 

[Bob Quintal]

m:

No problem! I appreciate the help very much!


I gave that a try but it blew right through it and printed anyway.
I also tried:
If IsNull(Me.cbSelectReport = True) Then
MsgBox "No document is selected!"
Exit Sub
End If

...but that didn't work, either.

Any thoughts? Thanks!

The = true should not be inside the brackets()
Change it to If IsNull(Me.cbSelectReport) = True Then

But I suspect that the listbox has a default value.
As an debugging tool, try this instead

If msgbox("Document " & me.cbSelectReport & _
" was selected, Print It? ", vbYesNo) = vbNo then
Exit Sub
End If

See if it says someting useful.

 

[johnlute]

The = true should not be inside the brackets()

Change it to  If IsNull(Me.cbSelectReport) = True Then

DOH! I should've caught that! Thanks - I corrected and it's just
spiffy now.

This is a great little feature but unfortunately, some of my reports
are so huge that opening them and then calling them to PDF results in
information being unable to be processed into the PDF file because
Access craps out. For these reports I've created "sister" reports that
have printer defaults to my PDF Writer. This is a good solution for ME
however I'm one of only a couple people that have a PDF writer. So as
it stands with some of the more complex reports: a user with a PDF
writer has an option but users without PDF writers could use the
ReportToPDF feature IF the reports weren't so huge. Their option would
then be to print the reports and scan them into electronic format.

I don't recall Radar O'Reily having to go through all of this!

Thanks, Bob!

 

[Dirk Goldgar]

DoCmd.OpenReport Me.cbSelectReport, acNormal _
, , strWhere

You should be using a dot in the above...not a bang.

Albert, are you really trying to say that there is anything technically
wrong with writing

Me!cbSelectReport

rather than

Me.cbSelectReport

.... assuming that cbSelectReport is a control on the form? Because if you
are, I'm going to argue with you very strongly.

 

[johnlute]

Hi, Dirk!

Albert, are you really trying to say that there is anything technically
wrong with writing

    Me!cbSelectReport

rather than

    Me.cbSelectReport

... assuming that cbSelectReport is a control on the form?  Because if you
are, I'm going to argue with you very strongly.

I'm glad you asked this because I wanted to know, too! I didn't bother
because I was happy just to get things resolved! I'm in the "habit" of
using the bang and so was a bit confused by Albert's statement.

 

[Albert D. Kallal]

Albert, are you really trying to say that there is anything technically
wrong with writing

Me!cbSelectReport

rather than

Me.cbSelectReport

... assuming that cbSelectReport is a control on the form? Because if you
are, I'm going to argue with you very strongly.

<smile>....

Yes, I do prefer dot in this case. Since this is a UI form and not editing
of data, the dot is preferred IMHO. Any mistyping of the control name will
be caught at compile time. If you use ! then the error is only caught at
runtime.

Furthermore, if you remove the control from the screen, and compile...then
again you get a compile error and will see any code that references that
control.

So, that's pretty much why in UI forms I prefer using dot as that means that
any un-bound controls etc do in fact exist regardless if the form has (or
has not) an underlying recordset.

No question that some of this will come down to one's coding style and
preferences, but I at least do have a "reason" for doing it this way. As
always, ones mileage will vary on this..

 

[Albert D. Kallal]

... assuming that cbSelectReport is a control on the form? Because if you
are, I'm going to argue with you very strongly.

I'm glad you asked this because I wanted to know, too! I didn't bother

because I was happy just to get things resolved! I'm in the "habit" of
using the bang and so was a bit confused by Albert's statement.

See my other post. By basic idea here is that if you use dot, and miss type
the control name, or remove the control name, then when you compile your
code you catch the error. If you use ! in this case, the code will compile
and you only get a failure at runtime...not compile time...

 

[Dirk Goldgar]

Yes, I do prefer dot in this case. Since this is a UI form and not editing
of data, the dot is preferred IMHO.

You confuse me with that one. While I know a number of reasons to use dot
instead of bang, I don't see the purpose of the form as being among them.

Any mistyping of the control name will be caught at compile time. If you
use ! then the error is only caught at runtime.

Furthermore, if you remove the control from the screen, and compile...then
again you get a compile error and will see any code that references that
control.

So, that's pretty much why in UI forms I prefer using dot as that means
that any un-bound controls etc do in fact exist regardless if the form has
(or has not) an underlying recordset.

I don't think the form's being bound or unbound has any relevance in this
case. The fact is that Access makes all controls on the form available via
both the bang and the dot reference -- where possible -- and it makes all
fields in the form's recordsource (if any) available both via bang and
dot -- again, where possible.

No question that some of this will come down to one's coding style and
preferences, but I at least do have a "reason" for doing it this way. As
always, ones mileage will vary on this..

Oh, I prefer to use the dot, too, when I can, and for the same reasons. You
do have to be careful, however, because there are times when you *cannot*
use the dot notation. if you happen to have a control or field whose name
is the same as one of the built-in properties of the form, then you
Me.<name> will be a reference to the property, not the control or field. In
that case you *must* use the bang notation.

Now, I don't believe I have ever given a control or field the same name as a
form property. Most developers know that it's best to avoid any reserved
words in naming objects. However, the majority of Access users are not
experienced developers, and I have often seen fields named "Name", for
example. In the case of a field or control named "Name", Me.Name and
Me!Name are two very different things. So you see, there are times when
Me!ControlName is correct and Me.ControlName is wrong, but there are *no*
times when Me.ControlName is correct and Me!ControlName is wrong.

I commented on your post because you seemed to be telling John that his use
of the bang in Me!cbSelectReport was wrong. This could be misleading to
John and to a lot of readers, because using the bang to refer to a control
is *never* wrong, so far as the working of the form is concerned; it's just
more convenient to the developer to get the benefits of using the dot
notation. Those benefits -- intellisense and compile-time name checking --
are compelling, I agree, but I would never tell someone his code is wrong if
he uses the bang, as Microsoft does in most of their examples.

 

[Albert D. Kallal]

Oh, I prefer to use the dot, too, when I can, and for the same reasons.
You do have to be careful, however, because there are times when you
*cannot* use the dot notation. if you happen to have a control or field
whose name is the same as one of the built-in properties of the form, then
you Me.<name> will be a reference to the property, not the control or
field. In that case you *must* use the bang notation.

Excellent answer + response.

Now, I don't believe I have ever given a control or field the same name as
a form property.

That is impressive. You set a very high bar here. I not adopted the above
coding standard. Without question a good many access developers do keep the
controls and the field names separate. I concor with this great idea. If I
worked on a project that had this standard, then I would 100% stick to that
standard.

(it been QUITE some time since I started a project from scratch, so I tend
to keep things the way they are).

So a good point about ! being wrong. I did not mean to say ! is wrong
here. I meant to say that in this case I prefer dot notation for some of the
given reasons.

I commented on your post because you seemed to be telling John that his
use of the bang in Me!cbSelectReport was wrong. This could be misleading
to John and to a lot of readers, because using the bang to refer to a
control is *never* wrong

Yup, most agreed. It interesting that we likely see eye to eye on the
advantages of compile time checking.

At the end of the day, it not wrong at all to use ! here, and that is not
the message I wanted to give to the access community.

 

[johnlute]

At the end of the day, it not wrong at all to use ! here, and that is not

the message I wanted to give to the access community.

You must be another one of those "Make ! not war." types...

 

[Dirk Goldgar]

That is impressive. You set a very high bar here. I not adopted the above
coding standard. Without question a good many access developers do keep
the
controls and the field names separate.

You may be thinking that I said something different than I did. I don't
give objects names that are already used by Access, such as property names,
VB function names, SQL keywords, DAO/ADO object names, etc. I don't have
any objection, though, to giving a control the same name as the field to
which it is bound. It just doesn't bother me, and makes my work in creating
a form or report a lot simpler.

What I do is, if I am going to be working with a control in code and
manipulating properties other than .Value, then I name it with a
control-type prefix; e.g., "cboThis", "lstThat", "txtTheOther". This
serves as documentation for me and anyone else that this is a control of a
particular type, and its type matters. But for controls that are only going
to be used to display and edit the values of their underlying fields, and
that I am not manipulating in code, I actually prefer that the controls have
the same name as the fields.

The only problem that ever arises from this is that, if you subsequently
change the controlsource to be something *other* than the original field,
you have to change the control name, or else an error is shown on the form.
I don't have a problem with that -- it's not a mistake I make often, and
when I do I know immediately what I forgot to do.

Different shops have their own coding standards, of course. What is
important is that your standards make sense and that you keep to them; or
else, as is sometimes necessary, you formally document that you are changing
standards as of "x" date.

It interesting that we likely see eye to eye on the
advantages of compile time checking.

Yep. The advantages are so great that there would have to be a significant
performance penalty to argue otherwise.