Results 1 to 4 of 4
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Listbox (varItem) is added to filename; instead need unbound column(s) added to file name

    Experts:



    I have a listbox with four fields (1 hidden, 3 visible). The listbox's SQL is shown below:

    Code:
    SELECT T402_WS_WatchStations.POC_StaffMemberID, T102_N1S_Staffmembers.All_LastName, T102_N1S_Staffmembers.All_FirstName, T102_N1S_Staffmembers.All_RankTitle
    FROM T402_WS_WatchStations INNER JOIN T102_N1S_Staffmembers ON T402_WS_WatchStations.POC_StaffMemberID = T102_N1S_Staffmembers.StaffMemberIDpk
    GROUP BY T402_WS_WatchStations.POC_StaffMemberID, T102_N1S_Staffmembers.All_LastName, T102_N1S_Staffmembers.All_FirstName, T102_N1S_Staffmembers.All_RankTitle
    ORDER BY T102_N1S_Staffmembers.All_LastName, T102_N1S_Staffmembers.All_FirstName;
    - Field #1 [POC_StaffMemberID] is hidden... column width = 0
    - Other 3 fields ([All_LastName], [All_FirstName], [All_RankTitle] are the only ones *displayed* in the listbox
    - Bound column is linked to column 1 ([POC_StaffMemberID])

    Now, below my listbox, I have a command button. Upon execution, my query is updated with >> strCriteria = "T402_WS_WatchStations.POC_StaffMemberID = " & Me!lstWSPOC.ItemData(varItem) & "" <<

    Then, I output the query with a custom filename: See line >> ReportFileName << where I currently append the ID number and date to the file (Excel).

    Again, see code below:

    Code:
    'Loop through the selected items in the list box and build a text string
    If Me!lstWSPOC.ItemsSelected.Count > 0 Then
        For Each varItem In Me!lstWSPOC.ItemsSelected
           strCriteria = "T402_WS_WatchStations.POC_StaffMemberID = " & Me!lstWSPOC.ItemData(varItem) & ""
                         
           'Build the new SQL statement incorporating the string
                    
            strSQL = "SELECT T402_WS_WatchStations.WS_Description, T402_WS_WatchStations.WS, T102_N1S_Staffmembers.All_LastName, T102_N1S_Staffmembers.All_FirstName, " & _
                     "T102_N1S_Staffmembers.All_RankTitle " & _
                     ...
                     ...
                     ... rest of the SQL/query (not relevant for this post).
    
    
    
    ReportFileName = Replace(Me!lstWSPOC.ItemData(varItem), "/", "_") & " -- " & Format(Date, "yyyy-mm-dd") & ".xlsx"
    OutputPathFileName = ReportPath & ReportFileName
    'Outputs only file(s) **with records** to specified file path
    If DCount("*", "Q462_WS_WatchStations_POCs") > 0 Then
     'Execute query
     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Q462_WS_WatchStations_POCs", OutputPathFileName, False
     NumReports = NumReports + 1
                    
    End If
    What I need some help with:
    - Right now, the XLS's filename includes the [POC_StaffMemberID] number in its file name.
    - While the number is accurate, it doesn't mean anything to the user.
    - So, while I still need to pass [POC_StaffMemberID] as criteria in my query, I want the All_LastName (or All_LastName & All_FirstName) -- instead of POC_StaffMemberID -- to be added to the file name.

    My question:
    How do I modify the line >> ReportFileName = Replace(Me!lstWSPOC.ItemData(varItem), "/", "_") & " -- " & Format(Date, "yyyy-mm-dd") & ".xlsx" << so that it won't append varItem (1st column) but varItem+1 (2nd column) and varItem+2 (3rd column) into the filename?

    Thank you,
    Tom

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Tom,
    Review the info in this link:https://docs.microsoft.com/en-us/off....itemsselected
    The key is on this line:
    Debug.Print ctl.Column(intI, varItm)
    In your case you know you only need lstWSPOC.Column(1,varItem) and lstWSPOC.Column(2,varItem), so concatenate them the way you need them in the file name.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad -- perfect. That helped.

    I changed line to:
    Code:
                 ReportFileName = Replace(Me!lstWSPOC.Column(1, varItem), "/", "_") & ", " & Replace(Me!lstWSPOC.Column(2, varItem), "/", "_") & " " & Replace(Me!lstWSPOC.Column(3, varItem), "/", "_") & " -- " & Format(Date, "yyyy-mm-dd") & ".xlsx"
    It works like a charm!! Thank you.

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You're very welcome!
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. new record is added against my will
    By mar7632 in forum Access
    Replies: 3
    Last Post: 02-18-2019, 05:37 AM
  2. Replies: 7
    Last Post: 03-14-2018, 04:14 PM
  3. Value cannot be added
    By mr_right in forum Access
    Replies: 2
    Last Post: 03-26-2013, 12:56 AM
  4. added text to entire column in the Query
    By Jerseynjphillypa in forum Queries
    Replies: 3
    Last Post: 06-12-2012, 09:39 AM
  5. Added items in a column.
    By Wrangler in forum Forms
    Replies: 3
    Last Post: 03-25-2006, 07:56 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums