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