Printing report based on employee selection

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

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 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

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

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

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

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, 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

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

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