0
votes

I still haven't been able to do this without running into errors.

For a project that I am doing, I am using Microsoft access to create a database that will be used to filter out data for a report from ONE table based on information that is entered in from Multiple multi-select listboxes, Multiple Textboxes and One Combo Box.

I know how to do for one Multi-Select listbox, but I am having issues adding in my other multi-select listboxes that I have. Is it possible to do it all from just one source or am I going to have to use multiple tables?

If I am able to do it from one table (as the source), how would I go about doing that and also adding in the textboxes and combobox? I can provide my code if necessary of what I currently have.

https://access-programmers.co.uk/forums/showthread.php?t=286294&page=2

Code:

Private Sub Command62_Click()

Dim db As DAO.Database

Dim qdf As DAO.QueryDef

Dim varItem As Variant

Dim strCriteria As String

Dim strCriteria1 As String

Dim strCriteria2 As String

Dim strSQL As String

Set db = CurrentDb()

Set qdf = db.QueryDefs("qryMultiselect")

For Each varItem In Me!District.ItemsSelected

strCriteria = strCriteria & ",'" & Me!District.ItemData(varItem) & "'"

Next varItem

If Len(strCriteria) = 0 Then

MsgBox "You did not select anything in the Contract field." _

    , vbExclamation, "Nothing to find!"

Exit Sub

End If

strCriteria = Right(strCriteria, Len(strCriteria) - 1)

For Each varItem In Me!MOPointofEntry.ItemsSelected

strCriteria1 = strCriteria1 & ",'" & Me!MOPointofEntry.ItemData(varItem) & "'"

Next varItem

If Len(strCriteria1) = 0 Then

MsgBox "You did not select anything in the Name field." _

    , vbExclamation, "Nothing to find!"

Exit Sub

End If

strCriteria1 = Right(strCriteria1, Len(strCriteria1) - 1)

For Each varItem In Me!MOMethodofEntry.ItemsSelected

strCriteria2 = strCriteria2 & ",'" & Me!MOMethodofEntry.ItemData(varItem) & "'"

Next varItem

If Len(strCriteria2) = 0 Then

MsgBox "You did not select anything in the Type field." _

    , vbExclamation, "Nothing to find!"

Exit Sub

End If

strCriteria2 = Right(strCriteria2, Len(strCriteria2) - 1)

strSQL = "SELECT * from TblDataEntry" & _

"WHERE TblDataEntry.District IN(" & strCriteria & ") AND TblDataEntry.MOPointofEntry IN(" & strCriteria1 & ") AND TblDataEntry.MOMethodofEntry IN(" & strCriteria2 & ");"

qdf.SQL = strSQL

DoCmd.OpenQuery "qryMultiselect"

1
Including your code is always helpful to show others how you've tried solving your problem so far, what errors/behaviours you're seeing, etc., so that they will know how to help you.phalteman
I have added my code. Hope that helps to get a better understanding. Sorry for the mis-stepNMats

1 Answers

0
votes

multi select list box do not lend themselves to being the source of a query criteria. this is a topic you can look up and see other Q/A out there as to the reason and complexity of attempting to use them.

changing to multiple single list box, if that is possible, is recommended.

another approach is to add a checkbox field to the table of the multi list records. then set up a sub form using the checkbox as the method to flag multiple records.