HELP HELP HELP HELP List box

[Julius]

I am pulling my hair out going crazy with this. I have two weeks before my
project is due. I need List box help. I have read everything plus the
reference website and I am lost.

All I want to do is use a Multiselect List box to run my reports based on
that list selection. Is there a very very very simple way to do this without
all the hoopla. If I create a Pass Parameter to Query what is the easiest
way to do this, if I have to write code how do I do it without needing a 10
year programming degree. Please I really need help.

 

[Beetle]

Looking at your previous thread, you were on the right track with the
code you had found, but you need to use the resulting Criteria string
as a Where condition when you open your report. Below is a
modified example (use your own control/report names).

Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![List72].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[CallCenterID]='" _
& Me![List72].ItemData(i) & "'"
Next i

' Open the report using selected items in the list box.

DoCmd.OpenReport "rptYourReport", , , Criteria


This code could be run from, for example, the Click event of a
command button. Also, the above assumes that the values you want
to use as criteria are in the bound column of your list box. If not, it
will need to be modified.

 

[Julius]

Not sure if I did this right but I get a compile error for On Error
GoToErr_Command52_Click(). Also I did not use this on a hidden control I put
it directly on the command button on the form that will 0pen the report, and
lastly can you look at below and see if I put all my control names in place.

Private Sub Command52_Click()
On Error GoTo Err_Command52_Click

Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![List50].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[CallCenterID]='" _
& Me![List50].ItemData(i) & "'"
Next i

' Open the report using selected items in the list box.

DoCmd.OpenReport "Mass Overflow Report", , , Criteria


End Sub

Looking at your previous thread, you were on the right track with the
code you had found, but you need to use the resulting Criteria string
as a Where condition when you open your report. Below is a
modified example (use your own control/report names).

Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![List72].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[CallCenterID]='" _
& Me![List72].ItemData(i) & "'"
Next i

' Open the report using selected items in the list box.

DoCmd.OpenReport "rptYourReport", , , Criteria


This code could be run from, for example, the Click event of a
command button. Also, the above assumes that the values you want
to use as criteria are in the bound column of your list box. If not, it
will need to be modified.

--
_________

Sean Bailey

I am pulling my hair out going crazy with this. I have two weeks before my
project is due. I need List box help. I have read everything plus the
reference website and I am lost.

All I want to do is use a Multiselect List box to run my reports based on
that list selection. Is there a very very very simple way to do this without
all the hoopla. If I create a Pass Parameter to Query what is the easiest
way to do this, if I have to write code how do I do it without needing a 10
year programming degree. Please I really need help.

 

[John W. Vinson]

Not sure if I did this right but I get a compile error for On Error
GoToErr_Command52_Click(). Also I did not use this on a hidden control I put
it directly on the command button on the form that will 0pen the report, and
lastly can you look at below and see if I put all my control names in place.

PMFJI but the error is that you're telling it to go to Err_Command52_Click -
and there is no such place to go to! Add a few lines to actually handle the
error:

Private Sub Command52_Click()
On Error GoTo Err_Command52_Click

Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![List50].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[CallCenterID]='" _
& Me![List50].ItemData(i) & "'"
Next i

' Open the report using selected items in the list box.

DoCmd.OpenReport "Mass Overflow Report", , , Criteria

Proc_Exit:
Exit Sub
Err_Command52_Click:
MsgBox "Error " & Err.Num & vbCrLf & Err.Description
Resume Proc_Exit

End Sub

 

[Julius]

Thank you so much, ok so I did as you requested. Now I am getting the
following error:

This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables.

Not sure if I did this right but I get a compile error for On Error
GoToErr_Command52_Click(). Also I did not use this on a hidden control I put
it directly on the command button on the form that will 0pen the report, and
lastly can you look at below and see if I put all my control names in place.

PMFJI but the error is that you're telling it to go to Err_Command52_Click -
and there is no such place to go to! Add a few lines to actually handle the
error:

Private Sub Command52_Click()
On Error GoTo Err_Command52_Click

Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![List50].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[CallCenterID]='" _
& Me![List50].ItemData(i) & "'"
Next i

' Open the report using selected items in the list box.

DoCmd.OpenReport "Mass Overflow Report", , , Criteria

Proc_Exit:
Exit Sub
Err_Command52_Click:
MsgBox "Error " & Err.Num & vbCrLf & Err.Description
Resume Proc_Exit

End Sub

 

[John W. Vinson]

Thank you so much, ok so I did as you requested. Now I am getting the
following error:

This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables.

What is the datatype of CallCenterID - Number or Text?

Try putting a breakpoint in the code: mouseclick in the grey bar to the left
of the code window next to some executable statement. Step through the code
using the F8 key (or the Debug menu options). Use the Immediate Window (type
Ctrl-G to see it if need be); when you get to the end of the loop type

