Name the output PDF using subform fields

[MyWaterloo]

This is kind of a take off from a previous thread:

http://bytes.com/topic/access/answers/898800-email-multiple-reports

I am outputting a report to pdf using the code below. The name of the fields [Me.BacTName] and [Form_BacTQuery Subform].BacT_ID make up the output file name. This code works good. Here is the problem: My subform can have multiple record sets each containing a [Form_BacTQuery Subform].BacT_ID. I want the file name I out put to be a composite of [Me.BacTName] and the first [Form_BacTQuery Subform].BacT_ID, hyphen, and then the last [Form_BacTQuery Subform].BacT_ID in the associated record sets. It should look something like this: "WVM212-220". Right now I am only getting: "WVM212". How do I code to include the last [Form_BacTQuery Subform].BacT_ID on the subform as part of the file name? Does this make sense?

DoCmd.OutputTo acOutputReport, "BacTPDF", "(*.pdf)", ("C:\Temp\" & Me.BacTName & [Form_BacTQuery Subform].BacT_ID & ".pdf")

[NeoPa]

It does make sense.

Firstly, you need to understand that [Form_BacTQuery Subform].BacT_ID refers simply to the currently active record on your subform. It needn't even necessarily be the first one.

I would advise checking out the RecordSet as found via your subform (rather than via the object class as you are currently - See Referring to Items on a Sub-Form). If you use .RecordSetClone then the current record needn't even be moved. You need to go to the start and the end and note the related values in [BacT_ID].

[MyWaterloo]

Neo,

Thank You. I sort of understand what you are saying. I still don't know how to implement it. I understand your concepts, but i don't know the structure for turning them into actual working code. So, is there a way to include the "first" and "last" BacT_ID that is currently showing/filtered in the subform?

[NeoPa]

The tips I gave you will give you access to the first and last of the items filtered. They may not be the ones visible, as there may be scrolling on your form, but they can be the first and last that qualify within the filter.

I'm happy to help here, but I'm not about to simply do it for you. I've provided you with the tools. Have a go. If you get stuck then explain where and we can help further.

[MyWaterloo]

Thank you for your...help? I have no idea how to go about using the .RecordSetClone or what the frame work should even look like in which it is used. I do not want it "done for me", but I do not know where to even begin with this. You say that "The tips I gave you will give you access to the first and last of the items filtered." How so? How does one use the .RecordSetClone to "go to the start and the end and note the related values in [BacT_ID]"? I do not see how the article you link to will give me the results I am looking for. Is there anywhere I can look for an example of how this is done?

[MyWaterloo]

I have been scouring the internet and reading anything that looks applicable to my problem. I am very stuck and very frustrated. I have yet to find anything that helps move me in the right direction i.e. How to use the first and last records on my subform to make up the outputted file name. Is there anyone who has done this before? Or has does anyone know how to return the values for the first and last records on a subform? Currently my entire app is hanging on having the ability to output a pdf file with a name made up of the records contained in it. Example: if I create a pdf that will contain records 1 through 10 for a client named Harold I would like to have that pdf named, HAROLD1-10. I am able to output HAROLD and the in focus record number.... but that's all. HELP!

[NeoPa]

Your post #5 at least gives me an idea of what you do and don't understand. The link shows you how to access the form that is associated with the subform control on your main form. I mentioned that accessing it, as you do, via the form's class name, is inadvisable.

Had I your project in front of me I could give clearer examples possibly, but I only have the info you've shared to date. This makes it hard for me to direct you in a way that makes sense to you contextually, and as there is so much new stuff you need it is probable that without this you will still be confused and feel I'm not giving you enough of a start. In this I am limited though, as much pertinent information has yet to be shared.

I will nevertheless have a go at explaining in more detail than I gave earlier, what you need to do to get your required information.

I assume from your earlier reference that the form you see in your Access Database window is called [BacTQuery Subform]. Referenced in code, the class of this form is [Form_BacTQuery Subform], which is referenced in your code. Somewhere though, from your statements, there is another form which has a SubForm control on it which has [BacTQuery Subform] as it's linked form. I will refer to the form as [MainForm] and the SubForm control as [SubFormCtrl]. You will have your own names, but I have no knowledge of them as yet.

Forms generally, have two main properties that can be used for work such as this :
.Recordset
.RecordsetClone
If you don't know what Recordsets are, then I suggest you find out, as I am about to lead you into some fairly typical use of one.

Specifically, .Recordset is a view within your code of the values shown in your form, whereas .RecordsetClone is a duplicate of that, which can be used to inquire of the data in the knowledge that no changes (of current record etc) will be reflected on the form to the operator.

Your code, presumably, is running within the module for [MainForm]. The code below gives an indication of what is required to inquire of the sub-form and determine it's first and last record values (of the [BacT_ID] control on your subform). It includes some Recordset manipulation, but as both of these two properties are already opened recordsets, it won't require the opening or closing usually associated with Recordsets.

Dim strFile As String, strFrom As String, strTo As String
 
strFile = "C:\Temp\%M%F-%T.pdf"
strFile = Replace(strFile, "%M", Me.BacTName)
With Me.SubFormCtrl.Form.RecordsetClone
    Call .MoveFirst
    strFile = Replace(strFile, "%F", .BacT_ID)
    Call .MoveLast
    strFile = Replace(strFile, "%T", .BacT_ID)
End With

Of course, "C:\Temp" may be better set as Environ("Temp"), but I'll leave that for you to work out.

[MyWaterloo]

Ok, I didn't exactly know where to place this code so I just gave it a stab and put it behind a button I stuck on the main form to see what would happen when clicked. When I click the button I get: "Method or data member not found." for SubFormCtrl. Any ideas? Thanks.

[MyWaterloo]

I Changed "With Me.SubFormCtrl.Form.RecordsetClone" to "With Me.BacTQuery_Subform.Form.RecordsetClone" It runs past this now and gets hung up at "strFile = Replace(strFile, "%F", .BacT_ID)" and tells me "Object doesn't support this property or method".

[NeoPa]

Why don't you post the exact code you have the error with and tell me the line # the error occurs on. That way I know what I'm working with.

Before you do that though, please reread my previous post and make sure you have understood it proerly. Post #8 was an example of simply not following what was already explained. I can deal with that type of question if required, but it's a bit of a waste of time when the answer's already there for you to read.

Generally, if you consider your post carefully before posting, simple things like posting the code you're referring to can save the to & fro which takes up so much time.

[MyWaterloo]

Thank you for the reply. I have been away from work the last few days and was unable to check in to see if there had been a reply to the previous post of mine. NeoPa, here is the code I am having trouble with, it is the code you have supplied me with line #8. When run I receive "Method or data member not found" and "SubFormCtrl" on line #8 is highlighted.

  Private Sub Command225_Click()
 
 
  Dim strFile As String, strFrom As String, strTo As String
 
    strFile = "C:\Temp\%M%F-%T.pdf"
    strFile = Replace(strFile, "%M", Me.BacTName)
    With Me.SubFormCtrl.Form.RecordsetClone
        Call .MoveFirst
        strFile = Replace(strFile, "%F", .BacT_ID)
        Call .MoveLast
        strFile = Replace(strFile, "%T", .BacT_ID)
   End With
 
 
End Sub 

As for not giving pertinent information, I don't know what else there would need to be given? It's a main form with a subform and I would like to output a report of the subform to pdf. All good so far. I need the output name on the pdf to be a composite of the first and last record name currently filtered on the subform, no good. =-) I am trying to follow what you have explained, but since there was no indication of where to run my code I decide to place it behind an on click event of a button on the main form just to see what would happen. It appears this code was meant to replace the name of a pdf that is already in C:\Temp with the name of the first and last record filtered on the subform. Great! Except if I am running it in the right place it is hanging up on line #8 as explained above. Thanks.

