SQL statement

J

Jessica

Hello Everyone,

I am stumped and need help. I have a list box with the following code
but it would not allow multiple selctions to be viewed as reports. Mark
has suggested a SQL statement and has posted part of it to get me
started but I don't relly understand what the SQL statement is saying to
finish the rest of it. Below is the code I had that wouldn't allow
multiple reports to be viewed and below that is the code Mark suggested.
Any help would be greatly appreciated.

Thank you,
Jessica

Private Sub Preview_Click()
Dim varSelectedUPC As Variant
Dim StrUPC As String

For Each varSelectedUPC In UPC.ItemsSelected
StrUPC = UPC.ItemData(varSelectedUPC)
Select Case StrUPC
Case "06010 11292" To "06010 11297", "06010 11588", "76808
52094", "76808 52138"
DoCmd.OpenReport "Barilla", acViewPreview, , "UPC = '" &
StrUPC & "'"
'preview report; change to acViewormal to print immediately
Case "41129 07764" To "41129 27463"
DoCmd.OpenReport "Classico", acViewPreview, , "UPC = '" &
StrUPC & "'"
Case "39400 01440" To "39400 01443"
DoCmd.OpenReport "Bush", acViewPreview, , "UPC = '" & StrUPC
& "'"
Case "64200 32716" To "64200 32723"
DoCmd.OpenReport "Classico Canadian", acViewPreview, , "UPC =
'" & StrUPC & "'"
Case Else

DoCmd.OpenReport "Others", acViewPreview, , "UPC = '" &
StrUPC & "'"
End Select
Next varSelectedUPC
End Sub
 
S

Sergey Poberezovskiy

Jessica,

I do not quite understand what you are trying to achieve:
by reading your code looks like you are opening multiple
reports in preview mode at the same time, while the later
selection in your list box may overwrite the previously
opened reports.

If you do need to more than one report being previewed at
the same time, you may need to follow slightly modified
code from Mark:


Private Sub addFilter(ByRef filter As String, ByVal value
As String)
If Len(filter) > 0 Then
filter = filter & ", "
End If
filter = filter & "'" & value & "'"
End Sub

