If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Printing report based on employee selection
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 |
#3
|
|||
|
|||
Printing report based on employee selection
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 |
#4
|
|||
|
|||
Printing report based on employee selection
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 |
#5
|
|||
|
|||
Printing report based on employee selection
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 |
#6
|
|||
|
|||
Printing report based on employee selection
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 |
#7
|
|||
|
|||
Printing report based on employee selection
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 |
#8
|
|||
|
|||
Printing report based on employee selection
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 |
#9
|
|||
|
|||
Printing report based on employee selection
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 |
#10
|
|||
|
|||
Printing report based on employee selection
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 |
|
Thread Tools | |
Display Modes | |
|
|