Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-13-2015, 08:12 PM   #1
Gavin59
Newly Registered User
 
Join Date: Jul 2015
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Gavin59 is on a distinguished road
Open report button to select data from 7 list boxes and present in a report

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
Attached Files
File Type: accdb HeliTeam1.accdb (1.19 MB, 50 views)

Gavin59 is offline   Reply With Quote
Old 08-13-2015, 08:58 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,390
Thanks: 13
Thanked 4,124 Times in 4,056 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Open report button to select data from 7 list boxes and present in a report

Basically build a string for each and put them together:

"EmpID IN(" & strWhere & ") And Field2 In(" & strWhere2 & ")"
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 08-14-2015, 11:51 AM   #3
Gavin59
Newly Registered User
 
Join Date: Jul 2015
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Gavin59 is on a distinguished road
Re: Open report button to select data from 7 list boxes and present in a report

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.

Gavin59 is offline   Reply With Quote
Old 08-14-2015, 12:20 PM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,390
Thanks: 13
Thanked 4,124 Times in 4,056 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Open report button to select data from 7 list boxes and present in a report

Field2 would be replaced by the field name for the second listbox. I thought my example demonstrated how to join strings together.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 08-14-2015, 01:37 PM   #5
Gavin59
Newly Registered User
 
Join Date: Jul 2015
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Gavin59 is on a distinguished road
Re: Open report button to select data from 7 list boxes and present in a report

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
Gavin59 is offline   Reply With Quote
Old 08-14-2015, 01:51 PM   #6
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,390
Thanks: 13
Thanked 4,124 Times in 4,056 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Open report button to select data from 7 list boxes and present in a report

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
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 08-14-2015, 02:36 PM   #7
Gavin59
Newly Registered User
 
Join Date: Jul 2015
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Gavin59 is on a distinguished road
Re: Open report button to select data from 7 list boxes and present in a report

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

Gavin59 is offline   Reply With Quote
Old 08-14-2015, 02:46 PM   #8
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,390
Thanks: 13
Thanked 4,124 Times in 4,056 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Open report button to select data from 7 list boxes and present in a report

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.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 08-16-2015, 07:40 PM   #9
Gavin59
Newly Registered User
 
Join Date: Jul 2015
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Gavin59 is on a distinguished road
Re: Open report button to select data from 7 list boxes and present in a report

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
Gavin59 is offline   Reply With Quote
Old 08-16-2015, 09:27 PM   #10
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Open report button to select data from 7 list boxes and present in a report

you messed up alright, check and study the code.
Attached Files
File Type: accdb HeliTeam1.accdb (672.0 KB, 46 views)
arnelgp is offline   Reply With Quote
Old 09-16-2015, 05:25 AM   #11
Gavin59
Newly Registered User
 
Join Date: Jul 2015
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Gavin59 is on a distinguished road
Re: Open report button to select data from 7 list boxes and present in a report

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 is offline   Reply With Quote
Old 09-16-2015, 05:27 AM   #12
Gavin59
Newly Registered User
 
Join Date: Jul 2015
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Gavin59 is on a distinguished road
Re: Open report button to select data from 7 list boxes and present in a report

Sorry Forgot to attach lastest efforts
Attached Files
File Type: accdb HeliTeam4.accdb (948.0 KB, 32 views)
Gavin59 is offline   Reply With Quote
Old 09-16-2015, 06:24 AM   #13
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Open report button to select data from 7 list boxes and present in a report

please try:
Attached Files
File Type: accdb HeliTeam1.accdb (864.0 KB, 33 views)
arnelgp is offline   Reply With Quote
Old 09-16-2015, 06:36 AM   #14
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Open report button to select data from 7 list boxes and present in a report

sorry didn't noticed the new one.
Attached Files
File Type: accdb HeliTeam4.accdb (1.04 MB, 45 views)
arnelgp is offline   Reply With Quote
Old 09-16-2015, 08:53 AM   #15
Gavin59
Newly Registered User
 
Join Date: Jul 2015
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Gavin59 is on a distinguished road
Re: Open report button to select data from 7 list boxes and present in a report

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 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Select multiple data in a list box to execute a query into report kevinv Forms 0 05-06-2013 11:02 AM
Open/Create a report based on form list box with multi select data. dstone10 Reports 8 12-01-2012 01:35 AM
Two List Boxes - Attempting to modify sample db - Multi Select list boxes as criteri CamL Forms 1 12-12-2008 10:28 AM
Report controlled by multiple multi select list boxes? kbrooks Reports 1 02-14-2008 05:12 PM
Button to open a report with ONLY that record number data in it Leopardfist Reports 10 02-22-2007 05:33 PM




All times are GMT -8. The time now is 01:06 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World