By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,464 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Populating the Criteria in a Query with the values from a Multilist box

MattFitzgerald
P: 30
Please Help!

I have a form [Frm_Main_Menu]
in this form I have a list box[List1ViewStatusSelect], I have set the property of Multi Select to Extended

I also have a query [Qry_OV Pending Cancelled Null]
in this query I have a field [Status] on which I wish to filter by my Multi Select List Box.

I have tried the following 3 things in the criteria of my query with no sucess:-

Forms![Frm_Main_Menu]![List1ViewStatusSelect]

Forms![Frm_Main_Menu]![List1ViewStatusSelect].MultiSelect

[Forms]![Frm_Main_Menu]![List1ViewStatusSelect].[ItemsSelected]

I have looked through the forum and think I need to use the following code:-

Expand|Select|Wrap|Line Numbers
  1. Dim Val1ViewStatusSelect As Variant
  2. Dim str1ViewStatusSelect As String 
  3.  
  4.     For Each Val1ViewStatusSelect In Me.listboxName.ItemsSelected
  5.         str1ViewStatusSelect = str1ViewStatusSelect & "'" & Frm_Main_Menu.List1ViewStatusSelect.ItemData(Val1ViewStatusSelect) & "', "
  6.     Next Val1ViewStatusSelect
  7.  
  8.     ' to remove trailing comma
  9.     str1ViewStatusSelect = Left(str1ViewStatusSelect, Len(str1ViewStatusSelect)-2)
I am only guessing at the code but think I may have it correct,
The problem I am having is that I do not know anything about code so I am unsure :-

If I need to attach the code to an event procedure on one of the properties of the list box[List1ViewStatusSelect] in form [Frm_Main_Menu] and then some how call the variant [Val1ViewStatusSelect] in the criteria of my query [Qry_OV Pending Cancelled Null]

or

If I should put the code somewhere in my Query [Qry_OV Pending Cancelled Null]
Sep 11 '07 #1
Share this Question
Share on Google+
8 Replies


MattFitzgerald
P: 30
Update on my problem:

I have tried adding the below code to the on click property of the command button for a report which calls the data from my query but I get the message "Object required" which is a little to ambiguous as I am new to access I do not know what object and why it is requiered.

Expand|Select|Wrap|Line Numbers
  1. Private Sub CmdPending_Report_Click()
  2. On Error GoTo Err_CmdPending_Report_Click
  3.  
  4.     Dim stDocName As String, Val1ViewStatusSelect As Variant, str1ViewStatusSelect As String
  5.  
  6. ' Concatenates all Status selected in the list box
  7.     For Each Val1ViewStatusSelect In Me.List1ViewStatusSelect.ItemsSelected
  8.         str1ViewStatusSelect = str1ViewStatusSelect & "'" & Frm_Main_Menu.List1ViewStatusSelect.ItemData(Val1ViewStatusSelect) & "', "
  9.     Next Val1ViewStatusSelect
  10.  
  11. ' To remove trailing comma
  12.     str1ViewStatusSelect = Left(str1ViewStatusSelect, Len(str1ViewStatusSelect) - 2)
  13.  
  14. ' Open the Report filtered on the selected Status
  15.     stDocName = "RPT_OV Pending Cancelled Null"
  16.     DoCmd.OpenReport stDocName, acPreview, , [Status] = str1ViewStatusSelect
  17.  
  18. Exit_CmdPending_Report_Click:
  19.     Exit Sub
  20.  
  21. Err_CmdPending_Report_Click:
  22.     MsgBox Err.Description
  23.     Resume Exit_CmdPending_Report_Click
  24.  
  25. End Sub
I am blundering through and hopefully not compounding any errors I have already made. All help is much appreciated as I am either going to solve this or really get myself in a mess
Sep 12 '07 #2

FishVal
Expert 2.5K+
P: 2,653
Update on my problem:

I have tried adding the below code to the on click property of the command button for a report which calls the data from my query but I get the message "Object required" which is a little to ambiguous as I am new to access I do not know what object and why it is requiered.

