I have a form in my database that it used to run various reports. There's essentially a separatre report for each of our internal systems/applications - the report is an output of a query that pulls a list of users with access to that system, their assigned role, job title, etc.
Users first select the system/application from a List Box in the form, and then click an "Export" button.
Then, I'm using the "DoCmd.OutputTo" function so that the output of that query automatically opens up within an Excel spreadsheet on the user's PC.
Currently, the queries are all declared/set in the "On Click" event behind the "Export" button in the form. I then have a series if If..ElseIf.. statements to determine which query should be outputted (based upon the system that they selected from the List Box on the form). Below is an snippet of that code.
I assume there must be a more elegant & efficient way to do this!!!
Code:
Private Sub btnExport_Click()
Dim strQuerySys1 As String
Dim strQuerySys2 As String
Dim strQuerySys3 As String
Dim strQuerySys4 As String
Dim strQuerySys5 As String
Dim strQuerySys6 As String
Dim strDate As String
Dim strUser As String
Dim currentHour As String
Dim currentMinute As String
Dim FileNameWithTime As String
strDate = Date
strUser = Environ("Username")
currentHour = Hour(Now)
currentMinute = Minute(Now)
FileNameWithTime = currentHour & currentMinute
strQuerySys1 = "qryUsers_With_Access_To_Sys1"
strQuerySys2 = "qryUsers_With_Access_To_Sys2"
strQuerySys3 = "qryUsers_With_Access_To_Sys3"
This part goes on several more lines...
Then, the If..ElseIf statements (which I should probably update to use the Select Case function if nothing else)
Code:
If IsNull(Me.listUAR.Column(0)) = True Then
MsgBox "u need to select a system from the list before the report can b run.", vbCritical
End If
If Me.listUAR.Column(0) = 1 Then
DoCmd.OutputTo acOutputQuery, strQuerySys1, acFormatXLSX, "User_Access_" & strUser & FileNameWithTime & ".xlsx"
ElseIf Me.listUAR.Column(0) = 2 Then
DoCmd.OutputTo acOutputQuery, strQuerySys2, acFormatXLSX, "User_Access_" & strUser & FileNameWithTime & ".xlsx"
And again, this goes on for several more lines.. There's 13 distinct options from the List Box on the form - so I have 13 queries defined for each one.