Results 1 to 7 of 7
  1. #1
    Lhoj is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2021
    Posts
    92

    How hard is to actually create a report on VBA from a query?

    Here's the deal:

    I have an unbound form with an unbound subform to perform searches based on different criteria. The form contains just a bunch of comboboxes and textboxes to choose the different criteria for what you want to search. Then after clicking a 'Search' button a SQL is built (with the appropriate tables and criteria) and set as the result subform's source object. The way I'm handling it for printing right now is basically setting the orientation to landscape and printing the form itself, which works great and since the main form's fields get printer too it helps check the criteria selected for the search, which is useful. But let's just say it's not the prettiest sight.

    So, considering I don't want to have 30 different forms and reports for the different search options and I want to keep handling the search through a diynamically built SQL, I'm trying to dynamically build a fitting report if the user clicks the print button -obviously controlling beforehand that a search has been made and there's a source object for the subform. I had two approaches for that in mind.



    1- Try to dynamically create a report in VBA from the results subform, same as you would by selecting it and choosing the report access button. So far I haven't been able to find a way to do that. Is it even possible through VBA?

    2- Create a blank report and set the subform's source object as the report's source object, then try to make it manageable. So far it's proven as hardly doable as it sounded. Got some code from a guy trying something similar, got it working (more or less) but it's always showing me just two records per page no matter how I arrange the fields (one per line or all in a single line), and I fail to understand how it's setting each record's height (just the textbox and label's heights). So if any of you can help me tweak the code so it shows as many records per page as there's space for, I'd really appreciate it.

    Code:
    Function CrearInforme(strSQL As String)
        Dim db As DAO.Database ' database object
        Dim rs As DAO.Recordset ' recordset object
        Dim fld As DAO.Field ' recordset field
        Dim txtNew As Access.TextBox ' textbox control
        Dim lblNew As Access.Label ' label control
        Dim rpt As Report ' hold report object
        Dim lngTop As Long ' holds top value of control position
        Dim lngLeft As Long ' holds left value of controls position
        Dim title As String 'holds title of report
    
         'set the title
         title = "Resultados de búsqueda"
     
         ' initialise position variables
         lngLeft = 0
         lngTop = 0
     
         'Create the report
         Set rpt = CreateReport
     
         ' set properties of the Report
         With rpt
             .Width = 8500
             .RecordSource = strSQL
             .Caption = title
         End With
     
         ' Open SQL query as a recordset
         Set db = CurrentDb
         Set rs = db.OpenRecordset(strSQL)
     
         ' Create Label Title
         Set lblNew = CreateReportControl(rpt.Name, acLabel, _
         acPageHeader, , "Title", 0, 0)
         lblNew.FontBold = True
         lblNew.FontSize = 12
         lblNew.SizeToFit
     
         ' Create corresponding label and text box controls for each field.
         For Each fld In rs.Fields
    
             ' Create new text box control and size to fit data.
             Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
             acDetail, , fld.Name, lngLeft + 1500, lngTop)
             txtNew.SizeToFit
    
             ' Create new label control and size to fit data.
             Set lblNew = CreateReportControl(rpt.Name, acLabel, acDetail, _
             txtNew.Name, fld.Name, lngLeft, lngTop, 1400, txtNew.Height)
             lblNew.SizeToFit
    
             ' Increment top value for next control
             lngTop = lngTop + txtNew.Height + 25
         Next
    
         ' Create datestamp in Footer
         Set lblNew = CreateReportControl(rpt.Name, acLabel, _
         acPageFooter, , Now(), 0, 0)
     
         ' Create page numbering on footer
         Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
         acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", rpt.Width - 1000, 0)
         txtNew.SizeToFit
     
         ' Open new report.
         rpt.DefaultView = 0
         rpt.PopUp = True
         rpt.Modal = True
         DoCmd.OpenReport rpt.Name, acViewPreview
    
         'reset all objects
         rs.Close
         Set rs = Nothing
         Set rpt = Nothing
         Set db = Nothing
     
    End Function
    Which results in:

    Click image for larger version. 

