Output a Form to MS Word 1

[jocat]

I have a database in Access 97 that records parking lot violations. Within that database is a violations history report that I must occasionally submit to a towing a towing agency that I email in MS Word. I normally save the report as an RTF file and then email it, but I want to automate the process so the clerks can do it.

I created a macro using the "OutputTo" function. When defining the output file, I want the file name to be the license plate number. I define it as follows:

C:\Documents and Settings\AJJC\My Documents\TOWING\[license#].doc

I was assuming that the file would be created using the license plate number as the name, but the file name keeps being saved as [license#].doc

I tried " ([license#]) ", =[license#] and a lot of differnt combinations, but I get the same result.

"License#" is the field name in the underlying query. I have [enter plate number] as the criteria for that field in the query so that when the report is run you are prompted to enter the plate number.

I tried using C:\Documents and Settings\AJJC\My Documents\TOWING\[enter plate number].doc but again I get the same result.

Does anyone know how I can get the macro to automatically generate a file name that corresponds to the plate number I am running the report for.

(I know---I ended my sentence with a preposition)

It can probably be done using VBA, but I am a novice in that area and I want to get this done quickly. Can it be done with a macro?

Would appreciate your help.

JOCAT

 

[GingerR]

Hi. First, do all of the clerks have a folder called "TOWING" in their My Documents folders? IF not, your code will fail. Maybe you want to send it to a server instead? I don't know, it's up to you.

Anyhow, the solution is to use VBA.......wait, before you freak out, just do this:

Say you have a form with a button that you push to get this report. Some place on the form, you have a text box that the user types in a license number. Say the text box is called txtLicensePlate. And say the form is called frmReportScreen. They hit the button and get the report for that particular license number, based on what is put in the text box (Forms!frmReportScreen!txtLicensePlate). YOu can set up your existing report like this--instead of asking the user for a license plate number, just change your criteria in the report to Forms!frmReportScreen!txtLicensePlate.

Anyhow, for exporting the word, there's various ways you can set this up, but let's just do it from a button on the same form. Using the reference to the text box on the form, we'll build the file name.

So say you have a second button. Name the button something sensible, like btnOutputToWord. In the button's OnClick event, pick [Event Procedure] and click the BUILD button to the right (the little button with the three dots on it). When it opens, paste in this:

    Dim strFileName, strUserID As String
    'Get User ID, so the file can go into their My Documents folder
    strUserID = Environ("Username")
    
    'Build file path and name
    strFileName = "C:\Documents and Settings\" & strUserID & "\My Documents\TOWING" & Forms!frmReportScreen!txtLicensePlate & ".doc"
    DoCmd.OutputTo acOutputReport, "Cost-WeeklyCharging-OHSummary", acFormatRTF, strFileName, True

I build the file path by getting the user's logon ID (AJJC in your case) and the license plate number and building the string. YOu can look up "OutputTo" in the VBA Help to see what all the pieces and parts mean.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[jocat]

GingerR

I won't freak out. I do use VBA but have to refer to the books because I don't program often enough to retain it in memory.

I will give it a try and let you know how I make out. I'm swamped with a lot of other stuff, so it might be a little while. I'll bet you know that routine.

By the way, it will be moved to the server. I was just experimenting on my computer before I send it there.

This forum is a godsend. It has helped me out in the past.

Thanks.

JOCAT