Naming an exported report

[Robin Chapple]

I regularly export various individual reports from a membership
database using a macro which exports the report as RTF.

Currently I use Windows Explorer to to add the membership ID to the
filename so that it is different to the rest of the recent reports.

As the workload increases I am telling myself that there must be a way
of incorporating the ID in the filename as I export it.

Am I right and if so where do I find the answer?

Thanks,

Robin Chapple

 

[Gary Miller]

Robin,

A bit tough in a Macro, but easy enough in VBA. Convert your
macro to VBA and post the code that you get and we can tweak
it to do what you are looking for.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

 

[Robin Chapple]

Gary,

Thanks for the help.

How do I convert a macro to VBA?

 

[Robin Chapple]

I will try to understand the two suggestions which have been made.

It sound like you are getting your membership ID from a
form and saving the report.

I have a command button on the main menu form which runs the macro on
click. The report uses a query with a parameter query to find the
required ID.

Below will use the report "report name" and name it by
concatinating The field/control [ID] on the current form
and append "nameyouwant.rtf" and save it to the
c:foldername directory

On Error GoTo exportreport_Err
Dim IDname As String
IDName = "C:\foldername\"&Me![ID] & "nameyouwant.rtf"

DoCmd.OutputTo acReport, "Report name", _
"RichTextFormat(*.rtf)", IDname, False, ""

exportreport_Exit:
Exit Sub
exportreport_Err:
MsgBox Error$
Resume exportreport_Exit

-----Original Message-----
I regularly export various individual reports from a membership
database using a macro which exports the report as RTF.

Currently I use Windows Explorer to to add the membership ID to the
filename so that it is different to the rest of the recent reports.

As the workload increases I am telling myself that there must be a way
of incorporating the ID in the filename as I export it.

Am I right and if so where do I find the answer?

Thanks,

Robin Chapple
.

 

[Gary Miller]

Robin,

In the database window select your macro. Now go to
Tools/Macros/Convert Macros to Visual Basic

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

 

[Robin Chapple]

Here is the VBA


'------------------------------------------------------------
' macPrintIndividualReport
'
'------------------------------------------------------------
Function macPrintIndividualReport()
On Error GoTo macPrintIndividualReport_Err

DoCmd.SetWarnings False
DoCmd.OutputTo acReport, "rptIndividualRecord",
"RichTextFormat(*.rtf)", "D:\###\##IndividualReport.RTF", True, "", 0
DoCmd.SetWarnings True


macPrintIndividualReport_Exit:
Exit Function

macPrintIndividualReport_Err:
MsgBox Error$
Resume macPrintIndividualReport_Exit

End Function


Many thanks,

Robin Chapple

 

[Gary Miller]

Robin,

Good job getting the code out. There are some critical
details that I don't have from you so you may have to do
some substitution on the names, but you should get the gist
I think. First we need to find the ID from the form that is
calling this and then 'concantenate' it into the report
name. If all of what you have is working fine, then this
would be the modification using the Me![YourIDField] to poke
the ID in. Of course, you will need to change it to your
field name...

Function macPrintIndividualReport()
On Error GoTo macPrintIndividualReport_Err

DoCmd.SetWarnings False
DoCmd.OutputTo acReport, "rptIndividualRecord",
"RichTextFormat(*.rtf)", "D:\###\" & Me![YourIDField] &
"IndividualReport.RTF", True, "", 0
DoCmd.SetWarnings True


macPrintIndividualReport_Exit:
Exit Function

macPrintIndividualReport_Err:
MsgBox Error$
Resume macPrintIndividualReport_Exit

End Function


--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

 

[Robin Chapple]

Gary,

It is beginning to make sense.

My ID field is [ID]. Very imaginative. <G>

I have made the function but when I compile I get an error message:

" Invalid use of Me Keyword "

What have I done wrong.

Robin

 

[Gary Miller]

Robin,

Sorry, I overlooked the fact that you will now need to pass
the ID to the function when you call it as the Function is
in a Global Module after the conversion. Modify the first
line of the Function from...

Function macPrintIndividualReport()
to...
Function macPrintIndividualReport(lngID as Long)

This will allow us to pass the ID in as text. Now modify
this section of the OutputTo line to replace the
Me![YourIDField]] to lngID...

DoCmd.OutputTo acReport, "rptIndividualRecord",
"RichTextFormat(*.rtf)", "D:\###\" & Me![YourIDField] &
"IndividualReport.RTF", True, "", 0

to...
DoCmd.OutputTo acReport, "rptIndividualRecord",
"RichTextFormat(*.rtf)", "D:\###\" & lngID &
"IndividualReport.RTF", True, "", 0

Now on your command button, we will need to call the
function in a way that we can pass the ID to it. In the
OnClick event line type in the following...

