MultiSelect lstbox data in query

Dan2kx

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

DonRayner

In the conditions for your query statement you can "or" the two listboxes.

Example:

Expand|Select|Wrap|Line Numbers

 
SELECT Table1.Field1, Table1.Field2, Table1.Field3

FROM Table1

WHERE (([Field1]=[Forms]![Form1]![ListBox1])) OR (([Field2]=[Forms]![Form1]![ListBox2]));

Dan2kx

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

 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

DonRayner

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.

ChipR

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,...)

MultiSelect lstbox data in query

Post your reply

Similar topics