?Criteria

in the immediate window to display the value of the variable. Please post it
here.

 

[Julius]

The CallCenterID is AutoNumber. Also I did the breakpoint and I can not
identify the problem infact it does not show the error. I was thinking maybe
the error was in the query itself. I am not using a hidden control on the
form to use this as a pass through. I put it directly on the command button
attached to the query, is this correct. I wanted to use a pass through using
a hidden control but I did not know what Event to use.

 

[John W. Vinson]

The CallCenterID is AutoNumber. Also I did the breakpoint and I can not
identify the problem infact it does not show the error. I was thinking maybe
the error was in the query itself. I am not using a hidden control on the
form to use this as a pass through. I put it directly on the command button
attached to the query, is this correct. I wanted to use a pass through using
a hidden control but I did not know what Event to use.

Please post the SQL string that is being generated.

Note that I have said *nothing* about any hidden controls. I guess I jumped
into the middle of a previous discussion because I know nothing about the
hidden control or PassThrough queries.

Do note that a Query cannot have a command button. You can put a command
button on a Form, and you can bind a Form to a select query, and the command
button can execute a query - but you can't "attach" a command button to a
query.

 

[Julius]

This is the SQL statement

Private Sub Command61_Click()

Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![List50].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[CallCenterID]='" _
& Me![List50].ItemData(i) & "'"
Next i

' Open the report using selected items in the list box.

DoCmd.OpenReport "MassOverflowReport", , , Criteria

Proc_Exit:
Exit Sub
Err_Command61_Click:

The error is on the DoCmd.OpenReport "MassOverflowReport",,,Criteria

 

[John W. Vinson]

The error is on the DoCmd.OpenReport "MassOverflowReport",,,Criteria

Ok; again, as requested previously...

Try putting a breakpoint in the code: mouseclick in the grey bar to the left
of the code window next to some executable statement. Step through the code
using the F8 key (or the Debug menu options). Use the Immediate Window (type
Ctrl-G to see it if need be); when you get to the end of the loop type

?Criteria

in the immediate window to display the value of the variable. Please post it
here.

And, again, as requested before (please read the full messages before you
respond):

What is the datatype of CallCenterID - Number or Text?

There's clearly an error in the criteria or the query string. I can't see it
from here!

 

[Beetle]

If [CallCenterID] is an Autonumber field, then you need to remove
the extra single quotes from the code. Modified code below;

Private Sub Command61_Click()

Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![List50].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[CallCenterID]=" _
& Me![List50].ItemData(i)
Next i

' Open the report using selected items in the list box.

DoCmd.OpenReport "MassOverflowReport", , , Criteria

Proc_Exit:
Exit Sub
Err_Command61_Click:

Also, as I mentioned before, make sure CallCenterID is the bound column
of your list box.

--
_________

Sean Bailey

This is the SQL statement

Private Sub Command61_Click()

Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![List50].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[CallCenterID]='" _
& Me![List50].ItemData(i) & "'"
Next i

' Open the report using selected items in the list box.

DoCmd.OpenReport "MassOverflowReport", , , Criteria

Proc_Exit:
Exit Sub
Err_Command61_Click:

The error is on the DoCmd.OpenReport "MassOverflowReport",,,Criteria

Please post the SQL string that is being generated.

Note that I have said *nothing* about any hidden controls. I guess I jumped
into the middle of a previous discussion because I know nothing about the
hidden control or PassThrough queries.

Do note that a Query cannot have a command button. You can put a command
button on a Form, and you can bind a Form to a select query, and the command
button can execute a query - but you can't "attach" a command button to a
query.

 

[Julius]

Bless u so much, I have been working on this all day and it works. One small
issue remains, it goes directly to the printer, I want to see the report
before I print it, how can I adjust this to do so.

If [CallCenterID] is an Autonumber field, then you need to remove
the extra single quotes from the code. Modified code below;

Private Sub Command61_Click()

Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![List50].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[CallCenterID]=" _
& Me![List50].ItemData(i)
Next i

' Open the report using selected items in the list box.

DoCmd.OpenReport "MassOverflowReport", , , Criteria

Proc_Exit:
Exit Sub
Err_Command61_Click:

Also, as I mentioned before, make sure CallCenterID is the bound column
of your list box.

--
_________

Sean Bailey

This is the SQL statement

Private Sub Command61_Click()

Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![List50].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[CallCenterID]='" _
& Me![List50].ItemData(i) & "'"
Next i

' Open the report using selected items in the list box.

DoCmd.OpenReport "MassOverflowReport", , , Criteria

Proc_Exit:
Exit Sub
Err_Command61_Click:

The error is on the DoCmd.OpenReport "MassOverflowReport",,,Criteria

