Report parameters and text options

[BruceM]

I am working on modifications to an inefficiently designed Approved Vendor
database. The vendors have certificates have expiration dates. Some
vendors have more than one certificate, so I have created a new table for
certificates, on the many side of a one-to-many relationship with the main
vendor table. With some help from this group I moved certificate
information from the main table to the related table. The form/subform
based on the related tables works as it should.

Here is the basic structure:

tblVendor
VendorID (PK)
VendorName
Other vendor information

tblCert
CertID (PK)
VendorID (FK)
CertType (text)
CertExp (Date/Time)
CertRequested (Yes/No)
CertReturned (Yes/No)

As a vendor's certificate expiration date approaches I need to send a
request for new information. In the current version of the database I have
three command buttons on an unbound form: one button to check for
certificates that are about to expire or have just expired (I do this about
once a month, and check the CertRequested box on the subform to show it was
requested), another to send a second reminder if CertRequested is True and
the expiration date has passed, and a third button for certificates that are
seriously overdue.

The request is a report (rptFaxCert) that is intended as a fax. It is
grouped on VendorName, with Force New Page set to Before Section. It is
based on a query (inner join) that includes both tables.

I placed this in the Click event of a comman botton on the unbound form:

Dim strCertSQL As String
strCertSQL = "[CertReturned] = True AND [CertExp] BETWEEN
DateAdd(""ww"",-2,Date()) AND DateAdd(""m"",1,Date())"

DoCmd.OpenReport "rptFaxCert", acPreview, , strCertSQL

This shows vendors with certificate expiration dates between two weeks ago
and a month into the future.

There are two points here. First, I need to add text for the fax body,
along the lines of:

Dim strFaxBody as String
strFaxBody = "Your " & [CertType] " is going to expire on " & [CertExp]
& ". Please send an updated certificate."

I can't get my brain around how best to do this. If I am to use the
strFaxBody code, would it be in the report's Print event, or what? Or is
there a better way to do this, such as a concatenated query field?

Second point: Does it make sense to use one command button to check for the
three categories: expiration date approaching or just past; late; and very
late? I would like to click one CheckCert command button (on the unbound
form), and to generate a separate fax with appropriate text for each
situation. When the report opens, Vendor1, whose cert is about to expire,
would get a fax that says "Your cert is about to expire". Vendor2, from
whom I have already requested a fax, and whose cert expired more than two
weeks ago, would get a fax that says "You're late", and so forth. Running
the report would produce all categories at once, without the need for three
separate command buttons on the unbound form.

I am not committed to any line here. I am trying to show that I have been
working on this, and am stuck. Any suggestions, including that I approach
this differently from the earliest step, are welcome.

 

[Steve Schapel]

Bruce,

No, it doesn't make sense to have three buttons to check for each of the
three mutually exclusive conditions.

There are probably quite a few approaches that could be taken here. One
would be a fairly complex Switch() function in the Control Source of a
textbox on the Report. Another would be 3 superimposed textboxes, one
to cover each of the the three conditions, and use code to toggle their
Visible property as required on the Format event of the applicable
report section. However, I think in this case I would prefer a
user-defined function to return the appropriate string for your
notification text. Maybe it would be something like this...

Public Function FaxText(CertExp As Date, CertType As String,
CertRequested As Boolean) As String
Dim TextType As Integer
If CertRequested And Then
If CertExp < Date - 30 Then
TextType = 3
Else
TextType = 2
End If
Else
If CertExp < Date + 30 And CertExp > Date -14 Then
TextType = 1
End If
End If
Select Case TextType
Case 1
FaxText = "Your " & CertType " is going to expire on " &
Format(CertExp, "mm/dd/yy") & ". Please send an updated certificate."
Case 2
FaxText = "Did you get the request?"
Case 3
FaxText = "Your cert is seriously expired."
End Select
End Function

And then, in the Control Source of the textbox on your report, you use
this function like...
=FaxText([CertExp],[CertType],[CertRequested])

(Note that the above is untested "air code", just to give the general
idea, I know it will require some refinement. )

--
Steve Schapel, Microsoft Access MVP

I am working on modifications to an inefficiently designed Approved Vendor
database. The vendors have certificates have expiration dates. Some
vendors have more than one certificate, so I have created a new table for
certificates, on the many side of a one-to-many relationship with the main
vendor table. With some help from this group I moved certificate
information from the main table to the related table. The form/subform
based on the related tables works as it should.

Here is the basic structure:

tblVendor
VendorID (PK)
VendorName
Other vendor information