[NeoPa]

If you refer back to paragraph #4 of post #7 you will see why you are having this issue. I have no knowledge of the subform control you have on your main form, so I used the name [SubFormCtrl] as a generic place-marker. For this to work for you, you will need to replace this name with the actual name of the subform control on your form.

NB. A sub-form (a form added onto another form) is not the same as a SubForm control (a control on a form which contains another form, as a sub-form). Unfortunately, the SubForm wizard names a SubForm control the same as the form that is used to fill it, so identifying what is what can admittedly be very confusing. It could be (deducing from the wizard naming behaviour) that your subform is called [BacTQuery Subform], but I suggest you find the control and look to see what its name actually is.

[NeoPa]

MyWaterloo:
but since there was no indication of where to run my code I decide to place it behind an on click event of a button on the main form just to see what would happen.

There was no mention in the question of where you should run the code. You gave me to understand you already had code running, that simply didn't quite do the job. I assumed you would put this (new) code within your existing code and simply replace that part that was doing the same job, with the new code that worked.

[MyWaterloo]

[BacTQuery Subform] is the subform control. I finally feel like maybe we are getting on the same page. Post #9 explains the issue I am currently having.

 
      Private Sub Command225_Click()
 
 
      Dim strFile As String, strFrom As String, strTo As String
 
        strFile = "C:\Temp\%M%F-%T.pdf"
        strFile = Replace(strFile, "%M", Me.BacTName)
        With Me.BacTQuery_Subform.Form.RecordsetClone
            Call .MoveFirst
           strFile = Replace(strFile, "%F", .BacT_ID)
           Call .MoveLast
           strFile = Replace(strFile, "%T", .BacT_ID)
      End With
 
 
   End Sub 
 
 

