By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,505 Members | 1,549 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,505 IT Pros & Developers. It's quick & easy.

Change the sort order in a report group from Visual Basic

100+
P: 675
On a Report, can I change any of the fields in the "Sorting and Grouping Dialog" from within Visual Basic??? Changing the OrderBy property of the Report doesn't change anything, nor does adding an "ORDER BY" clause to the RecordSource SQL Statement.

I have a table, named tInventory. It contains a text field called Item. There may be more than one row where Item is duplicated. I use a field called Year to distinguish them on the printed report. Of course there is a Primary Key field which is AutoNum, and therefore each row is unique.

I print a list of Item, with the Year added to the end. I want to list the Items alphabetically, grouped by the first letter of the Item. Originally, I listed the year as decending, as the newer of duplicate Items was the most important. I now want to give the user control of this, as the users are split on which order is best.

My report, would therefore have:
Expand|Select|Wrap|Line Numbers
  1. RecordSource = "SELECT Left([Item],1) AS FirstLetter, " & _
  2.         "[Year], " & _
  3.         "[Item] & "" ("" & [Year] & "")"" AS PrintName" & _
  4.         "FROM tInventory;"
  5.  
The "Sorting and Grouping Dialog is:
___Field/Expression___Sort Order
[{= FirstLetter_________Ascending
___Year_____________Descending
___PrintName________Ascending

The report has the appropriate PageHeader, GroupHeader, & Detail Sections. This all works very well.

Now I want to add an Option chkShowNewestFirst, giving users a choice. How do I change the field in the "Sorting and Grouping Dialog" from 'Decending' to 'Ascending' if so checked?

The above code is a simplification of the actual problem. There are actually several GroupHeader levels, each with user choices. For this simple case, I could add a new field in my SELECT statement as either "[Year] AS SortYear" or "9999-[Year] AS SortYear" before assigning to RecordSource, but that gets very messy very fast. It also slows down the program.
Jun 8 '07 #1
Share this Question
Share on Google+
10 Replies


puppydogbuddy
Expert 100+
P: 1,923
This link shows you step by step how :
http://www.fabalou.com/Access/Reports/sortorder.asp
Jun 8 '07 #2

ADezii
Expert 5K+
P: 8,599
On a Report, can I change any of the fields in the "Sorting and Grouping Dialog" from within Visual Basic??? Changing the OrderBy property of the Report doesn't change anything, nor does adding an "ORDER BY" clause to the RecordSource SQL Statement.

I have a table, named tInventory. It contains a text field called Item. There may be more than one row where Item is duplicated. I use a field called Year to distinguish them on the printed report. Of course there is a Primary Key field which is AutoNum, and therefore each row is unique.

I print a list of Item, with the Year added to the end. I want to list the Items alphabetically, grouped by the first letter of the Item. Originally, I listed the year as decending, as the newer of duplicate Items was the most important. I now want to give the user control of this, as the users are split on which order is best.

My report, would therefore have:
Expand|Select|Wrap|Line Numbers
  1. RecordSource = "SELECT Left([Item],1) AS FirstLetter, " & _
  2.         "[Year], " & _
  3.         "[Item] & "" ("" & [Year] & "")"" AS PrintName" & _
  4.         "FROM tInventory;"
  5.  
The "Sorting and Grouping Dialog is:
___Field/Expression___Sort Order
[{= FirstLetter_________Ascending
___Year_____________Descending
___PrintName________Ascending

The report has the appropriate PageHeader, GroupHeader, & Detail Sections. This all works very well.

Now I want to add an Option chkShowNewestFirst, giving users a choice. How do I change the field in the "Sorting and Grouping Dialog" from 'Decending' to 'Ascending' if so checked?

The above code is a simplification of the actual problem. There are actually several GroupHeader levels, each with user choices. For this simple case, I could add a new field in my SELECT statement as either "[Year] AS SortYear" or "9999-[Year] AS SortYear" before assigning to RecordSource, but that gets very messy very fast. It also slows down the program.
You modify Sorting and Grouping characteristics of any given Report via the GroupLevel Property where each Group Level is identified by an Index as in:
Expand|Select|Wrap|Line Numbers
  1. Reports![rptMovie].GroupLevel(0).KeepTogether = True       '1st Level
  2. Reports![rptMovie].GroupLevel(1).KeepTogether = True       '2nd Level
  3. Reports![rptMovie].GroupLevel(2).KeepTogether = True       '3rd Level
Jun 8 '07 #3

100+
P: 675
Many thanks to both for this simple answer. It completely solves my problem.

The link by 'puppydogbuddy' is almost exactly like my program, except I use a series of toggle buttons and options groups instead of combo boxes (one less mouse click).

The post by 'ADezii' is short & sweet, enough to solve my problem after the amount of research done before posting my question. I had spent many hours in Access Help, Google, and this site searching for an answer. I found no reference to 'GroupLevel' and really felt that this was a feature kept from VB by Access.

Thank you again for your serious consideration to my questions?

OldBirdman
Jun 8 '07 #4

P: 23
Anybody know how to modify to include group level in a dynamic report? There is no report saved in database so you can't go in on the report open sub.
Aug 26 '08 #5

ADezii
Expert 5K+
P: 8,599
Anybody know how to modify to include group level in a dynamic report? There is no report saved in database so you can't go in on the report open sub.
Just subscribing, I'll be back shortly.
Aug 26 '08 #6

ADezii
Expert 5K+
P: 8,599
Anybody know how to modify to include group level in a dynamic report? There is no report saved in database so you can't go in on the report open sub.
  1. How to create a Dynamic Report in VBA
    http://bytes.com/forum/thread696050.html
  2. The following code will create a Group Level on a [PayrollNumber] Field on a Report named rptEmployees. The Report on which the Group Level is to be created must initially be open in Design view.

    Expand|Select|Wrap|Line Numbers
    1. Dim varGroupLevel As Variant
    2. Dim blnCreateHeaderAlso As Boolean
    3. Dim blnCreateFooterAlso As Boolean
    4.  
    5. 'Let's create a Group Header but no Group Footer on [PayrollNumber]
    6. blnCreateHeaderAlso = True
    7. blnCreateFooterAlso = False
    8.  
    9. 'Critical that the Report is Open in Design View
    10. DoCmd.OpenReport "rptEmployees", acViewDesign, , , acHidden
    11.  
    12. 'Create new group level on the [PayrollNumber] Field.
    13. varGroupLevel = CreateGroupLevel("rptEmployees", "PayrollNumber", _
    14.                            blnCreateHeaderAlso, blnCreateFooterAlso)
    15.  
    16. 'Set the Height of the Group Header to 400
    17. Reports!rptEmployees.Section(acGroupLevel0Header).Height = 400
    18.  
    19. 'Save our lovely work
    20. DoCmd.Close acReport, "rptEmployees", acSaveYes
Aug 27 '08 #7

P: 23
Great, that works (my code looks slightly different). Now I'm trying to put labels in the newly created group headers. The labels currently go in the page header and the text boxes are in the acDetail section.

I'm getting an error - either the grouping level doesn't exist or the report creates properly but the labels are nowhere to be found.

Below is my code. Any thoughts? Also, can anyone recommend a good guide to formatting reports? That is basically the last part of my project, I just need the data to look good and have no idea how to resize/move/get everything looking proper.

Expand|Select|Wrap|Line Numbers
  1.   Case 16
  2.     group1 = CreateGroupLevel(rpt.Name, strCbo1, 0, 0)
  3.     group2 = CreateGroupLevel(rpt.Name, strCbo2, 0, 0)
  4.     group3 = CreateGroupLevel(rpt.Name, strCbo3, 1, 1)
  5.     group4 = CreateGroupLevel(rpt.Name, "Measure", 0, 0)
  6.     With rpt
  7.         .GroupLevel(group3).KeepTogether = 1
  8.         .GroupLevel(group4).SortOrder = False
  9.     End With
  10. End Select
  11.  
  12.      'create label on pageheader
  13.     For Each fld In rs.Fields
  14.         CreateReportControl rpt.Name, acLabel, acPageHeader, , fld.Name, 0, 0
  15.         CreateReportControl rpt.Name, acTextBox, acDetail, , fld.Name, 0, 0
  16.  
  17.     Next fld 
I've been trying acGroupLevel2Header and acGroupLevelgroup3header, nothing seems to work
Aug 27 '08 #8

ADezii
Expert 5K+
P: 8,599
Great, that works (my code looks slightly different). Now I'm trying to put labels in the newly created group headers. The labels currently go in the page header and the text boxes are in the acDetail section.

I'm getting an error - either the grouping level doesn't exist or the report creates properly but the labels are nowhere to be found.

Below is my code. Any thoughts? Also, can anyone recommend a good guide to formatting reports? That is basically the last part of my project, I just need the data to look good and have no idea how to resize/move/get everything looking proper.

Expand|Select|Wrap|Line Numbers
  1.   Case 16
  2.     group1 = CreateGroupLevel(rpt.Name, strCbo1, 0, 0)
  3.     group2 = CreateGroupLevel(rpt.Name, strCbo2, 0, 0)
  4.     group3 = CreateGroupLevel(rpt.Name, strCbo3, 1, 1)
  5.     group4 = CreateGroupLevel(rpt.Name, "Measure", 0, 0)
  6.     With rpt
  7.         .GroupLevel(group3).KeepTogether = 1
  8.         .GroupLevel(group4).SortOrder = False
  9.     End With
  10. End Select
  11.  
  12.      'create label on pageheader
  13.     For Each fld In rs.Fields
  14.         CreateReportControl rpt.Name, acLabel, acPageHeader, , fld.Name, 0, 0
  15.         CreateReportControl rpt.Name, acTextBox, acDetail, , fld.Name, 0, 0
  16.  
  17.     Next fld 
I've been trying acGroupLevel2Header and acGroupLevelgroup3header, nothing seems to work
Is the Report Open in Design Mode, this Step is critical?
Aug 27 '08 #9

P: 23
Yes, right before the Select Case statement I have:

Expand|Select|Wrap|Line Numbers
  1.  DoCmd.reportOpen rpt.Name, acViewDesign 
No luck.

If anybody is interested, here is all the nastiness:

Expand|Select|Wrap|Line Numbers
  1. Private Sub FormButton_Click()
  2. Dim strAVP, strBranch, strTC, strDrug, strPay, strTrend, strTime, strCbo1, strCbo2, strCbo3, strSQL As String
  3. Dim rpt As Report
  4. Dim lblNew As Access.Label
  5. Dim txtnew As Access.TextBox
  6. Dim db As DAO.Database
  7. Dim rs As DAO.Recordset
  8. Dim fld As DAO.Field
  9. Dim rptReport As Access.Report
  10. Dim strCaption As String
  11. Dim varGroupLevel As Variant
  12. Dim WhatToShow As String
  13.  
  14. 'Loop to Grab AVPList Box entries.  The loops are identical for all List Boxes.
  15. For Each varItem In Me.AvpList.ItemsSelected
  16.         strAVP = strAVP & ",'" & Me.AvpList.ItemData(varItem) _
  17.         & "'"
  18.     Next varItem
  19.  
  20.     'If user does not make a selection, select all choices in list box.
  21.     If Len(strAVP) = 0 Then
  22.         strAVP = "Like '*'"
  23.     Else
  24.         strAVP = Right(strAVP, Len(strAVP) - 1)
  25.         strAVP = "IN(" & strAVP & ")"
  26. End If
  27.  
  28. 'Loop to grab Branch List Box entries
  29. For Each varItem In Me.BranchList.ItemsSelected
  30.         strBranch = strBranch & ",'" & Me.BranchList.ItemData(varItem) _
  31.         & "'"
  32.     Next varItem
  33.  
  34.     If Len(strBranch) = 0 Then
  35.         strBranch = "Like '*'"
  36.     Else
  37.         strBranch = Right(strBranch, Len(strBranch) - 1)
  38.         strBranch = "IN(" & strBranch & ")"
  39. End If
  40.  
  41. 'Loop to grab TCList Box entries
  42. For Each varItem In Me.TCList.ItemsSelected
  43.         strTC = strTC & ",'" & Me.TCList.ItemData(varItem) _
  44.         & "'"
  45.     Next varItem
  46.  
  47.     If Len(strTC) = 0 Then
  48.         strTC = "Like '*'"
  49.     Else
  50.         strTC = Right(strTC, Len(strTC) - 1)
  51.         strTC = "IN(" & strTC & ")"
  52. End If
  53.  
  54. 'Loop to grab DrugList Box entries
  55. For Each varItem In Me.DrugList.ItemsSelected
  56.         strDrug = strDrug & ",'" & Me.DrugList.ItemData(varItem) _
  57.         & "'"
  58.     Next varItem
  59.  
  60.     If Len(strDrug) = 0 Then
  61.         strDrug = "Like '*'"
  62.     Else
  63.         strDrug = Right(strDrug, Len(strDrug) - 1)
  64.         strDrug = "IN(" & strDrug & ")"
  65. End If
  66.  
  67.  
  68. 'loop for PayList Box entries
  69. For Each varItem In Me.PayList.ItemsSelected
  70.         strPay = strPay & ",'" & Me.PayList.ItemData(varItem) _
  71.         & "'"
  72.     Next varItem
  73.  
  74.     If Len(strPay) = 0 Then
  75.         strPay = "Like '*'"
  76.     Else
  77.         strPay = Right(strPay, Len(strPay) - 1)
  78.         strPay = "IN(" & strPay & ")"
  79. End If
  80.  
  81. 'loop for TrendList Box entries
  82. For Each varItem In Me.TrendList.ItemsSelected
  83.         strTrend = strTrend & ",'" & Me.TrendList.ItemData(varItem) _
  84.         & "'"
  85.     Next varItem
  86.  
  87.     If Len(strTrend) = 0 Then
  88.         strTrend = "Like '*'"
  89.     Else
  90.         strTrend = Right(strTrend, Len(strTrend) - 1)
  91.         strTrend = "IN(" & strTrend & ")"
  92. End If
  93.  
  94.  
  95. For Each varItem In Me.PPList.ItemsSelected
  96.         strTime = strTime & ", tblAvpBrDg.[" & Me.PPList.ItemData(varItem) & "]"
  97.  
  98.         Next varItem
  99.         If Len(strTime) = 0 Then
  100.             MsgBox "You must select the dates you wish to see."
  101.             Exit Sub
  102.         Else
  103.             strTime = Right(strTime, Len(strTime) - 2)
  104.         End If
  105.  
  106. 'Define strings to be used for sorting purposes later
  107.      strCbo1 = Me.cboSortOrder1.Value
  108.      strCbo2 = Me.cboSortOrder2.Value
  109.      strCbo3 = Me.cboSortOrder3.Value
  110.      strCbo4 = Me.cboSortOrder4.Value
  111.      strCbo5 = Me.cboSortOrder5.Value
  112.  
  113. If strCbo1 = "None" Then
  114.     S1 = 0
  115.     Else
  116.     S1 = 1
  117. End If
  118.  
  119. If strCbo2 = "None" Then
  120.     S2 = 0
  121.     Else
  122.     S2 = 5
  123. End If
  124.  
  125. If strCbo3 = "None" Then
  126.     S3 = 0
  127.     Else
  128.     S3 = 10
  129. End If
  130.  
  131. If strCbo4 = "None" Or IsNull(strCbo4) = True Then
  132.     S4 = 0
  133.     Else
  134.     S4 = 20
  135. End If
  136.  
  137. If strCbo5 = "None" Or IsNull(strCbo5) = True Then
  138.     S5 = 0
  139.     Else
  140.     S5 = 40
  141. End If
  142.  
  143. WhatToShow = S1 + S2 + S3 + S4 + S5
  144. WhatToGroup = S1 + S2 + S3
  145.     Select Case WhatToShow
  146.         Case 0
  147.  
  148.                     strSQL = "SELECT tblAvpBrDg.[AVP], tblAvpBrDg.[Branch], tblAvpBrDg.[THERAPY_CLASS], tblAvpBrDg.[DRUG_GROUP], tblAvpBrDg.[PayorCode], tblAvpBrDg.[Measure], " & strTime & " FROM tblAvpBrDg " & _
  149.                             "Where AVP " & strAVP & " AND " & _
  150.                             "Branch " & strBranch & " AND " & "THERAPY_CLASS " & strTC & _
  151.                             " AND " & "DRUG_GROUP " & strDrug & " AND " & "PayorCode " & strPay & _
  152.                             " AND " & "Measure " & strTrend & ";"
  153.  
  154.         Case 1
  155.                     strSQL = "SELECT tblAvpBrDg.[" & strCbo1 & "], tblAvpBrDg.[Measure], " & strTime & " FROM tblAvpBrDg " & _
  156.                             "Where AVP " & strAVP & " AND " & _
  157.                             "Branch " & strBranch & " AND " & "THERAPY_CLASS " & strTC & _
  158.                             " AND " & "DRUG_GROUP " & strDrug & " AND " & "PayorCode " & strPay & _
  159.                             " AND " & "Measure " & strTrend & ";"
  160.  
  161.         Case 6
  162.              strSQL = "SELECT tblAvpBrDg.[" & strCbo1 & "], tblAvpBrDg.[" & strCbo2 & "], tblAvpBrDg.[Measure], " & strTime & " FROM tblAvpBrDg " & _
  163.                             "Where AVP " & strAVP & " AND " & _
  164.                             "Branch " & strBranch & " AND " & "THERAPY_CLASS " & strTC & _
  165.                             " AND " & "DRUG_GROUP " & strDrug & " AND " & "PayorCode " & strPay & _
  166.                             " AND " & "Measure " & strTrend & ";"
  167.         Case 16
  168.             strSQL = "SELECT tblAvpBrDg.[" & strCbo1 & "], tblAvpBrDg.[" & strCbo2 & "], tblAvpBrDg.[" & strCbo3 & "], tblAvpBrDg.[Measure], " & strTime & " FROM tblAvpBrDg " & _
  169.                             "Where AVP " & strAVP & " AND " & _
  170.                             "Branch " & strBranch & " AND " & "THERAPY_CLASS " & strTC & _
  171.                             " AND " & "DRUG_GROUP " & strDrug & " AND " & "PayorCode " & strPay & _
  172.                             " AND " & "Measure " & strTrend & ";"
  173.  
  174.         Case 36
  175.             strSQL = "SELECT tblAvpBrDg.[" & strCbo1 & "], tblAvpBrDg.[" & strCbo2 & "], tblAvpBrDg.[" & strCbo3 & "], tblAvpBrDg.[" & strCbo4 & "], tblAvpBrDg.[Measure], " & strTime & " FROM tblAvpBrDg " & _
  176.                             "Where AVP " & strAVP & " AND " & _
  177.                             "Branch " & strBranch & " AND " & "THERAPY_CLASS " & strTC & _
  178.                             " AND " & "DRUG_GROUP " & strDrug & " AND " & "PayorCode " & strPay & _
  179.                             " AND " & "Measure " & strTrend & ";"
  180.  
  181.         Case 76
  182.             strSQL = "SELECT tblAvpBrDg.[" & strCbo1 & "], tblAvpBrDg.[" & strCbo2 & "], tblAvpBrDg.[" & strCbo3 & "], tblAvpBrDg.[" & strCbo4 & "], tblAvpBrDg.[" & strCbo5 & "], tblAvpBrDg.[Measure], " & strTime & " FROM tblAvpBrDg " & _
  183.                             "Where AVP " & strAVP & " AND " & _
  184.                             "Branch " & strBranch & " AND " & "THERAPY_CLASS " & strTC & _
  185.                             " AND " & "DRUG_GROUP " & strDrug & " AND " & "PayorCode " & strPay & _
  186.                             " AND " & "Measure " & strTrend & ";"
  187.  
  188.     End Select
  189.  
  190. MsgBox strSQL
  191.  
  192.  
  193.      'set the title
  194.      Title = "Title for the Report"
  195.  
  196.      ' initialise position variables
  197.      lngleft = 0
  198.      lngtop = 0
  199.  
  200.      'Create the report
  201.      Set rpt = CreateReport
  202.  
  203.     ' set properties of the Report
  204.      With rpt
  205.         .Width = 8500
  206.         .RecordSource = strSQL
  207.         .Caption = Title
  208.         .Section(acDetail).Height = 100
  209.  
  210.      End With
  211.  
  212.      ' Open SQL query as a recordset
  213.      Set db = CurrentDb
  214.      Set rs = db.OpenRecordset(strSQL)
  215.  
  216.      DoCmd.OpenReport rpt.Name, acViewDesign
  217.  
  218. Select Case WhatToGroup
  219.  
  220.     Case 0
  221.         group1 = CreateGroupLevel(rpt.Name, "AVP", 0, 0)
  222.         group2 = CreateGroupLevel(rpt.Name, "Branch", 0, 0)
  223.         group3 = CreateGroupLevel(rpt.Name, "THERAPY_CLASS", 0, 0)
  224.         group4 = CreateGroupLevel(rpt.Name, "DRUG_GROUP", 0, 0)
  225.         group5 = CreateGroupLevel(rpt.Name, "PayorCode", 1, 1)
  226.         group6 = CreateGroupLevel(rpt.Name, "Measure", 0, 0)
  227.         With rpt
  228.             .GroupLevel(group1).KeepTogether = 1
  229.             .GroupLevel(group6).SortOrder = False
  230.         End With
  231.  
  232.     Case 1
  233.         group1 = CreateGroupLevel(rpt.Name, strCbo1, 1, 1)
  234.         group2 = CreateGroupLevel(rpt.Name, "Measure", 0, 0)
  235.         With rpt
  236.             .GroupLevel(group1).KeepTogether = 1
  237.             .GroupLevel(group2).SortOrder = False
  238.         End With
  239.  
  240.      Case 6
  241.         group1 = CreateGroupLevel(rpt.Name, strCbo1, 0, 0)
  242.         group2 = CreateGroupLevel(rpt.Name, strCbo2, 1, 1)
  243.         group3 = CreateGroupLevel(rpt.Name, "Measure", 0, 0)
  244.         With rpt
  245.             .GroupLevel(group2).KeepTogether = 1
  246.             .GroupLevel(group3).SortOrder = False
  247.         End With
  248.  
  249.     Case 16
  250.     group1 = CreateGroupLevel(rpt.Name, strCbo1, 0, 0)
  251.     group2 = CreateGroupLevel(rpt.Name, strCbo2, 0, 0)
  252.     group3 = CreateGroupLevel(rpt.Name, strCbo3, 1, 1)
  253.     group4 = CreateGroupLevel(rpt.Name, "Measure", 0, 0)
  254.     With rpt
  255.         .GroupLevel(group3).KeepTogether = 1
  256.         .GroupLevel(group4).SortOrder = False
  257.     End With
  258. End Select
  259.  
  260.      'create label on pageheader
  261.     For Each fld In rs.Fields
  262.         CreateReportControl rpt.Name, acLabel, acGroupLevelgroup3Header, , fld.Name, 0, 0
  263.         CreateReportControl rpt.Name, acTextBox, acDetail, , fld.Name, 0, 0
  264.  
  265.     Next fld
  266.  
  267.  
  268.     'arrange fields
  269.     For Each Ctrl In rpt.Controls
  270.  
  271.         Select Case Ctrl.ControlType
  272.           Case acTextBox
  273.                 If TextCol Then
  274.                     Ctrl.Name = Ctrl.ControlSource
  275.                     Ctrl.Move TextWidth, 0, Ctrl.Width, Ctrl.Height
  276.                     TextWidth = TextWidth + Ctrl.Width
  277.                 Else
  278.                     Ctrl.Name = Ctrl.ControlSource
  279.                     Ctrl.Move TextWidth, 0, Ctrl.Width, Ctrl.Height
  280.                     TextWidth = TextWidth + Ctrl.Width
  281.                 End If
  282.                 TextCol = False
  283.             Case acLabel
  284.                 If FirstCol Then
  285.                     Ctrl.Name = "lbl" & Ctrl.Caption
  286.                     Ctrl.Move ColWidth, 0, Ctrl.Width, Ctrl.Height
  287.  
  288.                 Else
  289.                     Ctrl.Name = "lbl" & Ctrl.Caption
  290.                     Ctrl.Move TextWidth, 0, Ctrl.Width, Ctrl.Height
  291.  
  292.                 End If
  293.                 Ctrl.FontSize = 8
  294.                 Ctrl.FontWeight = 700
  295.                 FirstCol = False
  296.             Case Else
  297.  
  298.         End Select
  299.  
  300.     Next Ctrl
  301.  
  302.      ' Create datestamp in Footer
  303.      Set lblNew = CreateReportControl(rpt.Name, acLabel, _
  304.      acPageFooter, , Now(), 0, 0)
  305.  
  306.      ' Create page numbering on footer
  307.      Set txtnew = CreateReportControl(rpt.Name, acTextBox, _
  308.      acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", rpt.Width - 1000, 0)
  309.      txtnew.SizeToFit
  310.  
  311.     DoCmd.OpenReport rpt.Name, acViewPreview
  312.  
  313.  
  314.      'reset all objects
  315.      rs.Close
  316.      Set rs = Nothing
  317.      Set rpt = Nothing
  318.      Set db = Nothing
  319. End Sub 
I'm very new to access and vba - I've pieced all this together from sites like this and tons of trial and error. I just need to figure out all the report formatting tricks.
Aug 27 '08 #10

ADezii
Expert 5K+
P: 8,599
Just be referencing Code Line #262,
Expand|Select|Wrap|Line Numbers
  1. CreateReportControl rpt.Name, acLabel, acGroupLevelgroup3Header, , fld.Name, 0, 0
it appears as if you are using the incorrect Intrinsic Constant for the Group Level, try:
Expand|Select|Wrap|Line Numbers
  1. CreateReportControl rpt.Name, acLabel, acGroupLevel3Header, , fld.Name, 0, 0
Aug 28 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.