Syntax problem for text criteria to filter a report

[vavroom]

Hello everyone,

I need assistance with filtering a report. It seems like such a silly
thing, but I can't seem to get the syntax right

I have a form with a combobox. I need the report to filter based on
the value of the combobox. The combobox's value is text (names), in
the format "Doe, John".

I know I am referencing the combo box properly because I can put a
textbox on the report that calls the value of the combobox correctly.

But when I try to filter, it just won't take. I've read on this
forum, read the help files, etc, I have tried a variety of variations
of single quotes, double quotes, etc, I just can't get my head around
the proper syntax.

The value I seek is:
Forms![frmSendPDF].cmbCoord.column(0)

The field in the report that has to be filtered is called
Coord

Ideally, I would like to apply the filter to the report from the form,
and was thinking about using something like this:
Reports![rptSendPDF].filter= ([Coord]= '" & Forms!
[frmSendPDF].cmbCoord.Value & "')

Thank you for any and all help you might be able to provide.

Nic

 

[BruceM]

Try:

Forms![frmSendPDF]!cmbCoord.column(0)

or maybe:

Forms![frmSendPDF]!cmbCoord

The column number is needed only if it is not the bound column, although it
does no harm if it is the bound column.

After establishing the filter you need to turn it on:

Me.FilterOn = True

I am not certain about applying a filter directly to a report, but I think
it would be in the Open event. I am uncertain because I don't think I have
filtered a report that way.

However, rather than applying a filter you may want to do something like
this in the event on the form used to open the report:

Dim strRptName as String, strCrit as String

strRptName = "frmSendPDF"
strCrit = Me.cmbCoord

DoCmd.OpenReport strRptName, acViewPreview, , strCrit

Another possiblilty would be to use (in whatever syntax works):

Forms![frmSendPDF]!cmbCoord.column(0)

As the criteria for a field in the report's Record Source query. Again, I
prefer to pass the value from a form because I find it to be the simplest,
but I must admit I don't know all the pros and cons or the various fitlering
options.

 

[vavroom]

Hi Bruce, thanks for your response.

Try:

Forms![frmSendPDF]!cmbCoord.column(0)

Yes, that works, to call the field on the report. But that's not what
I'm struggling with. If I use that in a filter, it returns nothing.
So my assumption is that I am not writing the filter properly.

After establishing the filter you need to turn it on:

Me.FilterOn = True

Yes, got that as well, should have mentionned it.

However, rather than applying a filter you may want to do something like
this in the event on the form used to open the report:

DoCmd.OpenReport strRptName, acViewPreview, , strCrit

Unfortunately, I am not using DoCmd, I'm using a different thing that
actually prints the report to PDF. In fact, what i am doing is
creating a report, printing it to PDF and attaching it to an email,
based on one or multiple recipients selected in my combo box. I have
everything working like a charm, except for the limiting the report to
the information relevant to the recipient.

Again, I
prefer to pass the value from a form because I find it to be the simplest,
but I must admit I don't know all the pros and cons or the various fitlering
options.

I'd rather pass the value from the form, in my routine But am at a bit
of a loss, being that I'm just kludging code together

(note, the following is not particularly elegant )

---
Private Sub cmdEmail_Click()
Dim Response
Dim varItem As Variant
Dim stSendto As String
Dim stMess As String
Dim stSubj As String
Dim stName As String
Dim stLName As String
Dim stBody As String
Dim stIntro As String
Dim stSig As String
Dim stReport As String
Dim strAttach As String

stReport = "rptNotifyCoordinatorArrangements"
stSig = vbNewLine & vbNewLine & "Thank you," & vbNewLine & vbNewLine &
"Nic"

If IsNull(Me.txtMess) Then
stMess = ""
Else
stMess = Me.txtMess
End If

If IsNull(Me.txtSubj) Then
stSubj = ""
Else
stSubj = Me.txtSubj
End If

varYear = DatePart("yyyy", Me.txtDate)
varMonth = DatePart("m", Me.txtDate)
varDay = DatePart("d", Me.txtDate)
varDateAgr = varYear & "-" & varMonth & "-" & varDay

strPath = "C:\Documents and Settings\user\Desktop\" & varDateAgr &
"\"
If Len(Dir(strPath, vbDirectory)) = 0 Then
MkDir strPath
End If

With Me!cmbCoord
For Each varItem In .ItemsSelected
stSendto = .Column(2, varItem)
stLName = .Column(1, varItem)
stName = .Column(0, varItem)
stIntro = "Hello " & stName & "," & vbNewLine & vbNewLine
stBody = stIntro & stMess & stSig
strAttach = strPath & varDateAgr & "-" & stLName & ".pdf"
Dim blRet As Boolean
blRet = ConvertReportToPDF(stReport, vbNullString,
strAttach, False, False, 0, "", "", 0, 0)
Call SendOutlookMessage(stSendto, "", "", stSubj, stBody,
True, strAttach)
Next varItem
End With

End Sub

 

[vavroom]

Well! I'm getting closer to a working solution

I now use the Docmd.OpenReport to pass the filter, then when I'm done,
I close the report. This works exactly how I want it, if I select
only one person in my list.

However, if I select two or more people, the only filter that seems to
apply is the last one in the selected items.
===
With Me!cmbCoord
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
stSendto = .Column(3, varItem)
stLName = .Column(2, varItem)
stName = .Column(1, varItem)
stIntro = "Hello " & stName & "," & vbNewLine & vbNewLine
stBody = stIntro & stMess & stSig
strAttach = strPath & varDateAgr & "-" & stLName & ".pdf"
Dim blRet As Boolean
Dim stFilter As String
stFilter = "[Coord] = '" & Forms!
[frmSendPDF].cmbCoord.Column(0) & "'"
DoCmd.OpenReport stReport, acViewPreview, , stFilter
blRet = ConvertReportToPDF(stReport, vbNullString,
strAttach, False, False, 0, "", "", 0, 0)
DoCmd.Close acReport, stReport, acSaveNo
Call SendOutlookMessage(stSendto, "", "", stSubj, stBody,
True, strAttach)
Next varItem
End If
End With
===
I thought that With with cycle through, look at the selected item, do
it's thing, and move to the next one, but that seems not to be the
case, what am I missing?

Thanks again for any and all assistance

 

[BruceM]

Comments inline. First, though, if you have not done so you should add
"Option Explicit" under Option Compare Database at the top of the module.
This will require variable declaration. Maybe some of these variables are
included in the Declarations section, but there is no way for me to know
that. In the VBA editor, go to Tools >> Options. Click the Editor tab, and
be sure the box is checked for Require Variable Declaration.

Hi Bruce, thanks for your response.

Try:

Forms![frmSendPDF]!cmbCoord.column(0)

Yes, that works, to call the field on the report. But that's not what
I'm struggling with. If I use that in a filter, it returns nothing.
So my assumption is that I am not writing the filter properly.

After establishing the filter you need to turn it on:

Me.FilterOn = True

Yes, got that as well, should have mentionned it.

However, rather than applying a filter you may want to do something like
this in the event on the form used to open the report:

DoCmd.OpenReport strRptName, acViewPreview, , strCrit

Unfortunately, I am not using DoCmd, I'm using a different thing that
actually prints the report to PDF. In fact, what i am doing is
creating a report, printing it to PDF and attaching it to an email,
based on one or multiple recipients selected in my combo box. I have
everything working like a charm, except for the limiting the report to
the information relevant to the recipient.

Again, I
prefer to pass the value from a form because I find it to be the
simplest,
but I must admit I don't know all the pros and cons or the various
fitlering
options.

I'd rather pass the value from the form, in my routine But am at a bit
of a loss, being that I'm just kludging code together

(note, the following is not particularly elegant )

---
Private Sub cmdEmail_Click()
Dim Response
Dim varItem As Variant
Dim stSendto As String
Dim stMess As String
Dim stSubj As String
Dim stName As String
Dim stLName As String
Dim stBody As String
Dim stIntro As String
Dim stSig As String
Dim stReport As String
Dim strAttach As String

stReport = "rptNotifyCoordinatorArrangements"
stSig = vbNewLine & vbNewLine & "Thank you," & vbNewLine & vbNewLine &
"Nic"

If IsNull(Me.txtMess) Then
stMess = ""
Else
stMess = Me.txtMess
End If

This could be:
stMess = Nz(Me.txtMess,"")

If IsNull(Me.txtSubj) Then
stSubj = ""
Else
stSubj = Me.txtSubj
End If

Same as above.

varYear = DatePart("yyyy", Me.txtDate)
varMonth = DatePart("m", Me.txtDate)
varDay = DatePart("d", Me.txtDate)
varDateAgr = varYear & "-" & varMonth & "-" & varDay

I don't see varYear etc. established as variants (or anything) in this code.
Even if this is done in the Declarations section of the module, why?
Couldn't you just do:
varDateAgr = Format(Me.txtDate,"yyyy-m-d"

strPath = "C:\Documents and Settings\user\Desktop\" & varDateAgr &
"\"

Variable declaration for strPath?

If Len(Dir(strPath, vbDirectory)) = 0 Then
MkDir strPath
End If

With Me!cmbCoord
For Each varItem In .ItemsSelected

I would expect cmbCoord to be a combo box, based on the name. It would need
to be a list box for there to be several items. If it is a multi-select
list box and you are building an array, I am frankly out of my depth. If it
is a combo box, that is why you only have one name.

stSendto = .Column(2, varItem)
stLName = .Column(1, varItem)
stName = .Column(0, varItem)
stIntro = "Hello " & stName & "," & vbNewLine & vbNewLine
stBody = stIntro & stMess & stSig
strAttach = strPath & varDateAgr & "-" & stLName & ".pdf"

From what I can tell, strPath includes varDateAgr already. I think this
will append varDateAgr to the end of that.
That is, strPath is already:
"C:\Documents and Settings\user\Desktop\2008-5-22\"
Now strAttach is:
"C:\Documents and Settings\user\Desktop\2008-5-22\2008-5-22-Vavroom.pdf"
Is this the intention? And do you really want to make a new folder on the
desktop for each day (if that is what is going on here)?

Dim blRet As Boolean
blRet = ConvertReportToPDF(stReport, vbNullString,
strAttach, False, False, 0, "", "", 0, 0)
Call SendOutlookMessage(stSendto, "", "", stSubj, stBody,
True, strAttach)
Next varItem
End With

End Sub

I'm not clear on the way in which the information is not limited to the
intended recipient. Is it the message itself, or the pdf file, or what
exactly?

 

[vavroom]

Hello Bruce,

Thanks for taking the time to go through my code, it's much
appreciated, and my apologies for the delay in coming back to you.

This will require variable declaration. Maybe some of these variables are
included in the Declarations section, but there is no way for me to know
that.

Yes, variables are included, I skipped that here, though I should have
mentionned something to that effect.

This could be:
stMess = Nz(Me.txtMess,"")

Thank you! Told you my stuff was not elegant at all This is good,
much appreciated.

I don't see varYear etc. established as variants (or anything) in this code.
Even if this is done in the Declarations section of the module, why?
Couldn't you just do:
varDateAgr = Format(Me.txtDate,"yyyy-m-d"

Hmm, I guess I could, never thought about it. I'm really just
kludging code together as I can.

I would expect cmbCoord to be a combo box, based on the name. It would need
to be a list box for there to be several items. If it is a multi-select
list box and you are building an array, I am frankly out of my depth. If it
is a combo box, that is why you only have one name.

It is a list box and I must have had a brain fart when I named it like
that. I'll change it.

"C:\Documents and Settings\user\Desktop\2008-5-22\2008-5-22-Vavroom.pdf"
Is this the intention? And do you really want to make a new folder on the
desktop for each day (if that is what is going on here)?

Yes, this is the intention. There's method to the madness, and some
of it is out of my control. Although it's not actually going to the
desktop, it's going on a server, but during development/testing, it's
just easier to plop everything on the desktop

I'm not clear on the way in which the information is not limited to the
intended recipient. Is it the message itself, or the pdf file, or what
exactly?

The message is fine, the pdf file is named properly, etc. However,
the content of the PDF does not filter correctly when there is more
than one person selected in the list box. If I do one person at a
time, no problem. If I have two or more, the filter applied to the
report is always the last person selected in the list box.

 

[BruceM]

I'm afraid the business of creating pdf attachments is outside my
experience. I answered the original question because it was about filtering
the report, but I do not know the mechanism by which the pdf file is
created. It seems to me that the problem lies there, in that it processes
the pdf file only once rather than for each record. If so, the issue may be
that ConvertReportToPDF (Lebans custom function, I think) doesn't handle
multiple pdf files in the way you want. This is just a guess; I don't know
if it is the case. I don't have time to sort through the code, but from
what I can see it works with a single snapshot file. I don't know if this
is correct, and if it is I don't know what to do about it. I wish I could
be more help. If nobody jumps in on this thread, I would suggest reposting
with the CovertReportToPDF function as part of the question.

 

[Nicholas Meyer]

cool

I'm afraid the business of creating pdf attachments is outside my
experience. I answered the original question because it was about
filtering the report, but I do not know the mechanism by which the pdf
file is created. It seems to me that the problem lies there, in that it
processes the pdf file only once rather than for each record. If so, the
issue may be that ConvertReportToPDF (Lebans custom function, I think)
doesn't handle multiple pdf files in the way you want. This is just a
guess; I don't know if it is the case. I don't have time to sort through
the code, but from what I can see it works with a single snapshot file. I
don't know if this is correct, and if it is I don't know what to do about
it. I wish I could be more help. If nobody jumps in on this thread, I
would suggest reposting with the CovertReportToPDF function as part of the
question.

 

[vavroom]

Thank you Bruce, much appreciated