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 |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
Printing report based on employee selection
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 |
#13
|
|||
|
|||
Printing report based on employee selection
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 |
#14
|
|||
|
|||
Printing report based on employee selection
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! |
#15
|
|||
|
|||
Printing report based on employee selection
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 |
#16
|
|||
|
|||
Printing report based on employee selection
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 |
#17
|
|||
|
|||
Printing report based on employee selection
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 |
#18
|
|||
|
|||
Printing report based on employee selection
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 |
|
Thread Tools | |
Display Modes | |
|
|