Open report button to select data from 7 list boxes and present in a report (1 Viewer)

Gavin59 · Aug 14, 2015

I have a form with 7 List boxes linked to 7 Query's which in turn are linked to a table.
Each list box if for a particular trade.

I am trying to select a person or persons from each List box and then have them sent to a report. I have Code to do one list box, but do not know how to link all boxes with code to a 'Open report' button.

The code I am using is as follows:-

Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
If Me.HLO.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 employee"
Exit Sub
End If
'add selected values to string
Set ctl = Me.HLO
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 "rptEmployees", acViewReport, , "EmpID IN(" & strWhere & ")"

Exit_cmdOpenReport_Click:
Exit Sub
Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click
End Sub

I have attached my efforts with the hope it may help to understand

It may be that there is a better way of approaching this and any suggestions greatly received. Excel more my thing, but I would like to be come more involved with Access as I see it has many possibilities.

Many thanks inadvance

pbaldy · Aug 14, 2015

Basically build a string for each and put them together:

"EmpID IN(" & strWhere & ") And Field2 In(" & strWhere2 & ")"

Gavin59 · Aug 14, 2015

Many thanks pbaldy for writing back.
I can see what you mean but where in my code do I insert this string.

Does Field2 refer to another name field heading. ie should I change Field2 to a heading in my table.

How do you join strings together?

many thanks for any directional help.

pbaldy · Aug 14, 2015

Field2 would be replaced by the field name for the second listbox. I thought my example demonstrated how to join strings together.

Gavin59 · Aug 14, 2015

Many thanks pbaldy,

I'm new to this, so all pointers greatly received. I'll try as you suggested. Just to clarify, does this go in the same line as the 'DoCmd.OpenReport'?
Many thanks

pbaldy · Aug 14, 2015

Yes, you're modifying the wherecondition argument of OpenReport to include more fields. Instead of what it is now:

"EmpID IN(" & strWhere & ")"

it will be more like:

"EmpID IN(" & strWhere & ") And Field2 In(" & strWhere2 & ")"

which is 2 fields, so yours will be longer. Because it will be longer and tricker to debug, I'd build it in stages (get 2 working, then get 3 working, etc), and use a variable:

strWhatever = "EmpID IN(" & strWhere & ") And Field2 In(" & strWhere2 & ")"

DoCmd.OpenReport "rptEmployees", acViewReport, , strWhatever

Gavin59 · Aug 14, 2015

Thanks for your reply pbaldy,

Understand about building up the string slowly, testing each step.

I have inserted my code amendments in blue. I tried using strWhatever but does not recognise statement so changed it back to strWhere.

Now saying : Syntax error ( missing Operator) in query expression 'EmpID IN(8,)DGA In(8,)'.

I am really struggling here.
Set ctl = Me.HLO refers to my first list box. Would it help if I were to add anymore controls for the other list boxes?

Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
If Me.HLO.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 employee"
Exit Sub
End If
'add selected values to string
Set ctl = Me.HLO
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
Next varItem
'trim trailing comma
strWhere = "EmpID IN(" & strWhere & ")DGA In(" & strWhere & ")"
'open the report, restricted to the selected items
DoCmd.OpenReport "rptEmployees", acViewReport, , strWhere

Exit_cmdOpenReport_Click:
Exit Sub
Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click
End Sub

Many thanks again in advance

pbaldy · Aug 14, 2015

For starters you didn't include the word AND between the criteria. More importantly, you'll need to loop each listbox separately, so this whole block will be repeated for each:

If Me.HLO.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 employee"
Exit Sub
End If
'add selected values to string
Set ctl = Me.HLO
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)

Also, you'd want to use a different variable for each, so instead of strWhere perhaps strHLO, strDGA, etc. Within each block, you'd set ctl equal to the listbox being looped.

Gavin59 · Aug 17, 2015

Good morning pbaldy,

I have been trying a few things. I can now open the report from any name selected from any list box. However all names appear in the report and not the selected names which is what I need.

Here is my code. It looks messy.

Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim strWhere As String
Dim strHLO As String
Dim strDGA As String
Dim strHTM As String
Dim strHA As String
Dim strRadio As String
Dim strReception As String
Dim ctl As Control
Dim varItem As Variant

