Results 1 to 10 of 10
  1. #1
    Norm is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2017
    Posts
    9

    Create ms access report from a recordset report in VBA with field names accross the report.

    Hi guys,

    Just need some vba help with creating a report in vba. Spenttoo many hours on this and now time to get some help please.
    All I require is the field names listed once(on every pageif possible) across the report as sub headings of the report with all the dataunderneath.
    At the moment I’m getting a repeat of the field names and a separatepage for each record and field name.
    Just for interest what I'm trying to do is for a user to select rows from a form and dynamically produce a report.
    Getting this data is no problem.


    Eg This is what I would like........

    Report name

    Field1.name1 field2.name field3.name field4.name

    Data data data data
    Data data data data
    Data data data data
    Etc etc

    Current code below. Kept issimple without headers etc to make it easier for you to read.


    Appreciate some help.
    Regards norm


    Dim strSQL As String,db As dao.Database, rs As dao.Recordset, fld As dao.Field, txtNew AsAccess.TextBox
    Dim lblNew AsAccess.Label, rpt As Report, lngTop As Long, lngLeft As Long, title As String
    lngLeft = 0
    lngTop = 0
    strSQL ="select * from list"
    Set rpt =CreateReport
    With rpt
    .Width = 8500
    .RecordSource= strSQL
    .Caption =title
    End With

    Set db =CurrentDb
    Set rs =db.OpenRecordset(strSQL)
    '--------------------------------------------------------------------------------------------------
    Set lblNew =CreateReportControl(rpt.Name, acLabel, acPageHeader, , "Title", 0, 0)
    lblNew.FontBold =True
    lblNew.FontSize =12

    For Each fld Inrs.Fields
    Set txtNew =CreateReportControl(rpt.Name, acTextBox, _
    acDetail, ,fld.Name, lngLeft + 1500, lngTop)
    txtNew.SizeToFit

    Set lblNew =CreateReportControl(rpt.Name, acLabel, acDetail, _
    txtNew.Name,fld.Name, lngLeft, lngTop, 1400, txtNew.Height)
    lblNew.SizeToFit

    lngTop =lngTop + txtNew.Height + 25
    Next
    '-----------------------------------------------------------------------------------------------------
    DoCmd.OpenReportrpt.Name, acViewPreview
    rs.Close
    Set rs = Nothing
    Set rpt = Nothing
    Set db = Nothing
    Last edited by Norm; 06-24-2017 at 08:38 PM. Reason: Needed to format it better

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    Norm is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2017
    Posts
    9
    Thanks but not what I'm looking for, Not interested in combo boxes and filters

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Hmmm. I was working from your post
    dynamically produce a report
    .

    Perhaps you could post a jpg of your current report with the repetition.
    It might also be helpful for readers if you posted a jpg of the form and the selected row(s) for the report.

    Good luck.

  5. #5
    Norm is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2017
    Posts
    9
    Thanks again for your reply. But all I need is some help with the format and syntax (createreportcontrol.................)
    for having the fields across the page and the records underneath the field names as displayed.
    At the moment I'm getting one page one record. I'm obtaining the data from a temp table created.
    so obtaining the data is no problem.
    Regards

  6. #6
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    This exmaple does what you want:

    Dynamic Report based on a Crosstab query



    it reads the field names to set the column headers.

  7. #7
    Norm is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2017
    Posts
    9
    Thanks very much HiTechCoach for your help. Saves also a lot of vba code and not requiring Createreport code.
    Will only need a little time to understand it as its new to me but very clever.
    Kind regards
    Norm

  8. #8
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Quote Originally Posted by Norm View Post
    Thanks very much HiTechCoach for your help. Saves also a lot of vba code and not requiring Createreport code.
    Will only need a little time to understand it as its new to me but very clever.
    Kind regards
    Norm
    Norm,

    Glad you found it useful.

    I use this as a template a lot. All I do to use it is edit the record source to the new query I want to use. There are a few lines of code that needed to be updated to the new query and any parameters. I have yet to have to make any real code changes in all the times I have added it into a client's databases.

  9. #9
    Norm is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2017
    Posts
    9
    Boyd thanks again but just one last question please.
    Oh all is working great by the way.
    Just one last question please

    If you use (tried it on your db as well)
    Right click mouseReportview
    Or openreport acviewreport
    No data including field headings are notdisplayed on report

    --------------------------------------------------------------------------------

    Right click mousePrintpreview
    Or openreportacviewpreview
    All data and headings are displayed.

    Just interested.
    Thanks again Norm


  10. #10
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    The code only works Print Preview mode.

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

Similar Threads

  1. Replies: 1
    Last Post: 06-20-2017, 09:00 AM
  2. Replies: 7
    Last Post: 01-19-2016, 05:28 PM
  3. Replies: 5
    Last Post: 01-16-2016, 10:44 AM
  4. Create report template from existing report
    By jmac1981 in forum Access
    Replies: 8
    Last Post: 11-04-2015, 03:43 PM
  5. Create a recordset for a report
    By vjboaz in forum Programming
    Replies: 0
    Last Post: 11-18-2008, 03:28 PM

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