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.RecordsetC lone" to "With Me.BacTQuery_Su bform.Form.Recordset Clone" 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 "SubFormCtr l" 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\*.*")