[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Assign Variable to Table for Access 2003 Query

Posted on 2011-09-27
9
Medium Priority
?
236 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 run the reports from the same table.  Because the TABLENAME will vary depending on the user who is running the report, I use the OpenArgs parameter when opening the report (docmd.openreport stDocName, acPreview,,,,UserName) and in the OPEN event of my report is use (Me.Recordsource = "SELECT * FROM " & me.OpenArgs).  This part was obtained with the help of Experts Exchange.

At this point I believe I have everything working as I need with one huge exception.  The RECORD SOURCE for some of the reports are pointing to a query.  So now I need to be able assign the table for a query to a variable.  Is this possible and if so, how?
0
Comment
Question by:Senniger1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36709177
yes, it is possible.
you have to use VBA codes to alter the SQL statement of the query using the QueryDef


dim qd as dao.querydef, db as dao.database
dim oSql as string, sSql as string
set qd=db.querydefs("nameOfQuery")

'get the original sql
oSql=qd.sql

sSql=replace(oSql,"tableName",Username)

qd.sql=sSql

' use the query here to open the report

'after closing the report, return the original Sql statement

qd.sql=oSql



0
 

Author Comment

by:Senniger1
ID: 36710034
Thank you for your response.  I tried to follow your instructions, but this is a little over my head.  I was getting an "object variable or with variable not set" message.

I've attached a Sample database.  I remmed out all steps where it pulls the spreadsheet from my desktop.  It's at the point where the table is name LDM which is the username.  The qryWRE was originally written to look for a table name WRE.  It now needs to look for a table named LDM.

I sincerely appreciate you help!
Sample.mdb
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 36710252
test this,
change made in codes, before opening the report and after opening the report
SampleRev.mdb
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Senniger1
ID: 36710384
First of all, thank you.

I tested the SampleRef.mdb file.  I'm not getting an error, but the report is blank.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36710500
because there are no records returned by the SQL statement


SELECT LDM.DueDate, LDM.CaseNumber, LDM.AppTitle, LDM.ActionDue, LDM.Attorney1, LDM.Attorney2, LDM.Attorney3, LDM.ActionRemarks
FROM LDM
GROUP BY LDM.DueDate, LDM.CaseNumber, LDM.AppTitle, LDM.ActionDue, LDM.Attorney1, LDM.Attorney2, LDM.Attorney3, LDM.ActionRemarks
HAVING (((LDM.DueDate) Between DateAdd("m",-1,Date()) And DateAdd("m",+3,Date())) AND ((LDM.Attorney1)="LDM")) OR (((LDM.DueDate) Between DateAdd("m",-1,Date()) And DateAdd("m",+3,Date())) AND ((LDM.Attorney2)="LDM")) OR (((LDM.DueDate) Between DateAdd("m",-1,Date()) And DateAdd("m",+3,Date())) AND ((LDM.Attorney3)="LDM"));


check if this is what supposed to be  the query



0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36710636
test this one,
click on the button , Testing

used "MEA" as the table
SampleRev.mdb
0
 

Author Comment

by:Senniger1
ID: 36710766
Thanks so much.  Let me work with it a bit and I'll get right back to you.
0
 

Author Closing Comment

by:Senniger1
ID: 36717127
For the most part, this worked absolutely beautiful.  

I did run into a couple of issues because of the query changing back and forth.  The Field name in the query would end up preceeded by Expr1, Expr2, Expr3, etc.  To get around this I had to alter my report so the Control Source for the Text Boxes were Expr1, Expr2, etc.  It wasn't such a big issue.

This was truly awesome and I appreciate all your time since this was quite involved.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36717176
to avoid { Expr1, Expr2, Expr3, }
include a table WRE with no records...
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

649 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