tblCert
CertID (PK)
VendorID (FK)
CertType (text)
CertExp (Date/Time)
CertRequested (Yes/No)
CertReturned (Yes/No)

As a vendor's certificate expiration date approaches I need to send a
request for new information. In the current version of the database I have
three command buttons on an unbound form: one button to check for
certificates that are about to expire or have just expired (I do this about
once a month, and check the CertRequested box on the subform to show it was
requested), another to send a second reminder if CertRequested is True and
the expiration date has passed, and a third button for certificates that are
seriously overdue.

The request is a report (rptFaxCert) that is intended as a fax. It is
grouped on VendorName, with Force New Page set to Before Section. It is
based on a query (inner join) that includes both tables.

I placed this in the Click event of a comman botton on the unbound form:

Dim strCertSQL As String
strCertSQL = "[CertReturned] = True AND [CertExp] BETWEEN
DateAdd(""ww"",-2,Date()) AND DateAdd(""m"",1,Date())"

DoCmd.OpenReport "rptFaxCert", acPreview, , strCertSQL

This shows vendors with certificate expiration dates between two weeks ago
and a month into the future.

There are two points here. First, I need to add text for the fax body,
along the lines of:

Dim strFaxBody as String
strFaxBody = "Your " & [CertType] " is going to expire on " & [CertExp]
& ". Please send an updated certificate."

I can't get my brain around how best to do this. If I am to use the
strFaxBody code, would it be in the report's Print event, or what? Or is
there a better way to do this, such as a concatenated query field?

Second point: Does it make sense to use one command button to check for the
three categories: expiration date approaching or just past; late; and very
late? I would like to click one CheckCert command button (on the unbound
form), and to generate a separate fax with appropriate text for each
situation. When the report opens, Vendor1, whose cert is about to expire,
would get a fax that says "Your cert is about to expire". Vendor2, from
whom I have already requested a fax, and whose cert expired more than two
weeks ago, would get a fax that says "You're late", and so forth. Running
the report would produce all categories at once, without the need for three
separate command buttons on the unbound form.

I am not committed to any line here. I am trying to show that I have been
working on this, and am stuck. Any suggestions, including that I approach
this differently from the earliest step, are welcome.

 

[BruceM]

Steve,

Thanks for replying. I think I see how this will work. I definitely prefer
the idea of the user-defined function. I won't be able to give it a try
until later today at the soonest, but I want to acknowledge the suggestion.
From what I can tell the command button will open the form, at which time
the function will come into play.

Bruce,

No, it doesn't make sense to have three buttons to check for each of the
three mutually exclusive conditions.

There are probably quite a few approaches that could be taken here. One
would be a fairly complex Switch() function in the Control Source of a
textbox on the Report. Another would be 3 superimposed textboxes, one to
cover each of the the three conditions, and use code to toggle their
Visible property as required on the Format event of the applicable report
section. However, I think in this case I would prefer a user-defined
function to return the appropriate string for your notification text.
Maybe it would be something like this...

Public Function FaxText(CertExp As Date, CertType As String,
CertRequested As Boolean) As String
Dim TextType As Integer
If CertRequested And Then
If CertExp < Date - 30 Then
TextType = 3
Else
TextType = 2
End If
Else
If CertExp < Date + 30 And CertExp > Date -14 Then
TextType = 1
End If
End If
Select Case TextType
Case 1
FaxText = "Your " & CertType " is going to expire on " &
Format(CertExp, "mm/dd/yy") & ". Please send an updated certificate."
Case 2
FaxText = "Did you get the request?"
Case 3
FaxText = "Your cert is seriously expired."
End Select
End Function

And then, in the Control Source of the textbox on your report, you use
this function like...
=FaxText([CertExp],[CertType],[CertRequested])

(Note that the above is untested "air code", just to give the general
idea, I know it will require some refinement. )

--
Steve Schapel, Microsoft Access MVP

I am working on modifications to an inefficiently designed Approved
Vendor database. The vendors have certificates have expiration dates.
Some vendors have more than one certificate, so I have created a new
table for certificates, on the many side of a one-to-many relationship
with the main vendor table. With some help from this group I moved
certificate information from the main table to the related table. The
form/subform based on the related tables works as it should.

Here is the basic structure:

tblVendor
VendorID (PK)
VendorName
Other vendor information

tblCert
CertID (PK)
VendorID (FK)
CertType (text)
CertExp (Date/Time)
CertRequested (Yes/No)
CertReturned (Yes/No)

