What is wrong with this script? (1 Viewer)

[bowes884]

Whats wrong with the script below. I keep getting a compile error:
expected: End of Statement


'------------------------------------------------------------
' Test_Locks_macro
'
'------------------------------------------------------------
' autoexec1
'
'------------------------------------------------------------
Function autoexec1()
On Error GoTo autoexec1_Err
SetWarnings = False
Dim sNow As String
Dim sPath As String

sNow = Format(Now(), "mmddyyyy-hhmmss")
sPath = "Y:\0001 Secondary Marketing Reports"

SendKeys "y", False
DoCmd.OutputTo acOutputReport, "LOCKS - Detail", acFormatRTF, sPath & "Y:\0001 Secondary Marketing Reports\14 - Lock Summary.rtf" & "_" & sNow & ".rtf", False
DoCmd.SetWarnings True


autoexec1_Exit:
Exit Function

autoexec1_Err:
MsgBox Error$
Resume autoexec1_Exit

End Function

 

[Mile-O]

Comment out the On Error line and find out at which line the code baulks.

 

[bowes884]

It bauks on the:

Function autoexec1()

The very first line. I dont really understand this stuff too much. I am just going off of an already workign macro and trying to edit it to display a time/date tag on teh name after it creates the file. From there I am getting this error.

 

[Mile-O]

Change Function to Sub

Also, have a look at the SPath variable. It's not needed since you repeat the information in the output destination.

 

[bowes884]

it is still showing me the same error. It also opens up a pop-up that says

yOption Compare Database

Also says the same error as before too, the

compile error:
expected: End of Statement

 

[ghudson]

I recognize some of this code so I will jump in [feet first].

Does the output to directory exist? If not then the code will error. You need to use the Dir() function to test if the directory exists.

The SetWarnings = False command is wrong. s/b DoCmd.SetWarnings False

The SendKeys function is a problem just waiting to happen. VBA can usually do what you want without using SendKeys.

You need to adjust your outputo code because you have the string and the sPath conflicting with each other.

You need to add more detail to your error trapping message.

You should not be using spaces and special characters in your object names.

Your code is a mess but this should get you closer...

Public Sub AutoExec1()
On Error GoTo Err_AutoExec1

    Dim sNow As String
    Dim sPath As String
    
    sNow = Format(Now(), "mmddyyyy-hhmmss")
    sPath = "Y:\0001 Secondary Marketing Reports\"
    
    If Dir(sPath, vbDirectory) = "" Then
        MsgBox "Output directory does not exist!", vbCritical, "Abort the mission!"
        Exit Sub 'stop the code!
    End If
    
    DoCmd.SetWarnings False
    SendKeys "y", False
    DoCmd.OutputTo acOutputReport, "LOCKS - Detail", acFormatRTF, sPath & "14 - Lock Summary" & "_" & sNow & ".rtf", False
    DoCmd.SetWarnings True

Exit_AutoExec1:
    DoCmd.SetWarnings True 'just incase
    Exit Sub

Err_AutoExec1:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_AutoExec1

End Sub

 

[bowes884]

Still got an error. It keeps highlighting Function AutoExec1() but a popup comes up in red with: yOption Compare Database. What does this mean?

code:

Option Compare Database

'------------------------------------------------------------
' Test_Locks_macro
'
'------------------------------------------------------------
' autoexec1
'
'------------------------------------------------------------
Public Sub AutoExec1()
On Error GoTo Err_AutoExec1

Dim sNow As String
Dim sPath As String

sNow = Format(Now(), "mmddyyyy-hhmmss")
sPath = "Y:\0001 Secondary Marketing Reports\"

If Dir(sPath, vbDirectory) = "" Then
MsgBox "Output directory does not exist!", vbCritical, "Abort the mission!"
Exit Function 'stop the code!
End If

DoCmd.SetWarnings False
SendKeys "y", False
DoCmd.OutputTo acOutputReport, "LOCKS - Detail", acFormatRTF, sPath & "14 - Lock Summary" & "_" & sNow & ".rtf", False
DoCmd.SetWarnings True

Exit_AutoExec1:
DoCmd.SetWarnings True 'just incase
Exit Sub

Err_AutoExec1:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_AutoExec1

End Sub

 

[ghudson]

Replace what you have with the below function. This function should be placed in a public module, not a form module! The Option Compare Database & Option Explicit lines should be at the top of the module and only listed once per module!

What is the purpose of your SendKeys command with the Y key?

Does your code compile?

Are you missing any references?

How are you calling this public sub?

If this does not work then you will have to post a sample of your db for I can not help you any further as is.

Option Compare Database
Option Explicit

