Why wont this code work? (send fax)

[Lee Taylor-Vaughan]

This code is to send a fax: which it does, but it sends all records in the
recordset to all the people.

e.g. there are 7 people in the query that is access (recordset). It sends
all customers info to every fax number, what am i doing wrong

in the open event of the report i have me.filter = strHospWhere (i
have a feeling that it is has something to do with this.)

Please, any pointers.

Lee




Function SendFax()
Dim dbsMICU As DAO.Database
Dim rstDisposNeeded As DAO.Recordset

Set dbsMICU = CurrentDb()
Set rstDisposNeeded = dbsMICU.OpenRecordset("qryDispoManagerFax", _
dbOpenDynaset)

If MsgBox("Do you want to send Dispo Faxes to ED's?", _
vbYesNo + vbQuestion, "Fax Dispos") = 6 Then
With rstDisposNeeded
Do Until .EOF
strHospWhere = "[Hosptial] = ![Hospital]"
If Len(![Fax]) > 0 Then

DoCmd.SendObject acSendReport, "rptDispoFax", acFormatRTF _
, "[fax: " & ![Fax] & "]", , , , , False

Else
'do nothing
End If
.MoveNext
Loop
End With
End If
rstDisposNeeded.Close

End Function

 

[Douglas J. Steele]

Because of the statement

Set rstDisposNeeded = dbsMICU.OpenRecordset("qryDispoManagerFax", _
dbOpenDynaset)

it'll send to every row of data returned from qryDispoManagerFax. Your
me.filter statement only applies to the form, not to that recordset.

You'll have to change that statement so that it too only returns the 7
people of interest.

 

[Lee Taylor-Vaughan]

I obtained the code from the knowledge base and tried to customize it a bit,
but i am still lost with it.
The knowledge base told me to put the me.filter string in the reports OnOpen
event. (no form is involved). With this, I am under the impression that each
time the report is opened, which ever record from the recordset is in the
loop at that time will open the report for that particular hospital. (or
that is what i want it to do).

I was also wondering if this string is correct

strHospWhere = "[Hosptial] = ![Hospital]"

I get mixed up with, when to use quotes etc. (hospital is a text field)

can you give me a bit of guidence on how i should change the statement?
I am not all that good with this type of code.

Set rstDisposNeeded = dbsMICU.OpenRecordset("qryDispoManagerFax", _
dbOpenDynaset)

If i dont get the code to work, i would at least like to understant how it
works, so could someone please explain each line for me.

thanks

Lee

Because of the statement

Set rstDisposNeeded = dbsMICU.OpenRecordset("qryDispoManagerFax", _
dbOpenDynaset)

it'll send to every row of data returned from qryDispoManagerFax. Your
me.filter statement only applies to the form, not to that recordset.

You'll have to change that statement so that it too only returns the 7
people of interest.

--
Doug Steele, Microsoft Access MVP

This code is to send a fax: which it does, but it sends all records in the
recordset to all the people.

e.g. there are 7 people in the query that is access (recordset). It sends
all customers info to every fax number, what am i doing wrong

in the open event of the report i have me.filter = strHospWhere (i
have a feeling that it is has something to do with this.)

Please, any pointers.

Lee




Function SendFax()
Dim dbsMICU As DAO.Database
Dim rstDisposNeeded As DAO.Recordset

Set dbsMICU = CurrentDb()
Set rstDisposNeeded = dbsMICU.OpenRecordset("qryDispoManagerFax", _
dbOpenDynaset)

If MsgBox("Do you want to send Dispo Faxes to ED's?", _
vbYesNo + vbQuestion, "Fax Dispos") = 6 Then
With rstDisposNeeded
Do Until .EOF
strHospWhere = "[Hosptial] = ![Hospital]"
If Len(![Fax]) > 0 Then

DoCmd.SendObject acSendReport, "rptDispoFax",

acFormatRTF

_
, "[fax: " & ![Fax] & "]", , , , , False

Else
'do nothing
End If
.MoveNext
Loop
End With
End If
rstDisposNeeded.Close

End Function

 

[Douglas J. Steele]

Just out of curiosity, what was the KB article?

If you filter a form or a report, you limit what appears on that form or
report. The problem is, you're not using the report's filtered recordset:
you're creating your own.

One way to change the recordset you're creating is to dynamically change the
SQL you use.

Rather than using

Set rstDisposNeeded = dbsMICU.OpenRecordset("qryDispoManagerFax", _
dbOpenDynaset)

set a string to the SQL represented by qryDispoManagerFax plus add an
appropriate WHERE clause to it. You can then use

Set rstDisposNeeded = dbsMICU.OpenRecordset(strSQL, _
dbOpenDynaset)

