Printing report based on employee selection

I haven't forgiven yet. The problem seems to be that in reports properties
the filter line is for example (17), shouldn't there be tunniste=17 (not
parentheses)? How can I put the name of the field in the code?
--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

In the code I posted, it uses the bound column of the list box. If you need
to use a different column, you either need to change the bound column or use
the column you want to filter on. When addressing a list box or combo box,
the column numbers start with 0. So you could use
Me.MyCombo.Column(n) Where n is the column number you want to filter on.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

Should I give up?

There´re three fields in my select list box: employee, "tunniste" and year,
because one employee may have many records and I want to select a specific
year. The boundcolumn is "tunniste". When I look strRptFilter variable name
in Visual Basic it shows the numbers from the field "tunniste" I have chosen.
However all the records print.

How does the report know that the field "tunniste" is the one that sholud be
used as a filter?

Do you give up? I´m sorry I can´t explain better...


--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

Sorry, I don't understand what you are asking in your last post.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

Hi Dave!

I have had a vacation so that I haven't been working with my problem. Yes,
"Valintalista" is the name of the list box control.

strRptFilter works but now some further information: There may be several
times the same employee, so the field "tunniste" is a unique one and
"valintalista" returns the value of "tunniste" from my selection. So the
report should show only these records... "tunniste" is the keyfield.

--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

Is Valintalista the name of the list box control?
The Open report, as written, will print the report. If you want it to open
in Print Preview, you have to tell the report to do that.

DoCmd.OpenReport "Vuosilomapalkkalaskelma", acViewPreview, , strRptFilter

If your report is not filtering as you expect. Run the code in debug mode
with a breakpoint on the OpenReport line. The see what the value of
strRptFilter is.

If you are not familiar with using debug mode, open the form module that for
the form and put the cursor on the OpenReport line. Now press F9. The line
will change color. Now open the form in form view, select some items in the
list box, and click the command button. The editor will open with the
OpenReport line highlighted. Hover over the strRptFilter variable name in
the line and it should show the value of the variable. If you can't see the
entire line, you can open the immediate window with ctrlG. Then type in
?strRptFilter and it will show the value of the variable.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

It seems to me that I can't make this work.

This is what I have in Visual Basic:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Private Sub Vaihto6_Click()
strRptFilter = BuildWhereCondition("Valintalista")
DoCmd.OpenReport "Vuosilomapalkkalaskelma", , , strRptFilter

End Sub

First there was en error but then it vanished and my button PRINTED
(shouldn't "Open Report" only open the report, not print?) ALL the records I
have in my table!

What to do?

Thanks a lot!

Tellu


"Klatuu" kirjoitti:

The function I posted should go in your form module. Paste it in by itself
at the top of the module but after any Option or Dim statements, but before
any event procedures.

These two lines:
strRptFilter = BuildWhereCondition("MyListBox")
Docmd.OpenReport "MyReportName", , , strRptFilter

Should go in the Click event of a command button.
Create a command button on your form.
Open the properties dialog box.
Select the events tab.
Click the small button with the 3 dots to the right of the OnClick event
Select Code Builder
Paste the lines into the VB editor.
You will need to change "MyListBox" to the actual name of your list box
control.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

You may now laugh to my questions, but I have no some basic problems (I know
nothing about Visual Basic):

When I put in the click event there comes
"Private Sub Vaihto6_GotFocus()

End Sub"

Should I take this away or put your function between these things?

And...

Where to put strRpt.... and
Docmd.OpenReport...


--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

There is a way, but it is not necessarily that simple. Here is how I would
do it.
I would not have any filtering in the report's record source query except
that which would apply to the report in all cases regardless of which
employees were selected.

I would have a multi select list box on the form to allow the user to select
all, or any number of employees.

I would have a command button to print the report. In the Click event of
the command button, I would loop through the ItemsSelected collection of the
list box and build a Where string that I would use in the OpenReport method
to filter the report's output.

Here is a function that will build the Where string. If no items are
selected from the list, it assumes you want all employees included;
otherwise, it includes those that are selected:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

To call it, you pass the name of the list box and assign the results to a
variable:

strRptFilter = BuildWhereCondition("MyListBox")

Then you use it to open the report:

Docmd.OpenReport "MyReportName", , , strRptFilter


--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

I have a report based on a query. Now I´d like to select which records
(employees) to print, not all either one, but several.

Is there any simple way...

--
Thanks a lot!

Tellu

Sorry, Tellu, I left out something very important. Once you build the filter
string, you have to put the name of the field at the beginning of it.
Pardon my omission:

strRptFilter = "FieldName" & strRptFilter
Docmd.OpenReport "MyReportName", , , strRptFilter
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

I haven't forgiven yet. The problem seems to be that in reports properties
the filter line is for example (17), shouldn't there be tunniste=17 (not
parentheses)? How can I put the name of the field in the code?
--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

In the code I posted, it uses the bound column of the list box. If you need
to use a different column, you either need to change the bound column or use
the column you want to filter on. When addressing a list box or combo box,
the column numbers start with 0. So you could use
Me.MyCombo.Column(n) Where n is the column number you want to filter on.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

Should I give up?

There´re three fields in my select list box: employee, "tunniste" and year,
because one employee may have many records and I want to select a specific
year. The boundcolumn is "tunniste". When I look strRptFilter variable name
in Visual Basic it shows the numbers from the field "tunniste" I have chosen.
However all the records print.

How does the report know that the field "tunniste" is the one that sholud be
used as a filter?

Do you give up? I´m sorry I can´t explain better...


--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

Sorry, I don't understand what you are asking in your last post.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

Hi Dave!

