Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old May 15th, 2005, 09:14 AM
Friend of Wrox
 
Join Date: Aug 2004
Location: , , .
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default Report - programmatically creating

Hi experts!
I have a report programm.created which i would like to have it
grouped by a field. How do i have it done by code?
Rgds Penta


  #2 (permalink)  
Old May 16th, 2005, 08:43 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Hi,

   I think all you need to do is to click on either the form header, page header, or detail, and then right click and select sorting and grouping. Then select the item you want to group on, and select Keep Together, and select Whole Group, or With First Detail.

HTH


mmcdonal
  #3 (permalink)  
Old May 16th, 2005, 09:23 AM
Friend of Wrox
 
Join Date: Aug 2004
Location: , , .
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Problem is i need the whole report been done by code. Why ? Because i have a
lay-out that wont be filled by normal interactive controls. So when i have
the report programmatically created the controls can be placed exactly where
needed with the distance necessary which are bellow the limits we will have
with design view. So far so good. Now the problem is to have a chosen field
in the Report Source to be coded as GroupOn. Why ? I dont want this field
(employee name) repeated every line on the report.
Thanks for ur care,
rgds P

  #4 (permalink)  
Old May 16th, 2005, 09:39 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: , , United Kingdom.
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Penta,

You can use the application command CreateGroupLevel

DoCmd.OpenReport "MyReport", acViewDesign
Application.CreateGroupLevel "MyReport", "GroupName", True, True
DoCmd.OpenReport "MyReport", acViewPreview

NOTE - You have to open the report first in design view, to add the group, then you can open normally.

Any Good?

Jon


  #5 (permalink)  
Old May 16th, 2005, 09:50 AM
Friend of Wrox
 
Join Date: Aug 2004
Location: , , .
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Jon,
I've tried the command u mention. My problem is i dont know how to programmatically accomplish the transfer of the grouping on field from the detail section to the GroupHeader section so it wont be repeated every line on the report.
rgds p


  #6 (permalink)  
Old May 16th, 2005, 09:52 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Move it in the report design view. If it can group on that field, you can move it from the detail section to the group header section with no ill effects. I do this all the time.


mmcdonal
  #7 (permalink)  
Old May 16th, 2005, 12:06 PM
Friend of Wrox
 
Join Date: Aug 2004
Location: , , .
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I must be doing something wrong. BTW i want it done by code and not by design
view. I've tried again the CreateGroupLevel command and when the report is
previewed the field is grouped but the value of the field is repeated on
every line. If i have the report opened in design view i can cut the field
from the detail section and paste it to the groupheader resulting on the
target i'm aiming. But i need this done by CODE !
Any help will be welcome,
P

  #8 (permalink)  
Old May 16th, 2005, 02:20 PM
Friend of Wrox
 
Join Date: Aug 2004
Location: , , .
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Should i after running the CreateGroupLevel run a command CreateReportControl
so the field would be created on the GroupLevel ? If that would work i could next delete on the detail section the field grouped on the header . But till now i could not have it working.
rgds P.

  #9 (permalink)  
Old May 17th, 2005, 02:00 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: , , United Kingdom.
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Penta,

That is exactly what you need to do. create the group, add the control to the group. Don't add the control to the detail atall.

    'Create Empty report based on normal
    Application.CreateReport CurrentProject.Path & "\" & CurrentProject.Name, "Normal"

    DoCmd.RunCommand acCmdSave 'Save a temp copy
    strRptName = Screen.ActiveReport.Name 'Get report name

    Screen.ActiveReport.RecordSource = "tbl_Clients" 'Set recordsource
    'Create Group
    Application.CreateGroupLevel strRptName, "Grouping Field", True, True
    'Create Group Control
    Application.CreateReportControl strRptName, acTextBox, acGroupLevel1Header, , , 200, 200, 2000, 200
    Screen.ActiveReport.Controls(0).ControlSource = "Client Name"


Good Luck

  #10 (permalink)  
Old May 17th, 2005, 11:59 AM
Friend of Wrox
 
Join Date: Aug 2004
Location: , , .
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Jon,
I've tried to code using ur way but i've been no lucky.
Here is the code i'm using with no success on grouping on the first field.
As a matter of fact when i try using the specific command CreateGroupLevel...
it goes to error trap. The way bellow the code runs the report but with no
grouping on the first field. Would u suggest any alteration so it runs
a report the way it should ?

'Create a report with a RecordSource assignment
Set rpt1 = CreateReport
rpt1.RecordSource = "SELECT * FROM ReportSource Query"
rpt1.PageHeaderSection.Height = 300 'tentar mudar altura do cabeçalho
rpt1.Section("Detail").Height = 265 '365

'Add page column label and text box for Código
Set txt1 = CreateReportControl(rpt1.Name, acTextBox, _
    acDetail, , "Código")
txt1.Name = "txtCódigo"
txt1.Height = 265 'tentar mudar altura da text box
Set lbl1 = CreateReportControl(rpt1.Name, acLabel, _
    acPageHeader)
lbl1.Name = "lblCódigo"
lbl1.Caption = "Código"
lbl1.Width = txt1.Width
lbl1.Height = 365 'txt1.Height
lbl1.FontBold = True

'Add page column label and text box for EVENTO
'Set the width of this column at 2700 twips, which is
'different than the default setting of 1440 twips.
'wid1 = txt1.Width
Set txt1 = CreateReportControl(rpt1.Name, acTextBox, _
    acDetail, , "EVENTO", constLeft)
txt1.Name = "txtDia"
txt1.Width = 1550 ' 1440
Set lbl1 = CreateReportControl(rpt1.Name, acLabel, _
    acPageHeader, , , constLeft)
lbl1.Name = "lblDia"
lbl1.Caption = "Dia"
lbl1.Width = 2700
lbl1.Height = txt1.Height
lbl1.FontBold = True
...
etc.
If u need the complete code please ask me.
Thks u so much for ur care,
rgds P





Similar Threads
Thread Thread Starter Forum Replies Last Post
creating multiple instances programmatically dcher BOOK: Access 2007 VBA Programmer's Reference ISBN: 978-0-470-04703-3 4 December 28th, 2007 02:21 PM
creating code programmatically mumphis Excel VBA 0 September 2nd, 2005 09:29 AM
Creating a table programmatically Bob Burns ASP.NET 1.0 and 1.1 Basics 2 July 15th, 2004 11:44 AM
Creating report programmatically liliarum BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 1 July 6th, 2004 09:34 AM
Creating Tables Programmatically stu9820 ASP.NET 1.0 and 1.1 Basics 10 October 22nd, 2003 04:25 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.