Re: Combo Box

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Syntax error in date in query expression '[gbuLocation] and [issueclosedate]
between ## and ##'.

The [issueclosedate] = issueclosedate between #" & Me.txtStart & "# and #" &
Me.txtEnd & "#"

The [gbuLocation] = stLinkCriteria = "[gbulocation] " & stAreaList & " and
[insurancetype] " & stProductList


"Douglas J Steele" wrote:

The text that accompanies the error should give some clue. What does it say?


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Dan @BCBS" <DanBCBS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4AE2C398-62EB-4094-86CD-205B8BAE871E@xxxxxxxxxxxxxxxx
Thanks for your time and patience.
No, to your first paragraph.
Yes, your assumptions are correct.

Everything except the Call seems to work correctly..

I assume this is where you tell me to call my report
(rptQuualityAuditList)..
I've tried a few combinations, most recent:

Dim stDocName As String
If.........
stDocName = "rptQualityAuditList"
DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True

Which gives me a runtime error '3075'








"Douglas J. Steele" wrote:

You missed my point. Your code isn't doing anything! You're not trying
to
tell me that you get different results depending on what values are in
txtStar or txtEnd, or what's been selected in lstArea are you?

Without knowing the names of your table fields and form controls, it's
difficult for me to give you a precise answer.

I'll make the following assumptions:

- you want to compare what's in numeric field Field1 to what's been
selected
in list box Listbox1
- you want to compare what's in text field Field2 to what's been
selected in
list box Listbox2
- you want to compare what's in text field Field3 to what's been
selected in
combo box Combobox1
- you want to launch report MyReport, but only if at least 1 record has
been
selected in Listbox1, at least one record has been selected in Listbox2
and
a record has been selected in Combobox1

That out of the way, you need code like:

Dim strError As String
Dim strWhere As String
Dim varItem As Variant

If Me.Listbox1.ItemsSelected.Count = 0 Then
strError = "Please pick an item in Listbox1." & vbCrLf
End If
If Me.Listbox2.ItemsSelected.Count = 0 Then
strError = strError & "Please pick an item in Listbox2." & vbCrLf
End If
If IsNull(Me.Combobox1) = True Then
strError = strError & "Please pick an item in Combobox2." & vbCrLf
End If

If Len(strError) > 0 Then
Msgbox strError
Else
strWhere = "Field1 In ("
For Each varItem In Me.Listbox1.ItemsSelected
strWhere = strWhere & Me.Listbox1.ItemData(varItem) & ", "
Next varItem
strWhere = Left(strWhere, Len(strWhere) - 2) & ") AND Field2 In ("
For Each varItem In Me.Listbox2.ItemsSelected
strWhere = strWhere & Chr$(34) & Me.Listbox1.ItemData(varItem)
& _
Chr$(34) & ", "
Next varItem
strWhere = Left(strWhere, Len(strWhere) - 2) & ") AND " & _
"Field2 = " & Chr$(34) & Me.Combobox1 & Chr$(34)
Call SetReportFilter("MyReport", strWhere)
DoCmd.SendObject acReport, "MyReport", acFormatRTF, , , , , , True
End If



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Dan @BCBS" <DanBCBS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EF595C7C-249E-4B11-A42C-83B26FF65CE6@xxxxxxxxxxxxxxxx
First, please understand that I really appreciate you Crystal and this
MSN
format to get help. I inharited many critial Access databases, some
very
old, many people have worked on them. I completly understand your
comments
about this code, I agree, but it produces an out that is being used
with
some
degree of correctness.

I just need to re-write it, bottom line is, I need output based on
2-List
boxes and 1-Combo Box...

Thanks for the help

"Douglas J Steele" wrote:

If you were using DoCmd.OpenReport, you'd add the reviewer details to
what's
already defined for stLinkCriteria. However, since you're trying to
mail
the
report, you can't use DoCmd.OpenReport.

What Crystal's given you is how to associated a filter with a report.
Once
you know what you would be passing as stLinkCriteria, you'd pass the
name
of
the report and stLinkCriteria to Crystal's routine. Then, when you
open
the
report, it will be limited to whatever was provided in
stLinkCriteria. In
other words, that's all that would be on the report when you use
DoCmd.SendObject.