As a vendor's certificate expiration date approaches I need to send a
request for new information. In the current version of the database I
have three command buttons on an unbound form: one button to check for
certificates that are about to expire or have just expired (I do this
about once a month, and check the CertRequested box on the subform to
show it was requested), another to send a second reminder if
CertRequested is True and the expiration date has passed, and a third
button for certificates that are seriously overdue.

The request is a report (rptFaxCert) that is intended as a fax. It is
grouped on VendorName, with Force New Page set to Before Section. It is
based on a query (inner join) that includes both tables.

I placed this in the Click event of a comman botton on the unbound form:

Dim strCertSQL As String
strCertSQL = "[CertReturned] = True AND [CertExp] BETWEEN
DateAdd(""ww"",-2,Date()) AND DateAdd(""m"",1,Date())"

DoCmd.OpenReport "rptFaxCert", acPreview, , strCertSQL

This shows vendors with certificate expiration dates between two weeks
ago and a month into the future.

There are two points here. First, I need to add text for the fax body,
along the lines of:

Dim strFaxBody as String
strFaxBody = "Your " & [CertType] " is going to expire on " &
[CertExp] & ". Please send an updated certificate."

I can't get my brain around how best to do this. If I am to use the
strFaxBody code, would it be in the report's Print event, or what? Or is
there a better way to do this, such as a concatenated query field?

Second point: Does it make sense to use one command button to check for
the three categories: expiration date approaching or just past; late;
and very late? I would like to click one CheckCert command button (on
the unbound form), and to generate a separate fax with appropriate text
for each situation. When the report opens, Vendor1, whose cert is about
to expire, would get a fax that says "Your cert is about to expire".
Vendor2, from whom I have already requested a fax, and whose cert expired
more than two weeks ago, would get a fax that says "You're late", and so
forth. Running the report would produce all categories at once, without
the need for three separate command buttons on the unbound form.

I am not committed to any line here. I am trying to show that I have
been working on this, and am stuck. Any suggestions, including that I
approach this differently from the earliest step, are welcome.

 

[Steve Schapel]

Bruce,

From what I can tell the command button will open the form, at which time
the function will come into play.

.... if I understand you correctly, this would be 'report', not 'form'?

 

[BruceM]

I have had a chance to try out the code, but have not been able to make any
real progress. Here is what I have so far in a public function in a code
module:

Public Function FaxText(CertExp As Date, CertType As String, _
CertReturned As Boolean) As String

Dim TextType As Integer

' CertReturned = True until a cert has been requested. CertReturned =
True
' for certs that are current.

If CertExp = True And CertReturned = True Then
If CertExp < DateAdd("m", 1, Date) And CertExp >= DateAdd("ww", -2,
Date) Then
TextType = 3
End If
Else
If CertExp > DateAdd("ww", -2, Date) _
And CertExp <= DateAdd("m", -2, Date) Then
TextType = 2
Else
If CertExp > DateAdd("m", -2, Date) Then
TextType = 1
End If
End If
End If

Select Case TextType
Case 1
FaxText = "Your " & CertType & " is going to expire on " & _
Format(CertExp, "mmmm dd, yyyy") & _
". Please send an updated certificate."
Case 2
FaxText = "Did you get the request?"
Case 3
FaxText = "Your cert is seriously expired."
End Select

End Function

I used CertReturned instead of CertRequested, and DateAdd rather than Date +
or Date -, but other than that it seems to be what you suggested. The code
compiles OK, but when I attempt to use it as the control source for an
unbound text box on the report:
=FaxText([CertExp],[CertType],[CertRequested]) I receive the following error
message when I attempt to open the report:
Syntax error (comma) in query expression '[
=FaxText([CertExp],[CertType],[CertRequested])]'
If I attempt to open the report from a command button, I receive the same
error message, but because of error handling I can identify it as Error
3075.

Can you tell what I'm missing here?

Bruce,

No, it doesn't make sense to have three buttons to check for each of the
three mutually exclusive conditions.

There are probably quite a few approaches that could be taken here. One
would be a fairly complex Switch() function in the Control Source of a
textbox on the Report. Another would be 3 superimposed textboxes, one to
cover each of the the three conditions, and use code to toggle their
Visible property as required on the Format event of the applicable report
section. However, I think in this case I would prefer a user-defined
function to return the appropriate string for your notification text.
Maybe it would be something like this...

Public Function FaxText(CertExp As Date, CertType As String,
CertRequested As Boolean) As String
Dim TextType As Integer
If CertRequested And Then
If CertExp < Date - 30 Then
TextType = 3
Else
TextType = 2
End If
Else
If CertExp < Date + 30 And CertExp > Date -14 Then
TextType = 1
End If
End If
Select Case TextType
Case 1
FaxText = "Your " & CertType " is going to expire on " &
Format(CertExp, "mm/dd/yy") & ". Please send an updated certificate."
Case 2
FaxText = "Did you get the request?"
Case 3
FaxText = "Your cert is seriously expired."
End Select
End Function

