Senniger1
asked on
Assign Variable to Table for Access 2003 Query
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
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 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?
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 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?
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
First of all, thank you.
I tested the SampleRef.mdb file. I'm not getting an error, but the report is blank.
I tested the SampleRef.mdb file. I'm not getting an error, but the report is blank.
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
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
ASKER
Thanks so much. Let me work with it a bit and I'll get right back to you.
ASKER
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.
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.
to avoid { Expr1, Expr2, Expr3, }
include a table WRE with no records...
include a table WRE with no records...
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("nameOfQue
'get the original sql
oSql=qd.sql
sSql=replace(oSql,"tableNa
qd.sql=sSql
' use the query here to open the report
'after closing the report, return the original Sql statement
qd.sql=oSql