Line #11 has an error at

 strFile = Replace(strFile, "%F", .BacT_ID)

The error states that "Object doesn't support this property or method". What is that telling me exactly?

[MyWaterloo]

This is the code being used to output a report to a pdf and then attach and email it. I then delete the pdf in the temp folder with the Kill command. Line #1 is where I create the pdf. Any ideas where I would place the code that has been suggested to change the pdf file name?

DoCmd.OutputTo acOutputReport, "BacTPDF", "(*.pdf)", ("C:\Temp\" & Me.BacTName & ".pdf")
 
 Dim stPathName As String
 
    stPathName = "Me.BacTName"
 
   Dim ObjOutlook As Outlook.Application
 Dim ObjOutlookMsg As Outlook.MailItem
 Dim objOutlookRecip As Outlook.Recipient
 Dim objOutlookAttach As Outlook.Attachment
 
 Set ObjOutlook = New Outlook.Application
 Set ObjOutlookMsg = ObjOutlook.CreateItem(olMailItem)
 
 With ObjOutlookMsg
 
      Set objOutlookRecip = .Recipients.Add(Me.Text199)
      'repeat previous steps for as many users that you're going to send the email too.
      objOutlookRecip.Type = olTo
      .Subject = "BacT Results " & Me.Report_Heading
      .Body = "The content of this email is the confidential property of the ######### and should not be copied, modified, retransmitted, or used for any purpose except with the ##########'s written authorization.  If you are not the intended recipient, please delete all copies and notify us immediately."
      Set objectlookAttach = .Attachments.Add("C:\temp\" & Me.BacTName & ".pdf")
Kill ("C:\Temp\*.*")

[NeoPa]

I'm not sure I know to be honest. It must be referring to the Me.BacTQuery_Subform.Form.RecordsetClone object. The reference for the field is probably !BacT_ID, rather than .BacT_ID. Try it that way and see what you get. Unfortunately I have nothing to try it on, so I have to do it mostly in my head. Let me know how that works.

[NeoPa]

MyWaterloo:
Any ideas where I would place the code that has been suggested to change the pdf file name?

Let's keep to one question at a time if we can. Multiple questions at the same time lead to confusion and a thread that's difficult to follow.

The answer is basically to put the code before what you have there as it sets up the value to use in line #1. Clearly, you'd need to change line #1 so that the last parameter, which was ("C:\Temp\" & Me.BacTName & ".pdf"), becomes strFile, as set up by the suggested code which will preceed it.

[MyWaterloo]

Getting somewhere! Here is what is currently happening. The original code:

    Dim strFile As String, strFrom As String, strTo As String
 
    strFile = "C:\Temp\%M%F-%T.pdf"
    strFile = Replace(strFile, "%M", Me.BacTName)
    With  With Me.BacTQuery_Subform.Form.RecordsetClone
        Call .MoveFirst
        strFile = Replace(strFile, "%F", .BacT_ID)
        Call .MoveLast
        strFile = Replace(strFile, "%T", .BacT_ID)
   End With
 
 
DoCmd.OutputTo acOutputReport, "BacTPDF", "(*.pdf)", strFile

...has (strFile, "%T", .BacT_ID) on line #7 and #9. The problem is that there is no BacT_ID on the main form, only on the subform.


So I changed the code to:

    Dim strFile As String, strFrom As String, strTo As String
 
    strFile = "C:\Temp\%M%F-%T.pdf"
    strFile = Replace(strFile, "%M", Me.BacTName)
    With  With Me.BacTQuery_Subform.Form.RecordsetClone
        Call .MoveFirst
        strFile = Replace(strFile, "%F", [Form_BacTQuery Subform].BacT_ID)
        Call .MoveLast
        strFile = Replace(strFile, "%T", [Form_BacTQuery Subform].BacT_ID)
   End With
 
 
DoCmd.OutputTo acOutputReport, "BacTPDF", "(*.pdf)", strFile

Note line #7 and #9 now. This pulls the BacT_ID number from the subform BacT_ID field and creates a pdf file in the Temp folder. Great! Unfortunately the file name is only the BacT_ID that is in focus on the subform and just uses it twice, i.e. VSF2273-2273 instead of VSF2273-2275. I have never until now used the Replace function so I don't know how to troubleshoot this. Ideas? Thanks.

[MyWaterloo]

I am thinking maybe the problem is I am using the actual record in line #7 and #9 with

[Form_BacTQuery Subform].BacT_ID)

instead of using the RecordsetClone. Just thinking out loud. Because everything is working except moving the record with .MoveFirst and .MoveLast. If I click on the middle record bringing it in focus then the code will output a pdf file with the name of that record.

[MyWaterloo]

I have created and attached a quick and dirty database that shows what I am trying to make happen. The database opens to a from that includes a button for exporting to a pdf in the C:\Temp folder. The code that we have been using is behind this button. Maybe it will help if there is something to actually get ones hands on and work with. Thanks.

[NeoPa]

MyWaterloo:
Just thinking out loud.

You're there. That's exactly the reason.

You don't say what happened when you tried my suggestion from post #16, but as you've attached a db copy to your latest post I'll try it for you and see what I get.

You'll be hearing from me again soon.

[NeoPa]

Sooner than I'd hoped it seems.

I don't have Access 2007 (and never want to, if I can possibly avoid it). Here are some general purpose instructions I often post for when people are about to post databases. Usually it is only when requested to do so, but I'm happy to look at it for you in this case as it's dragged on long enough and hands-on would be a good idea at this stage.

When attaching your work please follow the following steps first :

1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
4. If the database includes any code, ensure that all modules are set to Option Explicit (See Require Variable Declaration).
5. If you've done anything in steps 1 to 4 then make sure that the problem you're experiencing is still evident in the updated version.
6. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
7. Compact the database (Tools / Database Utilities / Compact and Repair Database...).
8. Compress the database into a ZIP file.
9. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.

[MyWaterloo]

It works! The funny thing is, the database I created and attached ended up working... so why didn't my other database work? Come to find out it was as simple as changing line 7# and #9 BacT_ID to [BacT ID] with the brackets. I have no idea why this is so.

So after almost driving NeoPa to a lifestyle of seclusion as far away from a computer as possible... Here is the final results.

 Dim strFile As String, strFrom As String, strTo As String
 
    strFile = "C:\Temp\%M%F-%T.pdf"
    strFile = Replace(strFile, "%M", Me.BacTName)
    With [Form_BacTQuery Subform].RecordsetClone
      Call .MoveFirst
        strFile = Replace(strFile, "%F", ![BacT ID])
        Call .MoveLast
       strFile = Replace(strFile, "%T", ![BacT ID])
   End With
 
 
DoCmd.OutputTo acOutputReport, "BacTPDF", "(*.pdf)", strFile
 

This is the code that works to output a pdf with a name made up of the first and last filtered record on my subform. Thank you so much NeoPa... there were a few moments there where you were you were not exactly on my list of people to invite this years Christmas party, ...but I'm sure the feeling was mutual. =-P
I really appreciate your time and endurance. So until the next crisis...........

God bless,
MyWaterloo

[MyWaterloo]

*sniff* back so soon. The code to output the file works great! I just can't figure out how to attach the file now.

 
 
   Dim strFile As String, strFrom As String, strTo As String
 
    strFile = "C:\Documents and Settings\User\Desktop\BacT Reports PDF\Current Month\%M%F-%T.pdf"
    strFile = Replace(strFile, "%M", Me.BacTName)
    With [Form_BacTQuery Subform].RecordsetClone
      Call .MoveFirst
        strFile = Replace(strFile, "%F", ![BacT ID])
        Call .MoveLast
       strFile = Replace(strFile, "%T", ![BacT ID])
   End With
 
 
DoCmd.OutputTo acOutputReport, "BacTPDF", "(*.pdf)", strFile
 
 
 Dim stPathName As String
 
     stPathName = "Me.BacTName"
 
    Dim ObjOutlook As Outlook.Application
  Dim ObjOutlookMsg As Outlook.MailItem
  Dim objOutlookRecip As Outlook.Recipient
  Dim objOutlookAttach As Outlook.Attachment
 
  Set ObjOutlook = New Outlook.Application
  Set ObjOutlookMsg = ObjOutlook.CreateItem(olMailItem)
 
  With ObjOutlookMsg
 
       Set objOutlookRecip = .Recipients.Add(Me.Text199)
       'repeat previous steps for as many users that you're going to send the email too.
       objOutlookRecip.Type = olTo
      .Subject = "BacT Results " & Me.Report_Heading
       .Body = "The content of this email is the confidential property of the ######### and should not be copied, modified, retransmitted, or used for any purpose except with the ##########'s written authorization.  If you are not the intended recipient, please delete all copies and notify us immediately."
       Set objectlookAttach = .Attachments.Add(strFile & ".pdf") 

Line #37, I don't know how to pick up the file to attach it to the email. Is strFile what I am looking to use there? Suggestion?

[MyWaterloo]

I keep answering my own questions. I think sometimes just typing out the problem and posting gives the answer itself.

Set objectlookAttach = .Attachments.Add(strFile)

This works.

[NeoPa]

MyWaterloo:
there were a few moments there where you were you were not exactly on my list of people to invite this years Christmas party

LoL :-D

Honesty is always good in my book. People don't always like it, but I'm an admirer, so you're welcome at my Christmas party.

As for the code now working, there seem to be two issues here :

1. The simpler one (from post #16) of replacing the dot (.) with the bang (!).
2. The more complicated one, especially for me, of Access sometimes using an underscore character (_) to replace a space ( ). In my experience from 2003 and earlier, I only know this occurs when creating an event procedure for an object which contains a space. This doesn't seem to be exactly what's happening here, but you're working in 2007 which is new territory. Maybe it is doing something similar with a control which has a space in the name. That would be my guess, but it was never mentioned that the name of the control was different from what was used in the code.
   
   The correct way of handling spaces in names when referring to them in code (after avoiding them completely of course) is the latter one which you found to work. To whit - surrounding the whole, correct, name in brackets ([BacT ID]).

[NeoPa]

I just thought it through again and it seems that the confusion is probably that I based my code sample on the assumption that the control on the form would have the same name as the field in the recordset. This sounds less like a 2007 issue and more a simple one of Access naming the form control for you by taking the field name and replacing the space with the underscore. I expect this is done routinely by Access (as the problems with spaces in item names is quite well understood generally), but as I never use them anyway, I obviously haven't come across it much in my experience. I think this is a more likely explanation of your problem though.

Let us know if that explains it.

[MyWaterloo]

Yes, I think I get where you are coming from. By using the underscored version of the field name I was referencing the form control instead of the field in the recordset. When I changed it to bracketed Access understood me to mean the actual recodset field instead of the control... correct? Also, I know spaces are a no no, and I make sure to no longer allow spaces in my projects. This project happens to be about 3yrs old and I have yet to find the time to redo it to accepted standards.

[NeoPa]

MyWaterloo:
When I changed it to bracketed Access understood me to mean the actual recodset field instead of the control... correct?

Not quite. Good thinking though. I can see you're coming along a fair way.

The brackets allow the name to be recognised at all. The difference between the control and the field appears to me to be the difference between [BacT_ID] and [BacT ID] (The underscore "_" and the space " "). The brackets can be used for both, but only the one with the space needs them.

[MyWaterloo]

Ahhhh I think I see. So I was referring to the control (BacT_ID) when I wanted to be referring to the field ([BacT ID]). Access understands either one when bracketed, but could not understand the field unless it was bracketed. I guess the point is, don't use spaces!

[NeoPa]

Pretty well yes. Without the brackets Access is looking to find a field called BacT (as it sees the space as the end of the name). The brackets tell it not to be fooled into thinking the name's finished just because it hits a space, and to wait till the closing bracket for the whole name.

And yes - Spaces in names make it harder for humans to work with, definitely.