Set File Name and set destination folder relative to Database Folder?

[Cosmos75]

I have this code behind a button on my form.

    Dim stDocName As String
    Dim Person As String
    Dim ReportType As String
    Dim StartWeek As Date
    Dim EndWeek As Date
    Dim NameReport As String
    Dim FileName As String
    
    Person = Me.LstPeople.Column(1)
    Office = Me.LstOffice
    StartWeek = Me.LstStartWeek
    EndWeek = Me.LstEndWeek
    

    If Me.FrameOption = 1 Then
    stDocName = "rptProductivityDateRangePerson"
    ReportType = "Person"
    NameReport = ReportType & "-" & Person
    Else
    stDocName = "rptProductivityDateRangeOffice"
    ReportType = "Office"
    NameReport = ReportType & "-" & Office
    End If
   
    FileName = NameReport & " for period " & StartWeek & " - " & EndWeek
    
    DoCmd.OutputTo acReport, stDocName, "SnapshotFormat(*.snp)", FileName, True, ""

How do I set the file path to the same directory as the database? Or a folder named Reports that is in the folder that the database is in?

Also, I keep getting this error message when I press the button.
"The report snapshot was not created beaces you don't have enough free disk space for temporary work file."

 

[Travis]

Return the Applications Path

Access 97 and older use Application.Path

Access 2000 CurrentProject.Path

This will return the path as a string. You can then concatinate the "\Reports" on the end to have it point to the Reports Directory.

Use Dir and the MkDir commands to check to see if the directory exists and create it if it does not exist.

 

[Cosmos75]

Travis,

Thanks for replying!

Change the line to

DoCmd.OutputTo acReport, stDocName, "SnapshotFormat(*.snp)", CurrentProject.Path & "\" & FileName & ".snp", True

But I still get that darned error mesage.
"The report snapshot was not created beaces you don't have enough free disk space for temporary work file."

I have no problem previwing or printing it, why am I having problems saving it as a snapshot file?

 

[Cosmos75]

Tried something...

This works

DoCmd.OutputTo acReport, stDocName, "SnapshotFormat(*.snp)", CurrentProject.Path & "\" & "Test" & ".snp", True

but this doesn't

DoCmd.OutputTo acReport, stDocName, "SnapshotFormat(*.snp)", CurrentProject.Path & "\" & FileName & ".snp", True

 

[ghudson]

Call a message box to display the mystery value.

MsgBox CurrentProject.Path & "\" & FileName & ".snp"

If I had to guess, I would say your file FileName has slashes and Windows does not allow / or \ in the file name.

You might have to do something like this...
FileName = NameReport & " for period " & Format(StartWeek,"mmddyyyy") & " - " & Format(EndWeek,"mmddyyyy")

HTH

 

[Cosmos75]

Tried it

MsgBox "File will be saved as " & FileName & ".snp in " &_
CurrentProject.Path & "\"
        
DoCmd.OutputTo acReport, stDocName, "SnapshotFormat(*.snp)",_
CurrentProject.Path & "\" & FileName, True

Got this

"File will be saved as Smith-4/4/2003 to 5/3/1003.snp in N:\Folder\"

Changed code to

FileName = NameReport & " for " & Format(StartWeek, "mm-dd-yyyy") &_
" to " & Format(EndWeek, "mm-dd-yyyy")
    
MsgBox "File will be saved as " & FileName & ".snp in " &_ CurrentProject.Path & "\"
        
DoCmd.OutputTo acReport, stDocName, "SnapshotFormat(*.snp)", 
CurrentProject.Path & "\" & FileName & ".snp", True

It works!!! Thank you, ghudson!

 

[Cosmos75]

In case anyone might find this helpfull, I'm also using a button to email the report.

    Dim stDocName As String
    Dim Person As String
    Dim PersonFirst As String
    Dim ReportType As String
    Dim StartWeek As Date
    Dim EndWeek As Date
    Dim NameReport As String
    Dim FileName As String
    
    Person = Me.LstPeople.Column(1)
    PersonFirst = Me.LstPeople.Column(2)
    Office = Me.LstOffice
    StartWeek = Me.LstStartWeek
    EndWeek = Me.LstEndWeek
    

    If Me.FrameOption = 1 Then
    stDocName = "rptProductivityDateRangePerson"
    ReportType = "Person"
    NameReport = Person & ", " & PersonFirst
    Else
    stDocName = "rptProductivityDateRangeOffice"
    ReportType = "Office"
    NameReport = Office
    End If
    
    FileName = NameReport & " for the period " & Format(StartWeek, "mm-dd-yyyy") &_
" to " & Format(EndWeek, "mm-dd-yyyy")
    
    MsgBox "Productivity report file will be emailed for " & FileName
        
    DoCmd.SendObject acReport, stDocName, "SnapshotFormat (*.snp)", , , , FileName,_
"Here is the " & ReportType & " productivity report for " & FileName

THe only thing is I can't control the name of the file that is being emailed. At least, I haven't found a way.

 

[Cosmos75]

Access 97 problem

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

    Dim db As Database
    Dim stDocName As String
    Dim SavePath As String
    Dim RDate As Date
    Dim FileName As String
    
    Set db = CurrentDb()
    stDocName = Me.cboDailyReport
    SavePath = Left(db.Name, Len(db.Name) - Len(Dir(db.Name))) & "Reports\Daily"
    RDate = Me.ReportDate
    FileName = Me.cboDailyReport.Column(1) & " - " 
& Format(RDate, "mm-dd-yyyy") & ".snp"
    
    MsgBox "Saving report as" & Chr(13) & FileName 
& Chr(13) & "in folder" & Chr(13) & SavePath, vbOKOnly
    
    DoCmd.OutputTo acReport, stDocName, "Snapshot Format", SavePath & FileName, True

Exit_cmdSave_Click:
    Exit Sub

Err_cmdSave_Click:
    MsgBox Err.Description
    Resume Exit_cmdSave_Click
    
End Sub

I have this code in an Access 97 that saves a report as a snapshot file.

The MsgBox line returns a good message, the date is formattted as 01-01-2003.

BUT, I get this error message

The SendObject action was cancelled
You used a method of DoCmd object to carry out an action in Visual basic, but then clicked cancel in a dialog box.
For example, you used the close method to close to a changed form, then clicked Cancel in the dialog box that asks if you want to save the changes you made to the form

Huh?? There isn't a cancel button anywhere that I pressed. I did press the OK button for the msgbox.

 

[Cosmos75]

My bad!

Never mind, I'm a dummy...

I forgot to include the folder that ReportsDaily is in.

Should be
SavePath = Left(db.Name, Len(db.Name) - Len(Dir(db.Name))) & "Back End\ReportsDaily