And then, in the Control Source of the textbox on your report, you use
this function like...
=FaxText([CertExp],[CertType],[CertRequested])

(Note that the above is untested "air code", just to give the general
idea, I know it will require some refinement. )

--
Steve Schapel, Microsoft Access MVP

I am working on modifications to an inefficiently designed Approved
Vendor database. The vendors have certificates have expiration dates.
Some vendors have more than one certificate, so I have created a new
table for certificates, on the many side of a one-to-many relationship
with the main vendor table. With some help from this group I moved
certificate information from the main table to the related table. The
form/subform based on the related tables works as it should.

Here is the basic structure:

tblVendor
VendorID (PK)
VendorName
Other vendor information

tblCert
CertID (PK)
VendorID (FK)
CertType (text)
CertExp (Date/Time)
CertRequested (Yes/No)
CertReturned (Yes/No)

As a vendor's certificate expiration date approaches I need to send a
request for new information. In the current version of the database I
have three command buttons on an unbound form: one button to check for
certificates that are about to expire or have just expired (I do this
about once a month, and check the CertRequested box on the subform to
show it was requested), another to send a second reminder if
CertRequested is True and the expiration date has passed, and a third
button for certificates that are seriously overdue.

The request is a report (rptFaxCert) that is intended as a fax. It is
grouped on VendorName, with Force New Page set to Before Section. It is
based on a query (inner join) that includes both tables.

I placed this in the Click event of a comman botton on the unbound form:

Dim strCertSQL As String
strCertSQL = "[CertReturned] = True AND [CertExp] BETWEEN
DateAdd(""ww"",-2,Date()) AND DateAdd(""m"",1,Date())"

DoCmd.OpenReport "rptFaxCert", acPreview, , strCertSQL

This shows vendors with certificate expiration dates between two weeks
ago and a month into the future.

There are two points here. First, I need to add text for the fax body,
along the lines of:

Dim strFaxBody as String
strFaxBody = "Your " & [CertType] " is going to expire on " &
[CertExp] & ". Please send an updated certificate."

I can't get my brain around how best to do this. If I am to use the
strFaxBody code, would it be in the report's Print event, or what? Or is
there a better way to do this, such as a concatenated query field?

Second point: Does it make sense to use one command button to check for
the three categories: expiration date approaching or just past; late;
and very late? I would like to click one CheckCert command button (on
the unbound form), and to generate a separate fax with appropriate text
for each situation. When the report opens, Vendor1, whose cert is about
to expire, would get a fax that says "Your cert is about to expire".
Vendor2, from whom I have already requested a fax, and whose cert expired
more than two weeks ago, would get a fax that says "You're late", and so
forth. Running the report would produce all categories at once, without
the need for three separate command buttons on the unbound form.

I am not committed to any line here. I am trying to show that I have
been working on this, and am stuck. Any suggestions, including that I
approach this differently from the earliest step, are welcome.

 

[Steve Schapel]

Bruce,

I'm sorry, I can't immediately see what is causing this problem.

There is an oddity in your code...
CertExp = True
.... where CertExp is a date - but I don't imagine this would lead to the
error you are receiving.

What is the actual data in the CertType field? Does it ever have an
apostrophe or parentheses or whatnot in there?

 

[BruceM]

In response to your reply in another part of this thread, I meant to say
that a command button on the form will open the report, not that it will
open the form.

CertType is a text field. Other than alphanumeric characters the data may
contain a colon, a hyphen, or a slash. That wasn't the problem. More
shortly.

