-
Pls need hlp with Report
Hi, I have database with form and report. The form has list of individuals with rows of data. When the data is listed in the form, I can highlighte the rows I want to print to report. However, my issue is that there maybe several rows for one member and when I highlight only one row, all the rows for that member are listed in my report. This is the code that I have now. How can I revise the code to only send to report print the highlighted rows only even if there are other rows for the same member??? Any help with this would be very much appreciated. Thank you.
Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim strWhere As String
Dim ctl As Control1
Dim varItem As Variant
'make sure a selection has been made
If Me.lstmember.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 employee"
Exit Sub
End If
'add selected values to string
Set ctl = Me.lstmember
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
Next VarItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) -1)
'open the report, restricted to the selected items
DoCmd.OpenReport "rpt_fnl_cm_mbrs3", acPreview, , "member IN(" & strWhere & ")"
Exit_cmdOpenReport_Click:
Exit Sub
Err_cmdOpenReport_Click:
MsgBox Err.Description
resume Exit_cmdOpenReport_Click
End Sub
Err_
-
There would need to be another field you could use to identify the selected record. You'd use it either in addition to the member field you're using now, or instead of.
-
Hi, thank you so much for your quick response. Ok I found another field that I can add. How do I write that into the code? The filed name is "case_id"??? I need to print to report either rows with ,r,nember and case_id or just member. Can you please show me how to add this to the code above? thank you.
Last edited by Nyrubi; 03-28-2017 at 09:46 PM.
-
Me.lstmember is selecting a member, you want it selecting your case_Id. If its broken down to have each case ID on that record it should work.
-
I am sorry but I can't seem to get this to work. Below is the code i have for a list box list box is referenced in the code (lstmember) that has several fields. I replaced the field to Case_ID so that only row highlighted would print to report instead of all the rows associated to the member. This is not working. when I test it I get the following error message. "Syntax error (missing operator) in query expression 'Case_ID In(,)'. Can someone help please? Thank you.
Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim strWhere As String
Dim ctl As Control1
Dim varItem As Variant
'make sure a selection has been made
If Me.lstmember.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 employee"
Exit Sub
End If
'add selected values to string
Set ctl = Me.lstmember
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
Next VarItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) -1)
'open the report, restricted to the selected items
DoCmd.OpenReport "rpt_fnl_cm_mbrs3", acPreview, , "member IN(" & strWhere & ")"
Exit_cmdOpenReport_Click:
Exit Sub
Err_cmdOpenReport_Click:
MsgBox Err.Description
resume Exit_cmdOpenReport_Click
End Sub
-
Where is that error message coming from? Your code says "member IN".
-
oops I copied the code above but in my db I changed the member In to read "case_ID IN" and I am getting the error "Syntax error (missing operator) in query expression 'Case_ID In(,)'. I tested further and when I select a row without the case_id I get the message. How can I make it so that whether I choose a case id or a non case id row it will print to the report??? Thank you.
-
If there's no case ID, how is it to open the report filtered to case ID?
-
Can it have a combination of Case ID and Member or Member or Case ID. If it can how do I write that into code please? thank you.
-
Not sure I understand. How about a clear description of what the listbox contains, and what you want to happen based on what may or may not be selected. Attaching the db here may clarify matters as well.
-
Hi,
I can't put copy of db due to company privacy issues. I m going to try and describe what I have. I have form where the list member box pulls the data from query. In the list box, these the fields member, case id, gndr, type, date open, date close, # of days, diag, results, co name, co number.
The list box will list members and cases for certain company. From the list I have to highlight either cases that have a case number and member number or just a case number or just a member number. Once they are highlighted, I click on a button and print view a report based only on the highlighted items. That is what the code above does but only if there is a member number or if I change the member number to case id. But I need to print view report with a combination of items. I hope you can help me. Thank you.
-
Hi, just want to let you know that the way I figured this out. I created a new field and it is an "autonumber" field and I bound this field to the list box. Changed the code above to read DoCmd.OpenReport "rpt_fnl_cm_mbrs3", acPreview, , "autonumber IN(" & strWhere & ")". Allows me to highlight a field that has a member number and a case id or just case id to print view to report. So far I have tested and all is good. Hope this will help someone else.
Thank you.
-
Glad you got it working. That's the type of thing I was suggesting in post 2.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules