Solved

Assign Variable to Record Source of an Access 2003 Report

Posted on 2011-09-19
3
331 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
Comment Utility
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
Comment Utility
Perfect.  This was exactly what I needed.  And thank you for the quick response.
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Glad to help :)
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

772 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

12 Experts available now in Live!

Get 1:1 Help Now