Expand|Select|Wrap|Line Numbers
  1. Private Sub CmdPending_Report_Click()
  2. On Error GoTo Err_CmdPending_Report_Click
  3.  
  4.     Dim stDocName As String, Val1ViewStatusSelect As Variant, str1ViewStatusSelect As String
  5.  
  6. ' Concatenates all Status selected in the list box
  7.     For Each Val1ViewStatusSelect In Me.List1ViewStatusSelect.ItemsSelected
  8.         str1ViewStatusSelect = str1ViewStatusSelect & "'" & Frm_Main_Menu.List1ViewStatusSelect.ItemData(Val1ViewStatusSelect) & "', "
  9.     Next Val1ViewStatusSelect
  10.  
  11. ' To remove trailing comma
  12.     str1ViewStatusSelect = Left(str1ViewStatusSelect, Len(str1ViewStatusSelect) - 2)
  13.  
  14. ' Open the Report filtered on the selected Status
  15.     stDocName = "RPT_OV Pending Cancelled Null"
  16.     DoCmd.OpenReport stDocName, acPreview, , [Status] = str1ViewStatusSelect
  17.  
  18. Exit_CmdPending_Report_Click:
  19.     Exit Sub
  20.  
  21. Err_CmdPending_Report_Click:
  22.     MsgBox Err.Description
  23.     Resume Exit_CmdPending_Report_Click
  24.  
  25. End Sub
I am blundering through and hopefully not compounding any errors I have already made. All help is much appreciated as I am either going to solve this or really get myself in a mess
Hi, Matt.

You have wrong SQL syntax in criteria string you pass to DoCmd.OpenReport method.
The right syntax for multiple comparisson is
[FieldName] IN (Value1, Value2, ... ValueN)
Sep 12 '07 #3

MattFitzgerald
P: 30
Thanks Fishval

I have tried to take on board your reply but I think I have missed something in the way I have applied it as I now recieve Compile error: Expected end of statement when typing the code I think it should be.

My code now looks like this:-

Expand|Select|Wrap|Line Numbers
  1. Private Sub CmdPending_Report_Click()
  2. On Error GoTo Err_CmdPending_Report_Click
  3.  
  4.     Dim stDocName As String, Val1ViewStatusSelect As Variant, str1ViewStatusSelect As String
  5.  
  6. ' Concatenates all Status selected in the list box
  7.     For Each Val1ViewStatusSelect In Me.List1ViewStatusSelect.ItemsSelected
  8.         str1ViewStatusSelect = str1ViewStatusSelect & "'" & Frm_Main_Menu.List1ViewStatusSelect.ItemData(Val1ViewStatusSelect) & "', "
  9.     Next Val1ViewStatusSelect
  10.  
  11. ' To remove trailing comma
  12.     str1ViewStatusSelect = Left(str1ViewStatusSelect, Len(str1ViewStatusSelect) - 2)
  13.     str1ViewStatusSelect = "( " & str1ViewStatusSelect & " )"
  14.  
  15. ' Open the Report filtered on the selected Status
  16.     stDocName = "RPT_OV Pending Cancelled Null"
  17.     DoCmd.OpenReport stDocName, acPreview, , [Status]IN str1ViewStatusSelect
  18.  
  19. Exit_CmdPending_Report_Click:
  20.     Exit Sub
  21.  
  22. Err_CmdPending_Report_Click:
  23.     MsgBox Err.Description
  24.     Resume Exit_CmdPending_Report_Click
  25.  
  26. End Sub
The line of code with the error is

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport stDocName, acPreview, , [Status]IN str1ViewStatusSelect
I think the answer is almost there I just need another push in the right direction
Sep 13 '07 #4

FishVal
Expert 2.5K+
P: 2,653
Thanks Fishval

I have tried to take on board your reply but I think I have missed something in the way I have applied it as I now recieve Compile error: Expected end of statement when typing the code I think it should be.

My code now looks like this:-

Expand|Select|Wrap|Line Numbers
  1. Private Sub CmdPending_Report_Click()
  2. On Error GoTo Err_CmdPending_Report_Click
  3.  
  4.     Dim stDocName As String, Val1ViewStatusSelect As Variant, str1ViewStatusSelect As String
  5.  
  6. ' Concatenates all Status selected in the list box
  7.     For Each Val1ViewStatusSelect In Me.List1ViewStatusSelect.ItemsSelected
  8.         str1ViewStatusSelect = str1ViewStatusSelect & "'" & Frm_Main_Menu.List1ViewStatusSelect.ItemData(Val1ViewStatusSelect) & "', "
  9.     Next Val1ViewStatusSelect
  10.  
  11. ' To remove trailing comma
  12.     str1ViewStatusSelect = Left(str1ViewStatusSelect, Len(str1ViewStatusSelect) - 2)
  13.     str1ViewStatusSelect = "( " & str1ViewStatusSelect & " )"
  14.  
  15. ' Open the Report filtered on the selected Status
  16.     stDocName = "RPT_OV Pending Cancelled Null"
  17.     DoCmd.OpenReport stDocName, acPreview, , [Status]IN str1ViewStatusSelect
  18.  
  19. Exit_CmdPending_Report_Click:
  20.     Exit Sub
  21.  
  22. Err_CmdPending_Report_Click:
  23.     MsgBox Err.Description
  24.     Resume Exit_CmdPending_Report_Click
  25.  
  26. End Sub
