Senniger1
asked on
Assign Variable to Record Source of an Access 2003 Report
I have an Access 2003 database that imports an Excel 2003 spreadsheet from the user's desktop and names the table with the username. Here's my code:
Private Sub cmdRunReport_Click()
Dim stDocName As String
Dim strFile As String 'Desktop XLS
Dim strFindID As String
Dim UserName As String
' Assign username to a variable
UserName = Environ("USERNAME")
' Convert username to UPPERCASE
strFindID = StrConv([UserName], 1)
' Create spreadsheet name from a variable (LDM.xls)
strFile = strFindID & ".xls" 'strFile = Leigh.xls
' Create path to desktop spreadsheet using a variable (C:\Documents and Settings\LDM\Desktop\ldm.x ls)
strFile = Environ("USERPROFILE") & "\Desktop\" & [strFile] 'C:\Documents and Settings\LDM\Desktop\ldm.x ls
' If it existed, the previous table will be deleted
DeleteTable strFindID
'Call the Function
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strFindID, strFile, True
stDocName = "Report1" ' Report1 is the name of the report
DoCmd.CopyObject , "TABLENAME", acTable, UserName
DoCmd.OpenReport stDocName, acPreview
'Delete the desktop spreadsheet
Kill (strFile)
End Sub
I have multiple reports and I'd like to be able to run the reports from the same table, however, the TABLENAME will vary depending on the user who is running the report.
Therefore, if I go to the Record Source of the report, I need to change TABLENAME to USERNAME.
Can I assign the Tablename to a variable and if so how?
Private Sub cmdRunReport_Click()
Dim stDocName As String
Dim strFile As String 'Desktop XLS
Dim strFindID As String
Dim UserName As String
' Assign username to a variable
UserName = Environ("USERNAME")
' Convert username to UPPERCASE
strFindID = StrConv([UserName], 1)
' Create spreadsheet name from a variable (LDM.xls)
strFile = strFindID & ".xls" 'strFile = Leigh.xls
' Create path to desktop spreadsheet using a variable (C:\Documents and Settings\LDM\Desktop\ldm.x
strFile = Environ("USERPROFILE") & "\Desktop\" & [strFile] 'C:\Documents and Settings\LDM\Desktop\ldm.x
' If it existed, the previous table will be deleted
DeleteTable strFindID
'Call the Function
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strFindID, strFile, True
stDocName = "Report1" ' Report1 is the name of the report
DoCmd.CopyObject , "TABLENAME", acTable, UserName
DoCmd.OpenReport stDocName, acPreview
'Delete the desktop spreadsheet
Kill (strFile)
End Sub
I have multiple reports and I'd like to be able to run the reports from the same table, however, the TABLENAME will vary depending on the user who is running the report.
Therefore, if I go to the Record Source of the report, I need to change TABLENAME to USERNAME.
Can I assign the Tablename to a variable and if so how?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad to help :)
ASKER