?
Solved

MS Access 2002 VBA code for button to Save to File for a Report

Posted on 2008-02-09
18
Medium Priority
?
899 Views
Last Modified: 2013-11-27
Need VBA code for a button that will SAVE TO File an Access Report (MS Access 2002)
0
Comment
Question by:deer777
  • 9
  • 6
  • 2
  • +1
18 Comments
 
LVL 14

Expert Comment

by:Glenn_Moore
ID: 20858260
I would suggest a learning experience.  It appears that you have already created your report.  With the Reports menu open, go to the access menu and Click on Tools, Macro, Record a new macro.  Then click on the report, and complete all the steps to save the report to a file.  Then Stop the Macro from recording.  Next, you have to create a command button and put the code into the properties of the mouse click on the command button.  
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 20858500
Will the "DoCmd.OutputTo" method do what you want?

    DoCmd.OutputTo acOutputReport, "YourReport", acFormatRTF, "C:\Temp\YourReport.rtf"
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20859281
Glenn_Moore,

I'm using Access 2000/2003.

I do not have a "Record New Macro" menu option.
(I have only "VBEditor, "Run", and "Convert...)

Is this new in Acc 2007?

JeffCoachman
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20859363
deer777,

JezWalters's solution should work for you.

Just keep in mind, that the OutPutTo command will *TRY* to simulate the report's layout in a Word processing format.
It will not be perfect.

If all you need is the *Data* from the report, try exporting just the RecordSource of the report.
Use...
    DoCmd.TransferText acExportDelim, , "YourReportsRecordSource", "C:\Temp\YourReport.txt"
...for text

Or export to Excel:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "YourReportsRecordSource", "C:\Temp\YourReport.txt"

Hope this helps as well

JeffCoachman

0
 
LVL 17

Expert Comment

by:JezWalters
ID: 20862110
Jeff's quite right about how unsuccessful Access can be sometimes when it converts a report to Rich Text Format - it's best to try it yourself and see how well it works with the particular report you have in mind!

Good luck!  :-)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20864588
JezWalters,

JW,

Just to keep things cool between us, I was not trying to say:
"Jez's solution is OK, but it has a downside"
;-)

It's just that there are so many posts here about the .rtf Report "not looking right", that I thought I should mention it.

So, anytime I see the Q, I ask for clarification.

Good luck with you continued ascent to the top of the ranks here!
:-)

JeffCoachman
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 20864873
Hey, no problem Jeff - actually, I think my solution DOES have the downside you mentioned!  :-)

I only added my comment just to say I knew about the limitations too.  Like I said, it's well worth giving it a whirl to see how well the report comes out before worrying too much.

Somehow, I don't think I'll be climbing any higher in the ranks though - I have a day job you know!!  ;-)
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 20864891
As a parting comment, it might be worth using a PDF printer driver (such as www.cutepdf.com) to save the report - you won't get any conversion problems printing to file!

Just a thought!  :-)
0
 

Author Comment

by:deer777
ID: 20870407
I was thinking something along the lines of:

dhFileDialog  

Just don't know how to bring up that Save screen
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20870803
deer777,

<Just don't know how to bring up that Save screen>

This can be done, but first, make sure you Export code is working flawlessly.

JeffCoachman
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 20873532
It'd probably be easier to use the FileDialog property instead of messing around with an API call:

http://support.microsoft.com/kb/279508

You'll need to add a reference to the latest version of the Microsoft Office Library to do this.
Dim dlgSave As FileDialog
 
Set dlgSave = FileDialog(msoFileDialogSaveAs)
dlgSave.Show

Open in new window

0
 
LVL 17

Expert Comment

by:JezWalters
ID: 20876654
Where exactly were you thinking of invoking the Save dialog box from?
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 20876878
I'm just wondering how FileDialog (or for that matter, dhFileDialog) will help - it just selects a file to save, but doesn't actually DO the save.
0
 
LVL 17

Accepted Solution

by:
JezWalters earned 2000 total points
ID: 20877086
Is this what you're trying to do?
Dim dlgSave As FileDialog
 
Set dlgSave = FileDialog(msoFileDialogSaveAs)
With dlgSave
    .Title = "Select file to save..."
    .ButtonName = "Save Report"
    .InitialFileName = "YourReport.rtf"
    If .Show Then
        DoCmd.OutputTo acOutputReport, "YourReport", acFormatRTF, .SelectedItems(1)
    End If
End With

Open in new window

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20877176
deer777,

Are you still out there?

Jez, is providing lots of info.

One more thing though, whatever method use youes to invoke the save As dialog box, try to use a method that does not require any Dll/Ocx, registration issues.
This way a user does not have to worry about this.

JeffCoachman
0
 

Author Closing Comment

by:deer777
ID: 31429520
Thanks much!!
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 20882833
Just since Jeff mentions it (another very valid point!), of course the FileDialog property doesn't need any DLL registration.

On the other hand, FileDialog is ultimately not as flexible as directly calling GetSaveFileName() from comdlg32.dll, but has the advantage of being simpler - as long as you can do what you want!

Glad I could be of assistance!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20884478
Go Jez!
;)
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question