473,321 Members | 1,669 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

MultiSelect lstbox data in query

365 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
4 1403
DonRayner
489 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
Dan2kx
365 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
489 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
ChipR
1,287 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

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

Similar topics

3
by: arthur-e | last post by:
Hi I'm trying to use a multiselect listbox to limit records in a report. My version at work is 97 but now at home I'm using Access2002- I know I can't go backwards ( to use this or similar code at...
3
by: louise | last post by:
hi i am trying to set up a mail merge button which takes records from a multi-select listbox (the contents of which are decided by a query created by a search from) and not from a specific...
2
by: Cassie Pennington | last post by:
I am trying to write various items from a multiselect list box to an SQL statement to update a report, without success. SQL only appears to accept hard-coded data or control values from a form, not...
1
by: Jillian Cee | last post by:
I have a multiselect list box (simple). I need to find out how to get Access to recognise my multiple selections firstly (I believe) then I want these selections to go into a query so that I can...
1
by: tod4 | last post by:
Hi, My problem: I have query with value klient and product. On my form Im using multiselect box as filter of klient value. Now I would like to use second multiselect on this form for product...
3
by: jschmidt | last post by:
Hello Everyone, I have an access application that has a listbox on a form. The list box is set to multiselect. I want to be able to use the selected items in a parameterized query that is...
5
by: martin DH | last post by:
Hello, The details are below, but I have a simple form (Form1) with two objects and a "search" command button. When the two objects are cascading combo boxes (the form creates the parameters for a...
1
by: Redbeard | last post by:
I am a newbie running Access 2003. A couple of weeks back a member named ADezil help me write a code that let a , MultiSelect Listboxs that run a Query on a Form (many thanks). I have used this...
1
by: synergy | last post by:
I have a similar problem to other posts but the answers have not worked for me. I have a command button on a form that runs a query. One field in the query has criteria from a list box on the form....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.