'add selected values to string
Set ctl = Me.HLO
For Each varItem In ctl.ItemsSelected
strWhere = strHLO & ctl.ItemData(varItem) & ","
Next varItem
Set ctl = Me.DGA
For Each varItem In ctl.ItemsSelected
strWhere = strDGA & ctl.ItemData(varItem) & ","
Next varItem
Set ctl = Me.HTM
For Each varItem In ctl.ItemsSelected
strWhere = strHA & ctl.ItemData(varItem) & ","
Next varItem
Set ctl = Me.HA
For Each varItem In ctl.ItemsSelected
strWhere = strHTM & ctl.ItemData(varItem) & ","
Next varItem
Set ctl = Me.Radio
For Each varItem In ctl.ItemsSelected
strWhere = strRadio & ctl.ItemData(varItem) & ","
Next varItem
Set ctl = Me.Reception
For Each varItem In ctl.ItemsSelected
strWhere = strReception & ctl.ItemData(varItem) & ","
Next varItem

'trim trailing comma
strWhere = Left(strHLO, Len(strWhere) - 1)

'open the report, restricted to the selected items
DoCmd.OpenReport "rptEmployees", acViewReport, , strWhere

Exit_cmdOpenReport_Click:
Exit Sub
Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click
End Sub

arnelgp · Aug 17, 2015

you messed up alright, check and study the code.

Gavin59 · Sep 16, 2015

Good afternoon, I'm back again.
I have been looking at what you have given me. Many thanks. I have adjusted some of the criteria in the queries. How ever I am not getting the order in the report that I require.

I need to have the report show in this order

HLO, Dangerous Goods specialist, Helideck Team Member, Helideck Assistant, Radio Operator Heli Reception.

Where abouts would I need to amend to have the desired order?

many thanks,

Gavin59

Gavin59 · Sep 16, 2015

Sorry Forgot to attach lastest efforts

arnelgp · Sep 16, 2015

please try:

arnelgp · Sep 16, 2015

sorry didn't noticed the new one.

Gavin59 · Sep 16, 2015

Many thanks for you reply.

I see that you have inserted an extra column in qryAllEmployees.
That works a treat.

I'll finish this project now and then insert the finished thing so that other people may benefit from it.

Many thanks again

Gavin59 · Mar 6, 2016

Good morning I'm back again. I have been trying to improve on the database and have managed to sort most things out.
However I am stuck again.
If I do not select 'Dangerous Goods Specialist' I need to have on the report 'Freight Must not be loaded on this flight', and when I do, then no comment.

Whats the best way to achive this?

Also how do I put a frame around the report when it's printed?

File size 4.75MB so have Zip attachement. Hope it works.

Many thanks in advance

pbaldy · Mar 7, 2016

You already have an IIf() that would do this. Is it not working correctly? Where you have it, it would use the first record. If you wanted to test whether one or more records had that position, this would work (though not in the page footer, only in the report footer or a group footer):

=Sum(IIf([Position]="Dangerous Goods Specialist",1,0))

Gavin59 · Mar 7, 2016

Good evening Paul
Many thanks for your reply.

I have tried your solution and it does indeed count the number of Dangerous goods specialist. So that is handy to know but not what I am after.

However, What I am after is, If Dangerous Goods Specialist (DGS), is not present in the report, then a message to be flagged up, " NO Freight" and if a DGS is present in the report, then a Blank.

I have used the IIf function and it works to a degree. It seems to be reading all positions and it recognises other positions and flags a message "No Freight" regardless if a DGS has been selected.
This is what I have used but not sure how to make it work for what I need.
=IIf([Position]="Dangerous Goods Specialist","","No Freight")

Not sure If I have made my self clear, but any pointers would be greatfully appreciated.

Again,
many thanks for you help and time

pbaldy · Mar 7, 2016

I realize it isn't what you're after directly, but if gives you something to test in your IIF(), does it not?

Gavin59 · Mar 8, 2016

Good morning Paul

I have had a look through various things.

I found that by changing the order of the SORT in my qryAllEmployees I have the Dangerous Goods Specialist at the bottom of the report, and the Iif Function now recognises this. So if you now select all persons except the DGS in the frmOpenReport the message No Freight now appears on the Report page and also when the DGS is selected the Message clears.

So to a degree it works but I'm sure there is a better and correct method that should be used to reach the same result.

I also have the same person in the tblEmployees twice. ( Because they have duel Job descriptions) I have had to put a fullstop (.) at the end of on entry as it really messes up the SORT.

Still new at this Access work so any pointers welcomed.
I have attached my latest efforts

Open report button to select data from 7 list boxes and present in a report (1 Viewer)

Registered User.

Attachments

Wino Moderator

Registered User.

Wino Moderator

Registered User.

Wino Moderator

Registered User.

Wino Moderator

Registered User.

..forever waiting... waiting for jellybean!

Attachments

Registered User.

Registered User.

Attachments

..forever waiting... waiting for jellybean!

Attachments

..forever waiting... waiting for jellybean!

Attachments

Registered User.

Registered User.

Attachments

Wino Moderator

Registered User.

Wino Moderator

Registered User.

Attachments

Similar threads

Users who are viewing this thread