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
4 1403
In the conditions for your query statement you can "or" the two listboxes.
Example: -
SELECT Table1.Field1, Table1.Field2, Table1.Field3
-
FROM Table1
-
WHERE (([Field1]=[Forms]![Form1]![ListBox1])) OR (([Field2]=[Forms]![Form1]![ListBox2]));
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: - On Error GoTo Trap
-
Dim msg As String, HolCom As String, SID As String, row As Variant, row2 As Variant
-
Dim tdef As TableDef, mySQL As String
-
If IsNull(Me.cDept) Then msg = msg & " - Department" & vbCrLf
-
If IsNull(Me.txtDateFrom) Then msg = msg & " - Date From" & vbCrLf
-
If IsNull(Me.txtDateTo) Then msg = msg & " - Date To" & vbCrLf
-
If Me.lHolCom.ItemsSelected.count = 0 Then msg = msg & " - Leave Comment/s" & vbCrLf
-
If Me.lStaff.ItemsSelected.count = 0 Then msg = msg & " - Staff Members/s" & vbCrLf
-
If Not msg = "" Then MsgBox "Please Complete the following fields..." & vbCrLf & msg, vbCritical, "Some Details Missing..." Else GoTo Main:
-
Exit Sub
-
-
Main:
-
Set tdef = CurrentDb.CreateTableDef(LogStaffID & "tblReports")
-
With tdef
-
.Fields.Append .CreateField("Name", dbText)
-
.Fields.Append .CreateField("Date", dbDate)
-
.Fields.Append .CreateField("AM", dbInteger)
-
.Fields.Append .CreateField("PM", dbInteger)
-
.Fields.Append .CreateField("MinsUsed", dbInteger)
-
.Fields.Append .CreateField("Comments", dbText)
-
CurrentDb.TableDefs.Append tdef
-
End With
-
DoCmd.SetWarnings False
-
For Each row In Me.lHolCom.ItemsSelected
-
For Each row2 In Me.lStaff.ItemsSelected
-
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;"
-
Next
-
Next
-
DoCmd.SetWarnings True
-
DoCmd.OpenReport "rptReports", acViewDesign
-
[Reports]![rptReports].RecordSource = LogStaffID & "tblReports"
-
[Reports]![rptReports]![Label12].Caption = "Staff Leave Reports " & Me.txtDateFrom & " " & Me.txtDateTo
-
DoCmd.OpenReport "rptReports", acViewPreview
-
Exit Sub
-
Trap:
-
Select Case Err.Number
-
Case 3010
-
DoCmd.DeleteObject acTable, LogStaffID & "tblReports"
-
Resume
-
Case Else
-
MsgBox Err.Number & " " & Err.Description
-
End Select
-
Any "easier" solutions welcome
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.
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,...)
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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...
| |