Name the output PDF using subform fields

[NeoPa]

I'm not sure I know to be honest. It must be referring to the Me.BacTQuery_Su bform.Form.Reco rdsetClone 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!