Solved

Assign Variable to Record Source of an Access 2003 Report

Posted on 2011-09-19
3
332 Views
Last Modified: 2012-05-12
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.xls)
    strFile = Environ("USERPROFILE") & "\Desktop\" & [strFile]  'C:\Documents and Settings\LDM\Desktop\ldm.xls
' 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?
0
Comment
Question by:Senniger1
  • 2
3 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 36560416
Use the OpenArgs parameter when opening the report:

docmd.openreport stDocName, acPreview,,,,UserName


And in the OPEN event of your report:

Me.Recordsource = "SELECT * FROM " & me.OpenArgs
0
 

Author Closing Comment

by:Senniger1
ID: 36560484
Perfect.  This was exactly what I needed.  And thank you for the quick response.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36560615
Glad to help :)
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now