I have had a vacation so that I haven't been working with my problem. Yes,
"Valintalista" is the name of the list box control.

strRptFilter works but now some further information: There may be several
times the same employee, so the field "tunniste" is a unique one and
"valintalista" returns the value of "tunniste" from my selection. So the
report should show only these records... "tunniste" is the keyfield.

--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

Is Valintalista the name of the list box control?
The Open report, as written, will print the report. If you want it to open
in Print Preview, you have to tell the report to do that.

DoCmd.OpenReport "Vuosilomapalkkalaskelma", acViewPreview, , strRptFilter

If your report is not filtering as you expect. Run the code in debug mode
with a breakpoint on the OpenReport line. The see what the value of
strRptFilter is.

If you are not familiar with using debug mode, open the form module that for
the form and put the cursor on the OpenReport line. Now press F9. The line
will change color. Now open the form in form view, select some items in the
list box, and click the command button. The editor will open with the
OpenReport line highlighted. Hover over the strRptFilter variable name in
the line and it should show the value of the variable. If you can't see the
entire line, you can open the immediate window with ctrlG. Then type in
?strRptFilter and it will show the value of the variable.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

It seems to me that I can't make this work.

This is what I have in Visual Basic:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Private Sub Vaihto6_Click()
strRptFilter = BuildWhereCondition("Valintalista")
DoCmd.OpenReport "Vuosilomapalkkalaskelma", , , strRptFilter

End Sub

First there was en error but then it vanished and my button PRINTED
(shouldn't "Open Report" only open the report, not print?) ALL the records I
have in my table!

What to do?

Thanks a lot!

Tellu


"Klatuu" kirjoitti:

The function I posted should go in your form module. Paste it in by itself
at the top of the module but after any Option or Dim statements, but before
any event procedures.

These two lines:
strRptFilter = BuildWhereCondition("MyListBox")
Docmd.OpenReport "MyReportName", , , strRptFilter

Should go in the Click event of a command button.
Create a command button on your form.
Open the properties dialog box.
Select the events tab.
Click the small button with the 3 dots to the right of the OnClick event
Select Code Builder
Paste the lines into the VB editor.
You will need to change "MyListBox" to the actual name of your list box
control.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

You may now laugh to my questions, but I have no some basic problems (I know
nothing about Visual Basic):

When I put in the click event there comes
"Private Sub Vaihto6_GotFocus()

End Sub"

Should I take this away or put your function between these things?

And...

Where to put strRpt.... and
Docmd.OpenReport...


--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

There is a way, but it is not necessarily that simple. Here is how I would
do it.
I would not have any filtering in the report's record source query except
that which would apply to the report in all cases regardless of which
employees were selected.

I would have a multi select list box on the form to allow the user to select
all, or any number of employees.

I would have a command button to print the report. In the Click event of
the command button, I would loop through the ItemsSelected collection of the
list box and build a Where string that I would use in the OpenReport method
to filter the report's output.

Here is a function that will build the Where string. If no items are
selected from the list, it assumes you want all employees included;
otherwise, it includes those that are selected:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

To call it, you pass the name of the list box and assign the results to a
variable:

strRptFilter = BuildWhereCondition("MyListBox")

Then you use it to open the report:

Docmd.OpenReport "MyReportName", , , strRptFilter


--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

I have a report based on a query. Now I´d like to select which records
(employees) to print, not all either one, but several.

Is there any simple way...

--
Thanks a lot!

Tellu

Sorry, my lousy english (forgive me).

This is what I have in code:

Private Sub Vaihto6_Click()
strRptFilter = BuildWhereCondition("Valintalista")
strRptFilter = "Tunniste" & strRptFilter
DoCmd.OpenReport "Vuosilomapalkkalaskelma", acViewPreview, , strRptFilter

End Sub

The value of strRptFilter seems to be for example "Tunniste='11'". However
I get a run-time error 3464 (type mismatch in condition or something like
that).
Why are those ' there - are they too much?


--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

Sorry, Tellu, I left out something very important. Once you build the filter
string, you have to put the name of the field at the beginning of it.
Pardon my omission:

strRptFilter = "FieldName" & strRptFilter
Docmd.OpenReport "MyReportName", , , strRptFilter
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

I haven't forgiven yet. The problem seems to be that in reports properties
the filter line is for example (17), shouldn't there be tunniste=17 (not
parentheses)? How can I put the name of the field in the code?
--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

In the code I posted, it uses the bound column of the list box. If you need
to use a different column, you either need to change the bound column or use
the column you want to filter on. When addressing a list box or combo box,
the column numbers start with 0. So you could use
Me.MyCombo.Column(n) Where n is the column number you want to filter on.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

Should I give up?

There´re three fields in my select list box: employee, "tunniste" and year,
because one employee may have many records and I want to select a specific
year. The boundcolumn is "tunniste". When I look strRptFilter variable name
in Visual Basic it shows the numbers from the field "tunniste" I have chosen.
However all the records print.

How does the report know that the field "tunniste" is the one that sholud be
used as a filter?

Do you give up? I´m sorry I can´t explain better...


--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

Sorry, I don't understand what you are asking in your last post.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

Hi Dave!

I have had a vacation so that I haven't been working with my problem. Yes,
"Valintalista" is the name of the list box control.

strRptFilter works but now some further information: There may be several
times the same employee, so the field "tunniste" is a unique one and
"valintalista" returns the value of "tunniste" from my selection. So the
report should show only these records... "tunniste" is the keyfield.

--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

Is Valintalista the name of the list box control?
The Open report, as written, will print the report. If you want it to open
in Print Preview, you have to tell the report to do that.

