Multiple selections on a list to be used in query field 1

[Triacona]

Dear All,

Thanks for a great forum!!

I have a queryCpXResults.

It has a field called AllUses.

In that fields criteria I have put the following code:

Like "*" & [Forms]![MainScreen].[ListCpXresult] & "*"

Multi Select is set to simple.

So I want to be able to select multiple codes on ListCpXresult, or just be able to leave it blank.

Unfortunately the above is not working, I select multiple values and it gives an output that is the same as if nothing is selected.

Am I doing this right?

Thank you for your help in advance


Thank you,

Kind regards

Triacona

 

[PHV]

I want to be able to select multiple codes on ListCpXresult
Only VBA is able to do that.
Already plenty of code posted here for building a WHERE clause in VBA.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

got to the queries forum. Hit the Faqs button. Go down to using listboxes in queries. There are several examples in there.

 

[TheAceMan1]

How are ya Triacona . . .

Typically to accomplish this you'll modify the queries SQL in code. Once modified you use the query as you normally would. The following is an example. The code should reside in the class module of the form where the listbox takes residence:

[blue]Public Sub ModifyCpXResults()
   Dim db As DAO.Database, qdf As DAO.QueryDef, SQL As String
   Dim Lbx As ListBox, idx, Pack As String
   
   Set Lbx = [purple][b]ListCpXresult[/b][/purple]
   SQL = "Select * " & _
         "FROM [purple][B][I]YourTableName[/I][/B][/purple]"
   
   For Each idx In Lbx.ItemsSelected
      If Pack <> "" Then
         Pack = Pack & " OR ([AllUses] LIKE *" & Lbx.Column(1, idx) & "*)"
      Else
         Pack = " WHERE ([AllUses] LIKE *" & Lbx.Column(1, idx) & "*)"
      End If
   Next
   
   Set db = CurrentDb
   Set qdf = db.QueryDefs("[purple][b]CpXResults[/b][/purple]")
   
   If Pack <> "" Then
      qdf.SQL = SQL & Pack & ";"
   Else
      qdf.SQL = SQL & ";"
   End If
   
   qdf.Close
   
   Set qdf = Nothing
   Set db = Nothing
   Set Lbx = Nothing

End Sub[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Triacona]

Dear All,

Thank you so very much for the replies

I will reply as soon as I have implemented the solutions

Thank you,

Kind regards

Triacona

 

[Triacona]

Dear TheAceMan1,

Thank you for your brilliant reply!

I had a look at the FAQ's but they don't make as much sense as your code does.

I have a few questions....

SQL = "Select * " & _
         "FROM YourTableName"

The above code... what is YourTableName? Is it where I get the list data or the query that has the criteria?

  If Pack <> "" Then         Pack = Pack & " OR ([AllUses] 
LIKE *" & Lbx.Column(1, idx) & "*)"

What is Pack in the above code, I am a newbie to VB.
I am assuming

[AllUses]

is my field in to which the criteria is inserted into?

Also in the above code

 Lbx.Column(1,idx)

Is that calling the listbox column 1 from the list?
idx is the index number?

Maybe the right question is how does the loop work?

For each idx in Lbx.ItemsSelected

'does this go through the list sequentially and check which items are selected?

If Pack is not blank then 
Pack = Pack &"PR([AllUses] LIKE*&Lbx.Column(1,idx&"*)"

does this then insert the selected values into the query?

Else Pack ="WHERE([AllUses] LIKE*"&Lbx.Column(1,idx)&"*)"

Is this executed if it is blank and then enable a null value so all are used?

   If Pack <> "" Then
      qdf.SQL = SQL & Pack & ";"
   Else
      qdf.SQL = SQL & ";"
   End If
   
   qdf.Close

Does the above code check if Pack is not blank and then qdf a data base object? = SQL & Pack&";" What does this mean and do?
The same for the Else statement?

Thank you ever so much for your help!!


Thank you,

Kind regards

Triacona

 

[Triacona]

Dear TheAceMan1,

Just one more question, what do I insert in the Query's Criteria for the Field

AllUses

Is it ModifyCpXResults?

Thank you again!

Thank you,

Kind regards

Triacona

 

[TheAceMan1]

Triacona . . .

What the code does is construct a new SQL ... complete with a where clause comprising the like statements via selections made in [blue]ListCpXresult[/blue], that will replace the SQL of the query [blue]CpXResults[/blue]. There-after the results of running the query [blue]CpXResults[/blue] is according to the new SQL! Before I answer your questions ... post back the SQL of [blue]CpXResults[/blue] as you origionally had it.
[ol][li]

[blue]... what is YourTableName?[/blue]

The [blue]name of the table[/blue] used in query [blue]CpXResults[/blue].[/li]
[li]

