pull result of query into caption name

[aftershokk]

I have a report that when I print to .pdf is places the name stored in the cation field in the report properties.

Right now caption states "name" so when I print/convert to .pdf a pop-up box appears and wants to save the file as name.pdf

What I want to do is have the caption store the result of a query so the file name will be automatically placed and saved with the correct file name.]

any help is appreciated!
Brian

 

[donbettis]

Not sure if I am understanding your question right...

I think you are wanting the file to be named the same as the query...

If so, just leave the Caption field blank in the Property Sheet of the report it should default to the name of the qry.

 

[aftershokk]

thanks but that does not work. It is based on a different query that has a different value based on a check box that is selected to run the report and it varies each time the report runs

 

[aftershokk]

Here is another post I had about this

Hi all, I have a report the runs based on a check box that limits the data to a filtered range. I use send commands to convert to a .pdf. See below.

1. Open report for print preview

Function redesign_send()
On Error GoTo redesign_send_Err

DoCmd.Echo True, ""
DoCmd.OpenReport "fee_schedule_rpt_new", acViewPreview, "", "", acNormal
DoCmd.RunMacro "send_commands", , ""


redesign_send_Exit:
Exit Function

redesign_send_Err:
MsgBox Error$
Resume redesign_send_Exit

End Function

----------------------------------

2. Use send commands to convert to .pdf

Function send_commands()
On Error GoTo send_commands_Err

SendKeys "alt p", False
SendKeys "{ENTER}", True


send_commands_Exit:
Exit Function

send_commands_Err:
MsgBox Error$
Resume send_commands_Exit

End Function
---------------------------------

What I want to do is have the report convert to .pdf based on the name of the category used to filter the list (check box). I have a separate query (see figure 1 below) to return the unique filtered category.

Example: Right now I click the check box for the category named Pediatrics and the report is created for Pediatric values. When I create the .pdf I want the report to be named Pediatrics.pdf AUTOMATICALLY? Right now I type the name into the pop-up box of the location to save the file.

make any sense?

figure 1
SELECT speciality_lookup_table_08.type, speciality_lookup_table_08.report_indicator
FROM speciality_lookup_table_08
WHERE (((speciality_lookup_table_08.report_indicator)=Ye s));

thanks

 

[donbettis]

Are the check boxes a part of an Option group or just checkboxes? And what is the odds of having more then one box checked?

 

[aftershokk]

I tried the open event for the report because there is not an unload option

Me.Caption = Forms![file_name]![type]

I get an error message

It is looking for a macro named me.

 

[aftershokk]

check boxes are stored in a table for filtering purposes and I do not want more than one checked at a time because I know it would be a mess unless I could create some kind of concatenate

 

[donbettis]

The earlier option I spoke about was to be placed in the On Load event of the Report - I think the error came from it being in the On Open...but I am not sure it would work for this...

When the form is loaded are the check boxes already checked or is this an option once the form is opened?

I am trying to determine how you have the form layed out... Is this like a report form that opens and is used only for reports or is it something else?

 

[aftershokk]

The attached screen shot (zipped) may help.
I click the check box in the form and then click the run to .pdf command box which calls the access report.

The check box is used as a table join to the underlying query of the report to limit the results to that specialty. I hope this helps?

again thanks!

 

[donbettis]

Take a look at this sample database...By not knowning exactly how your database is setup I am not sure if this is what you are looking for. Hopefully it'll lead you in the right direction.

The database is in an Access 2000 format however, is will work in Access 2007 as well.

 

[aftershokk]

thanks for the efforts but I guess I am not doing a good job explaining

Let me create some more screen shots

 

[aftershokk]

When I call the report by clicking the convert to .pdf button, it only calls one existing report. I only have one report in the database in it only displays data for the specialty based on what was checked. The send commands convert to a .pdf but I want the name tied to the check box for the report name. see screen shot

The screen shot shows what happens when I run my .pdf macro. Right now I have to type in the report name for each specialty I run. Currently, I run one at a time and type in the name like allergy.pdf, run again and then type medicine.pdf etc..

thanks!

 

[donbettis]

I still don't totally understand how your report and query works...However this attached database uses one report and one query...it changes the name of the to be saved .pdf file to the name of what is checked...

I have CutePDF set as my default printer...If I have Allergy checked when the save window pops up the .pdf is named Allergy. If I have something esle checked it has that name in the filename box of the save window.

Without having a copy of the database I am not sure beyond this...

Hopefully by looking at my code you will get a better idea as to how I am passing the name change to the Caption of the report based upon what is check...at best this may lead you in the right direction.

 

[aftershokk]

ah ha, I get it now and I think this will work. I will work on it today and let you know.

thanks for your assistance!

 

[aftershokk]

I am close and you have the right idea but my form with the check boxes is only one line in design view as the list is based on a table (done this way so I can add new specialties dynamically) which is shown in the attached first screen shot. The second screen shot shows the form in design view.

thanks