DoCmd.OpenReport "Vuosilomapalkkalaskelma", acViewPreview, , strRptFilter

If your report is not filtering as you expect. Run the code in debug mode
with a breakpoint on the OpenReport line. The see what the value of
strRptFilter is.

If you are not familiar with using debug mode, open the form module that for
the form and put the cursor on the OpenReport line. Now press F9. The line
will change color. Now open the form in form view, select some items in the
list box, and click the command button. The editor will open with the
OpenReport line highlighted. Hover over the strRptFilter variable name in
the line and it should show the value of the variable. If you can't see the
entire line, you can open the immediate window with ctrlG. Then type in
?strRptFilter and it will show the value of the variable.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

It seems to me that I can't make this work.

This is what I have in Visual Basic:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Private Sub Vaihto6_Click()
strRptFilter = BuildWhereCondition("Valintalista")
DoCmd.OpenReport "Vuosilomapalkkalaskelma", , , strRptFilter

End Sub

First there was en error but then it vanished and my button PRINTED
(shouldn't "Open Report" only open the report, not print?) ALL the records I
have in my table!

What to do?

Thanks a lot!

Tellu


"Klatuu" kirjoitti:

The function I posted should go in your form module. Paste it in by itself
at the top of the module but after any Option or Dim statements, but before
any event procedures.

These two lines:
strRptFilter = BuildWhereCondition("MyListBox")
Docmd.OpenReport "MyReportName", , , strRptFilter

Should go in the Click event of a command button.
Create a command button on your form.
Open the properties dialog box.
Select the events tab.
Click the small button with the 3 dots to the right of the OnClick event
Select Code Builder
Paste the lines into the VB editor.
You will need to change "MyListBox" to the actual name of your list box
control.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

You may now laugh to my questions, but I have no some basic problems (I know
nothing about Visual Basic):

When I put in the click event there comes
"Private Sub Vaihto6_GotFocus()

End Sub"

Should I take this away or put your function between these things?

And...

Where to put strRpt.... and
Docmd.OpenReport...


--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

There is a way, but it is not necessarily that simple. Here is how I would
do it.
I would not have any filtering in the report's record source query except
that which would apply to the report in all cases regardless of which
employees were selected.

I would have a multi select list box on the form to allow the user to select
all, or any number of employees.

I would have a command button to print the report. In the Click event of
the command button, I would loop through the ItemsSelected collection of the
list box and build a Where string that I would use in the OpenReport method
to filter the report's output.

Here is a function that will build the Where string. If no items are
selected from the list, it assumes you want all employees included;
otherwise, it includes those that are selected:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

To call it, you pass the name of the list box and assign the results to a
variable:

strRptFilter = BuildWhereCondition("MyListBox")

Then you use it to open the report:

Docmd.OpenReport "MyReportName", , , strRptFilter


--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

I have a report based on a query. Now I´d like to select which records
(employees) to print, not all either one, but several.

Is there any simple way...

--
Thanks a lot!

Tellu

You English is fine.
Since the field you are wanting to compare against is a numeric field, you
need to remove the qoute marks. Change these lines:

strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"

To:

strWhere = "= " & _
ctl.ItemData(ctl.ItemsSelected(0))

And:
strWhere = strWhere & "'" & .ItemData(varItem) & "', "

To:

strWhere = strWhere & .ItemData(varItem) & ", "

--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

Sorry, my lousy english (forgive me).

This is what I have in code:

Private Sub Vaihto6_Click()
strRptFilter = BuildWhereCondition("Valintalista")
strRptFilter = "Tunniste" & strRptFilter
DoCmd.OpenReport "Vuosilomapalkkalaskelma", acViewPreview, , strRptFilter

End Sub

The value of strRptFilter seems to be for example "Tunniste='11'". However
I get a run-time error 3464 (type mismatch in condition or something like
that).
Why are those ' there - are they too much?


--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

Sorry, Tellu, I left out something very important. Once you build the filter
string, you have to put the name of the field at the beginning of it.
Pardon my omission:

strRptFilter = "FieldName" & strRptFilter
Docmd.OpenReport "MyReportName", , , strRptFilter
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

I haven't forgiven yet. The problem seems to be that in reports properties
the filter line is for example (17), shouldn't there be tunniste=17 (not
parentheses)? How can I put the name of the field in the code?
--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

In the code I posted, it uses the bound column of the list box. If you need
to use a different column, you either need to change the bound column or use
the column you want to filter on. When addressing a list box or combo box,
the column numbers start with 0. So you could use
Me.MyCombo.Column(n) Where n is the column number you want to filter on.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

Should I give up?

There´re three fields in my select list box: employee, "tunniste" and year,
because one employee may have many records and I want to select a specific
year. The boundcolumn is "tunniste". When I look strRptFilter variable name
in Visual Basic it shows the numbers from the field "tunniste" I have chosen.
However all the records print.

How does the report know that the field "tunniste" is the one that sholud be
used as a filter?

Do you give up? I´m sorry I can´t explain better...


--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

Sorry, I don't understand what you are asking in your last post.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

Hi Dave!

I have had a vacation so that I haven't been working with my problem. Yes,
"Valintalista" is the name of the list box control.

strRptFilter works but now some further information: There may be several
times the same employee, so the field "tunniste" is a unique one and
"valintalista" returns the value of "tunniste" from my selection. So the
report should show only these records... "tunniste" is the keyfield.

--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

Is Valintalista the name of the list box control?
The Open report, as written, will print the report. If you want it to open
in Print Preview, you have to tell the report to do that.

DoCmd.OpenReport "Vuosilomapalkkalaskelma", acViewPreview, , strRptFilter