[blue]What is Pack in the above code[/blue]

If you goto the beginning of the code you'll see that Pack is a [blue]Dimmed[/blue] variable as string. Pack is used to concatenate the [blue]where clause[/blue] of the SQL ... which comprises [blue]Like[/blue] statements of the selections in [blue]ListCpXresult[/blue].[/li]
[li]

[blue]I am assuming [blue][AllUses][/blue] is my field in to which the criteria is inserted into?[/blue]

No! ... [blue][AllUses][/blue] is inserted directly in the [blue]WHERE clause[/blue] of the SQl.[/li]
[li]

[blue]Lbx.Column(1,idx) ... Is that calling the listbox column 1 from the list?[/blue]

Yes! ... you only have to insure your calling the right column. Just keep in mind that [blue]column index[/blue] starts at zero and includes any hidden columns in the [blue]Column Widths[/blue] property.[/li]
[li]

[blue]Maybe the right question is how does the loop work? ... does this go through the list sequentially and check which items are selected?[/blue]

[blue]For each idx in Lbx.ItemsSelected[/blue]

No! ... it goes thru the items selected directly![/li]
[li]

[blue]If Pack is not blank then 
Pack = Pack &"PR([AllUses] LIKE*&Lbx.Column(1,idx&"*)"[/blue]

Pack concatenates the the where clause of the query ... which comprises [blue]LIKE statemtnts[/blue] of the selections made in [blue]ListCpXresult[/blue] listbox.[/li][/ol]


See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Triacona]

Dear TheAceMan1,

Thank very much for your help!
Have a star![star]
Below is the SQL code in my query, which I have now copied and renamed TestCpXResults

SELECT UNI7LIVE_CPINFO.REFVAL, UNI7LIVE_CPINFO.TRADEAS, UNI7LIVE_CPINFO.ADDRESS AS [Add], UNI7LIVE_CPINFO.CPUSE AS MainUse, CpUseCodes.CODETEXT AS MainUseDsc, UNI7LIVE_CPINFO.CONTACT, UNI7LIVE_CPUSES.CPUSE AS AllUses, CpUsesCodes.CODETEXT AS AllUsesDsc
FROM ((UNI7LIVE_CPINFO INNER JOIN CpUseCodes ON UNI7LIVE_CPINFO.CPUSE = CpUseCodes.CODEVALUE) INNER JOIN UNI7LIVE_CPUSES ON UNI7LIVE_CPINFO.KEYVAL = UNI7LIVE_CPUSES.PKEYVAL) INNER JOIN CpUseCodes AS CpUsesCodes ON UNI7LIVE_CPUSES.CPUSE = CpUsesCodes.CODEVALUE
GROUP BY UNI7LIVE_CPINFO.REFVAL, UNI7LIVE_CPINFO.TRADEAS, UNI7LIVE_CPINFO.ADDRESS, UNI7LIVE_CPINFO.CPUSE, CpUseCodes.CODETEXT, UNI7LIVE_CPINFO.CONTACT, UNI7LIVE_CPUSES.CPUSE, CpUsesCodes.CODETEXT, UNI7LIVE_CPINFO.CLOSEDD
HAVING (((UNI7LIVE_CPUSES.CPUSE) Like "*" & [Forms]![MainScreen].[ListCpXresult] & "*") AND ((UNI7LIVE_CPINFO.CLOSEDD) Is Null))
ORDER BY UNI7LIVE_CPINFO.TRADEAS;

As you can tell within the Query TestCpXResults there are 4 tables joining to eachother.

These tables are:

UNI7LIVE_CPINFO
UNI7LIVE_CPUSES
CpUsesCodes
CpUseCodes

UNI7LIVE_CPINFO is joined to UNI7LIVE_CPUSES via UNI7LIVE_CPINFO.KEYVAL to UNI7LIVE_CPUSES.PKEYVAL

UNI7LIVE_CPINFO is joined to CpUseCodes via UNI7LIVE_CPINFO.CPUSE to CpUseCodes.CODEVALUE

UNI7LIVE_CPUSES is joined to CpUsesCodes via UNI7LIVE_CPUSES.CPUSE to CpUsesCodes.CODEVALUE

I hope the above clarifies what the query consists of
Thank you for your help and thanks in advance for the help you are going to give.

How would I modify the code to use the list within this query?



Thank you,

Kind regards

Triacona

 

[TheAceMan1]

Triacona . . .

Aw Cmon! I can't read that SQL ... it has to be deciphered!

As far as the having clause is concerned ... to you intend to [blue]AND[/blue] the clauses or [blue]OR[/blue]?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Triacona]

Dear TheAceMan1,

Thank you for your reply

Errrm how do you decipher the SQL?

The Having Clause I intend to use [blue]AND.[/blue]

Do you need more info, other that the table joins?

