Results 1 to 4 of 4
  1. #1
    Processor is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jul 2017
    Posts
    15

    Help with VBA code to correct bad data

    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.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Please put code between CODE tags to retain indentation and readability.

    Debug. Set breakpoints and step through code. Do variables set to expected values?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Processor is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jul 2017
    Posts
    15
    Thank you for the effort but this goes over my head. I don't understand code tags. I just don't understand the terminologies. I'm not really sure I understand the question either. The problem is that the variables are not setting to expected value.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Click the # button to insert the CODE tags and paste your code between them.

    Review link at bottom of my post for guidance on debug techniques.

    Is correct value pulled from Me.PDF_FOLDER?

    Just from reading your code I do not see reason for the issue you describe.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. IS this code correct?
    By Jen0dorf in forum Queries
    Replies: 1
    Last Post: 10-24-2015, 04:23 PM
  2. Replies: 5
    Last Post: 10-13-2015, 02:53 PM
  3. Replies: 5
    Last Post: 09-12-2014, 06:41 AM
  4. code to determine correct dimensions
    By mbar in forum Programming
    Replies: 3
    Last Post: 05-03-2012, 04:27 PM
  5. Correct code for challenging dates?
    By thekruser in forum Queries
    Replies: 6
    Last Post: 11-08-2010, 03:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums