| 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
| |
Share this Question
| Expert 100+
P: 489
|
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]));
| |
| 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: - 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
| |
| 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.
| |
| 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,...)
| | | |  Question stats - viewed: 1048
- replies: 4
- date asked: Feb 3 '09
|