Name:	Informe.png 
Views:	18 
Size:	37.0 KB 
ID:	45941

    So, any way I can be using/removing those spaces marked in orange before wasting time trying to make the report look better? Thank you so much!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make the query, click the REPORT WIZARD, click 4 more buttons.
    Done.

    No code needed.

  3. #3
    Lhoj is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2021
    Posts
    92
    The query is dynamically generated from the user's selected criteria after clicking the search button, gotta do it through coding.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Looks like you need to adjust the detail height?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Lhoj is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2021
    Posts
    92
    Quote Originally Posted by Welshgasman View Post
    Looks like you need to adjust the detail height?
    Duh, why was I thinking the detail section would be the whole page?

    Putting all the fields in a row and setting rpt.Section(acDetail).height=txtNew.height worked wonders. Now I just need to polish it a bit.

    Thanks Welshgasman!

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Quote Originally Posted by Lhoj View Post
    Duh, why was I thinking the detail section would be the whole page?

    Putting all the fields in a row and setting rpt.Section(acDetail).height=txtNew.height worked wonders. Now I just need to polish it a bit.

    Thanks Welshgasman!
    You are welcome. might want to post your finished code as well please.
    That way it can help others, as I saw where I think you got all that code, and it never allowed for that issue.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    Lhoj is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2021
    Posts
    92
    In the end I left it with one field per row cause tweaking the textboxs' length to accommodate the text is a real pain. So I just set the detail section height to 1 twip and set the cangrow property to yes so it can fit any amount of fields regardless of the query made on the lookup form,

    This is the carcass code for creating the report and filling it with the query data one field per row (without any other customization for my particular case).

    Code:
    Function CrearInforme(strSQL As String)
        Dim db As DAO.Database ' database object
        Dim rs As DAO.Recordset ' recordset object
        Dim fld As DAO.Field ' recordset field
        Dim txtNew As Access.TextBox ' textbox control
        Dim lblNew As Access.Label ' label control
        Dim rpt As Report ' hold report object
        Dim lngTop As Long ' holds top value of control position
        Dim lngLeft As Long ' holds left value of controls position
        Dim title As String 'holds title of report
    
         'set the title
         title = "Resultados de búsqueda"
     
         ' initialise position variables
         lngLeft = 0
         lngTop = 0
     
         'Create the report
         Set rpt = CreateReport
     
         ' set properties of the Report
         With rpt
             .Width = 8500
             .RecordSource = strSQL
             .Caption = title
         End With
     
         ' Open SQL query as a recordset
         Set db = CurrentDb
         Set rs = db.OpenRecordset(strSQL)
     
         ' Create Label Title
         Set lblNew = CreateReportControl(rpt.Name, acLabel, _
         acPageHeader, , "Title", 0, 0)
         lblNew.FontBold = True
         lblNew.FontSize = 12
         lblNew.SizeToFit
     
         ' Set the detail section height to fit the content
         rpt.Section(acDetail).Height = 1
         rpt.Section(acDetail).CanGrow = True
    
         ' Create corresponding label and text box controls for each field.
         For Each fld In rs.Fields
    
             ' Create new text box control and size to fit data.
             Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
             acDetail, , fld.Name, lngLeft + 1500, lngTop)
             txtNew.SizeToFit
    
             ' Create new label control and size to fit data.
             Set lblNew = CreateReportControl(rpt.Name, acLabel, acDetail, _
             txtNew.Name, fld.Name, lngLeft, lngTop, 1400, txtNew.Height)
             lblNew.SizeToFit
    
             ' Increment top value for next control
             lngTop = lngTop + txtNew.Height + 25
         Next
    
         ' Create datestamp in Footer
         Set lblNew = CreateReportControl(rpt.Name, acLabel, _
         acPageFooter, , Now(), 0, 0)
     
         ' Create page numbering on footer
         Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
         acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", rpt.Width - 1000, 0)
         txtNew.SizeToFit
     
         ' Open new report.
         rpt.DefaultView = 0
         rpt.PopUp = True
         rpt.Modal = True
         DoCmd.OpenReport rpt.Name, acViewPreview
    
         'reset all objects
         rs.Close
         Set rs = Nothing
         Set rpt = Nothing
         Set db = Nothing
     
    End Function
    Thanks again for the help!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 03-31-2017, 08:35 AM
  2. Replies: 1
    Last Post: 10-08-2015, 10:59 AM
  3. Replies: 3
    Last Post: 06-25-2015, 12:22 PM
  4. Create Query Object vs Hard Code
    By sstiebinger in forum Queries
    Replies: 5
    Last Post: 05-13-2015, 08:37 AM
  5. Replies: 7
    Last Post: 01-16-2014, 09:17 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