Multiple Selectrion List - Comma Separated String - Run Query & Create Report

[sirhannick]

Multiple Selection List - Comma Separated String - Run Query & Create Report

I've been battling this for a couple days now and I'm stumped/overwhelmed. My SQL experience isn't great. I've been doing a lot of VBA programming though. I'm using Access 2007.

So far I have a Multi Select enabled list on which the user selects the serial numbers they want. They then click the "Report" button which will trigger a query based on the selected serial numbers to create a report on those serial numbers.

I have the code for the multi-select list working already. It creates a string of comma separated values that are the serial numbers which are selected in the list. Somehow I need to pass this string to my query so it can use it as a filter. How would be the best way to use this?

I have found several links which may be pieced together for a a solution (can't post them yet)

Here is some of my code:

Option Compare Database
Option Explicit

Private Sub Form_Current()
    Dim oItem As Variant
    Dim bFound As Boolean
    Dim sTemp As String
    Dim sValue As String
    Dim sChar As String
    Dim iCount As Integer
    Dim iListItemsCount As Integer
    
    iListItemsCount = 0
    bFound = False
    iCount = 0
        
    For iCount = 1 To Len(sTemp) + 1
    sChar = Mid(sTemp, iCount, 1)
        If StrComp(sChar, ",") = 0 Or iCount = Len(sTemp) + 1 Then
            bFound = False
            Do
                If StrComp(Trim(EightD_List.ItemData(iListItemsCount)), Trim(sValue)) = 0 Then
                    EightD_List.Selected(iListItemsCount) = True
                    bFound = True
                End If
                iListItemsCount = iListItemsCount + 1
            Loop Until bFound = True Or iListItemsCount = EightD_List.ListCount
            sValue = ""
        Else
            sValue = sValue & sChar
        End If
    Next iCount
End Sub

Private Sub EightD_Email_Button_Click()
    Dim oItem As Variant
    Dim sTemp As String
    Dim iCount As Integer
    Dim rs As DAO.Recordset
    
    iCount = 0
            
    If EightD_List.ItemsSelected.Count <> 0 Then
        For Each oItem In EightD_List.ItemsSelected
            If iCount = 0 Then
                sTemp = sTemp & EightD_List.ItemData(oItem)
                iCount = iCount + 1
            Else
                sTemp = sTemp & "," & EightD_List.ItemData(oItem)
                iCount = iCount + 1
            End If
        Next oItem
    Else
        MsgBox "Nothing was selected from the list", vbInformation
        Exit Sub  'Nothing was selected
    End If
    
    MsgBox sTemp
    
    
    Set rs = CurrentDb.OpenRecordset("SELECT SpeciesTB.yourFieldName1, SpeciesTB.Species FROM SpeciesTB " & _
    "WHERE SpeciesTB.Species IN (" & StrCriteria & ");")

    ' (Do something with the resulting records)
    
    rs.Close
    
End Sub

Also I have this routine which could be used to check to see if the serial numbers are in the list. I'm just not sure on how to patch all of this together.

Option Compare Database

Function myInList(StringToCheck As String) As Boolean

    Dim myListArray() As String
    myListArray = Split(ListToCheckAgainst, ",")
    
    For Each Item In myListArray
        If ValueToCheck = Val(Item) Then
            myInList = True
            Exit For
        End If
    Next Item

End Function

Here's my current query in SQL:
(see post #4)

Then finally how to I get the query to execute and create a report based on all of this?

I may need to supply more information for someone to be able to help me, but I appreciate any and all advice. Thank you very much in advance!

 

[JHB]

Does the below line not return the correct result?

    Set rs = CurrentDb.OpenRecordset("SELECT SpeciesTB.yourFieldName1, SpeciesTB.Species FROM SpeciesTB " & _     "WHERE SpeciesTB.Species IN (" & StrCriteria & ");")

Can't you use the reports open argument is set the record source for the report like the above line?

 

[sirhannick]

To be honest, I don't really understand how to use that section of the code. I copied that from the same website that provided the "myInList" routine. The part where I get lost is after I am able to display the list of selected values separated by comma's in a message box.
:banghead:
If I went the route of using the "DoCmd.OpenReport" function what would be the SQL WHERE function I would best put in there? Should I use the myInList routine and and say WHERE myInList = TRUE. Perhaps there are some better links or methods that I could use. Does anyone have links to a similar thread where these issues are worked through?

 

[sirhannick]

Here was the query I was trying to post:

SELECT [NOx Inventory].[CQTS Event], [NOx Inventory].[CQTS Sequence],  [NGK Tracking].[NGK  Report].FileName, [NOx Inventory].Customer, [8D  Tracking].[CQTS Updated  Date], [8D Tracking].[C3 Status], [8D  Tracking].[C4 Status], [8D  Tracking].Comments
FROM ([NGK Tracking] INNER JOIN [NOx Inventory] ON [NGK  Tracking].Serial=[NOx Inventory].[Serial #]) INNER JOIN [8D Tracking] ON  [NOx Inventory].[Serial #]=[8D Tracking].[Serial #]
WHERE (((myInList([NOx Inventory].[Serial #]))=True));

The syntax for the WHERE statement is not valid right now, so it's tripping on that. Maybe this is what I need help on?

 

[sirhannick]

I am now understanding this a little better. It seems that by using the DoCmd.OpenReport I can send the "WHERE" part of the SQL function to my existing query. The entire "WHERE......" line in my query can be deleted as I will send it from the DoCmd.OpenReport command.
For instance, here is what I would use for the SQL behind my query:

SELECT [NOx Inventory].[CQTS Event], [NOx Inventory].[CQTS Sequence],  [NGK Tracking].[NGK  Report].FileName, [NOx Inventory].Customer, [8D  Tracking].[CQTS Updated  Date], [8D Tracking].[C3 Status], [8D  Tracking].[C4 Status], [8D  Tracking].Comments
FROM ([NGK Tracking] INNER JOIN [NOx Inventory] ON [NGK  Tracking].Serial=[NOx Inventory].[Serial #]) INNER JOIN [8D Tracking] ON  [NOx Inventory].[Serial #]=[8D Tracking].[Serial #]

Then the command to execute the query & generate the report would something like:

DoCmd.OpenReport ([NGK FA Report], acViewNormal, ,[NGK Tracking].[Serial]=myInList(sTemp)=True)

Of course the exact syntax of the above code is completely wrong. Any idea how to write it correctly to use a function to determine the conditions of the query?

 

[pbaldy]

Does this help?

http://www.baldyweb.com/multiselect.htm

 

[sirhannick]

Does this help?

Most definately. And wow is that code MUCH more efficient than what I am using....I think I will do some major stripping of the code once I get it going.

Stuck with an error right now though, I'm sure it's a minor one about the handling of my text variable (not a number). Here's the exact code I have written:

DoCmd.OpenReport "8D Report", acViewPreview, , "[NGK Tracking].[Serial] IN(" & sTemp & ")"

And the error I got:
Run-time error '3075':
Syntax error (missing operator) in query expression '[NGK Tracking].[Serial] IN(1012210006710 01)'.

I will see if I can get it going, but if I don't post a response I haven't figured it out yet. Thanks for any & all help.

 

[pbaldy]

Did you notice in the link the line that said to use this if your value is text?

strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"

 

[sirhannick]

Did you notice in the link the line that said to use this if your value is text?

strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"

I did. I tried it both. I then removed all of my old version of the code as seen above and replaced it with the code from that webpage. I was getting a different error, but then stumbled upon what might have been another one of my issues. I did not understand that a report has its own query which is hidden behind the scenes as opposed to the one which I thought it was linked to. The report was not looking in the correct location for the serial number and was throwing an error. Here's my current code:

Private Sub EightD_Email_Button_Click()
    Dim strWhere As String
    Dim ctl As Control
    Dim varItem As Variant
    Dim lngLen As Long

    'make sure a selection has been made
    If EightD_List.ItemsSelected.Count = 0 Then
        MsgBox "Must select at least 1 employee"
        Exit Sub
    End If

    'add selected values to string
    Set ctl = EightD_List
    For Each varItem In ctl.ItemsSelected
        'strWhere = strWhere & ctl.ItemData(varItem) & ","
        'Use this line if your value is text
        strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
    Next varItem
    
    'Remove trailing comma. Add field name, IN operator, and brackets.
    lngLen = Len(strWhere) - 1
    If lngLen > 0 Then
        strWhere = "[NOx Inventory].[Serial #] IN (" & Left$(strWhere, lngLen) & ")"
    End If
    
    MsgBox strWhere
    
    DoCmd.OpenReport "8D Report", acViewPreview, , strWhere
    
End Sub

And the SQL code in the query of the report:

SELECT [NOx Inventory].[Serial #], [NOx Inventory].[CQTS Event], [NOx Inventory].[CQTS Sequence], [NGK Tracking].[NGK Report].FileName, [NGK Tracking].Customer, [8D Tracking].[CQTS Updated Date], [8D Tracking].[C3 Status], [8D Tracking].[C4 Status], [8D Tracking].Comments
FROM ([NGK Tracking] INNER JOIN [NOx Inventory] ON [NGK Tracking].Serial = [NOx Inventory].[Serial #]) INNER JOIN [8D Tracking] ON [NOx Inventory].[Serial #] = [8D Tracking].[Serial #];

It now appears to all work correctly! The only addition I had to make to the code from the website was removing the trailing comma. No biggie. Thanks for all your help!

 

[pbaldy]

Happy to help! Not sure what you mean by "The only addition I had to make to the code from the website was removing the trailing comma". The code in my link does remove the trailing comma.

 

[sirhannick]

I had to add this to make the code work:

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
    strWhere = "[NOx Inventory].[Serial #] IN (" & Left$(strWhere, lngLen) & ")"
End If

 

[pbaldy]

Mine had these two lines, which you must have missed:

'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)