Using Microsoft Access form to filter data from Multiple Listboxes, Textboxes and a ComboBox

Question

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"

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
– phalteman, Commented Jan 14, 2019 at 19:13
I have added my code. Hope that helps to get a better understanding. Sorry for the mis-step — NMats
– NMats, Commented Jan 28, 2019 at 17:43

Cahaba Data · Accepted Answer · 2019-01-14 22:01:58Z

0

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.

answered Jan 14, 2019 at 22:01

Cahaba Data

6321 gold badge4 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

NMats Over a year ago

I have looked at various Q/A, but most of them either only use one or when they do use multiple ones, either I'm still left confused, or they are using multiple tables, where I only have one.

Cahaba Data Over a year ago

the problem of the multi select control (i.e.list box) is that the bound values' structure do not lend themselves to be the criteria of a query. If one always just selects only 1 value it will work. It isn't at all related to multiple tables - it is entirely an issue of the structure/syntax needed for a query criteria. These controls are fine - as long as they are not intended to be used as the criteria of another query.

NMats Over a year ago

Okay, that makes sense. The only issues that I really have is just adding multiple things to the code. I can make it where one works, but I can't figure out why I can't get a second or third one to work properly. Once that is done then everything will pretty much be done.

Cahaba Data Over a year ago

A query wants its criteria exactly structured i.e. "This" OR "That" with quotes in the correct place. A multi list control bounds: this, that which doesn't work.... when you have just 1 it calls in as: "this" but when you have multiple it calls in as "this,that" which isn't going to be correct. Attempting to modify that string on the fly is a big hassle. You need to change to making a sub form with a check box and get away from the multi list control

NMats Over a year ago

The thing is that what I want isn't necessarily a query, its just a way to use a multi-list to search multiple things. This is for a potential database that the police can use and to help for when they are look for specific cases where say they want to know about hate crimes that happen in districts A and C. Hopefully that makes sense.

Collectives™ on Stack Overflow

Using Microsoft Access form to filter data from Multiple Listboxes, Textboxes and a ComboBox

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related