where strSQL is the SQL you created.

You probably want

strHospWhere = "[Hosptial] = " & ![Hospital]

if Hosptial is a numeric field, or

strHospWhere = "[Hosptial] = " & Chr$(34) & ![Hospital] & Chr$(34)

if it's a text field. However, you aren't using strHospWhere anywhere in
your code, so it strikes me as somewhat moot.

It sounds to me as though you're hoping that strHospWhere will limit what
appears on rptDispoFax when you send it. Unfortunately, I don't believe you
can pass a filter to a report in the DoCmd.SendObject command

--
Doug Steele, Microsoft Access MVP

I obtained the code from the knowledge base and tried to customize it a bit,
but i am still lost with it.
The knowledge base told me to put the me.filter string in the reports OnOpen
event. (no form is involved). With this, I am under the impression that each
time the report is opened, which ever record from the recordset is in the
loop at that time will open the report for that particular hospital. (or
that is what i want it to do).

I was also wondering if this string is correct

strHospWhere = "[Hosptial] = ![Hospital]"

I get mixed up with, when to use quotes etc. (hospital is a text field)

can you give me a bit of guidence on how i should change the statement?
I am not all that good with this type of code.

Set rstDisposNeeded = dbsMICU.OpenRecordset("qryDispoManagerFax", _
dbOpenDynaset)

If i dont get the code to work, i would at least like to understant how it
works, so could someone please explain each line for me.

thanks

Lee

Because of the statement

Set rstDisposNeeded = dbsMICU.OpenRecordset("qryDispoManagerFax", _
dbOpenDynaset)

it'll send to every row of data returned from qryDispoManagerFax. Your
me.filter statement only applies to the form, not to that recordset.

You'll have to change that statement so that it too only returns the 7
people of interest.

--
Doug Steele, Microsoft Access MVP

This code is to send a fax: which it does, but it sends all records in the
recordset to all the people.

e.g. there are 7 people in the query that is access (recordset). It sends
all customers info to every fax number, what am i doing wrong

in the open event of the report i have me.filter = strHospWhere (i
have a feeling that it is has something to do with this.)

Please, any pointers.

Lee




Function SendFax()
Dim dbsMICU As DAO.Database
Dim rstDisposNeeded As DAO.Recordset

Set dbsMICU = CurrentDb()
Set rstDisposNeeded = dbsMICU.OpenRecordset("qryDispoManagerFax", _
dbOpenDynaset)

If MsgBox("Do you want to send Dispo Faxes to ED's?", _
vbYesNo + vbQuestion, "Fax Dispos") = 6 Then
With rstDisposNeeded
Do Until .EOF
strHospWhere = "[Hosptial] = ![Hospital]"
If Len(![Fax]) > 0 Then

DoCmd.SendObject acSendReport, "rptDispoFax",

acFormatRTF

_
, "[fax: " & ![Fax] & "]", , , , , False

Else
'do nothing
End If
.MoveNext
Loop
End With
End If
rstDisposNeeded.Close

End Function

 

[Lee Taylor-Vaughan]

thanks for the tips and help; the article is 231797 "HOW TO: Fax from
Microsoft Access Using SendObject Command In Access 2000"

You are right, i am under the impression that the strHospWhere will limit to
that particular record, but clearly it is not working.

I shall go and try you suggestion and post back with result, thanks again

Lee

Just out of curiosity, what was the KB article?

If you filter a form or a report, you limit what appears on that form or
report. The problem is, you're not using the report's filtered recordset:
you're creating your own.

One way to change the recordset you're creating is to dynamically change the
SQL you use.

Rather than using

Set rstDisposNeeded = dbsMICU.OpenRecordset("qryDispoManagerFax", _
dbOpenDynaset)

set a string to the SQL represented by qryDispoManagerFax plus add an
appropriate WHERE clause to it. You can then use

Set rstDisposNeeded = dbsMICU.OpenRecordset(strSQL, _
dbOpenDynaset)

where strSQL is the SQL you created.

You probably want

strHospWhere = "[Hosptial] = " & ![Hospital]

if Hosptial is a numeric field, or

strHospWhere = "[Hosptial] = " & Chr$(34) & ![Hospital] & Chr$(34)

if it's a text field. However, you aren't using strHospWhere anywhere in
your code, so it strikes me as somewhat moot.

It sounds to me as though you're hoping that strHospWhere will limit what
appears on rptDispoFax when you send it. Unfortunately, I don't believe you
can pass a filter to a report in the DoCmd.SendObject command

--
Doug Steele, Microsoft Access MVP