The CallCenterID is AutoNumber. Also I did the breakpoint and I can not
identify the problem infact it does not show the error. I was thinking maybe
the error was in the query itself. I am not using a hidden control on the
form to use this as a pass through. I put it directly on the command button
attached to the query, is this correct. I wanted to use a pass through using
a hidden control but I did not know what Event to use.

Please post the SQL string that is being generated.

Note that I have said *nothing* about any hidden controls. I guess I jumped
into the middle of a previous discussion because I know nothing about the
hidden control or PassThrough queries.

Do note that a Query cannot have a command button. You can put a command
button on a Form, and you can bind a Form to a select query, and the command
button can execute a query - but you can't "attach" a command button to a
query.

 

[Beetle]

DoCmd.OpenReport "MassOverflowReport", acViewPreview , , Criteria
--
_________

Sean Bailey

Bless u so much, I have been working on this all day and it works. One small
issue remains, it goes directly to the printer, I want to see the report
before I print it, how can I adjust this to do so.

If [CallCenterID] is an Autonumber field, then you need to remove
the extra single quotes from the code. Modified code below;

Private Sub Command61_Click()

Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![List50].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[CallCenterID]=" _
& Me![List50].ItemData(i)
Next i

' Open the report using selected items in the list box.

DoCmd.OpenReport "MassOverflowReport", , , Criteria

Proc_Exit:
Exit Sub
Err_Command61_Click:

Also, as I mentioned before, make sure CallCenterID is the bound column
of your list box.

--
_________

Sean Bailey

This is the SQL statement

Private Sub Command61_Click()

Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![List50].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[CallCenterID]='" _
& Me![List50].ItemData(i) & "'"
Next i

' Open the report using selected items in the list box.

DoCmd.OpenReport "MassOverflowReport", , , Criteria

Proc_Exit:
Exit Sub
Err_Command61_Click:

The error is on the DoCmd.OpenReport "MassOverflowReport",,,Criteria
:

The CallCenterID is AutoNumber. Also I did the breakpoint and I can not
identify the problem infact it does not show the error. I was thinking maybe
the error was in the query itself. I am not using a hidden control on the
form to use this as a pass through. I put it directly on the command button
attached to the query, is this correct. I wanted to use a pass through using
a hidden control but I did not know what Event to use.

Please post the SQL string that is being generated.

Note that I have said *nothing* about any hidden controls. I guess I jumped
into the middle of a previous discussion because I know nothing about the
hidden control or PassThrough queries.

Do note that a Query cannot have a command button. You can put a command
button on a Form, and you can bind a Form to a select query, and the command
button can execute a query - but you can't "attach" a command button to a
query.

 

[Julius]

that worked beautifully, thank u so much. Last questions, I have reports
that run off of macros can I still use this like I did at the end of the code

DoCmd.RunMacro "Run Mass Center Average Weighted Report"

DoCmd.OpenReport "MassOverflowReport", acViewPreview , , Criteria
--
_________

Sean Bailey

Bless u so much, I have been working on this all day and it works. One small
issue remains, it goes directly to the printer, I want to see the report
before I print it, how can I adjust this to do so.

If [CallCenterID] is an Autonumber field, then you need to remove
the extra single quotes from the code. Modified code below;

Private Sub Command61_Click()

Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![List50].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[CallCenterID]=" _
& Me![List50].ItemData(i)
Next i

' Open the report using selected items in the list box.

DoCmd.OpenReport "MassOverflowReport", , , Criteria

Proc_Exit:
Exit Sub
Err_Command61_Click:

Also, as I mentioned before, make sure CallCenterID is the bound column
of your list box.

--
_________

Sean Bailey


:

This is the SQL statement

Private Sub Command61_Click()

Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![List50].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[CallCenterID]='" _
& Me![List50].ItemData(i) & "'"
Next i

' Open the report using selected items in the list box.

DoCmd.OpenReport "MassOverflowReport", , , Criteria

Proc_Exit:
Exit Sub
Err_Command61_Click:

The error is on the DoCmd.OpenReport "MassOverflowReport",,,Criteria
:

The CallCenterID is AutoNumber. Also I did the breakpoint and I can not
identify the problem infact it does not show the error. I was thinking maybe
the error was in the query itself. I am not using a hidden control on the
form to use this as a pass through. I put it directly on the command button
attached to the query, is this correct. I wanted to use a pass through using
a hidden control but I did not know what Event to use.

Please post the SQL string that is being generated.

Note that I have said *nothing* about any hidden controls. I guess I jumped
into the middle of a previous discussion because I know nothing about the
hidden control or PassThrough queries.

Do note that a Query cannot have a command button. You can put a command
button on a Form, and you can bind a Form to a select query, and the command
button can execute a query - but you can't "attach" a command button to a
query.