=macPrintIndividualReport([ID]

It will now pick up the ID from the form and pass it to the
function for processing.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

Gary,

It is beginning to make sense.

My ID field is [ID]. Very imaginative. <G>

I have made the function but when I compile I get an error message:

" Invalid use of Me Keyword "

What have I done wrong.

Robin

Robin,

Good job getting the code out. There are some critical
details that I don't have from you so you may have to do
some substitution on the names, but you should get the gist
I think. First we need to find the ID from the form that is
calling this and then 'concantenate' it into the report
name. If all of what you have is working fine, then this
would be the modification using the Me![YourIDField] to poke
the ID in. Of course, you will need to change it to your
field name...

Function macPrintIndividualReport()
On Error GoTo macPrintIndividualReport_Err

DoCmd.SetWarnings False
DoCmd.OutputTo acReport, "rptIndividualRecord",
"RichTextFormat(*.rtf)", "D:\###\" & Me![YourIDField] &
"IndividualReport.RTF", True, "", 0
DoCmd.SetWarnings True


macPrintIndividualReport_Exit:
Exit Function

macPrintIndividualReport_Err:
MsgBox Error$
Resume macPrintIndividualReport_Exit

End Function

 

[Robin Chapple]

Gary,

Thanks.

I have made the changes to the function and it now complies.

I have copied the line for the "On Click" event and I get the error
message:

"""The expression contains ambiguous name.
You may have two or more functions with the same name in different
modules. Rename the functions so that each one has a unique name. """

I noticed that your instruction had an incomplete bracket so I have
tried the complete new name with the same error message.

=macPrintIndividualReport(lngID as Long)

Now I am lost.

Robin

 

[Gary Miller]

This error usually occurs when you have two procedures with
the same name. Have you made a copy of it in a form module
as well as the global one? I would suggest doing an
Edit/Find for the module name while in a Code window and
have it search all modules and see if there is more than one
version. You can also feel free to change the name of the
function if you like.

Also, it looks like I double typed the "]" bracket. Make
sure that you just have one on each side.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

Gary,

Thanks.

I have made the changes to the function and it now complies.

I have copied the line for the "On Click" event and I get the error
message:

"""The expression contains ambiguous name.
You may have two or more functions with the same name in different
modules. Rename the functions so that each one has a unique name. """

I noticed that your instruction had an incomplete bracket so I have
tried the complete new name with the same error message.

=macPrintIndividualReport(lngID as Long)

Now I am lost.

Robin

Robin,

Sorry, I overlooked the fact that you will now need to pass
the ID to the function when you call it as the Function is
in a Global Module after the conversion. Modify the first
line of the Function from...

Function macPrintIndividualReport()
to...
Function macPrintIndividualReport(lngID as Long)

This will allow us to pass the ID in as text. Now modify
this section of the OutputTo line to replace the
Me![YourIDField]] to lngID...

DoCmd.OutputTo acReport, "rptIndividualRecord",
"RichTextFormat(*.rtf)", "D:\###\" & Me![YourIDField] &
"IndividualReport.RTF", True, "", 0

to...
DoCmd.OutputTo acReport, "rptIndividualRecord",
"RichTextFormat(*.rtf)", "D:\###\" & lngID &
"IndividualReport.RTF", True, "", 0

Now on your command button, we will need to call the
function in a way that we can pass the ID to it. In the
OnClick event line type in the following...

=macPrintIndividualReport([ID]

It will now pick up the ID from the form and pass it to the
function for processing.

 

[Robin Chapple]

Gary,

Thanks. I am another step closer.

You were right. The original conversion of the macro had the same
name. I have deleted it and I can proceed.

Now the command button produces this error message:

"""The expression you entered as the event property produced the
following error:
The object doesn't contain the automation object ID"""

I am unsure about the brackets so I have tried:

=macPrintIndividualReport([ID])

Which is responsible for the above error message

and

=macPrintIndividualReport[ID]

because you said one each side and the error massage is

"""[ID] invalid syntax"""

Many thanks for your attention.

Robin Chapple

 

[Gary Miller]

Good. Had a feeling you would find it. I guess I was moving
a little fast when I typed out the reference to the ID field
on your form. It needs to be Me! for the form and then ID
for the ID field, so it would be...

=macPrintIndividualReport(Me![ID])


--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

Gary,

Thanks. I am another step closer.

You were right. The original conversion of the macro had the same
name. I have deleted it and I can proceed.

Now the command button produces this error message:

"""The expression you entered as the event property produced the
following error:
The object doesn't contain the automation object ID"""

I am unsure about the brackets so I have tried:

=macPrintIndividualReport([ID])

Which is responsible for the above error message

and

=macPrintIndividualReport[ID]

because you said one each side and the error massage is

"""[ID] invalid syntax"""

Many thanks for your attention.

Robin Chapple

This error usually occurs when you have two procedures with
the same name. Have you made a copy of it in a form module
as well as the global one? I would suggest doing an
Edit/Find for the module name while in a Code window and
have it search all modules and see if there is more than one
version. You can also feel free to change the name of the
function if you like.

Also, it looks like I double typed the "]" bracket. Make
sure that you just have one on each side.

 

[Robin Chapple]

Gary,

This is a marathon. Thanks for sticking with it.

I am concerned that you refer to a form when, as far as I can see,
there is no form open.

The command button is on the Main Form which is just for navigation.
Is that the reason for the current error message?

""" The expression on click you entered as the event property setting
produced the following error:

The object doesn't contain the automation object 'Me.'. """

Cheers,

Robin

 

[Gary Miller]

Robin,

If you are opening this from your Main Form, how are you
deciding who's ID you want to use? Normally something like
this is done from a form where you can be on the record
containing the ID that you want. You can try moving or
recreating another button to a location where the code can
read the ID from the form (Me!ID) or if not, we will need to
come up with a method for you to manually input it. Tell me
which way you want to go. Also, you may want to copy the
code in the event line so I can see what you have at the
moment.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

Gary,

This is a marathon. Thanks for sticking with it.

I am concerned that you refer to a form when, as far as I can see,
there is no form open.

The command button is on the Main Form which is just for navigation.
Is that the reason for the current error message?

""" The expression on click you entered as the event property setting
produced the following error:

The object doesn't contain the automation object 'Me.'. """

Cheers,

Robin


Good. Had a feeling you would find it. I guess I was moving
a little fast when I typed out the reference to the ID field
on your form. It needs to be Me! for the form and then ID
for the ID field, so it would be...

=macPrintIndividualReport(Me![ID])