Page 2 of 2 FirstFirst 12
Results 16 to 21 of 21
  1. #16
    jazzy is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    77
    meant the code that ssanfu (Steve) provided. my code above works. he suggested you don't have to open the report first. when i ran his code
    Code:
     DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strFilePath & strFileName, False
    it wanted to print all the contracts because it is missing the criteria of the ContractsID.

  2. #17
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Oh, sorry for the misunderstanding. Just to clarify, you don't need to open the report first (to filter it by the selected\current ID) if the report's record source is already incorporating that in the criteria (that is what Steve is saying towards the bottom of his post); if your report is set to include all records then you need to open it (like you do) first to filter it as OutputTo doesn't have that option. The problem in the first post is that you don't isolate the ID variable so the Where condition would look for the literal "[Forms]![frm_Contract]![ContractsID]":
    'First let's open the reportstrReport = "rpt_Contract"
    DoCmd.OpenReport strReport, acViewPreview, , "[ContractsID] = [Forms]![frm_Contract]![ContractsID]", acWindowNormal
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #18
    jazzy is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    77
    put filter on load to YES and used
    Code:
    DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strFilePath & strFileName, False
    much faster and cleaner. Thanks Steve and Vlad

    new code looks like this

    Code:
    Private Sub CreatePDF_Click()DoCmd.RunCommand acCmdSaveRecord
    
    
    Dim UserVenuePath As String
    Dim strFilePath As String
    Dim strFileName As String
    Dim strReport As String
    Dim Venue As String
    
    
    UserVenuePath = DLookup("[FilePath]", "tbl_SystemInfo", "[SystemInfoID] = 1")
    Venue = DLookup("[Venue]", "tbl_SystemInfo", "[SystemInfoID] = 1")
    strFilePath = UserVenuePath & Environ("username") & "\OneDrive - Caterer\" & Venue & "\Contracts\" & Format(Forms!frm_Contract.DateofFunction, "MM-DD-yyyy") & "\" & Forms!frm_Contract.DayTimeFunction & "\"
    strFileName = Forms!frm_Contract.NameonContract & ".pdf"
    
    
    MakePDF = MsgBox(strFilePath, vbOKCancel, "Create PDF for " & strFileName)
    If MakePDF = vbOK Then
    
    
    If FolderExists(strFilePath) = False Then
              Call MakeDir(strFilePath)
    Else
              MsgBox "This folder already exists.", vbInformation, "Folder Exists"
    End If
    
    
    'First let's open the report
    strReport = "rpt_Contract"
    'DoCmd.OpenReport strReport, acViewPreview, , "[ContractsID] = [Forms]![frm_Contract]![ContractsID]", acWindowNormal
    
    
    'Now let's save the open report to .PDF
    'DoCmd.SelectObject acReport, strReport
    'DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strFilePath & strFileName, False, , , acExportQualityPrint
    DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strFilePath & strFileName, False
    'DoCmd.Close acReport, strReport
    
    
    'Display Message
    MsgBox "New file has created successfully in the new folder!", vbInformation, "VBAF1"
    
    
    End If
    
    
    End Sub

  4. #19
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Glad to hear! So you are using a saved filter on the report and Filter On Load =Yes with the report record source still returning all records? I guess that works too but it can be a bit hit and miss as a user can inadvertently change the report's design (remove filter, add a different one, etc.) and save it. I think what Steve (and I) were suggesting to not use a table (or a query that returns all records) as the source of the report but instead a query that limits the records to only the ones matching the ContractID control on the form, something like this:
    Code:
    Select tblContract.* From tblContract WHERE COntractId=[Forms]![frm_Contract]![ContractsID]
    You would save this as qrySelectedContract and in the report's properties window under Record Source instead of tblContract you would enter qrySelectedContract (obviously changed to match your object names but I'm sure you get the idea).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #20
    jazzy is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    77
    updated to your suggestions. works well, thanks.

  6. #21
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @Vlad, you were/are much clearer than I was - that is what I was trying to say.

    Glad jazzy got it worked out.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. DB design same unique ID between two systems
    By johnseito in forum Database Design
    Replies: 8
    Last Post: 10-17-2018, 11:15 AM
  2. Replies: 4
    Last Post: 08-21-2012, 11:10 AM
  3. Operating systems
    By nashr1928 in forum Access
    Replies: 3
    Last Post: 12-05-2011, 03:24 PM
  4. Student Information Systems
    By DrCreosote in forum Access
    Replies: 1
    Last Post: 09-29-2010, 06:33 PM
  5. systems tables
    By ldbeeman in forum Access
    Replies: 2
    Last Post: 09-18-2010, 05:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums