Selecting query parameters from a listbox - select all

[Frankie_The_Flyer]

Hi Accessers!

I've got a code that allows me to select one or many names from a listbox on a form and return data relevant to the name(s) selected from a query.

The following code is triggered by a button on the form...

Private Sub Toggle4_Click()
'Set it all up for CSM selection
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

'Identify Query
Set db = CurrentDb()
Set qdf = db.QueryDefs("Qry_CSM")
'Find selected data in Listbox
For Each varItem In Me!CSM_List.ItemsSelected
strCriteria = strCriteria & "'" & Me!CSM_List.ItemData(varItem) & "',"
Next varItem

' Warning if none selected
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If

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

'Prepare String for query
strSQL = "SELECT * FROM tbl_CSM " & "WHERE tbl_CSM.CSM IN(" & strCriteria & "); "

' put it in the Query
Debug.Print strSQL
qdf.SQL = strSQL
qdf.Close

Call Toggle10_Click
End Sub

(Toggle 10 goes to the code for the second listbox which has the same code with different tables refered to giving two selections in the query.)

What I want to do is replace the "warning if nothing found" with a code to show data against all the names in the list box if nothing is selected in the listbox.

Any ideas please??

 

[Pat Hartman]

if strCriteria is empty, create the select without a where clause.

PS - you have a procedure or more specifically a subroutine. A code is a value in a list or an abbreviation for something.

 

[Frankie_The_Flyer]

You saw through my facade and into limited knowledge Pat!
So i've inserted the following into the IF statement

If Len(strCriteria) = 0 Then
strSQL = "SELECT * FROM tbl_CSM "

and nothing happens.......! I'm missing something aren't I?

Is there any way I could add "All" to the names listbox and use that to get all the data out? (Operate the "All" the same a a blank I guess)

 

[madefemere]

Hello,

Try :

If Len(strCriteria) = 0 or IsNull(strCriteria) Then
strSQL = "SELECT * FROM tbl_CSM "

You can use a UNION QUERY to add "All" in the source of a ListBox as :

SELECT Table1.Field1,Table1.Field2,Table1.Field3 
FROM Table1
UNION
SELECT "All",Null,Null FROM Table1

The queries in UNION must have the same number of fields.

Good continuation

 

[Frankie_The_Flyer]

Thanks madefemere. Not sure the "all" code will work. I've done the selection by having the listbox associated with the CSM query selecting names from a table of names. The CSM query is then linked to the CSM field in the main projects table so none of my queries appear to have the [forms]![forms]! Bits in them. Still, I'll have a go at getting it working with your first suggestion and then try the "all" method as an improvement once that works

 

[Pat Hartman]

You need both sides of the If. "Nothing happens" isn't very informative. Add a breakpoint on the if statement and follow the code, line by line from there.

If Len(strCriteria) = 0 Then
    strSQL = "SELECT * FROM tbl_CSM "
Else
    strSQL = ...
End If

 

[Frankie_The_Flyer]

Hi Pat. Literely nothing happens when I click on the button in the form. The button changes to "Hover" and "Click" colours, but the form stays in place (it should close) and there is no error warning. It's as if the button has no effect at all.
Having said that your latest input works up to where I try to open the query. It appears that because there is nothing in the criteria cell the I get a missing Operator warning

SQL is as follows

