Listbox with array and query

[zezo2021]

Hello

first
how can I put list box column to array?
second
How can I use this array in query for example in cretieria (in (Array))

Thank you so much

 

[Uncle Gizmo]

Need more information.

You could consider a list box an "array" it already stores the information and you can handle it in a similar manner. If you provide more information about what you are trying to do there may be a better solution than putting the information into an array.

 

[ebs17]

First: Use Split (ValuesList) or GetRows (Table/Query).

Second: Not directly. The database engine cannot handle VBA objects such as arrays. You need processing.

 

[zezo2021]

First: Use Split (ValuesList) or GetRows (Table/Query).

Second: Not directly. The database engine cannot handle VBA objects such as arrays. You need processing.

We can use function in query

But the code for steps

 

[ebs17]

Function? Where does it fall from the sky?

Recordset.GetRows method (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

GetRows Method (ADO) - ActiveX Data Objects (ADO)

GetRows Method (ADO)

learn.microsoft.com

Split function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

I had adjusted to your short-winded communication and I think you like that. A code in steps is concrete. It would therefore have to refer to concrete and known initial conditions that you have not yet provided. Incorporation into a query is also very dependent on the query and target direction - the next big unknown.

 

[Pat Hartman]

how can I put list box column to array?

Since the listbox IS an array, the question is why when your data is already in an array. OR, use the query or table that is the RowSource for the listbox. Those are also "arrays".

 

[The_Doc_Man]

how can I put list box column to array?

As noted by others, a list-box provides an ARRAY of rows that can contain one or more fields (columns). Yes, that makes it a 2-dimensional array, and though the syntax is messy, it works just fine.

When you have a populated list-box, you can directly address it as though it were an array. Therefore, trying to load your data to an array seems at first glance that you are trying to do the same work twice on the same data. Further, unless you are using "cascaded combo-box" techniques on the list-box, it would probably be statically defined and therefore would be a relatively stable array.

How can I use this array in query for example in cretieria (in (Array))

In a very strict sense, you cannot. The SQL engine, whether it is Jet or ACE or SQL Server, will be in a different memory segment and different task than the VBA code, and thus that array is invisible to the query engine, whatever it is.

However, if this array came from a listbox then it potentially can come from an ordinary query (since a list-box's .Rowsource IS a query). If you can make a named query to match the .Rowsource, then you can in theory make a SELECT query that includes a WHERE XYZ IN (SELECT ZYX FROM MyRowSourceQuery) (and of course whatever else needs to be in the WHERE clause).

I suppose you are thinking that having the array would make something faster - but the fastest thing you are likely to get would be to use a query to reference an IN clause.

 

[zezo2021]

Sorry Frinds

Only Selected List box Item

 

[MajP]

Two examples to get a Where clause from the multislect listbox. These examples apply it to a form filter, but you could easily create a dynamic QDF or dynamic SQL