467,907 Members | 1,406 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,907 developers. It's quick & easy.

MultiSelect lstbox data in query

100+
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
  • viewed: 1174
Share:
4 Replies
DonRayner
Expert 256MB
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+
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 256MB
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 1GB
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.

Similar topics

3 posts views Thread by arthur-e | last post: by
2 posts views Thread by Cassie Pennington | last post: by
1 post views Thread by tod4 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.