SELECT Qry_All_Projects.ID, Qry_All_Projects.[Enquiry #], Qry_All_Projects.[Project #], Qry_All_Projects.[Stage 1 / ROI Received], Qry_All_Projects.[Re-submission Date], Qry_All_Projects.Contact, Qry_All_Projects.Entity, Qry_All_Projects.[Project Description], Qry_All_Projects.State, Qry_All_Projects.[Application Status], Qry_All_Projects.CSM, Qry_All_Projects.[Allocated to CSM], Qry_All_Projects.CM, Qry_All_Projects.Component, Qry_All_Projects.[$K], Qry_All_Projects.[Panel Meeting], Qry_All_Projects.[Customer Meeting Held], Qry_All_Projects.[Draft Received], Qry_All_Projects.[Feedback Sent], Qry_All_Projects.[Final Received], Qry_All_Projects.[Tracking Sheet Sent to CM], Qry_All_Projects.[Tracking Sheet Back from CM], Qry_All_Projects.[Schd'ld for D'cisn], Qry_All_Projects.[Delegate Approval], Qry_All_Projects.[Agreement Executed], Qry_All_Projects.Rejected, Qry_All_Projects.Variations, Qry_All_Projects.[Report Due], Qry_All_Projects.[Report Received], Qry_All_Projects.[Assessment Sent to CM], Qry_All_Projects.[Assessment Back from CM], Qry_All_Projects.[Payment Completed], Qry_All_Projects.Comments
FROM Qry_CSM INNER JOIN Qry_All_Projects ON Qry_CSM.CSM = Qry_All_Projects.CSM;

 

[Pat Hartman]

Is there code attached to the click event of the button?

 

[Frankie_The_Flyer]

Yes. Before Call Toggle 10 I have the following...

' put it in the Query
Debug.Print strSQL
qdf.SQL = strSQL
qdf.Close

'Open the query to view
DoCmd.Close
DoCmd.OpenQuery "Qry_All_CSMs_Projects"
DoCmd.Close acQuery, "Qry_All_CSMs_Projects"
DoCmd.OpenForm "Frm_All_CSMs_Projects"

' Finish Macro
Set db = Nothing
Set qdf = Nothing
End Sub

It may seem strange to open and close the query, but I found that without doing that the subsequent form would not be up-dated from the previous query. (Long winded maybe, but it works!)

 

[Pat Hartman]

You shouldn't have to run the query first. Opening the form runs the query. The only time you would have an issue is if you left the form open. If you open an "open" form, it just gets the focus but doesn't run its Open event so the query isn't rerun.

 

[Frankie_The_Flyer]

OK. I've removed the Open / Close bit, but still get
the Syntax error Missing Operator in query expression tbl_CSM_in
Current sub routine is.................

Private Sub Toggle4_Click()
'Set it all up for CSM selection
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

'Identify Query
Set db = CurrentDb()
Set qdf = db.QueryDefs("Qry_CSM")

'Find selected data in Listbox
For Each varItem In Me!CSM_List.ItemsSelected
strCriteria = strCriteria & "'" & Me!CSM_List.ItemData(varItem) & "',"
Next varItem

' select all if no selection has been made
If Len(strCriteria) = 0 Then
strSQL = "SELECT * FROM tbl_CSM "
Else
strSQL = "SELECT * FROM tbl_CSM " & "WHERE tbl_CSM.CSM IN(" & strCriteria & "); "

Exit Sub
End If

'Prepare String for query
strSQL = "SELECT * FROM tbl_CSM " & "WHERE tbl_CSM.CSM IN(" & strCriteria & "); "

' put it in the Query
Debug.Print strSQL
qdf.SQL = strSQL
qdf.Close

'Open the Form to view
DoCmd.Close
DoCmd.OpenForm "Frm_All_CSMs_Projects"
' Finish Macro
Set db = Nothing
Set qdf = Nothing
End Sub

 

[Pat Hartman]

Why do you exit after setting the strSQL with a where clause?

Why do you set the strSQL value a second time so if the code gets this far, it ALWAYS has a where clause?

To validate the SQL strings, put a stop in the code immediately after the string is built. Print the string to the immediate window. Copy it and paste it into the SQL view of the QBE. You will frequently get better error messages. Tweak the query until it works, then change your code as necessary.

 

[Frankie_The_Flyer]

Oops! I've been leaving the old way in. Meant to put an ' by the second line of strSQL. End sub is a lack of concentration.......
Thanks Pat. I'll try this all out next week (at work)

 

[Frankie_The_Flyer]

That cracked it Thanks Pat!