none
Get List; Syntax Assist please RRS feed

  • Question

  • Good Afternoon Access World,
    Trying to get a list of locations a printer model exists in. This is an unbound field on a Report that counts each of the printer types into a single line item returned with the count of that type. example,

    • HPMFP577      15
    • Sharp634        03
    • Cannon9720    07

    Attempting with

    =GetList("[qry_Printer_Toner_Count]","[Location]","[Location]",",","[Asset] =" & [Asset])

    "[qry_Printer_Toner_Count]" is the name of the query (the data source of the Report)

    Location is where the printer exists. Some locations have multiple printers

    Asset is the unique ID of the printer

    Thank you,


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Tuesday, October 30, 2018 7:25 PM

Answers

  • I missed this post earlier, but the GetList function was written by me some years ago and is illustrated in my Concat.zip demo in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    You seem to be using the same query as the report's RecordSource and as the strTable argument of the function.  This can work if the duplication of rows returned is suppressed in the report by the sorting and grouping mechanism, but more usually separate queries would be used, that for the report's RecordSource returning distinct rows, and that for the function returning rows with each of the values to be concatenated returned in separate rows.

    This function is particularly efficient because it calls the GetString method of the ADO recordset object rather than looping through a recordset, as is done by DAO concatenation functions.  It was Jamie Collins who first alerted me to this method in the old Access newsgroup days.  I later implemented it as a function, which is what you are now using.  It does of course require a reference to the Microsoft ActiveX Data Objects library.

    I'd suggest that you revisit my demo at the above link and study it carefully so that you understand the methodology, and are able to apply it to your own requirements.

    BTW, have you considered using a subreport with 'across-then-down' column layout rather than concatenating the values?  I find this solution is often better as it  vertically aligns each item in the lists of values, which can look better than stringing them together with delimiters.


    Ken Sheridan, Stafford, England

    • Marked as answer by KCDW Wednesday, December 5, 2018 10:52 PM
    Thursday, November 22, 2018 6:10 PM

