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

MultiSelect lstbox data in query

100+
P: 365
Good eve peeps,

got a quicky for ya (hopefully) i have two multiselect listboxes on a form and i would like a query to show all the selected values from both boxes, i tried to sting the data first but no luck, one is a string value the other a long (but i tried to string this too)

lHolCom as String
lStaff as Long

any ideas? can it be done? am i gna have to run seperate queries into a table?

Dan
Feb 3 '09 #1
Share this Question
Share on Google+
4 Replies


DonRayner
Expert 100+
P: 489
In the conditions for your query statement you can "or" the two listboxes.

Example:
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.Field1, Table1.Field2, Table1.Field3
  2. FROM Table1
  3. WHERE (([Field1]=[Forms]![Form1]![ListBox1])) OR (([Field2]=[Forms]![Form1]![ListBox2]));
Feb 4 '09 #2

100+
P: 365
My problem is with two different multiselect listboxes
users should select multiple items from both and i want all related records to be shown
for lack of a "better" way i have created this code:

Expand|Select|Wrap|Line Numbers
  1. On Error GoTo Trap
  2. Dim msg As String, HolCom As String, SID As String, row As Variant, row2 As Variant
  3. Dim tdef As TableDef, mySQL As String
  4.     If IsNull(Me.cDept) Then msg = msg & " - Department" & vbCrLf
  5.     If IsNull(Me.txtDateFrom) Then msg = msg & " - Date From" & vbCrLf
  6.     If IsNull(Me.txtDateTo) Then msg = msg & " - Date To" & vbCrLf
  7.     If Me.lHolCom.ItemsSelected.count = 0 Then msg = msg & " - Leave Comment/s" & vbCrLf
  8.     If Me.lStaff.ItemsSelected.count = 0 Then msg = msg & " - Staff Members/s" & vbCrLf
  9.     If Not msg = "" Then MsgBox "Please Complete the following fields..." & vbCrLf & msg, vbCritical, "Some Details Missing..." Else GoTo Main:
  10. Exit Sub
  11.  
  12. Main:
  13. Set tdef = CurrentDb.CreateTableDef(LogStaffID & "tblReports")
  14. With tdef
  15.     .Fields.Append .CreateField("Name", dbText)
  16.     .Fields.Append .CreateField("Date", dbDate)
  17.     .Fields.Append .CreateField("AM", dbInteger)
  18.     .Fields.Append .CreateField("PM", dbInteger)
  19.     .Fields.Append .CreateField("MinsUsed", dbInteger)
  20.     .Fields.Append .CreateField("Comments", dbText)
  21.     CurrentDb.TableDefs.Append tdef
  22. End With
  23.     DoCmd.SetWarnings False
  24.     For Each row In Me.lHolCom.ItemsSelected
  25.         For Each row2 In Me.lStaff.ItemsSelected
  26.             DoCmd.RunSQL "INSERT INTO [" & LogStaffID & "tblReports] ( Name, [Date], AM, PM, MinsUsed, Comments ) SELECT [Surname] & ', ' & [Forename] AS Name, tbl_Holidays.Date, tbl_Holidays.AM, tbl_Holidays.PM, IIf([UseMins]=True,[MinutesUsed],0) AS MinsUsed, tbl_Holidays.Comments FROM tblStaff INNER JOIN tbl_Holidays ON tblStaff.StaffID = tbl_Holidays.StaffID WHERE (((tbl_Holidays.Date) Between " & Me.txtDateFrom & " And " & Me.txtDateTo & ") AND ((tbl_Holidays.Comments)='" & Me.lHolCom.ItemData(row) & "') AND ((tbl_Holidays.StaffID)=" & Me.lStaff.ItemData(row2) & ") AND ((tbl_Holidays.AuthBY) Is Not Null) AND ((tbl_Holidays.CancBY) Is Null)) ORDER BY tblStaff.Surname;"
  27.         Next
  28.     Next
  29.     DoCmd.SetWarnings True
  30.     DoCmd.OpenReport "rptReports", acViewDesign
  31.     [Reports]![rptReports].RecordSource = LogStaffID & "tblReports"
  32.     [Reports]![rptReports]![Label12].Caption = "Staff Leave Reports " & Me.txtDateFrom & " " & Me.txtDateTo
  33.     DoCmd.OpenReport "rptReports", acViewPreview
  34. Exit Sub
  35. Trap:
  36.     Select Case Err.Number
  37.         Case 3010
  38.             DoCmd.DeleteObject acTable, LogStaffID & "tblReports"
  39.                 Resume
  40.         Case Else
  41.             MsgBox Err.Number & " " & Err.Description
  42.     End Select
  43.  
Any "easier" solutions welcome
Feb 4 '09 #3

DonRayner
Expert 100+
P: 489
Dan, I'll take a look at it again in the morning. My thought is to set the query's criteria to a function and use the function to build up the where statement based on the listbox selections.
Feb 4 '09 #4

Expert 100+
P: 1,287
If you can string together the values from the list box, you can easily use the SQL IN condition.

SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)
Feb 4 '09 #5

Post your reply

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