I don't for the life of me understand what your existing code is
supposed
to
be accomplishing. You're doing all sorts of fussing, checking whether
a
start date and end date have been provided, and looking to see what's
selected in a list box of areas, but you're not using start date, end
date
or area anywhere!

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Dan @BCBS" <DanBCBS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:725EAD66-AE86-425D-B8C2-95D70016244E@xxxxxxxxxxxxxxxx
My appoligies to Crystal.....
Yes, the code I posted is the code I currently run.

As you noted, it uses the DoCmd.SendObject - so the user can just
email
the
results..
Based on your comments I have added a DoCmd.OpenReport - so the
user
can
do
either.

But in both cases I am struggling with the results specific to the
Reviewer
(which is the combo box "cboReviewer")

I'm not sure how to plug the where "cboReviewer" into:
"DoCmd.OpenReport stDocName, acNormal, , stLinkCriteria"



"Douglas J Steele" wrote:

Yes, Crystal's attempting to answer your question. You're trying
to
create a
filtered report, but nowhere are you passing the criteria you've
generated
to the report. If you were using DoCmd.OpenReport, you could
specific
a
Where clause, but you don't have that option using
DoCmd.SendObject
to
send
a report. That means you need to open the report in design mode
and
save
the
criteria as a filter that will be used the next time you run the
report.

Or is the code you posted not the actual code you're trying to
get
running?


For what it's worth,

stPerson = stPerson = " & Me.cboReviewer & "

is definitely incorrect.

You need something like:

If IsNull(Me.cboReviewer) Then
MsgBox "Please pick a reviewer."
Exit Sub
Else
stCriteria = "Person = " & Chr$(34) & Me.cboReviewer &
Chr$(34)
End If

This assumes Me.cboReviewer returns a name. If it returns a
number,
remove
the two & Chr$(34)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Dan @BCBS" <DanBCBS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:546550FF-0651-466B-8914-A08B19231458@xxxxxxxxxxxxxxxx
Are you answering the same question I asked>????
If so, I'm not following, why I would need to add all this
code..
All I need is "If a certain person is picked from a combo box
print
only
those records"
I have been trying combinations like this, but I keep getting
everyone's
records..


If IsNull(Me.cboReviewer) Then
MsgBox "Please pick a reviewer."
Exit Sub
Else
stPerson = stPerson = " & Me.cboReviewer & "
End If




"strive4peace" wrote:

Before you use
SendObject
you need to SAVE the filter to the report. Here is a
general procedure you can use:

'~~~~~~~~~~~~~~~~~~~~~

Sub SetReportFilter( _
ByVal pReportName As String, _
ByVal pFilter As String)

' written by Crystal
' Strive4peace2007 at yahoo dot com

' PARAMETERS:
' pReportName is the name of your report
' pFilter is a valid filter string

' USEAGE:
' SetReportFilter "MyReportname","someID=1000"
' SetReportFilter "MyAppointments", _
"City='Denver' AND dt_appt=#9/18/05#"

On Error Goto Proc_Err

'---------- declare variables
Dim rpt As Report

'---------- open design view of report
' --- and set the report object variable

DoCmd.OpenReport pReportName, acViewDesign
Set rpt = Reports(pReportName)

'---------- set report filter and turn it on
.

Relevant Pages

  • Re: searching for names - multiple names per record
    ... Doug Steele, Microsoft Access MVP ... (no private e-mails, please) ... Developer Mary Brown ...
    (microsoft.public.access.gettingstarted)
  • Re: searching for names - multiple names per record
    ... Doug Steele, Microsoft Access MVP ... (no private e-mails, please) ... Developer Mary Brown ...
    (microsoft.public.access.gettingstarted)
  • Re: Combo Box
    ... Doug Steele, Microsoft Access MVP ... I assume this is where you tell me to call my report ... Dim stDocName As String ...
    (microsoft.public.access.formscoding)
  • Re: Problem with Call Shell
    ... Doug Steele, Microsoft Access MVP ... (no e-mails, please!) ... Dim stAppName As String ...
    (microsoft.public.access.formscoding)
  • Re: email pdf document
    ... Doug Steele, Microsoft Access MVP ... (no e-mails, please!) ... ' Create new instance of Outlook or open current instance. ...
    (microsoft.public.access.forms)