All replies

  • Hi,

    Can you post the code for GetList()? Thanks.

    Tuesday, October 30, 2018 8:21 PM
  • What? You mean GetList isn't a standard VBA thingy? You want me to think or something?

    Just kidding. - History

    In another database exists the following (which I am borrowing for this. I don't remember how I came up with the original)

    Form: Company Utility Form

    Record Source: SELECT Company.CompanyID,...
    FROM Company
    WHERE (((Company.InActiveCompany)<>-1))
    ORDER BY Company.Company;

    Textbox: txt53

    Control Source: =GetList("[RCompanyIDUnion]","[PEBNumber]","[PEBNumber]",",","[CompanyID] = " & [CompanyID])

    This returns to text53 all the PEB Numbers associated with that CompanyID. This requires the Union query due to the complexity of what constitutes a distinct company in that db. However in this db, we don't have that complexity requiring a union query. Just a simple query to generate a list of distinct printer types. See below.

    queryname: qry_Printer_Toner_Count

    sql: SELECT Assets.[Asset Description], Assets.User, Assets.Location, Assets.[Asset ]
    FROM Assets
    WHERE (((Assets.[Asset Description]) Not In ("Dymo LabelWriter 400","Dymo LabelWriter Duo","Label Printer")) AND ((Assets.Category)="Printer"));

    Report: rpt_Printer_Toner_Count

    Record Source SURPRISE (qry_Printer_Toner_Count)

    Control Source: =GetList("[qry_Printer_Toner_Count]","[Location]","[Location]",",","[Asset ] =" & [Asset ])

    **************************************************

    So I tried beginning with this control source and modifying as below it.

    1. =GetList("[RCompanyIDUnion]","[PEBNumber]","[PEBNumber]",",","[CompanyID] = " & [CompanyID])
    2. =GetList("[qry_Printer_Toner_Count]","[Location]","[Location]",",","[Asset ] =" & [Asset ])

    In 1. after PEBNumber there is a series of ,","," I am not certain of the purpose other than perhaps stripping out the first characters of the string perhaps which is something I don't need in this case but I left it in just in case.

    I don't have any code that is called GetList that I can relate back to the original form other than a module called GetListofPEBNumbers but there is no call to it from the Form. I'll post the code anyway.

    Option Compare Database
    Option Explicit
    
    Public Function GetList(strTable As String, strColumn As String, strSortColumn As String, strDelim As String, Optional strFilter As String = "True") As String
    
        Const NOCURRENTRECORD = 3021
        Dim rst As ADODB.Recordset
        Dim strSQL As String
        Dim strList As String
            
        strSQL = "SELECT " & strColumn & " FROM " & strTable & " WHERE " & strFilter & " ORDER BY " & strSortColumn
       
        Set rst = New ADODB.Recordset
        
        With rst
            Set .ActiveConnection = CurrentProject.Connection
            .Open _
                Source:=strSQL, _
                CursorType:=adOpenForwardOnly, _
                Options:=adCmdText
            
            On Error Resume Next
            strList = .GetString(adClipString, , strDelim & " ", strDelim & " ")
            .Close
            Select Case Err.Number
                Case 0
                ' no error so remove trailing space and semi-colon
                ' and return string
                GetList = Left(strList, Len(strList) - 2)
                Case NOCURRENTRECORD
                ' no rows in table so return
                ' zero length string
                '"Not Selected on Any PEB's"
                Case Else
                ' unknown error
                GetList = "Error"
            End Select
        End With
        
    End Function
    

    Thank you for looking at this.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Tuesday, October 30, 2018 9:23 PM
  • Hi Chris,

    Not sure I follow. Are you trying to list values or count them? For example, do you want something like:

    "HP,Epson,Dell,Koniqa"

    or something like:

    "Printers: 4"

    Tuesday, October 30, 2018 10:09 PM
  • I don't have any code that is called GetList that I can relate back to the original form other than a module called GetListofPEBNumbers but there is no call to it from the Form. I'll post the code anyway.

    Hi Chris,

    According to Help, GetList is a method of an ADO recordset.

    If you know what kind of list you want, it is very easy to make a small functions (also in DAO) to produce that list. If you store that function in a general module, it acts the same way as any method.

    Imb.

    Wednesday, October 31, 2018 10:48 AM
  • Hi .theDBguy,

    Well I already can get a count of each model like

    • HPMFP577      15
    • Sharp634        03
    • Cannon9720    07

    So if there are 15 of the HPMFP577 printers they will show in 1 line. That's not the issue I already have that working. What I need is to include in a textbox all 15 locations (room numbers) these printers are in. So it will look like

    • HPMFP577      15          Rooms B02, B07, B19, P06, P85...
    • Sharp634        03         Rooms B04, B21, P01
    • Cannon9720    07         Rooms B16, P03, P05, P25, ...

    In another db I use the expression in a textbox on the Form to return the values of which records a Company is associated with...

    =GetList("[RCompanyIDUnion]","[PEBNumber]","[PEBNumber]",",","[CompanyID] = " & [CompanyID])

    I am trying to modify that expression to pull the printer locations into a textbox on a report...

    =GetList("[qry_Printer_Toner_Count]","[Location]","[Location]",",","[Asset ] =" & [Asset ])

    In the original, it is stripping off the first 3 characters of text to return only the numbers. I don't want to strip the text this time.

    Thank you.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    • Edited by KCDW Wednesday, October 31, 2018 1:27 PM 2018 10 31 8:27am. Correct the bulleting format
    Wednesday, October 31, 2018 1:25 PM
  • Hi Chris,

    This looks right to me. What is it doing now?

    =GetList("[qry_Printer_Toner_Count]","[Location]","[Location]",",","[Asset ] =" & [Asset ])

    Wednesday, October 31, 2018 3:29 PM
  • Sorry its been a busy day.

    When I run the Report, I get a popup...runtime error -2147217904

    No values are given for one or more required parameters.

    Debug shows.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Wednesday, October 31, 2018 10:15 PM
  • Hi Chris,

    You might try opening the recordset in the Immediate Window to see what argument/parameter is missing.

    Just a thought...

    Thursday, November 1, 2018 3:26 PM
  • Sorry I've been away for so long. Juggling responsibilities...well you know.

    So the GetList Function actually works with an objects textbox call to the function like this

    =GetList("[qry_Printer_Toner_Count]","[Location]","[Asset Description]",",","[Asset ] =""" & [Asset ]  & """")

    This almost works? There is no longer an error but I am only getting the first location of that printer description along with the delimiter. See screenshot below. Just need the result to give me the comma delimited location results so I can have single line of room locations for each printer Description. Circled in Red the Control Source. Circled in blue is a printer description in multiple locations

    Thank you.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Wednesday, November 21, 2018 5:57 PM
  • I missed this post earlier, but the GetList function was written by me some years ago and is illustrated in my Concat.zip demo in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    You seem to be using the same query as the report's RecordSource and as the strTable argument of the function.  This can work if the duplication of rows returned is suppressed in the report by the sorting and grouping mechanism, but more usually separate queries would be used, that for the report's RecordSource returning distinct rows, and that for the function returning rows with each of the values to be concatenated returned in separate rows.

    This function is particularly efficient because it calls the GetString method of the ADO recordset object rather than looping through a recordset, as is done by DAO concatenation functions.  It was Jamie Collins who first alerted me to this method in the old Access newsgroup days.  I later implemented it as a function, which is what you are now using.  It does of course require a reference to the Microsoft ActiveX Data Objects library.

    I'd suggest that you revisit my demo at the above link and study it carefully so that you understand the methodology, and are able to apply it to your own requirements.

    BTW, have you considered using a subreport with 'across-then-down' column layout rather than concatenating the values?  I find this solution is often better as it  vertically aligns each item in the lists of values, which can look better than stringing them together with delimiters.


    Ken Sheridan, Stafford, England

    • Marked as answer by KCDW Wednesday, December 5, 2018 10:52 PM
    Thursday, November 22, 2018 6:10 PM
  • Thanks Ken,

    I thought that might be your work but wasn't sure. When I get back in the office Wednesday, I'll have a go at it. I did review your db. I not sure what the syntax of the subquery would need to be. But I'll play around with it.

    Thanks again!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Monday, November 26, 2018 10:54 PM
  • I not sure what the syntax of the subquery would need to be.
    There is no subquery, but two separate queries.  One is the report's RecordSource and should return a distinct set of printer models.  The other is the one on which the GetList function operates and should include a foreign key which references a primary key identifying each printer model and one row per location of each model.

    Ken Sheridan, Stafford, England

    Tuesday, November 27, 2018 12:06 AM
  • Hi Ken,

    I've been reviewing and trying to make your method work for this application but so far I am unable to get it going. Compiles without error. (Set to Break on all Errors).

    Wondering if the issue may be that yours draws data from two tables where this application only draws from one Query based on a single Table.

    So I modified your reports module code to fit this applications module. The first question I have is since you are looking at number and I am looking at text do I need to use the block...

    Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
    
        ' strip off leading comma and space and assign value of
        ' strQuantitiesOrdered variable to txtQuantitiesOrdered control
        txtQuantitiesOrdered = Mid(strQuantitiesOrdered, 3)
        
    End Sub

    Thank you.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Wednesday, November 28, 2018 9:23 PM
  • It looks like you are using the option from my  demo to 'Concatenate values directly in a report'.  That option does not call the Get List function at all, but does all the concatenation (of the quantities ordered in my example from Northwind) in the report's module as follows:

    Option Compare Database
    Option Explicit

    Dim strQuantitiesOrdered As String

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

        ' add current quantity, preceded by a comma and space
        ' to strQuantitiesOrdered variable
        strQuantitiesOrdered = strQuantitiesOrdered & ", " & Quantity
        
    End Sub

    Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)

        ' strip off leading comma and space and assign value of
        ' strQuantitiesOrdered variable to txtQuantitiesOrdered control
        txtQuantitiesOrdered = Mid(strQuantitiesOrdered, 3)
        
    End Sub


    Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)

        'initialise strQuantitiesOrdered variable to a zero-length string
        strQuantitiesOrdered = ""
        MoveLayout = False
        
    End Sub

    The data type of the values being concatenated is immaterial, and you do need to strip off the leading comma and space, as the expression which incrementally concatenates the values:

        strQuantitiesOrdered = strQuantitiesOrdered & ", " & Quantity

    will add a leading comma and space regardless of the data type.  This is a simpler method to adding a trailing comma and space, and then removing the final comma and space, as the above method does not need to get the length of the string.

    I shall be away for a day or two, so probably won't be able to revisit this thread until the weekend.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Wednesday, November 28, 2018 11:39 PM Final paragraph added.
    Wednesday, November 28, 2018 11:37 PM
  • Hi Ken, Thank you for getting back with me. Hope you had a good couple of days and are enjoying the weekend.

    You are correct, I am attempting to use the option from your demo to 'Concatenate values directly in a report'.

    I have the following:

    Your modified code blocks from my Report;

    Option Compare Database
    Option Explicit
    
    'These code blocks provided by Ken Sheridan at
    '(https://social.msdn.microsoft.com/Forums/en-US/4db98517-e1fd-4365-8b9c-f2b44942c50f/get-list-syntax-assist-please?forum=accessdev#fd7c541e-55e3-4f0c-89fa-ffd4f4d9250c)
    
    Dim strLocations As String
    
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    
        ' add  current Location, preceded by a comma and space
        ' to strLocation variable
        
        ' Location is the field in the Reports underlying query from the Assets Table
        
        strLocations = strLocations & ", " & Location
        
    End Sub
    
    Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
    
        ' strip off leading comma and space and assign value of
        ' strLocation variable to txtLocation control
        
        ' Locations is the name of the unbound control on the report I am trying to get the comma separated list into
        
         Locations = Mid(strLocations, 3)
        
    End Sub
    
    
    Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
    
        'initialise strLocations variable to a zero-legth string
        strLocations = ""
        MoveLayout = False
        
    End Sub
    
    
    Private Sub Report_Activate()
    
        DoCmd.Maximize
        
    End Sub
    
    
    Private Sub Report_Deactivate()
    
        DoCmd.Restore
        
    End Sub



    The Reports base query is

    SELECT DISTINCT Assets.[Asset Description], location
    FROM Assets
    WHERE (((Assets.[Asset Description]) Not In ("Dymo LabelWriter 400","Dymo LabelWriter Duo","Label Printer","SHARP COPIER")) AND ((Assets.Category)="Printer"));
    

    This gives me the distinct list of printers with the count of each model. The report has four fields; Asset Description, AccessTotalsAsset Description1, Location, Locations.

    Asset Description and Location are both from the Reports query.

    Asset Description resides in the Reports, Asset Description Header While Location resides in the Detail section.

    AccessTotalsAsset Description1 is generated by Access to count each distinct model. This resides in the Asset Description Header

    Locations is the unbound textbox for the concatenated list. This resides in the Reports, Asset Description Footer

    I think I have everything set up accordingly. I still do not get the concatenated string of room locations. I probably haven't correctly changed your code blocks.

    Thanks again for helping with this.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Friday, November 30, 2018 8:59 PM
  • Firstly, are you attempting to open the report in report view?  The event procedures will only execute in print preview or if the report is sent to a printer.  If not, I don't see anything wrong with the code at first sight, and would suggest placing a breakpoint at the top of the detail section's Format event procedure and stepping into the code.  This will allow you to examine the value of the strLocations variable as its value is built at each execution of the event procedure.

    Ken Sheridan, Stafford, England

    Saturday, December 1, 2018 12:53 PM
  • Aha. I was trying to open in Report view. I see the result of the concat now in Print Preview. One problem now just surfaced I didn't have before...

    As you can see the odd number rows are One line tall and the even rows are two lines tall. The descriptions on all wrapped rows (even rows) are missing the descriptions and total counts. This did not happen before the code and does not occur in Report View. Any thoughts on that?

    Thank you Ken!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Tuesday, December 4, 2018 10:15 PM
  • At first sight it looks like the group header is being printed twice per asset with the second instance hiding the value of the bound controls, possibly by virtue of their HideDuplicates property being True, with each instance being referenced by separate subsets of locations.  I think you need to examine the basis of the grouping in the context of the report’s recordset. 

    Ken Sheridan, Stafford, England

    Tuesday, December 4, 2018 10:45 PM
  • I've been trying different things here. The only change I can seem to make happen is by changing this line code.

    Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
    
        'initialise strLocations variable to a zero-legth string
        strLocations = ""
    '
        MoveLayout = False 
    
        'changing MoveLayout = False to True corrects the issue of the missing descriptions however the locations footer gets moved down as shown in the picture below.
        
    End Sub
    


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Wednesday, December 5, 2018 3:51 PM
  • The MoveLayout property, if False, causes the next section to be printed on the same line as the section in which it is called.  If it's True, which is the default, then the next section prints on the line below.  This does not explain the behaviour you are experiencing, however.

    You should be able to obtain the same results by amending the report design as follows:

    1. Move the control in the group header to the group footer.

    2. Size the group header to zero height and remove the line which calls the MoveLayout property from its Format event procedure.

    With the report in my demo this gives exactly the same results as the current design.  I'd expect it to do the same in your case, but there might be other unknown factors which are causing the behavior which you are experiencing, so I can't guarantee this will be the case.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Wednesday, December 5, 2018 6:35 PM
    Wednesday, December 5, 2018 6:33 PM
  • Thank you Ken,

    Move the controls from the group header to the group footer. Commented out the line of code MoveLayout = False. Resulting in all the descriptions and quantities showing but now the locations are gone again.

    The Grouping is set as follows:

    Group on Asset Description, Sort A to Z, by entire value, with Asset Description totaled, No title, No Header Section, No Footer Section, Do not keep group together on one page.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Wednesday, December 5, 2018 7:27 PM
  • Eureka!!!

    Acting on your prompts about Headers and Footers, Returned the Group Fields back to the header and using your strategy for setting field heights to 0", then setting the header height to 0" and finally adding to the Footer two fields for the Asset Description and the Count (why the header isn't enough I don't know) Returning the code line MoveLayout = False.

    Resulting in the Asset Description, Quantities and the locations as desired.

    Ken, I can't thank you enough for your solution and your sticktuitiveness in finding a solution to the issue.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Wednesday, December 5, 2018 7:46 PM