Firstly, let me state that while I am quite well versed in in excel, i have no experience in Access. I have barely opened an application. I had someone write a small macro a while back which seemed to be working well, but now i realize that there is a small bug and need to get some assistance. Any help would be appreciated.
This access file includes local queries, a form, some reports, a few tables, and a macro. The idea is the external data is logged to a "Trending" file wich contains various data such as timestamp and other user selectable data from an external application. Then, the external app triggers this file to open, a macro runs automatically, and the file is closed. Then this process is run over and over. My idea of what is being done in the macro is as follows:
The data from the trending file is used to complete the report.
The report is saved as a .pdf and .xls using the user input filenamewithdatetimestamp.
The "Trending" file is deleted using a delete query.
Here is the issue... The .pdf does not save with the correct filename. It always saves with the previous filename. The .xls does not have this issue.
My thought is that because the "trending" file is not updating with information until it is opened, that perhaps the .pdf file is being generated so fast, it is not recognizing the newest filename. Is this possible? If so, I wonder why i wouldn't see other anomalies with the data. Here is the code...
Private Sub Form_Load()
Dim myRS As DAO.Recordset
Dim db As Database
Dim strSQL_Outer, strSQL_Inner As String
Dim Block, QUERY_NAME As String
Dim PDF_Rep_Name, Excel_File_Name
' Pretend strSQL is a different query that gives five records
Set dbs = CurrentDb
strSQL_Outer = "SELECT DISTINCT TREND002.Test_ID FROM TREND002;"
Set myRS = dbs.OpenRecordset(strSQL_Outer)
'Save TREND002 to Excel File RRR_20140821_122302.pdf
QUERY_NAME = "QRY_TREND002"
My_Date = Format(Date, "yyyymmdd")
My_Time = Format(Time, "HHMMSS")
Excel_File_Name1 = My_Date & "_" & My_Time & ".xls"
PDF_File_Name1 = My_Date & "_" & My_Time & ".pdf"
'MsgBox Excel_File_Name
'DoCmd.OpenQuery QUERY_NAME
If Not myRS.EOF Then
Block = myRS!Test_ID
Me.Block = Block
Excel_File_Name = Me.EXCEL_FOLDER & "" & Me.Block & "_" & Excel_File_Name1
PDF_File_Name = Me.PDF_FOLDER & "" & Me.Block & "_" & PDF_File_Name1
'MsgBox PDF_File_Name
DoCmd.OutputTo acQuery, QUERY_NAME, "MicrosoftExcel(*.xls)", Excel_File_Name, False, ""
Me.TimerInterval = 1000
DoCmd.OutputTo acOutputReport, "RPT_Block", acFormatPDF, PDF_File_Name, True
' myRS.MoveNext
End If
DoCmd.SetWarnings False
DoCmd.OpenQuery "QRY_Delete_TREND001"
DoCmd.OpenQuery "QRY_Delete_TREND002"
'On Error Resume Next
'Application.FollowHyperlink PDF_File_Name
DoCmd.Close
DoCmd.Quit
End Sub
Again, any help would be most appreciated.