'------------------------------------------------------------
' Test_Locks_macro
'
'------------------------------------------------------------
' OutputLocksReport
'
'------------------------------------------------------------
Public Sub OutputLocksReport()
On Error GoTo Err_OutputLocksReport

    Dim sNow As String
    Dim sPath As String
    
    sNow = Format(Now(), "mmddyyyy-hhmmss")
    sPath = "Y:\0001 Secondary Marketing Reports\"
    
    If Dir(sPath, vbDirectory) = "" Then
        MsgBox "Output directory does not exist!", vbCritical, "Abort the mission!"
        Exit Sub 'stop the code!
    End If
    
    DoCmd.SetWarnings False
        SendKeys "y", False
        DoCmd.OutputTo acOutputReport, "LOCKS - Detail", acFormatRTF, sPath & "14 - Lock Summary" & "_" & sNow & ".rtf", False
    DoCmd.SetWarnings True
    
Exit_OutputLocksReport:
    DoCmd.SetWarnings True 'just incase
    Exit Sub
    
Err_OutputLocksReport:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_OutputLocksReport

End Sub

 

[bowes884]

Ok, I replaced everything in my macro with what you have above. Now, I want to be able to run this under the macros menu. How can I do that? I eventually want this to run on its own everytime I open up the database. Right now I am having a hard time getting this macro to appear under my macros list. The file its converted from runs its old format not the new one you gave me even after I save it.

 

[bowes884]

Also, the purpose my my send key "y" is when I run the macro it used to replace the old one and the send key just clicked yes to replace when the macro ran. When I step through the code you gave me it all runs great and creates the file I wanted it to create. I just cant get it to appear under my macros list.

 

[ghudson]

I just cant get it to appear under my macros list.

First off you have to change your Public Sub OutputLocksReport() back to a function [see below] so you need to replace the four Sub words with the word Function so that you can run it from a macro.

Then you have to create a new macro named AutoExec. Within the AutoExec macro you have to add a new RunCode action. In the Function Name field you type OutputLocksReport (). You can find the public function by browsing through and selecting the non built-in option directory and then selecting from the listing to the right. You might have to select the name of the module and then the fucntions within that module will be displayed in the last column.

Option Compare Database
Option Explicit

'------------------------------------------------------------
' Test_Locks_macro
'
'------------------------------------------------------------
' OutputLocksReport
'
'------------------------------------------------------------
Public Function OutputLocksReport()
On Error GoTo Err_OutputLocksReport

    Dim sNow As String
    Dim sPath As String
    
    sNow = Format(Now(), "mmddyyyy-hhmmss")
    sPath = "Y:\0001 Secondary Marketing Reports\"
    
    If Dir(sPath, vbDirectory) = "" Then
        MsgBox "Output directory does not exist!", vbCritical, "Abort the mission!"
        Exit Function 'stop the code!
    End If
    
    DoCmd.SetWarnings False
        SendKeys "y", False
        DoCmd.OutputTo acOutputReport, "LOCKS - Detail", acFormatRTF, sPath & "14 - Lock Summary" & "_" & sNow & ".rtf", False
    DoCmd.SetWarnings True
    
Exit_OutputLocksReport:
    DoCmd.SetWarnings True 'just incase
    Exit Function
    
Err_OutputLocksReport:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_OutputLocksReport

End Function

 

[bowes884]

HUGE Kudos to you for your help. Serious helper. Now I get to take that date/ Time stamp and figure how to apply it to other macros that print reports. I had just messed with that runcode thing right before I read this post and got it to work. Thank you so much!

Bowes

 

[ghudson]

Glad you got it to work.

You need to drop the macros and use VBA. Macros are too limiting and VBA can do so much more. Error trapping and handling runtime errors is a huge step forward when using VBA compared to macros.

 

[bowes884]

Yeh,

My boss is big into macros, I personally want to study VBA more, I am only an Intern" And eventually do that here. My boss is jsut trying to keep things simple for when we want to use the date/time stamp for another type of macro that prints a report. I am about to try and get this stamp to work with another access table I have right now too. Thanks for your help

 

[bowes884]

Ive got another date/time stamp question.

Is there a way to convert an excel file to an access table and have the table name include the Date/Time? Thanks. I already know hwo to create a macro to convert the excel file to access table. Thanks.

 

[ghudson]

Your boss needs to step up (grow up and look beyond the limitations of macros.

I question the logic to create a table with a date time stamp in the table name. That goes against "normalization". Have your boss search the forum or google for the keyword "normalization". Or you do it and report your findings to your boss and show him how to create and design a good database.

You should store all related records in the same table. You can easily add a field to the table with the data and time stamp. Just create the table field and set the default value to =Now().