Avoiding repeated conditions?

R

Ruth Isaacs

Hello All

I have inherited a db with a fairly complex module that I am trying to
simplify. Essentally there are a lot (35) of reports in the db that are
generated for a given [practice]. The reports are uploaded to a website and
then sent as emails, and/or printed and posted, to the Practice. The report
is generated for a [practice] if the value of a corresponding yes/no field
(there are 35 such 'report' fields) on the current open form is 'yes', and
the email/print decision depends on the values of of two other yes/no fields
'printreports' and 'emailreports' on the curent form. Currently the module
first looks at the value of 'emaileports' and , if this is true, it then
looks at the value of each of the 35 yes/no 'report' fields and if this is
true some code if executed that converts the report to PDF then adds it to a
file (for later emailing and uploadng.

e.g. for the report called [rpt epping pcg] the corresponding yes/no
'report' field is called [epping rpt], and I have the following

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
flname = mth & "-Payroll-PS4 Substitute"
blret = ConvertReportToPDF(rptname, vbNullString, drname & flname &
".pdf", False)
DoCmd.RunSQL "INSERT INTO [" & strname & "] ( reportname ) SELECT "
& "'" & flname & "'" & " AS reportname "
End If

The trouble is that there are 35 'If' conditions like the one above that all
relate to the 'umbrella' condition that 'emailreports' is true: following
that, all 35 'If' conditions are then repeated for the 'umbrella' condition
that 'printreports' is true:

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
DoCmd.OpenReport rptname, acNormal, "", ""
End If

Is it possible to avoid all this repitition - also without repeating the
'printreports' and 'emailreports' conditions?
I wondered whether it would be possible to create an expression called
something like "reporting_method" that would be defined along the lines of:

If (Forms![frm practices]![emailreports] = True) Then
reporting_method =
flname = mth & "-Payroll-PS4 Substitute"
blret = ConvertReportToPDF(rptname, vbNullString, drname & flname &
".pdf", False)
DoCmd.RunSQL "INSERT INTO [" & strname & "] ( reportname ) SELECT "
& "'" & flname & "'" & " AS reportname "
Else
reporting_method =
DoCmd.OpenReport rptname, acNormal, "", ""
End If

.... and then to go through the 35 'report' conditions with something like:

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
reporting_method
End If

I realise this question has become rather long (!), but I very much hope
someone is still 'with it' and can help me work this out.

Many thanks in advance.

Leslie Isaacs
 
J

John Spencer

Well, you could write a sub that takes the same arguments as
ConvertReportToPDF and add an additional argument to tell it whether to
print or create pdf. Something on the order of

Sub sHandleReport (strReportName as string, strSomething as string, strPath
as string, tfSomething as boolean, tfPrint as boolean)

if TfPrint = False then

blret = ConvertReportToPDF(strReportName, vbNullString, strPath &
".pdf", False)

'looks as if you also need to pass the name of the table you are
inserting data into
DoCmd.RunSQL "INSERT INTO [" & strname & _
"] ( reportname ) Values ("'" &
strReportname & "')

Else
DoCmd.OpenReport strReportName, acNormal
End If
End Sub
You might be able to simplify this even more if you had a table of the
reports along with the titles you wanted to insert into your table.

Ruth Isaacs said:
Hello All

I have inherited a db with a fairly complex module that I am trying to
simplify. Essentally there are a lot (35) of reports in the db that are
generated for a given [practice]. The reports are uploaded to a website
and
then sent as emails, and/or printed and posted, to the Practice. The
report
is generated for a [practice] if the value of a corresponding yes/no field
(there are 35 such 'report' fields) on the current open form is 'yes', and
the email/print decision depends on the values of of two other yes/no
fields
'printreports' and 'emailreports' on the curent form. Currently the module
first looks at the value of 'emaileports' and , if this is true, it then
looks at the value of each of the 35 yes/no 'report' fields and if this is
true some code if executed that converts the report to PDF then adds it to
a
file (for later emailing and uploadng.

e.g. for the report called [rpt epping pcg] the corresponding yes/no
'report' field is called [epping rpt], and I have the following

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
flname = mth & "-Payroll-PS4 Substitute"
blret = ConvertReportToPDF(rptname, vbNullString, drname & flname &
".pdf", False)
DoCmd.RunSQL "INSERT INTO [" & strname & "] ( reportname ) SELECT "
& "'" & flname & "'" & " AS reportname "
End If

The trouble is that there are 35 'If' conditions like the one above that
all
relate to the 'umbrella' condition that 'emailreports' is true: following
that, all 35 'If' conditions are then repeated for the 'umbrella'
condition
that 'printreports' is true:

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
DoCmd.OpenReport rptname, acNormal, "", ""
End If

Is it possible to avoid all this repitition - also without repeating the
'printreports' and 'emailreports' conditions?
I wondered whether it would be possible to create an expression called
something like "reporting_method" that would be defined along the lines
of:

If (Forms![frm practices]![emailreports] = True) Then
reporting_method =
flname = mth & "-Payroll-PS4 Substitute"
blret = ConvertReportToPDF(rptname, vbNullString, drname & flname &
".pdf", False)
DoCmd.RunSQL "INSERT INTO [" & strname & "] ( reportname ) SELECT "
& "'" & flname & "'" & " AS reportname "
Else
reporting_method =
DoCmd.OpenReport rptname, acNormal, "", ""
End If

... and then to go through the 35 'report' conditions with something like:

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
reporting_method
End If

I realise this question has become rather long (!), but I very much hope
someone is still 'with it' and can help me work this out.

Many thanks in advance.

Leslie Isaacs
Click to expand...
 
L

Leslie Isaacs

Hello John

Many thanks for your reply.
I'm afraid I am still very much a learner with vb, and so am struggling to
understand how I would 'plumb in' your Sub sHandleReport with my existing
code.
Currently I have:

If (Forms![frm practices]![printreports] = True) Then

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
flname = mth & "-Payroll-PS4 Substitute"
blret = ConvertReportToPDF(rptname, vbNullString, drname & flname &
".pdf", False)
DoCmd.RunSQL "INSERT INTO [" & strname & "] ( reportname ) SELECT "
& "'" & flname & "'" & " AS reportname "
End If

If (Forms![frm practices]![malling rpt] = True) Then
rptname = "rpt malling pcg"
flname = mth & "-Payroll-PS4 Substitute Malling"
blret = ConvertReportToPDF(rptname, vbNullString, drname & flname &
".pdf", False)
DoCmd.RunSQL "INSERT INTO [" & strname & "] ( reportname ) SELECT "
& "'" & flname & "'" & " AS reportname "
End If

etc
etc
(35 such commands altogether)

End If

Then I have

If (Forms![frm practices]![emailreports] = True) Then


If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
DoCmd.OpenReport rptname, acNormal, "", ""
End If

If (Forms![frm practices]![malling rpt] = True) Then
rptname = "rpt malling pcg"
DoCmd.OpenReport rptname, acNormal, "", ""
End If

etc
etc
(35 such commands altogether)
End If

Using your suggested Sub sHandleReport approach, would it be a case of
defining the Sub exactly as you have written (in a separate module?) and
then having something like:

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
Sub sHandleReport (rptname, flname , strPath as string, tfSomething
as boolean, tfPrint as boolean)
End If

If (Forms![frm practices]![malling rpt] = True) Then
rptname = "rpt malling pcg"
Sub sHandleReport (rptname, flname , strPath as string, tfSomething
as boolean, tfPrint as boolean)
End If

etc
etc
(35 such commands altogether)

The main thing I'm unsure about is how to match the parameters of your
suggested Sub sHandleReport with the existing parameters in my code - i.e.
I assume that 'strReportName' corresponds to my 'rptname', but what do

strSomething as string
strPath as string
tfSomething as boolean
tfPrint as boolean

.... correspond to in my code?

I hope you don't give up on this John - I really am very grateful for the
help!

Many thanks
Les



John Spencer said:
Well, you could write a sub that takes the same arguments as
ConvertReportToPDF and add an additional argument to tell it whether to
print or create pdf. Something on the order of

Sub sHandleReport (strReportName as string, strSomething as string,
strPath as string, tfSomething as boolean, tfPrint as boolean)

if TfPrint = False then

blret = ConvertReportToPDF(strReportName, vbNullString, strPath &
".pdf", False)

'looks as if you also need to pass the name of the table you are
inserting data into
DoCmd.RunSQL "INSERT INTO [" & strname & _
"] ( reportname ) Values ("'" &
strReportname & "')

Else
DoCmd.OpenReport strReportName, acNormal
End If
End Sub
You might be able to simplify this even more if you had a table of the
reports along with the titles you wanted to insert into your table.

Ruth Isaacs said:
Hello All

I have inherited a db with a fairly complex module that I am trying to
simplify. Essentally there are a lot (35) of reports in the db that are
generated for a given [practice]. The reports are uploaded to a website
and
then sent as emails, and/or printed and posted, to the Practice. The
report
is generated for a [practice] if the value of a corresponding yes/no
field
(there are 35 such 'report' fields) on the current open form is 'yes',
and
the email/print decision depends on the values of of two other yes/no
fields
'printreports' and 'emailreports' on the curent form. Currently the
module
first looks at the value of 'emaileports' and , if this is true, it then
looks at the value of each of the 35 yes/no 'report' fields and if this
is
true some code if executed that converts the report to PDF then adds it
to a
file (for later emailing and uploadng.

e.g. for the report called [rpt epping pcg] the corresponding yes/no
'report' field is called [epping rpt], and I have the following

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
flname = mth & "-Payroll-PS4 Substitute"
blret = ConvertReportToPDF(rptname, vbNullString, drname & flname
&
".pdf", False)
DoCmd.RunSQL "INSERT INTO [" & strname & "] ( reportname ) SELECT
"
& "'" & flname & "'" & " AS reportname "
End If

The trouble is that there are 35 'If' conditions like the one above that
all
relate to the 'umbrella' condition that 'emailreports' is true: following
that, all 35 'If' conditions are then repeated for the 'umbrella'
condition
that 'printreports' is true:

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
DoCmd.OpenReport rptname, acNormal, "", ""
End If

Is it possible to avoid all this repitition - also without repeating the
'printreports' and 'emailreports' conditions?
I wondered whether it would be possible to create an expression called
something like "reporting_method" that would be defined along the lines
of:

If (Forms![frm practices]![emailreports] = True) Then
reporting_method =
flname = mth & "-Payroll-PS4 Substitute"
blret = ConvertReportToPDF(rptname, vbNullString, drname & flname
&
".pdf", False)
DoCmd.RunSQL "INSERT INTO [" & strname & "] ( reportname ) SELECT
"
& "'" & flname & "'" & " AS reportname "
Else
reporting_method =
DoCmd.OpenReport rptname, acNormal, "", ""
End If

... and then to go through the 35 'report' conditions with something
like:

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
reporting_method
End If

I realise this question has become rather long (!), but I very much hope
someone is still 'with it' and can help me work this out.

Many thanks in advance.

Leslie Isaacs
Click to expand...
Click to expand...
 
L

Leslie Isaacs

John

Oops - just spotted that in the sample conditions I gave in my response to
your reply I got the
If (Forms![frm practices]![printreports] = True) Then
....
and the
If (Forms![frm practices]![emailreports] = True) Then
....
conditions the wrong way round! (with respect the the method of sending the
reports specified within the condition): you you can make sense of this!

Les


John Spencer said:
Well, you could write a sub that takes the same arguments as
ConvertReportToPDF and add an additional argument to tell it whether to
print or create pdf. Something on the order of

Sub sHandleReport (strReportName as string, strSomething as string,
strPath as string, tfSomething as boolean, tfPrint as boolean)

if TfPrint = False then

blret = ConvertReportToPDF(strReportName, vbNullString, strPath &
".pdf", False)

'looks as if you also need to pass the name of the table you are
inserting data into
DoCmd.RunSQL "INSERT INTO [" & strname & _
"] ( reportname ) Values ("'" &
strReportname & "')

Else
DoCmd.OpenReport strReportName, acNormal
End If
End Sub
You might be able to simplify this even more if you had a table of the
reports along with the titles you wanted to insert into your table.

Ruth Isaacs said:
Hello All

I have inherited a db with a fairly complex module that I am trying to
simplify. Essentally there are a lot (35) of reports in the db that are
generated for a given [practice]. The reports are uploaded to a website
and
then sent as emails, and/or printed and posted, to the Practice. The
report
is generated for a [practice] if the value of a corresponding yes/no
field
(there are 35 such 'report' fields) on the current open form is 'yes',
and
the email/print decision depends on the values of of two other yes/no
fields
'printreports' and 'emailreports' on the curent form. Currently the
module
first looks at the value of 'emaileports' and , if this is true, it then
looks at the value of each of the 35 yes/no 'report' fields and if this
is
true some code if executed that converts the report to PDF then adds it
to a
file (for later emailing and uploadng.

e.g. for the report called [rpt epping pcg] the corresponding yes/no
'report' field is called [epping rpt], and I have the following

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
flname = mth & "-Payroll-PS4 Substitute"
blret = ConvertReportToPDF(rptname, vbNullString, drname & flname
&
".pdf", False)
DoCmd.RunSQL "INSERT INTO [" & strname & "] ( reportname ) SELECT
"
& "'" & flname & "'" & " AS reportname "
End If

The trouble is that there are 35 'If' conditions like the one above that
all
relate to the 'umbrella' condition that 'emailreports' is true: following
that, all 35 'If' conditions are then repeated for the 'umbrella'
condition
that 'printreports' is true:

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
DoCmd.OpenReport rptname, acNormal, "", ""
End If

Is it possible to avoid all this repitition - also without repeating the
'printreports' and 'emailreports' conditions?
I wondered whether it would be possible to create an expression called
something like "reporting_method" that would be defined along the lines
of:

If (Forms![frm practices]![emailreports] = True) Then
reporting_method =
flname = mth & "-Payroll-PS4 Substitute"
blret = ConvertReportToPDF(rptname, vbNullString, drname & flname
&
".pdf", False)
DoCmd.RunSQL "INSERT INTO [" & strname & "] ( reportname ) SELECT
"
& "'" & flname & "'" & " AS reportname "
Else
reporting_method =
DoCmd.OpenReport rptname, acNormal, "", ""
End If

... and then to go through the 35 'report' conditions with something
like:

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
reporting_method
End If

I realise this question has become rather long (!), but I very much hope
someone is still 'with it' and can help me work this out.

Many thanks in advance.

Leslie Isaacs
Click to expand...
Click to expand...
 
J

John Spencer

Once you got the sHandleReport working correctly, you could do something
like

IF Forms![frm practices]![printreports] AND Forms![frm practices]![malling
rpt] = True then
iActions = 3
ElseIF Forms![frm practices]![malling rpt] = True Then
iActions = 1
ElseIF Forms![frm practices]![printreports] = True then
iActions = 2
Else
exit sub (or exit function)
End if

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
flname = mth & "-Payroll-PS4 Substitute"
sHandleReport rptName,vbNullString, DrName & flName &
.."pdf",False,iActions
End If

I may have misunderstood something. Do you both create a PDF AND print a
report in the same run? If so, the above won't work since I assumed that
you were going to either print a report OR create a PDF. However, you could
do both by simply modify the sHandleReport routine to

Sub sHandleReport (strReportName as string, strSomething as string, _
strDir as String, strflName as string, _
tfSomething as boolean, intPrint as Integer)
'Intprint 1 = pdf, 2 = print report, 3 = both
if intprint = 1 or intPrint = 3 then
ConvertReportToPDF strReportName, vbNullString, strDir & strflName &
".pdf", False)

DoCmd.RunSQL "INSERT INTO [" & strname & _
"] ( reportname ) Values ("'" &
strflName & "')
'**** What is strName? Is it a variable containing a tablename? What not
just embed that in the query string?

Elseif IntPrint = 2 or intprint = 3 then
DoCmd.OpenReport strReportName, acNormal
End If
End Sub


Leslie Isaacs said:
Hello John

Many thanks for your reply.
I'm afraid I am still very much a learner with vb, and so am struggling to
understand how I would 'plumb in' your Sub sHandleReport with my existing
code.
Currently I have:

If (Forms![frm practices]![printreports] = True) Then

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
flname = mth & "-Payroll-PS4 Substitute"
blret = ConvertReportToPDF(rptname, vbNullString, drname & flname &
".pdf", False)
DoCmd.RunSQL "INSERT INTO [" & strname & "] ( reportname ) SELECT "
& "'" & flname & "'" & " AS reportname "
End If

If (Forms![frm practices]![malling rpt] = True) Then
rptname = "rpt malling pcg"
flname = mth & "-Payroll-PS4 Substitute Malling"
blret = ConvertReportToPDF(rptname, vbNullString, drname & flname &
".pdf", False)
DoCmd.RunSQL "INSERT INTO [" & strname & "] ( reportname ) SELECT "
& "'" & flname & "'" & " AS reportname "
End If

etc
etc
(35 such commands altogether)

End If

Then I have

If (Forms![frm practices]![emailreports] = True) Then


If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
DoCmd.OpenReport rptname, acNormal, "", ""
End If

If (Forms![frm practices]![malling rpt] = True) Then
rptname = "rpt malling pcg"
DoCmd.OpenReport rptname, acNormal, "", ""
End If

etc
etc
(35 such commands altogether)
End If

Using your suggested Sub sHandleReport approach, would it be a case of
defining the Sub exactly as you have written (in a separate module?) and
then having something like:

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
Sub sHandleReport (rptname, flname , strPath as string, tfSomething
as boolean, tfPrint as boolean)
End If

If (Forms![frm practices]![malling rpt] = True) Then
rptname = "rpt malling pcg"
Sub sHandleReport (rptname, flname , strPath as string, tfSomething
as boolean, tfPrint as boolean)
End If

etc
etc
(35 such commands altogether)

The main thing I'm unsure about is how to match the parameters of your
suggested Sub sHandleReport with the existing parameters in my code - i.e.
I assume that 'strReportName' corresponds to my 'rptname', but what do

strSomething as string
strPath as string
tfSomething as boolean
tfPrint as boolean

... correspond to in my code?

I hope you don't give up on this John - I really am very grateful for the
help!

Many thanks
Les



John Spencer said:
Well, you could write a sub that takes the same arguments as
ConvertReportToPDF and add an additional argument to tell it whether to
print or create pdf. Something on the order of

Sub sHandleReport (strReportName as string, strSomething as string,
strPath as string, tfSomething as boolean, tfPrint as boolean)

if TfPrint = False then

blret = ConvertReportToPDF(strReportName, vbNullString, strPath &
".pdf", False)

'looks as if you also need to pass the name of the table you are
inserting data into
DoCmd.RunSQL "INSERT INTO [" & strname & _
"] ( reportname ) Values ("'" &
strReportname & "')

Else
DoCmd.OpenReport strReportName, acNormal
End If
End Sub
You might be able to simplify this even more if you had a table of the
reports along with the titles you wanted to insert into your table.

Ruth Isaacs said:
Hello All

I have inherited a db with a fairly complex module that I am trying to
simplify. Essentally there are a lot (35) of reports in the db that are
generated for a given [practice]. The reports are uploaded to a website
and
then sent as emails, and/or printed and posted, to the Practice. The
report
is generated for a [practice] if the value of a corresponding yes/no
field
(there are 35 such 'report' fields) on the current open form is 'yes',
and
the email/print decision depends on the values of of two other yes/no
fields
'printreports' and 'emailreports' on the curent form. Currently the
module
first looks at the value of 'emaileports' and , if this is true, it then
looks at the value of each of the 35 yes/no 'report' fields and if this
is
true some code if executed that converts the report to PDF then adds it
to a
file (for later emailing and uploadng.

e.g. for the report called [rpt epping pcg] the corresponding yes/no
'report' field is called [epping rpt], and I have the following

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
flname = mth & "-Payroll-PS4 Substitute"
blret = ConvertReportToPDF(rptname, vbNullString, drname & flname
&
".pdf", False)
DoCmd.RunSQL "INSERT INTO [" & strname & "] ( reportname ) SELECT
"
& "'" & flname & "'" & " AS reportname "
End If

The trouble is that there are 35 'If' conditions like the one above that
all
relate to the 'umbrella' condition that 'emailreports' is true:
following
that, all 35 'If' conditions are then repeated for the 'umbrella'
condition
that 'printreports' is true:

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
DoCmd.OpenReport rptname, acNormal, "", ""
End If

Is it possible to avoid all this repitition - also without repeating the
'printreports' and 'emailreports' conditions?
I wondered whether it would be possible to create an expression called
something like "reporting_method" that would be defined along the lines
of:

If (Forms![frm practices]![emailreports] = True) Then
reporting_method =
flname = mth & "-Payroll-PS4 Substitute"
blret = ConvertReportToPDF(rptname, vbNullString, drname & flname
&
".pdf", False)
DoCmd.RunSQL "INSERT INTO [" & strname & "] ( reportname ) SELECT
"
& "'" & flname & "'" & " AS reportname "
Else
reporting_method =
DoCmd.OpenReport rptname, acNormal, "", ""
End If

... and then to go through the 35 'report' conditions with something
like:

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
reporting_method
End If

I realise this question has become rather long (!), but I very much hope
someone is still 'with it' and can help me work this out.

Many thanks in advance.

Leslie Isaacs
Click to expand...
Click to expand...
Click to expand...
 
L

Leslie Isaacs

Hello John

Thanks for your further reply
I think I may be getting out of my depth here - I'm certainly strugging to
see how to get your suggested code working with my existing code. The main
thing I'm unsure about is how to match the parameters of your suggested Sub
sHandleReport with the existing parameters in my code - i.e.

I assume that 'strReportName' corresponds to my 'rptname', but what do

strSomething as string
strPath as string
tfSomething as boolean
tfPrint as boolean

.... correspond to in my code?

If you feel that my query has become too complex for the 'gratis' help
available via the newsgroups I do understand: would you (or anyone you know)
be willing and able to 'fix' the module if I emailed it to you - for a fee.

Many thanks
Les


John Spencer said:
Once you got the sHandleReport working correctly, you could do something
like

IF Forms![frm practices]![printreports] AND Forms![frm practices]![malling
rpt] = True then
iActions = 3
ElseIF Forms![frm practices]![malling rpt] = True Then
iActions = 1
ElseIF Forms![frm practices]![printreports] = True then
iActions = 2
Else
exit sub (or exit function)
End if

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
flname = mth & "-Payroll-PS4 Substitute"
sHandleReport rptName,vbNullString, DrName & flName &
."pdf",False,iActions
End If

I may have misunderstood something. Do you both create a PDF AND print a
report in the same run? If so, the above won't work since I assumed that
you were going to either print a report OR create a PDF. However, you could
do both by simply modify the sHandleReport routine to

Sub sHandleReport (strReportName as string, strSomething as string, _
strDir as String, strflName as string, _
tfSomething as boolean, intPrint as Integer)
'Intprint 1 = pdf, 2 = print report, 3 = both
if intprint = 1 or intPrint = 3 then
ConvertReportToPDF strReportName, vbNullString, strDir & strflName &
".pdf", False)

DoCmd.RunSQL "INSERT INTO [" & strname & _
"] ( reportname ) Values ("'" &
strflName & "')
'**** What is strName? Is it a variable containing a tablename? What not
just embed that in the query string?

Elseif IntPrint = 2 or intprint = 3 then
DoCmd.OpenReport strReportName, acNormal
End If
End Sub


Leslie Isaacs said:
Hello John

Many thanks for your reply.
I'm afraid I am still very much a learner with vb, and so am struggling to
understand how I would 'plumb in' your Sub sHandleReport with my existing
code.
Currently I have:

If (Forms![frm practices]![printreports] = True) Then

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
flname = mth & "-Payroll-PS4 Substitute"
blret = ConvertReportToPDF(rptname, vbNullString, drname & flname &
".pdf", False)
DoCmd.RunSQL "INSERT INTO [" & strname & "] ( reportname ) SELECT "
& "'" & flname & "'" & " AS reportname "
End If

If (Forms![frm practices]![malling rpt] = True) Then
rptname = "rpt malling pcg"
flname = mth & "-Payroll-PS4 Substitute Malling"
blret = ConvertReportToPDF(rptname, vbNullString, drname & flname &
".pdf", False)
DoCmd.RunSQL "INSERT INTO [" & strname & "] ( reportname ) SELECT "
& "'" & flname & "'" & " AS reportname "
End If

etc
etc
(35 such commands altogether)

End If

Then I have

If (Forms![frm practices]![emailreports] = True) Then


If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
DoCmd.OpenReport rptname, acNormal, "", ""
End If

If (Forms![frm practices]![malling rpt] = True) Then
rptname = "rpt malling pcg"
DoCmd.OpenReport rptname, acNormal, "", ""
End If

etc
etc
(35 such commands altogether)
End If

Using your suggested Sub sHandleReport approach, would it be a case of
defining the Sub exactly as you have written (in a separate module?) and
then having something like:

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
Sub sHandleReport (rptname, flname , strPath as string, tfSomething
as boolean, tfPrint as boolean)
End If

If (Forms![frm practices]![malling rpt] = True) Then
rptname = "rpt malling pcg"
Sub sHandleReport (rptname, flname , strPath as string, tfSomething
as boolean, tfPrint as boolean)
End If

etc
etc
(35 such commands altogether)

The main thing I'm unsure about is how to match the parameters of your
suggested Sub sHandleReport with the existing parameters in my code - i.e.
I assume that 'strReportName' corresponds to my 'rptname', but what do

strSomething as string
strPath as string
tfSomething as boolean
tfPrint as boolean

... correspond to in my code?

I hope you don't give up on this John - I really am very grateful for the
help!

Many thanks
Les



John Spencer said:
Well, you could write a sub that takes the same arguments as
ConvertReportToPDF and add an additional argument to tell it whether to
print or create pdf. Something on the order of

Sub sHandleReport (strReportName as string, strSomething as string,
strPath as string, tfSomething as boolean, tfPrint as boolean)

if TfPrint = False then

blret = ConvertReportToPDF(strReportName, vbNullString, strPath &
".pdf", False)

'looks as if you also need to pass the name of the table you are
inserting data into
DoCmd.RunSQL "INSERT INTO [" & strname & _
"] ( reportname ) Values ("'" &
strReportname & "')

Else
DoCmd.OpenReport strReportName, acNormal
End If
End Sub
You might be able to simplify this even more if you had a table of the
reports along with the titles you wanted to insert into your table.

Hello All

I have inherited a db with a fairly complex module that I am trying to
simplify. Essentally there are a lot (35) of reports in the db that are
generated for a given [practice]. The reports are uploaded to a website
and
then sent as emails, and/or printed and posted, to the Practice. The
report
is generated for a [practice] if the value of a corresponding yes/no
field
(there are 35 such 'report' fields) on the current open form is 'yes',
and
the email/print decision depends on the values of of two other yes/no
fields
'printreports' and 'emailreports' on the curent form. Currently the
module
first looks at the value of 'emaileports' and , if this is true, it then
looks at the value of each of the 35 yes/no 'report' fields and if this
is
true some code if executed that converts the report to PDF then adds it
to a
file (for later emailing and uploadng.

e.g. for the report called [rpt epping pcg] the corresponding yes/no
'report' field is called [epping rpt], and I have the following

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
flname = mth & "-Payroll-PS4 Substitute"
blret = ConvertReportToPDF(rptname, vbNullString, drname & flname
&
".pdf", False)
DoCmd.RunSQL "INSERT INTO [" & strname & "] ( reportname ) SELECT
"
& "'" & flname & "'" & " AS reportname "
End If

The trouble is that there are 35 'If' conditions like the one above that
all
relate to the 'umbrella' condition that 'emailreports' is true:
following
that, all 35 'If' conditions are then repeated for the 'umbrella'
condition
that 'printreports' is true:

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
DoCmd.OpenReport rptname, acNormal, "", ""
End If

Is it possible to avoid all this repitition - also without repeating the
'printreports' and 'emailreports' conditions?
I wondered whether it would be possible to create an expression called
something like "reporting_method" that would be defined along the lines
of:

If (Forms![frm practices]![emailreports] = True) Then
reporting_method =
flname = mth & "-Payroll-PS4 Substitute"
blret = ConvertReportToPDF(rptname, vbNullString, drname & flname
&
".pdf", False)
DoCmd.RunSQL "INSERT INTO [" & strname & "] ( reportname ) SELECT
"
& "'" & flname & "'" & " AS reportname "
Else
reporting_method =
DoCmd.OpenReport rptname, acNormal, "", ""
End If

... and then to go through the 35 'report' conditions with something
like:

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
reporting_method
End If

I realise this question has become rather long (!), but I very much hope
someone is still 'with it' and can help me work this out.

Many thanks in advance.

Leslie Isaacs
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

My "Air Code" was just an example. My problem is that I don't know what all
the code in your function looks like. I do know that your code passed
vbNullString as one of the arguments to the ConvertReportToPDF. I don't
know why it did that and I don't know if it did that in *** EVERY ***
instance of your code.

In other words, to actually write the sub for you, I would need to have the
entire sub or function (code) that you are using and not just the snippets.
Plus it would be a good idea to have the code for ConvertReportToPDF.

As for further help, I will contact you offline. I won't charge you for the
help *** IF *** I decide I can do it with minimal effort.

Leslie Isaacs said:
Hello John

Thanks for your further reply
I think I may be getting out of my depth here - I'm certainly strugging to
see how to get your suggested code working with my existing code. The main
thing I'm unsure about is how to match the parameters of your suggested
Sub
sHandleReport with the existing parameters in my code - i.e.

I assume that 'strReportName' corresponds to my 'rptname', but what do

strSomething as string
strPath as string
tfSomething as boolean
tfPrint as boolean

... correspond to in my code?

If you feel that my query has become too complex for the 'gratis' help
available via the newsgroups I do understand: would you (or anyone you
know)
be willing and able to 'fix' the module if I emailed it to you - for a
fee.

Many thanks
Les


John Spencer said:
Once you got the sHandleReport working correctly, you could do something
like

IF Forms![frm practices]![printreports] AND Forms![frm
practices]![malling
rpt] = True then
iActions = 3
ElseIF Forms![frm practices]![malling rpt] = True Then
iActions = 1
ElseIF Forms![frm practices]![printreports] = True then
iActions = 2
Else
exit sub (or exit function)
End if

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
flname = mth & "-Payroll-PS4 Substitute"
sHandleReport rptName,vbNullString, DrName & flName &
."pdf",False,iActions
End If

I may have misunderstood something. Do you both create a PDF AND print a
report in the same run? If so, the above won't work since I assumed that
you were going to either print a report OR create a PDF. However, you could
do both by simply modify the sHandleReport routine to

Sub sHandleReport (strReportName as string, strSomething as string, _
strDir as String, strflName as string, _
tfSomething as boolean, intPrint as Integer)
'Intprint 1 = pdf, 2 = print report, 3 = both
if intprint = 1 or intPrint = 3 then
ConvertReportToPDF strReportName, vbNullString, strDir &
strflName &
".pdf", False)

DoCmd.RunSQL "INSERT INTO [" & strname & _
"] ( reportname ) Values ("'" &
strflName & "')
'**** What is strName? Is it a variable containing a tablename? What
not
just embed that in the query string?

Elseif IntPrint = 2 or intprint = 3 then
DoCmd.OpenReport strReportName, acNormal
End If
End Sub


Leslie Isaacs said:
Hello John

Many thanks for your reply.
I'm afraid I am still very much a learner with vb, and so am struggling to
understand how I would 'plumb in' your Sub sHandleReport with my existing
code.
Currently I have:

If (Forms![frm practices]![printreports] = True) Then

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
flname = mth & "-Payroll-PS4 Substitute"
blret = ConvertReportToPDF(rptname, vbNullString, drname &
flname &
".pdf", False)
DoCmd.RunSQL "INSERT INTO [" & strname & "] ( reportname )
SELECT "
& "'" & flname & "'" & " AS reportname "
End If

If (Forms![frm practices]![malling rpt] = True) Then
rptname = "rpt malling pcg"
flname = mth & "-Payroll-PS4 Substitute Malling"
blret = ConvertReportToPDF(rptname, vbNullString, drname &
flname &
".pdf", False)
DoCmd.RunSQL "INSERT INTO [" & strname & "] ( reportname )
SELECT "
& "'" & flname & "'" & " AS reportname "
End If

etc
etc
(35 such commands altogether)

End If

Then I have

If (Forms![frm practices]![emailreports] = True) Then


If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
DoCmd.OpenReport rptname, acNormal, "", ""
End If

If (Forms![frm practices]![malling rpt] = True) Then
rptname = "rpt malling pcg"
DoCmd.OpenReport rptname, acNormal, "", ""
End If

etc
etc
(35 such commands altogether)
End If

Using your suggested Sub sHandleReport approach, would it be a case of
defining the Sub exactly as you have written (in a separate module?)
and
then having something like:

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
Sub sHandleReport (rptname, flname , strPath as string, tfSomething
as boolean, tfPrint as boolean)
End If

If (Forms![frm practices]![malling rpt] = True) Then
rptname = "rpt malling pcg"
Sub sHandleReport (rptname, flname , strPath as string, tfSomething
as boolean, tfPrint as boolean)
End If

etc
etc
(35 such commands altogether)

The main thing I'm unsure about is how to match the parameters of your
suggested Sub sHandleReport with the existing parameters in my code - i.e.
I assume that 'strReportName' corresponds to my 'rptname', but what do

strSomething as string
strPath as string
tfSomething as boolean
tfPrint as boolean

... correspond to in my code?

I hope you don't give up on this John - I really am very grateful for the
help!

Many thanks
Les



Well, you could write a sub that takes the same arguments as
ConvertReportToPDF and add an additional argument to tell it whether
to
print or create pdf. Something on the order of

Sub sHandleReport (strReportName as string, strSomething as string,
strPath as string, tfSomething as boolean, tfPrint as boolean)

if TfPrint = False then

blret = ConvertReportToPDF(strReportName, vbNullString, strPath &
".pdf", False)

'looks as if you also need to pass the name of the table you
are
inserting data into
DoCmd.RunSQL "INSERT INTO [" & strname & _
"] ( reportname ) Values ("'" &
strReportname & "')

Else
DoCmd.OpenReport strReportName, acNormal
End If
End Sub
You might be able to simplify this even more if you had a table of the
reports along with the titles you wanted to insert into your table.

Hello All

I have inherited a db with a fairly complex module that I am trying
to
simplify. Essentally there are a lot (35) of reports in the db that are
generated for a given [practice]. The reports are uploaded to a website
and
then sent as emails, and/or printed and posted, to the Practice. The
report
is generated for a [practice] if the value of a corresponding yes/no
field
(there are 35 such 'report' fields) on the current open form is
'yes',
and
the email/print decision depends on the values of of two other yes/no
fields
'printreports' and 'emailreports' on the curent form. Currently the
module
first looks at the value of 'emaileports' and , if this is true, it then
looks at the value of each of the 35 yes/no 'report' fields and if this
is
true some code if executed that converts the report to PDF then adds it
to a
file (for later emailing and uploadng.

e.g. for the report called [rpt epping pcg] the corresponding yes/no
'report' field is called [epping rpt], and I have the following

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
flname = mth & "-Payroll-PS4 Substitute"
blret = ConvertReportToPDF(rptname, vbNullString, drname & flname
&
".pdf", False)
DoCmd.RunSQL "INSERT INTO [" & strname & "] ( reportname ) SELECT
"
& "'" & flname & "'" & " AS reportname "
End If

The trouble is that there are 35 'If' conditions like the one above that
all
relate to the 'umbrella' condition that 'emailreports' is true:
following
that, all 35 'If' conditions are then repeated for the 'umbrella'
condition
that 'printreports' is true:

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
DoCmd.OpenReport rptname, acNormal, "", ""
End If

Is it possible to avoid all this repitition - also without repeating the
'printreports' and 'emailreports' conditions?
I wondered whether it would be possible to create an expression
called
something like "reporting_method" that would be defined along the lines
of:

If (Forms![frm practices]![emailreports] = True) Then
reporting_method =
flname = mth & "-Payroll-PS4 Substitute"
blret = ConvertReportToPDF(rptname, vbNullString, drname & flname
&
".pdf", False)
DoCmd.RunSQL "INSERT INTO [" & strname & "] ( reportname ) SELECT
"
& "'" & flname & "'" & " AS reportname "
Else
reporting_method =
DoCmd.OpenReport rptname, acNormal, "", ""
End If

... and then to go through the 35 'report' conditions with something
like:

If (Forms![frm practices]![epping rpt] = True) Then
rptname = "rpt epping pcg"
reporting_method
End If

I realise this question has become rather long (!), but I very much hope
someone is still 'with it' and can help me work this out.

Many thanks in advance.

Leslie Isaacs
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Syntax of quotes? 7
Capturing OnNoData error with ConvertReportToPDF 2
Lebans Rpt to pdf file question 3
Too few parameters? 11
Close Report & Database in an External DB (not in the Current Data 3
Removing OrderBy from Tables and Queries in VBA 2
Access Report Coding 0
Email attachments 3

Top