If your report is not filtering as you expect. Run the code in debug mode
with a breakpoint on the OpenReport line. The see what the value of
strRptFilter is.

If you are not familiar with using debug mode, open the form module that for
the form and put the cursor on the OpenReport line. Now press F9. The line
will change color. Now open the form in form view, select some items in the
list box, and click the command button. The editor will open with the
OpenReport line highlighted. Hover over the strRptFilter variable name in
the line and it should show the value of the variable. If you can't see the
entire line, you can open the immediate window with ctrlG. Then type in
?strRptFilter and it will show the value of the variable.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

It seems to me that I can't make this work.

This is what I have in Visual Basic:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Private Sub Vaihto6_Click()
strRptFilter = BuildWhereCondition("Valintalista")
DoCmd.OpenReport "Vuosilomapalkkalaskelma", , , strRptFilter

End Sub

First there was en error but then it vanished and my button PRINTED
(shouldn't "Open Report" only open the report, not print?) ALL the records I
have in my table!

What to do?

Thanks a lot!

Tellu


"Klatuu" kirjoitti:

The function I posted should go in your form module. Paste it in by itself
at the top of the module but after any Option or Dim statements, but before
any event procedures.

These two lines:
strRptFilter = BuildWhereCondition("MyListBox")
Docmd.OpenReport "MyReportName", , , strRptFilter

Should go in the Click event of a command button.
Create a command button on your form.
Open the properties dialog box.
Select the events tab.
Click the small button with the 3 dots to the right of the OnClick event
Select Code Builder
Paste the lines into the VB editor.
You will need to change "MyListBox" to the actual name of your list box
control.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

You may now laugh to my questions, but I have no some basic problems (I know
nothing about Visual Basic):

When I put in the click event there comes
"Private Sub Vaihto6_GotFocus()

End Sub"

Should I take this away or put your function between these things?

And...

Where to put strRpt.... and
Docmd.OpenReport...


--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

There is a way, but it is not necessarily that simple. Here is how I would
do it.
I would not have any filtering in the report's record source query except
that which would apply to the report in all cases regardless of which
employees were selected.

I would have a multi select list box on the form to allow the user to select
all, or any number of employees.

I would have a command button to print the report. In the Click event of
the command button, I would loop through the ItemsSelected collection of the
list box and build a Where string that I would use in the OpenReport method
to filter the report's output.

Here is a function that will build the Where string. If no items are
selected from the list, it assumes you want all employees included;
otherwise, it includes those that are selected:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

To call it, you pass the name of the list box and assign the results to a
variable:

strRptFilter = BuildWhereCondition("MyListBox")

Then you use it to open the report:

Docmd.OpenReport "MyReportName", , , strRptFilter


--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

I have a report based on a query. Now I´d like to select which records
(employees) to print, not all either one, but several.

Is there any simple way...

--
Thanks a lot!

Thank you for your patience, Dave!

This works excellently now! Have a nice summer!

Thanks a lot!

Tellu


"Klatuu" kirjoitti:

You English is fine.
Since the field you are wanting to compare against is a numeric field, you
need to remove the qoute marks. Change these lines:

strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"

To:

strWhere = "= " & _
ctl.ItemData(ctl.ItemsSelected(0))

And:
strWhere = strWhere & "'" & .ItemData(varItem) & "', "

To:

strWhere = strWhere & .ItemData(varItem) & ", "

--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

Sorry, my lousy english (forgive me).

This is what I have in code:

Private Sub Vaihto6_Click()
strRptFilter = BuildWhereCondition("Valintalista")
strRptFilter = "Tunniste" & strRptFilter
DoCmd.OpenReport "Vuosilomapalkkalaskelma", acViewPreview, , strRptFilter

End Sub

The value of strRptFilter seems to be for example "Tunniste='11'". However
I get a run-time error 3464 (type mismatch in condition or something like
that).
Why are those ' there - are they too much?


--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

Sorry, Tellu, I left out something very important. Once you build the filter
string, you have to put the name of the field at the beginning of it.
Pardon my omission:

strRptFilter = "FieldName" & strRptFilter
Docmd.OpenReport "MyReportName", , , strRptFilter
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

I haven't forgiven yet. The problem seems to be that in reports properties
the filter line is for example (17), shouldn't there be tunniste=17 (not
parentheses)? How can I put the name of the field in the code?
--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

In the code I posted, it uses the bound column of the list box. If you need
to use a different column, you either need to change the bound column or use
the column you want to filter on. When addressing a list box or combo box,
the column numbers start with 0. So you could use
Me.MyCombo.Column(n) Where n is the column number you want to filter on.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

Should I give up?

There´re three fields in my select list box: employee, "tunniste" and year,
because one employee may have many records and I want to select a specific
year. The boundcolumn is "tunniste". When I look strRptFilter variable name
in Visual Basic it shows the numbers from the field "tunniste" I have chosen.
However all the records print.

How does the report know that the field "tunniste" is the one that sholud be
used as a filter?

Do you give up? I´m sorry I can´t explain better...


--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

Sorry, I don't understand what you are asking in your last post.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

Hi Dave!

I have had a vacation so that I haven't been working with my problem. Yes,
"Valintalista" is the name of the list box control.

strRptFilter works but now some further information: There may be several
times the same employee, so the field "tunniste" is a unique one and
"valintalista" returns the value of "tunniste" from my selection. So the
report should show only these records... "tunniste" is the keyfield.

--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

Is Valintalista the name of the list box control?
The Open report, as written, will print the report. If you want it to open
in Print Preview, you have to tell the report to do that.

DoCmd.OpenReport "Vuosilomapalkkalaskelma", acViewPreview, , strRptFilter