I removed the CertExp = True oddity (an oversight), and still received the
syntax error message. I stripped the code (and the function) down to
checking for CertReturned, but it said I had an extra parentheses (or maybe
that I was missing one - I don't recall). That clearly was not the case, so
I created a new text box and put the function in its control source. This
time it worked.

I open the report from a command button. The command button code contains:

Dim strCertSQL as String

strCertSQL = "[CertReturned] = True and [CertExp] < Date()"

The strCertSQL is actually more complex than that, but it's the general
idea. strCertSQL is used as the Where part (after the last comma) of
DoCmd.OpenReport. The idea is that I will only include vendors whose
certificates need attention. If a vendor's cert expires in three months, I
don't need to know anything about it now, so the strCertSQL excludes that
vendor from the recordset. If there is a better way than the command
button's click event to filter the recordset I would be glad to hear about
it.

In any case, thanks for pointing me in the right direction. I would have
responded sooner, but I have been experimenting for a while. There is more
to this than I needed to explain, but some of the complexities left me
puzzled for a while. I thought I would need to ask for assistance, but I
eventually solved the difficulties (for now, at least).

Bruce,

I'm sorry, I can't immediately see what is causing this problem.

There is an oddity in your code...
CertExp = True
... where CertExp is a date - but I don't imagine this would lead to the
error you are receiving.

What is the actual data in the CertType field? Does it ever have an
apostrophe or parentheses or whatnot in there?

--
Steve Schapel, Microsoft Access MVP

I have had a chance to try out the code, but have not been able to make
any real progress. Here is what I have so far in a public function in a
code module:

Public Function FaxText(CertExp As Date, CertType As String, _
CertReturned As Boolean) As String

Dim TextType As Integer

' CertReturned = True until a cert has been requested. CertReturned
= True
' for certs that are current.

If CertExp = True And CertReturned = True Then
If CertExp < DateAdd("m", 1, Date) And CertExp >=
DateAdd("ww", -2, Date) Then
TextType = 3
End If
Else
If CertExp > DateAdd("ww", -2, Date) _
And CertExp <= DateAdd("m", -2, Date) Then
TextType = 2
Else
If CertExp > DateAdd("m", -2, Date) Then
TextType = 1
End If
End If
End If

Select Case TextType
Case 1
FaxText = "Your " & CertType & " is going to expire on " & _
Format(CertExp, "mmmm dd, yyyy") & _
". Please send an updated certificate."
Case 2
FaxText = "Did you get the request?"
Case 3
FaxText = "Your cert is seriously expired."
End Select

End Function

I used CertReturned instead of CertRequested, and DateAdd rather than
Date + or Date -, but other than that it seems to be what you suggested.
The code compiles OK, but when I attempt to use it as the control source
for an unbound text box on the report:
=FaxText([CertExp],[CertType],[CertRequested]) I receive the following
error message when I attempt to open the report:
Syntax error (comma) in query expression '[
=FaxText([CertExp],[CertType],[CertRequested])]'
If I attempt to open the report from a command button, I receive the same
error message, but because of error handling I can identify it as Error
3075.

Can you tell what I'm missing here?

 

[Steve Schapel]

Bruce,

Thanks a lot for the explanation. Very intreresting. I am pleased to
know that you have made progress. I still don't understand why it
worked in a new textbox but not in the old, but as long as the job is
done...

 

[BruceM]

I am working from a copy of the old database. I have noticed that sometimes
things just seem to get stuck, especially in a database that is modified
often (such as during development). Importing into a new database (which I
have done from time to time with this project as I clear away some of the
rubble) sometimes straightens things out, as does making a new control.
Name AutoCorrect causes phantom problems too, as I have learned here. As I
learn just what to expect from functions and so forth I should be able to
recognize the symptoms of an anomaly sooner, rather than wondering what I
have done wrong.

Bruce,

Thanks a lot for the explanation. Very intreresting. I am pleased to
know that you have made progress. I still don't understand why it worked
in a new textbox but not in the old, but as long as the job is done...

--
Steve Schapel, Microsoft Access MVP

In response to your reply in another part of this thread, I meant to say
that a command button on the form will open the report, not that it will
open the form.

CertType is a text field. Other than alphanumeric characters the data
may contain a colon, a hyphen, or a slash. That wasn't the problem.
More shortly.

I removed the CertExp = True oddity (an oversight), and still received
the syntax error message. I stripped the code (and the function) down to
checking for CertReturned, but it said I had an extra parentheses (or
maybe that I was missing one - I don't recall). That clearly was not the
case, so I created a new text box and put the function in its control
source. This time it worked.

I open the report from a command button. The command button code
contains:

Dim strCertSQL as String

strCertSQL = "[CertReturned] = True and [CertExp] < Date()"

The strCertSQL is actually more complex than that, but it's the general
idea. strCertSQL is used as the Where part (after the last comma) of
DoCmd.OpenReport. The idea is that I will only include vendors whose
certificates need attention. If a vendor's cert expires in three months,
I don't need to know anything about it now, so the strCertSQL excludes
that vendor from the recordset. If there is a better way than the
command button's click event to filter the recordset I would be glad to
hear about it.

In any case, thanks for pointing me in the right direction. I would have
responded sooner, but I have been experimenting for a while. There is
more to this than I needed to explain, but some of the complexities left
me puzzled for a while. I thought I would need to ask for assistance,
but I eventually solved the difficulties (for now, at least).