I obtained the code from the knowledge base and tried to customize it a bit,
but i am still lost with it.
The knowledge base told me to put the me.filter string in the reports OnOpen
event. (no form is involved). With this, I am under the impression that each
time the report is opened, which ever record from the recordset is in the
loop at that time will open the report for that particular hospital. (or
that is what i want it to do).

I was also wondering if this string is correct

strHospWhere = "[Hosptial] = ![Hospital]"

I get mixed up with, when to use quotes etc. (hospital is a text field)

can you give me a bit of guidence on how i should change the statement?
I am not all that good with this type of code.

Set rstDisposNeeded = dbsMICU.OpenRecordset("qryDispoManagerFax", _
dbOpenDynaset)

If i dont get the code to work, i would at least like to understant how it
works, so could someone please explain each line for me.

thanks

Lee

Because of the statement

Set rstDisposNeeded = dbsMICU.OpenRecordset("qryDispoManagerFax", _
dbOpenDynaset)

it'll send to every row of data returned from qryDispoManagerFax. Your
me.filter statement only applies to the form, not to that recordset.

You'll have to change that statement so that it too only returns the 7
people of interest.

--
Doug Steele, Microsoft Access MVP



This code is to send a fax: which it does, but it sends all records

in
the

recordset to all the people.

e.g. there are 7 people in the query that is access (recordset). It sends
all customers info to every fax number, what am i doing wrong

in the open event of the report i have me.filter = strHospWhere (i
have a feeling that it is has something to do with this.)

Please, any pointers.

Lee




Function SendFax()
Dim dbsMICU As DAO.Database
Dim rstDisposNeeded As DAO.Recordset

Set dbsMICU = CurrentDb()
Set rstDisposNeeded =

dbsMICU.OpenRecordset("qryDispoManagerFax",

_

dbOpenDynaset)

If MsgBox("Do you want to send Dispo Faxes to ED's?", _
vbYesNo + vbQuestion, "Fax Dispos") = 6 Then
With rstDisposNeeded
Do Until .EOF
strHospWhere = "[Hosptial] = ![Hospital]"
If Len(![Fax]) > 0 Then

DoCmd.SendObject acSendReport, "rptDispoFax", acFormatRTF
_
, "[fax: " & ![Fax] & "]", , , , , False

Else
'do nothing
End If
.MoveNext
Loop
End With
End If
rstDisposNeeded.Close

End Function

 

[Lee Taylor-Vaughan]

Hi again,

i just re-read your reply and something struck me.

It sounds to me as though you're hoping that strHospWhere will limit what
appears on rptDispoFax when you send it. Unfortunately, I don't believe you
can pass a filter to a report in the DoCmd.SendObject command

The strHospWhere is put into the reports OnOpen Event as me.filter =
strHospWhere, and not really being passed to the report, but being used as
the report is being opened-as each strHospWhere is change every time the
code goes through the do until loop.