Thank you for your help and future help

Thank you,

Kind regards

Triacona

 

[Triacona]

Hi TheAceMan1,

I have had a go at deciphering the SQL.

SELECT UNI7LIVE_CPINFO.REFVAL, UNI7LIVE_CPINFO.TRADEAS, UNI7LIVE_CPINFO.ADDRESS AS [Add], UNI7LIVE_CPINFO.CPUSE AS MainUse, CpUseCodes.CODETEXT AS MainUseDsc, UNI7LIVE_CPINFO.CONTACT, 

UNI7LIVE_CPUSES.CPUSE AS AllUses, CpUsesCodes.CODETEXT AS AllUsesDsc

Fields from above
[blue]REFVAL
TRADEAS
Add [red]UNI7LIVE_CPINFO.ADDRESS AS Add[/red]
MainUse [red]UNI7LIVE_CPINFO.CPUSE AS MainUse[/red]
MainUseDsc [red]CpUseCodes.CODETEXT AS MainUseDsc[/red]
CONTACT [red]UNI7LIVE_CPINFO.CONTACT[/red]
AllUses [red]UNI7LIVE_CPUSES.CPUSE AS AllUses[/red]
AllUsesDsc [red]CpUsesCodes.CODETEXT AS AllUsesDsc[/red]
[/blue]

FROM ((UNI7LIVE_CPINFO INNER JOIN CpUseCodes ON UNI7LIVE_CPINFO.CPUSE = CpUseCodes.CODEVALUE)
INNER JOIN UNI7LIVE_CPUSES ON UNI7LIVE_CPINFO.KEYVAL = UNI7LIVE_CPUSES.PKEYVAL) 
INNER JOIN CpUseCodes AS CpUsesCodes ON UNI7LIVE_CPUSES.CPUSE = CpUsesCodes.CODEVALUE 

GROUP BY UNI7LIVE_CPINFO.REFVAL, UNI7LIVE_CPINFO.TRADEAS, UNI7LIVE_CPINFO.ADDRESS, UNI7LIVE_CPINFO.CPUSE, CpUseCodes.CODETEXT, UNI7LIVE_CPINFO.CONTACT, UNI7LIVE_CPUSES.CPUSE, 

CpUsesCodes.CODETEXT, UNI7LIVE_CPINFO.CLOSEDD

Tables from above
[blue]
UNI7LIVE_CPINFO
UNI7LIVE_CPUSES
CpUsesCodes
CpUseCodes
[/blue]

Joins from above

UNI7LIVE_CPINFO is joined to UNI7LIVE_CPUSES via UNI7LIVE_CPINFO.KEYVAL to UNI7LIVE_CPUSES.PKEYVAL

UNI7LIVE_CPINFO is joined to CpUseCodes via UNI7LIVE_CPINFO.CPUSE to CpUseCodes.CODEVALUE

UNI7LIVE_CPUSES is joined to CpUsesCodes via UNI7LIVE_CPUSES.CPUSE to CpUsesCodes.CODEVALUE

HAVING (((UNI7LIVE_CPUSES.CPUSE) Like "*" & [Forms]![MainScreen].[ListCpXresult] & "*") AND ((UNI7LIVE_CPINFO.CLOSEDD) Is Null))

Criteria from above

UNI7LIVE_CPUSES.CPUSEAllUses criteria comming from the form MainScreen and the List - ListCpXresult

Like "*" & [Forms]![MainScreen].[ListCpXresult] & "*"

[blue]AND[/blue]
UNI7LIVE_CPINFO.CLOSEDD Is Null Closed Date CLOSEDD is NULL

ORDER BY UNI7LIVE_CPINFO.TRADEAS;

Ordering Above
TRADEAS sorted Ascending.

Is this the way to decipher it?

I hope this helps!


Thank you,

Kind regards

Triacona

 

[TheAceMan1]

Triacona . . .

Your latest post was un'necessary. I simply meant the readibility of the SQL because its packed in a paragraph. I intend to lay this out this evening.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Triacona]

Thank you!!

Thank you,

Kind regards

Triacona

 

[TheAceMan1]

Triacona . . .

Here ya go. Can you honestly tell me the query in your actually returns what your looking for?