The line of code with the error is

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport stDocName, acPreview, , [Status]IN str1ViewStatusSelect
I think the answer is almost there I just need another push in the right direction
Hi, Matt.

WhereCondition argument of DoCmd.OpenReport has to be string type.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport stDocName, acPreview, , "[Status] IN " & str1ViewStatusSelect
Sep 13 '07 #5

MattFitzgerald
P: 30
FishVal

Thanks once again

I tried your suggestion but I think there was something else wrong with my code. I found in a book on Access containing a section on trouble shooting where I found out about break points and setting a watch on my Variables and Strings.

I could see from watching my variable that the values from my variable did not populate my string as expected.

I have since found another example of code and have tried to alter this to my requirements and now my string contains what I expect to see when I use the watch function.

I have decided to use a different report and my variable and string names and list box have all changed, but I am still trying to achieve the same thing.

I still have not managed to get this working correctly I get the following error:-

Syntax error (missing operator) in query expression '([S&D Group] IN ('Newport,'Stratford,'Telesales))'.

Newport, Stratford & Telesales were my selections made in my listbox.

My new code is:-

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command29_Click()
  2. On Error GoTo Err_Command29_Click
  3.  
  4.     Dim stDocName As String, strWhere As String, varItem As Variant
  5.     stDocName = "RPT_Agent Cadbury and BT Data Summary"
  6.     ' Request to edit items selected in the list box
  7.     ' If no items selected, then nothing to do
  8.     If Me!ListSDG.ItemsSelected.Count = 0 Then Exit Sub
  9.     ' Loop through the items selected collection
  10.     For Each varItem In Me!ListSDG.ItemsSelected
  11.         ' Grab the ContactID column for each selected item
  12.         strWhere = strWhere & "'" & Me!ListSDG.ItemData(varItem) & ","
  13.         Next varItem
  14.     ' Throw away the extra comma on the "IN" string
  15.     strWhere = Left$(strWhere, Len(strWhere) - 1)
  16.     ' Open the contacts form filtered on the selected contacts
  17.     strWhere = "[S&D Group] IN (" & strWhere & ")"
  18.     DoCmd.OpenReport stDocName, acPreview, , strWhere
  19.  
  20. Exit_Command29_Click:
  21.     Exit Sub
  22.  
  23. Err_Command29_Click:
  24.     MsgBox Err.Description
  25.     Resume Exit_Command29_Click
  26.  
  27. End Sub
I feel quite out of my depth but am learning lots as I go along unfortunately I have not yet got the answer but I have faith that i'll get there with a little more help.
Sep 24 '07 #6

FishVal
Expert 2.5K+
P: 2,653
Hi, Matt.

Take a look at the string you obtain
'([S&D Group] IN ('Newport,'Stratford,'Telesales))'
String constants are not properly enclosed with single quotes. ;)
Make a little change to the code to make him add both quotes
Expand|Select|Wrap|Line Numbers
  1. strWhere = strWhere & "'" & Me!ListSDG.ItemData(varItem) & "',"
  2.  
Hope you've noticed this quote mark before comma.

Regards,
Fish
Sep 24 '07 #7

MattFitzgerald
P: 30
FishVal

Thank you very much for all your help and patience my listbox now works.

I have one last question regarding this:-

In my Listbox properties I have made the following changes

Row Source type = Value List
Row Source = "Newport";"Stratford";"Telesales";"Joint"
Default Value = "Newport";"Stratford";"Telesales";"Joint"

I have tried setting the default value to select all the values. this is because most of the time I will be interested in all values but not always

Thanks again Matt
Sep 27 '07 #8

FishVal
Expert 2.5K+
P: 2,653
FishVal

Thank you very much for all your help and patience my listbox now works.

I have one last question regarding this:-

In my Listbox properties I have made the following changes

Row Source type = Value List
Row Source = "Newport";"Stratford";"Telesales";"Joint"
Default Value = "Newport";"Stratford";"Telesales";"Joint"

I have tried setting the default value to select all the values. this is because most of the time I will be interested in all values but not always

Thanks again Matt
Hi, Matt.

To generate criteria consisting all listbox items you may use the same code having replaced cycle iterating through ItemsSelected with cycle iterating through all items.
Expand|Select|Wrap|Line Numbers
  1.     With Me.[Listbox name]
  2.         For i = 0 To .ListCount - 1
  3.             Debug.Print .Column(0, i)
  4.         Next i
  5.     End With
  6.  
Sep 27 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.