If your report is not filtering as you expect. Run the code in debug mode
with a breakpoint on the OpenReport line. The see what the value of
strRptFilter is.

If you are not familiar with using debug mode, open the form module that for
the form and put the cursor on the OpenReport line. Now press F9. The line
will change color. Now open the form in form view, select some items in the
list box, and click the command button. The editor will open with the
OpenReport line highlighted. Hover over the strRptFilter variable name in
the line and it should show the value of the variable. If you can't see the
entire line, you can open the immediate window with ctrlG. Then type in
?strRptFilter and it will show the value of the variable.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

It seems to me that I can't make this work.

This is what I have in Visual Basic:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Private Sub Vaihto6_Click()
strRptFilter = BuildWhereCondition("Valintalista")
DoCmd.OpenReport "Vuosilomapalkkalaskelma", , , strRptFilter

End Sub

First there was en error but then it vanished and my button PRINTED
(shouldn't "Open Report" only open the report, not print?) ALL the records I
have in my table!

What to do?

Thanks a lot!

Tellu


"Klatuu" kirjoitti:

The function I posted should go in your form module. Paste it in by itself
at the top of the module but after any Option or Dim statements, but before
any event procedures.

These two lines:
strRptFilter = BuildWhereCondition("MyListBox")
Docmd.OpenReport "MyReportName", , , strRptFilter

Should go in the Click event of a command button.
Create a command button on your form.
Open the properties dialog box.
Select the events tab.
Click the small button with the 3 dots to the right of the OnClick event
Select Code Builder
Paste the lines into the VB editor.
You will need to change "MyListBox" to the actual name of your list box
control.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

You may now laugh to my questions, but I have no some basic problems (I know
nothing about Visual Basic):

When I put in the click event there comes
"Private Sub Vaihto6_GotFocus()

End Sub"

Should I take this away or put your function between these things?

And...

Where to put strRpt.... and
Docmd.OpenReport...


--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

There is a way, but it is not necessarily that simple. Here is how I would
do it.
I would not have any filtering in the report's record source query except
that which would apply to the report in all cases regardless of which
employees were selected.

I would have a multi select list box on the form to allow the user to select
all, or any number of employees.

I would have a command button to print the report. In the Click event of
the command button, I would loop through the ItemsSelected collection of the
list box and build a Where string that I would use in the OpenReport method
to filter the report's output.

Here is a function that will build the Where string. If no items are
selected from the list, it assumes you want all employees included;
otherwise, it includes those that are selected:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

I am trying to do something very similar to this and I am getting an error
message that I have not been able to resolve. I have referenced the code in
this discussion as well as code that I was directed to in another post. It
was entitled "Use a multi-select list box to filter a report" and was
provided by allen Browne.

I have created a form with a select box that lists open issues using this
code for the list box

SELECT [Issues].[ID], [Issues].[Title], [Issues].[Status] FROM Issues WHERE
((([Issues].[Status])="open")) ORDER BY [Issues].[Title];

This appears to work fine. I then created a button that has the filtering
code in it.

If I do not select anything it will run the report with all records.
However, if I pick any records I get this error message.

Error 3071 – This expression is type 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.

Here is the code that I am using.

Private Sub cmdPreview_Click()

On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.

Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

strDelim = """" 'Delimiter appropriate to field type. See
note 1.
strDoc = "Details of Multiple Open Issues"

'Loop through the ItemsSelected in the list box.
With Me.lstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen 0 Then
strWhere = "[ID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Issues: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere
Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler

End Sub
----------------
The strDelim is a text field.

Can you see where I am going wrong. I'm new to VB so I am way over my head.

thanks for any help you can offer.

--
MikeA


"Klatuu" wrote:

You English is fine.
Since the field you are wanting to compare against is a numeric field, you
need to remove the qoute marks. Change these lines:

strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"

To:

strWhere = "= " & _
ctl.ItemData(ctl.ItemsSelected(0))

And:
strWhere = strWhere & "'" & .ItemData(varItem) & "', "

To:

strWhere = strWhere & .ItemData(varItem) & ", "

--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

Sorry, my lousy english (forgive me).

This is what I have in code:

Private Sub Vaihto6_Click()
strRptFilter = BuildWhereCondition("Valintalista")
strRptFilter = "Tunniste" & strRptFilter
DoCmd.OpenReport "Vuosilomapalkkalaskelma", acViewPreview, , strRptFilter

End Sub

The value of strRptFilter seems to be for example "Tunniste='11'". However
I get a run-time error 3464 (type mismatch in condition or something like
that).
Why are those ' there - are they too much?


--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

Sorry, Tellu, I left out something very important. Once you build the filter
string, you have to put the name of the field at the beginning of it.
Pardon my omission:

strRptFilter = "FieldName" & strRptFilter
Docmd.OpenReport "MyReportName", , , strRptFilter
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

I haven't forgiven yet. The problem seems to be that in reports properties
the filter line is for example (17), shouldn't there be tunniste=17 (not
parentheses)? How can I put the name of the field in the code?
--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

In the code I posted, it uses the bound column of the list box. If you need
to use a different column, you either need to change the bound column or use
the column you want to filter on. When addressing a list box or combo box,
the column numbers start with 0. So you could use
Me.MyCombo.Column(n) Where n is the column number you want to filter on.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

Should I give up?

There´re three fields in my select list box: employee, "tunniste" and year,
because one employee may have many records and I want to select a specific
year. The boundcolumn is "tunniste". When I look strRptFilter variable name
in Visual Basic it shows the numbers from the field "tunniste" I have chosen.
However all the records print.

How does the report know that the field "tunniste" is the one that sholud be
used as a filter?

Do you give up? I´m sorry I can´t explain better...


--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

Sorry, I don't understand what you are asking in your last post.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

Hi Dave!

I have had a vacation so that I haven't been working with my problem. Yes,
"Valintalista" is the name of the list box control.

strRptFilter works but now some further information: There may be several
times the same employee, so the field "tunniste" is a unique one and
"valintalista" returns the value of "tunniste" from my selection. So the
report should show only these records... "tunniste" is the keyfield.

--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

Is Valintalista the name of the list box control?
The Open report, as written, will print the report. If you want it to open
in Print Preview, you have to tell the report to do that.

DoCmd.OpenReport "Vuosilomapalkkalaskelma", acViewPreview, , strRptFilter

If your report is not filtering as you expect. Run the code in debug mode
with a breakpoint on the OpenReport line. The see what the value of
strRptFilter is.

If you are not familiar with using debug mode, open the form module that for
the form and put the cursor on the OpenReport line. Now press F9. The line
will change color. Now open the form in form view, select some items in the
list box, and click the command button. The editor will open with the
OpenReport line highlighted. Hover over the strRptFilter variable name in
the line and it should show the value of the variable. If you can't see the
entire line, you can open the immediate window with ctrlG. Then type in
?strRptFilter and it will show the value of the variable.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

It seems to me that I can't make this work.

This is what I have in Visual Basic:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Private Sub Vaihto6_Click()
strRptFilter = BuildWhereCondition("Valintalista")
DoCmd.OpenReport "Vuosilomapalkkalaskelma", , , strRptFilter

End Sub

First there was en error but then it vanished and my button PRINTED
(shouldn't "Open Report" only open the report, not print?) ALL the records I
have in my table!

What to do?

Thanks a lot!

Tellu


"Klatuu" kirjoitti:

The function I posted should go in your form module. Paste it in by itself
at the top of the module but after any Option or Dim statements, but before
any event procedures.

These two lines:
strRptFilter = BuildWhereCondition("MyListBox")
Docmd.OpenReport "MyReportName", , , strRptFilter

Should go in the Click event of a command button.
Create a command button on your form.
Open the properties dialog box.
Select the events tab.
Click the small button with the 3 dots to the right of the OnClick event
Select Code Builder
Paste the lines into the VB editor.
You will need to change "MyListBox" to the actual name of your list box
control.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

You may now laugh to my questions, but I have no some basic problems (I know
nothing about Visual Basic):

When I put in the click event there comes
"Private Sub Vaihto6_GotFocus()

End Sub"

Should I take this away or put your function between these things?

And...

Where to put strRpt.... and
Docmd.OpenReport...


--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

There is a way, but it is not necessarily that simple. Here is how I would
do it.
I would not have any filtering in the report's record source query except
that which would apply to the report in all cases regardless of which
employees were selected.

I would have a multi select list box on the form to allow the user to select
all, or any number of employees.

I would have a command button to print the report. In the Click event of
the command button, I would loop through the ItemsSelected collection of the
list box and build a Where string that I would use in the OpenReport method
to filter the report's output.

Here is a function that will build the Where string. If no items are
selected from the list, it assumes you want all employees included;
otherwise, it includes those that are selected:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

I was using the post dated 5/28/08 by Klatuu explaining how to set up VB code
for previewing a report based on items slected (many) from a list
box-multiselect.

this is my code below

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= " & _
ctl.ItemData(ctl.ItemsSelected(0))

Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & .ItemData(varItem) & ", "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

On the On click event of the command button to print what is selelcted is
below:

Private Sub Command13_Click()

Dim stDocName As String
Dim strRptFilter As String(Had to ADD this saying that it wasn't
defined-not sure if really a string???)


'To call it, you pass the name of the list box and assign the results to a
'variable:

strRptFilter = BuildWhereCondition("MyListBox")

'Then you use it to open the report:
DoCmd.OpenReport "LaborReport2 BP", acViewPreview, , strRptFilter
End Sub


With all of that I am getting the error
Runtime error 3075
Syntax error(missing operator) in query expression 'In(16,111,12)'

the Job # is the bound column and it is a number field.

when I do the break and hover over the strRptFilter string it says
strRptfilter = "job # In (16,111,12)"

any ideas on how to get rid of the error???
thanks,
barb

"Klatuu" wrote:

You English is fine.
Since the field you are wanting to compare against is a numeric field, you
need to remove the qoute marks. Change these lines:

strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"

To:

strWhere = "= " & _
ctl.ItemData(ctl.ItemsSelected(0))

And:
strWhere = strWhere & "'" & .ItemData(varItem) & "', "

To:

strWhere = strWhere & .ItemData(varItem) & ", "

--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

Sorry, my lousy english (forgive me).

This is what I have in code:

Private Sub Vaihto6_Click()
strRptFilter = BuildWhereCondition("Valintalista")
strRptFilter = "Tunniste" & strRptFilter
DoCmd.OpenReport "Vuosilomapalkkalaskelma", acViewPreview, , strRptFilter

End Sub

The value of strRptFilter seems to be for example "Tunniste='11'". However
I get a run-time error 3464 (type mismatch in condition or something like
that).
Why are those ' there - are they too much?


--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

Sorry, Tellu, I left out something very important. Once you build the filter
string, you have to put the name of the field at the beginning of it.
Pardon my omission:

strRptFilter = "FieldName" & strRptFilter
Docmd.OpenReport "MyReportName", , , strRptFilter
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

I haven't forgiven yet. The problem seems to be that in reports properties
the filter line is for example (17), shouldn't there be tunniste=17 (not
parentheses)? How can I put the name of the field in the code?
--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

In the code I posted, it uses the bound column of the list box. If you need
to use a different column, you either need to change the bound column or use
the column you want to filter on. When addressing a list box or combo box,
the column numbers start with 0. So you could use
Me.MyCombo.Column(n) Where n is the column number you want to filter on.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

Should I give up?

There´re three fields in my select list box: employee, "tunniste" and year,
because one employee may have many records and I want to select a specific
year. The boundcolumn is "tunniste". When I look strRptFilter variable name
in Visual Basic it shows the numbers from the field "tunniste" I have chosen.
However all the records print.

How does the report know that the field "tunniste" is the one that sholud be
used as a filter?

Do you give up? I´m sorry I can´t explain better...


--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

Sorry, I don't understand what you are asking in your last post.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

Hi Dave!

I have had a vacation so that I haven't been working with my problem. Yes,
"Valintalista" is the name of the list box control.

strRptFilter works but now some further information: There may be several
times the same employee, so the field "tunniste" is a unique one and
"valintalista" returns the value of "tunniste" from my selection. So the
report should show only these records... "tunniste" is the keyfield.

--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

Is Valintalista the name of the list box control?
The Open report, as written, will print the report. If you want it to open
in Print Preview, you have to tell the report to do that.

DoCmd.OpenReport "Vuosilomapalkkalaskelma", acViewPreview, , strRptFilter

If your report is not filtering as you expect. Run the code in debug mode
with a breakpoint on the OpenReport line. The see what the value of
strRptFilter is.

If you are not familiar with using debug mode, open the form module that for
the form and put the cursor on the OpenReport line. Now press F9. The line
will change color. Now open the form in form view, select some items in the
list box, and click the command button. The editor will open with the
OpenReport line highlighted. Hover over the strRptFilter variable name in
the line and it should show the value of the variable. If you can't see the
entire line, you can open the immediate window with ctrlG. Then type in
?strRptFilter and it will show the value of the variable.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

It seems to me that I can't make this work.

This is what I have in Visual Basic:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Private Sub Vaihto6_Click()
strRptFilter = BuildWhereCondition("Valintalista")
DoCmd.OpenReport "Vuosilomapalkkalaskelma", , , strRptFilter

End Sub

First there was en error but then it vanished and my button PRINTED
(shouldn't "Open Report" only open the report, not print?) ALL the records I
have in my table!

What to do?

Thanks a lot!

Tellu


"Klatuu" kirjoitti:

The function I posted should go in your form module. Paste it in by itself
at the top of the module but after any Option or Dim statements, but before
any event procedures.

These two lines:
strRptFilter = BuildWhereCondition("MyListBox")
Docmd.OpenReport "MyReportName", , , strRptFilter

Should go in the Click event of a command button.
Create a command button on your form.
Open the properties dialog box.
Select the events tab.
Click the small button with the 3 dots to the right of the OnClick event
Select Code Builder
Paste the lines into the VB editor.
You will need to change "MyListBox" to the actual name of your list box
control.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

You may now laugh to my questions, but I have no some basic problems (I know
nothing about Visual Basic):

When I put in the click event there comes
"Private Sub Vaihto6_GotFocus()

End Sub"

Should I take this away or put your function between these things?

And...

Where to put strRpt.... and
Docmd.OpenReport...


--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

There is a way, but it is not necessarily that simple. Here is how I would
do it.
I would not have any filtering in the report's record source query except
that which would apply to the report in all cases regardless of which
employees were selected.

I would have a multi select list box on the form to allow the user to select
all, or any number of employees.

I would have a command button to print the report. In the Click event of
the command button, I would loop through the ItemsSelected collection of the
list box and build a Where string that I would use in the OpenReport method
to filter the report's output.

Here is a function that will build the Where string. If no items are
selected from the list, it assumes you want all employees included;
otherwise, it includes those that are selected:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Hi Barb,

Not sure how/why you're getting...
strRptfilter = "job # In (16,111,12)"
when hovering over strRptFilter in break mode.

In your code the field name job # is never appended to the start of
strRptFilter. This needs to be done, and as there seems to a space in the
field name then it would need to be enclosed in square brackets.

Try replacing your line of code:
strRptFilter = BuildWhereCondition("MyListBox")

With the line of code:
strRptFilter = "[job #]" & BuildWhereCondition("MyListBox")

HTH
Sam

"babs" wrote in message
...

I was using the post dated 5/28/08 by Klatuu explaining how to set up VB
code
for previewing a report based on items slected (many) from a list
box-multiselect.

this is my code below

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= " & _
ctl.ItemData(ctl.ItemsSelected(0))

Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & .ItemData(varItem) & ", "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

On the On click event of the command button to print what is selelcted is
below:

Private Sub Command13_Click()

Dim stDocName As String
Dim strRptFilter As String(Had to ADD this saying that it wasn't
defined-not sure if really a string???)


'To call it, you pass the name of the list box and assign the results to a
'variable:

strRptFilter = BuildWhereCondition("MyListBox")

'Then you use it to open the report:
DoCmd.OpenReport "LaborReport2 BP", acViewPreview, , strRptFilter
End Sub


With all of that I am getting the error
Runtime error 3075
Syntax error(missing operator) in query expression 'In(16,111,12)'

the Job # is the bound column and it is a number field.

when I do the break and hover over the strRptFilter string it says
strRptfilter = "job # In (16,111,12)"

any ideas on how to get rid of the error???
thanks,
barb

"Klatuu" wrote:

You English is fine.
Since the field you are wanting to compare against is a numeric field,
you
need to remove the qoute marks. Change these lines:

strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"

To:

strWhere = "= " & _
ctl.ItemData(ctl.ItemsSelected(0))

And:
strWhere = strWhere & "'" & .ItemData(varItem) & "',
"

To:

strWhere = strWhere & .ItemData(varItem) & ", "

--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

Sorry, my lousy english (forgive me).

This is what I have in code:

Private Sub Vaihto6_Click()
strRptFilter = BuildWhereCondition("Valintalista")
strRptFilter = "Tunniste" & strRptFilter
DoCmd.OpenReport "Vuosilomapalkkalaskelma", acViewPreview, ,
strRptFilter

End Sub

The value of strRptFilter seems to be for example "Tunniste='11'".
However
I get a run-time error 3464 (type mismatch in condition or something
like
that).
Why are those ' there - are they too much?


--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

Sorry, Tellu, I left out something very important. Once you build
the filter
string, you have to put the name of the field at the beginning of
it.
Pardon my omission:

strRptFilter = "FieldName" & strRptFilter
Docmd.OpenReport "MyReportName", , , strRptFilter
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

I haven't forgiven yet. The problem seems to be that in reports
properties
the filter line is for example (17), shouldn't there be tunniste=17
(not
parentheses)? How can I put the name of the field in the code?
--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

In the code I posted, it uses the bound column of the list box.
If you need
to use a different column, you either need to change the bound
column or use
the column you want to filter on. When addressing a list box or
combo box,
the column numbers start with 0. So you could use
Me.MyCombo.Column(n) Where n is the column number you want to
filter on.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

Should I give up?

There´re three fields in my select list box: employee,
"tunniste" and year,
because one employee may have many records and I want to select
a specific
year. The boundcolumn is "tunniste". When I look strRptFilter
variable name
in Visual Basic it shows the numbers from the field "tunniste"
I have chosen.
However all the records print.

How does the report know that the field "tunniste" is the one
that sholud be
used as a filter?

Do you give up? I´m sorry I can´t explain better...


--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

Sorry, I don't understand what you are asking in your last
post.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

Hi Dave!

I have had a vacation so that I haven't been working with
my problem. Yes,
"Valintalista" is the name of the list box control.

strRptFilter works but now some further information: There
may be several
times the same employee, so the field "tunniste" is a
unique one and
"valintalista" returns the value of "tunniste" from my
selection. So the
report should show only these records... "tunniste" is the
keyfield.

--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

Is Valintalista the name of the list box control?
The Open report, as written, will print the report. If
you want it to open
in Print Preview, you have to tell the report to do that.

DoCmd.OpenReport "Vuosilomapalkkalaskelma",
acViewPreview, , strRptFilter

If your report is not filtering as you expect. Run the
code in debug mode
with a breakpoint on the OpenReport line. The see what
the value of
strRptFilter is.

If you are not familiar with using debug mode, open the
form module that for
the form and put the cursor on the OpenReport line. Now
press F9. The line
will change color. Now open the form in form view,
select some items in the
list box, and click the command button. The editor will
open with the
OpenReport line highlighted. Hover over the strRptFilter
variable name in
the line and it should show the value of the variable.
If you can't see the
entire line, you can open the immediate window with
ctrlG. Then type in
?strRptFilter and it will show the value of the variable.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

It seems to me that I can't make this work.

This is what I have in Visual Basic:

Private Function BuildWhereCondition(strControl As
String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) &
"'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" &
.ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) -
2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Private Sub Vaihto6_Click()
strRptFilter = BuildWhereCondition("Valintalista")
DoCmd.OpenReport "Vuosilomapalkkalaskelma", , ,
strRptFilter

End Sub

First there was en error but then it vanished and my
button PRINTED
(shouldn't "Open Report" only open the report, not
print?) ALL the records I
have in my table!

What to do?

Thanks a lot!

Tellu


"Klatuu" kirjoitti:

The function I posted should go in your form module.
Paste it in by itself
at the top of the module but after any Option or Dim
statements, but before
any event procedures.

These two lines:
strRptFilter = BuildWhereCondition("MyListBox")
Docmd.OpenReport "MyReportName", , , strRptFilter

Should go in the Click event of a command button.
Create a command button on your form.
Open the properties dialog box.
Select the events tab.
Click the small button with the 3 dots to the right
of the OnClick event
Select Code Builder
Paste the lines into the VB editor.
You will need to change "MyListBox" to the actual
name of your list box
control.
--
Dave Hargis, Microsoft Access MVP


"Tellu" wrote:

You may now laugh to my questions, but I have no
some basic problems (I know
nothing about Visual Basic):

When I put in the click event there comes
"Private Sub Vaihto6_GotFocus()

End Sub"

Should I take this away or put your function
between these things?

And...

Where to put strRpt.... and
Docmd.OpenReport...


--
Thanks a lot!

Tellu


"Klatuu" kirjoitti:

There is a way, but it is not necessarily that
simple. Here is how I would
do it.
I would not have any filtering in the report's
record source query except
that which would apply to the report in all cases
regardless of which
employees were selected.

I would have a multi select list box on the form
to allow the user to select
all, or any number of employees.

I would have a command button to print the
report. In the Click event of
the command button, I would loop through the
ItemsSelected collection of the
list box and build a Where string that I would
use in the OpenReport method
to filter the report's output.

Here is a function that will build the Where
string. If no items are
selected from the list, it assumes you want all
employees included;
otherwise, it includes those that are selected:

Private Function BuildWhereCondition(strControl
As String) As String
'Set up the WhereCondition Argument for the
reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _

ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In
.ItemsSelected
strWhere = strWhere & "'" &
.ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere,
Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function