GPT and a Multi-Select List box.

[Thales750]

The GPT gave me this code to get a criteria from a Multi-Select List box.

Me:
"Write the code for a multiselect list box in Access VBA that will be the criteria for a query"
GPT:
"Here's an example code for creating a multiselect list box in Access VBA that will be used as a criteria for a query:"

Here's an example code for creating a multiselect list box in Access VBA that will be used as a criteria for a query:

vbnet
Copy code
Private Sub Form_Load()
    ' Populate the list box with items
    lstMultiSelect.AddItem "Item 1"
    lstMultiSelect.AddItem "Item 2"
    lstMultiSelect.AddItem "Item 3"
    lstMultiSelect.AddItem "Item 4"
    lstMultiSelect.AddItem "Item 5"
    lstMultiSelect.AddItem "Item 6"
  
    ' Set the list box to allow multiple selections
    lstMultiSelect.MultiSelect = 1
    lstMultiSelect.AllowMultipleSelections = True
End Sub

Private Sub cmdRunQuery_Click()
    Dim strCriteria As String
  
    ' Build the criteria string based on the selected items in the list box
    For Each varItem In lstMultiSelect.ItemsSelected
        strCriteria = strCriteria & ",'" & lstMultiSelect.ItemData(varItem) & "'"
    Next varItem
  
    ' Remove the leading comma from the criteria string
    strCriteria = Right(strCriteria, Len(strCriteria) - 1)
  
    ' Build the query string using the criteria string
    strSQL = "SELECT * FROM tblData WHERE FieldName IN (" & strCriteria & ")"
  
    ' Run the query
    DoCmd.OpenQuery "qryData", acViewNormal, acReadOnly
End Sub

GPT:
"In this code, the list box is named lstMultiSelect, and the button to run the query is named cmdRunQuery. In the Form_Load event, the list box is populated with items and set to allow multiple selections. In the cmdRunQuery_Click event, the selected items in the list box are used to build a criteria string that is used in the query. The IN operator is used to select records that match any of the selected items. Finally, the query is run using the DoCmd.OpenQuery method."

When I read this I wondered how that Select Statement got into the Querydef "qryData"
Allen Browne has an example that I've been using for years, but this seems so simple.

Do you think this will work or is it missing something?
Thanks

 

[pbaldy]

I think it would work for a text data type (you wouldn't want delimiters around numeric values). Most of us would never open a query for the user, it would be a form or report. I'd be more likely to build a wherecondition to open whichever. The leading comma could be removed by a single Mid() function rather than the Right/Len combo, and you'd want to test that the user had made at least one selection.

 

[Thales750]

I think it is missing this:
Dim qdef As DAO.QueryDef
Set qdef = MyDB.CreateQueryDef("qryData", strSQL)

 

[Thales750]

I think it would work for a text data type (you wouldn't want delimiters around numeric values). Most of us would never open a query for the user, it would be a form or report. I'd be more likely to build a wherecondition to open whichever. The leading comma could be removed by a single Mid() function rather than the Right/Len combo, and you'd want to test that the user had made at least one selection.

I totally agree. I was just wondering about if it would function. I added a line to create the Querydef.

I think the GPT screwed up.

 

[Thales750]

I sent this over to it.
Me:
I think it is missing this:
Dim qdef As DAO.QueryDef
Set qdef = MyDB.CreateQueryDef("qryData", strSQL)

ChatGPT:
You are correct. Thank you for pointing that out. Here's the updated cmdRunQuery_Click event code that includes the creation of the querydef object and sets the SQL for the query:

Private Sub cmdRunQuery_Click()
    Dim strCriteria As String
    Dim strSQL As String
    Dim qdef As DAO.QueryDef
    
    ' Build the criteria string based on the selected items in the list box
    For Each varItem In lstMultiSelect.ItemsSelected
        strCriteria = strCriteria & ",'" & lstMultiSelect.ItemData(varItem) & "'"
    Next varItem
    
    ' Remove the leading comma from the criteria string
    strCriteria = Right(strCriteria, Len(strCriteria) - 1)
    
    ' Build the SQL string using the criteria string
    strSQL = "SELECT * FROM tblData WHERE FieldName IN (" & strCriteria & ")"
    
    ' Create a querydef object and set the SQL for the query
    Set qdef = CurrentDb.CreateQueryDef("qryData", strSQL)
    
    ' Open the query in datasheet view
    DoCmd.OpenQuery "qryData", acViewNormal, acReadOnly
    
    ' Clean up
    Set qdef = Nothing
End Sub

ChatGPT:
"In this updated code, the strSQL string is used to build the SQL statement for the query. Then a DAO.QueryDef object is created using the CurrentDb.CreateQueryDef method, and the SQL for the query is set to strSQL. Finally, the qryData query is opened in datasheet view using the DoCmd.OpenQuery method, and the qdef object is set to Nothing to release any resources used by it."

 

[ebs17]

Me:
"Write the code for a multiselect list box in Access VBA that will be the criteria for a query"

...
    ' Remove the leading comma from the criteria string
    strCriteria = Right(strCriteria, Len(strCriteria) - 1)

With these last lines the question about the filter is completely and correctly answered.

How a filter is applied or built into a query is a completely different question. For an installation one should start from a given query, and in practice this can be much more complex than a "SELECT * FROM TableX".

Also that here a column with strings is evaluated, is naturally exemplary, so without concrete default With numbers or date values it looks a little bit differently, changes however nothing at the shown principle.

One should be able to take over received solutions according to the sense.

 

[pbaldy]

I think it is missing this:
Dim qdef As DAO.QueryDef
Set qdef = MyDB.CreateQueryDef("qryData", strSQL)

You are quite correct, I missed that.

 

[Pat Hartman]

Here's a sample database with three examples of using a multi-select listbox to filter a form.

Autofill and other examples

This database shows examples of how autofill works. It also shows examples of filtering with a multi-select listbox. I added an updated version of the database with additional examples.

www.access-programmers.co.uk