Sub previewReports()
Dim filters() As String
Dim upcValue As String
Dim reportNames() As String
Dim varSelectedUPC As Variant
Dim i As Integer
reportNames = Array
("Barilla", "Classico", "Bush", "Classico
Canadian", "Others")
ReDim filters(UBound(reportNames))
' construct the where clause
For Each varSelectedUPC In UPC.ItemsSelected
upcValue = UPC.ItemData(varSelectedUPC)
Select Case upcValue
Case "06010 11292" To "06010 11297", "06010
11588", "76808 52094", "76808 52138"
addFilter filters(0), upcValue
Case "41129 07764" To "41129 27463"
addFilter filters(1), upcValue
Case "39400 01440" To "39400 01443"
addFilter filters(2), upcValue
Case "64200 32716" To "64200 32723"
addFilter filters(3), upcValue
Case Else
addFilter filters(4), upcValue
End Select
Next
' Preview the reports
For i = 0 To UBound(reportNames)
If Len(filters(i)) > 0 Then
DoCmd.OpenReport reportNames(i), acViewPreview,
WhereCondition:="In (" & filters(i) & ")"
End If
Next
End Sub


HTH
 
J

Jessica

Hi Sergey,

Forgive me for asking I have been entering these codes as an event
procedure for a button on my form after upc's have been selected, would
I do the same for this code? I have tried opening several reports with
the event procedure I had before and it does open several reports but
not if two upc use the same report.

Thank you
Jessica
 
S

Sergey Poberezovskiy

Jessika,

I have not seen the original post that Mike relied to, and
do not quite understand your question.

Can you please describe what you are trying to achieve -
the whole task?

And what do you nean that same UPS can use more than one
report?
 
J

Jessica

Hi Sergey

I have a form with a list box that contains several UPC numbers from a
table I have. Right now I have atleast 5 different reports that have
different fields drawn from different queries. More than 1 UPC can use
the same report. I want to set it up so that if a user selects lets say
5 UPC from the list box and they are directed to the same report that
all 5 reports will be able to be viewed. Below I have a event procedure
that I used and everything works except that if more than 1 UPC is
selected and it is directed to the same report that it on shows the
first one.

Private Sub Preview_Click()
Dim varSelectedUPC As Variant
Dim StrUPC As String

For Each varSelectedUPC In UPC.ItemsSelected
StrUPC = UPC.ItemData(varSelectedUPC)
Select Case StrUPC
Case "06010 11292" To "06010 11297", "06010 11588", "76808
52094", "76808 52138"
DoCmd.OpenReport "Barilla", acViewPreview, , "UPC = '" &
StrUPC & "'"
'preview report; change to acViewormal to print immediately
Case "41129 07764" To "41129 27463"
DoCmd.OpenReport "Classico", acViewPreview, , "UPC = '" &
StrUPC & "'"
Case "39400 01440" To "39400 01443"
DoCmd.OpenReport "Bush", acViewPreview, , "UPC = '" & StrUPC
& "'"
Case "64200 32716" To "64200 32723"
DoCmd.OpenReport "Classico Canadian", acViewPreview, , "UPC =
'" & StrUPC & "'"
Case Else

DoCmd.OpenReport "Others", acViewPreview, , "UPC = '" &
StrUPC & "'"
End Select
Next varSelectedUPC
End Sub


Thanks,
Jess
 
S

Sergey Poberezvoskiy

Jessica,

Are you trying to open mltile copies of the same report?

If so, then you will need to make sure that every such
report has VBA module (HasModule = True)

Set rpt = New Report_Report1
rpt.Visible = True

HTH
 
S

Sergey Poberezovskiy

Jessica,
Are you trying to open mltile copies of the same report?

If so, then you will need to make sure that every such
report has VBA module (HasModule = True)
Click to expand...
Set rpt = New Report_Report1
rpt.Filter = myFilterString
rpt.FilterOn = True
rpt.Visible = True
 
J

Jessica

Sergey,

I'm lost. Where do I put this code > Set rpt = New Report_Report1
rpt.Filter = myFilterString
rpt.FilterOn = True
rpt.Visible = True
Click to expand...

The code below works except when I select 06010 11292 and 06010 11297
from my list box it only shows one of them as a report.

Thanks,
Jess
 
S

Sergey Poberezovskiy

Jessika,

in your event proc:

Private Sub Preview_Click()
Dim rpt As Report
....
For Each varSelectedUPC In UPC.ItemsSelected
...
Select Case StrUPC
Case "06010 11292" To "06010 11297", "06010
11588", "76808 52094", "76808 52138"
Set rpt = New Report_Barilla
rpt.Filter = "UPC = '" & StrUPC & "'"
Case ...
End Select
rpt.FilterOn = True
rpt.Visible = True
Next

HTH.
 
J

Jessica

Sergey,

Does this look correct?



Private Sub Preview_Click()
Dim varSelectedUPC As Variant
Dim StrUPC As String

For Each varSelectedUPC In UPC.ItemsSelected
StrUPC = UPC.ItemData(varSelectedUPC)
Select Case StrUPC
Case "06010 11292" To "06010 11588", "76808 52094", "76808 52138"
DoCmd.OpenReport "Barilla", acViewPreview, , "UPC = '" & StrUPC
& "'"
Set rpt = New Report_Barilla
rpt.Filter = "UPC = '" & StrUPC & "'"
'preview report; change to acViewormal to print immediately
Case "41129 07763" To "41129 27463"
DoCmd.OpenReport "Classico", acViewPreview, , "UPC = '" &
StrUPC & "'"
Case "39400 01440" To "39400 01443"
DoCmd.OpenReport "Bush", acViewPreview, , "UPC = '" & StrUPC
& "'"
Case "64200 32716" To "64200 32723"
DoCmd.OpenReport "Classico Canadian", acViewPreview, , "UPC =
'" & StrUPC & "'"
Case "74683 09925" To "74683 09950"
DoCmd.OpenReport "Emeril", acViewPreview, , "UPC = '" &
StrUPC & "'"
Case Else

DoCmd.OpenReport "Others", acViewPreview, , "UPC = '" &
StrUPC & "'"
End Select
rpt.FilterOn = True
rpt.Visible = True
Next varSelectedUPC
End Sub
 
S

Sergey Poberezovskiy

I do not think that you need to use DoCmd.OpenReport -
have not tested though...
 

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

event procedure 2
view multiple reports 3
Case Statement 0
case statement question please help 6
Case function 4

Top