[blue]Public Sub ModifyCpXResults()
   Dim db As DAO.Database, qdf As DAO.QueryDef, SQL As String
   Dim Lbx As ListBox, idx, Pack As String
   
   Set db = CurrentDb
   Set qdf = db.QueryDefs("CpXResults")
   Set Lbx = ListCpXresult
   SQL = "SELECT UCPI.REFVAL, UCPI.TRADEAS, UCPI.ADDRESS AS [Add], " & _
                "UCPI.CPUSE AS MainUse, CpUse.CODETEXT AS MainUseDsc, UCPI.CONTACT, " & _
                "UCPU.CPUSE AS AllUses, CpUsesCodes.CODETEXT AS AllUsesDsc " & _
         "FROM ((UNI7LIVE_CPINFO AS UCPI " & _
         "INNER JOIN CpUseCodes AS CpUse ON UCPI.CPUSE = CpUse.CODEVALUE) " & _
         "INNER JOIN UNI7LIVE_CPUSES AS UCPU ON UCPI.KEYVAL = UCPU.PKEYVAL) " & _
         "INNER JOIN CpUse AS CpUsesCodes ON UCPU.CPUSE = CpUsesCodes.CODEVALUE " & _
         "GROUP BY UCPI.REFVAL, UCPI.TRADEAS, UCPI.ADDRESS, UCPI.CPUSE, " & _
                  "CpUse.CODETEXT, UCPI.CONTACT, UCPU.CPUSE, " & _
                  "CpUsesCodes.CODETEXT, UCPI.CLOSEDD"
   
   For Each idx In Lbx.ItemsSelected
      If Pack <> "" Then
         Pack = Pack & " AND ((UCPU.CPUSE) Like '*" & Lbx.Column(1, idx) & "*')"
      Else
         Pack = "HAVING ((UCPU.CPUSE) Like '*" & Lbx.Column(1, idx) & "*')"
      End If
   Next
   
   If Pack <> "" Then
      Pack = Pack & " AND ((UNI7LIVE_CPINFO.CLOSEDD) Is Null) " & _
            "ORDER BY UNI7LIVE_CPINFO.TRADEAS;"
      qdf.SQL = SQL & Pack
   Else
      Pack = Pack & "ORDER BY UNI7LIVE_CPINFO.TRADEAS;"
      qdf.SQL = SQL & Pack
   End If
   
   qdf.Close
   
   Set qdf = Nothing
   Set db = Nothing
   Set Lbx = Nothing
End Sub[/blue]

Remember were actually changing the SQL of the query.To verify ... check the SQL of the query after the code is run.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Triacona]

Thank you so much TheAceMan1!!

I have used a combo box on the query and that does work, i.e. if the the user chose INSCOP in the ombo box it would display all cases relating to that.
And so on...

But sometimes the users needed to see numbers of cases if INSCOP and SENSIT were chosen and so on...therefore I thought to use the multiselect on the ListBox.

Thanks so much for your help!


Thank you,

Kind regards

Triacona

 

[Triacona]

Dear TheAceMan1,

I have inserted the code into the class module on the form, i.e. I clicked (when the form was in focus) on design and then clicked on the VB code Icon.

Inserted the code.

It doesn't seem to be working.

Do I need to change the syntax of the SQL as it is on multiple lines broken up?

The form the code is inserted into, has a few buttons
Run Report
Run Query
a list on the LHS {ListCP}and the list in question ListCpXresult on the RHS.

I changed the

Set qdf = db.QueryDefs("CpXResults")

TO

Set qdf = db.QueryDefs("TestCpXResults")

As this is my test query and this has the SQL I gave you.


I took out the criteria on the AllUses field.

like "*" [FORMS]![MainScreen].[ListCpXResult]

Am I missing something?
Do I need to create the query, or does the code create a virtual query each time, therefore do I need to delete TestCpXResults?

Thank you again for all the help!

Thank you,

Kind regards

Triacona

 

[TheAceMan1]

Triacona . . .

In the code below insert the line in [purple]purple[/purple] where you see it. Then run the code. When done post back whats printed in the [blue]Immediate Window[/blue]. What your looking for is proper syntax, spacing, start/end parenthese pairs.

[blue]   If Pack <> "" Then
      Pack = Pack & " AND ((UNI7LIVE_CPINFO.CLOSEDD) Is Null) " & _
            "ORDER BY UNI7LIVE_CPINFO.TRADEAS;"
      qdf.SQL = SQL & Pack
   Else
      Pack = Pack & "ORDER BY UNI7LIVE_CPINFO.TRADEAS;"
      qdf.SQL = SQL & Pack
   End If
   
   [purple][b]Debug.Print SQL & Pack[/b][/purple]
   
   qdf.Close[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Triacona]

Dear TheAceMan1,

Thanks I will do!

I have been sick for a while


But I will get on to it asap.

Thank you again!

Thank you,

Kind regards

Triacona

 

[Triacona]

Dear TheAceMan1,

I have added the code to a button on the form, it seems not to execute when run as a public sub.

I have run it as a private sub (on the button) and the debug info I get is

Syntax error (missing operator) in query expression 'UCPI.CLOSEDDORDER BY UNI7LIVE_CPINFO.TRADEAS'

I hope this is clear...
Thank you very much!

Thank you,

Kind regards

Triacona