maybe what I could do, which really is paraphrasing part of your previous
statement is to open a rst and do a "for each next" statement (in each "for
each next" pass I could just have it do the sendobject, which would have its
own saved recordset in the report being sent, and then send the listing to
each hospital ... how would it get started on this? I can understand code
when written but i have no clue in knowing where to start when i need to
create it.

Thanks again

Lee

Just out of curiosity, what was the KB article?

If you filter a form or a report, you limit what appears on that form or
report. The problem is, you're not using the report's filtered recordset:
you're creating your own.

One way to change the recordset you're creating is to dynamically change the
SQL you use.

Rather than using

Set rstDisposNeeded = dbsMICU.OpenRecordset("qryDispoManagerFax", _
dbOpenDynaset)

set a string to the SQL represented by qryDispoManagerFax plus add an
appropriate WHERE clause to it. You can then use

Set rstDisposNeeded = dbsMICU.OpenRecordset(strSQL, _
dbOpenDynaset)

where strSQL is the SQL you created.

You probably want

strHospWhere = "[Hosptial] = " & ![Hospital]

if Hosptial is a numeric field, or

strHospWhere = "[Hosptial] = " & Chr$(34) & ![Hospital] & Chr$(34)

if it's a text field. However, you aren't using strHospWhere anywhere in
your code, so it strikes me as somewhat moot.

It sounds to me as though you're hoping that strHospWhere will limit what
appears on rptDispoFax when you send it. Unfortunately, I don't believe you
can pass a filter to a report in the DoCmd.SendObject command

--
Doug Steele, Microsoft Access MVP

I obtained the code from the knowledge base and tried to customize it a bit,
but i am still lost with it.
The knowledge base told me to put the me.filter string in the reports OnOpen
event. (no form is involved). With this, I am under the impression that each
time the report is opened, which ever record from the recordset is in the
loop at that time will open the report for that particular hospital. (or
that is what i want it to do).

I was also wondering if this string is correct

strHospWhere = "[Hosptial] = ![Hospital]"

I get mixed up with, when to use quotes etc. (hospital is a text field)

can you give me a bit of guidence on how i should change the statement?
I am not all that good with this type of code.

Set rstDisposNeeded = dbsMICU.OpenRecordset("qryDispoManagerFax", _
dbOpenDynaset)

If i dont get the code to work, i would at least like to understant how it
works, so could someone please explain each line for me.

thanks

Lee

Because of the statement

Set rstDisposNeeded = dbsMICU.OpenRecordset("qryDispoManagerFax", _
dbOpenDynaset)

it'll send to every row of data returned from qryDispoManagerFax. Your
me.filter statement only applies to the form, not to that recordset.

You'll have to change that statement so that it too only returns the 7
people of interest.

--
Doug Steele, Microsoft Access MVP



This code is to send a fax: which it does, but it sends all records

in
the

recordset to all the people.

e.g. there are 7 people in the query that is access (recordset). It sends
all customers info to every fax number, what am i doing wrong

in the open event of the report i have me.filter = strHospWhere (i
have a feeling that it is has something to do with this.)

Please, any pointers.

Lee




Function SendFax()
Dim dbsMICU As DAO.Database
Dim rstDisposNeeded As DAO.Recordset

Set dbsMICU = CurrentDb()
Set rstDisposNeeded =

dbsMICU.OpenRecordset("qryDispoManagerFax",

_

dbOpenDynaset)

If MsgBox("Do you want to send Dispo Faxes to ED's?", _
vbYesNo + vbQuestion, "Fax Dispos") = 6 Then
With rstDisposNeeded
Do Until .EOF
strHospWhere = "[Hosptial] = ![Hospital]"
If Len(![Fax]) > 0 Then

DoCmd.SendObject acSendReport, "rptDispoFax", acFormatRTF
_
, "[fax: " & ![Fax] & "]", , , , , False

Else
'do nothing
End If
.MoveNext
Loop
End With
End If
rstDisposNeeded.Close

End Function

 

[Douglas J. Steele]

Looking at the KB article, what you're doing makes a little more sense now.
I see that strHospWhere is a global variable, so if you've put it into the
report's OnOpen event, it will limit what gets shown on the report. (You do
need to make the modification I said earlier about the quotes, though) That
should ensure that the correct Fax is sent to each recipient, so all you
should need to worry about now is getting the list of correct recipients.

As I stated before, you'd do this by changing how you open rstDisposNeeded.
But I'm afraid that without more information about the SQL that makes up
qryDispoManagerFax, I can't offer anything more concrete.

 

[Lee Taylor-Vaughan]

The SQL used in the qryDispoManagerFax is:

SELECT [Receiving Hospital].RecHospID, [Receiving Hospital].ERFaxNumber AS
FAX
FROM TblDispatch LEFT JOIN (TblPatients LEFT JOIN [Receiving Hospital] ON
TblPatients.ReceivingHospital = [Receiving Hospital].ReceivingHospital) ON
TblDispatch.DispatchID = TblPatients.DispatchID
WHERE (((DateDiff("d",CDate(Format([dispatchdate],"Short Date") & " " &
Format([DispatchAvailable],"Short Time")),Now()))<20) AND
((TblPatients.ReceivingHospital)<>"None") AND
((TblPatients.Support)<>"Refused Medical Treatment") AND
((IIf([support]="release to bls","N/A",[ALSreleasestatus])) Is Null)) OR
(((DateDiff("d",CDate(Format([dispatchdate],"Short Date") & " " &
Format([DispatchAvailable],"Short Time")),Now()))<20) AND
((TblPatients.ReceivingHospital)<>"None") AND
((TblPatients.Support)<>"Refused Medical Treatment") AND
((IIf([Support]="Advanced Life Support","N/A",[BLSReleaseStatus])) Is Null))
GROUP BY [Receiving Hospital].RecHospID, [Receiving Hospital].ERFaxNumber;

The SQL for the report is the same as above but it is not a GroupBy and also
there is the Patients Name Fields on the report.


Please dont solve the whole issue for me, but instead give me pointers to
look at so that i can have a shot of fixing it.

Thanks

lee

 

[Lee Taylor-Vaughan]

I feel like such a berk! i just looked at the report and saw that i had the
Filter property set to "off"!!! Once i turned it on it worked just fine. I
also had to remove the ' from the global variable as is did not like it and
gave and error of an incompatible variable type.

Thanks for all you help any how.... This has been a great learning
experience.


lee