change format for report using VBA 2

[maswien]

I'm new in access vba and report. I want write a report from following resultset:

  dept  div  unit  appl actual
  ----- ---- ----  ---- -------
  T&Y   IT   oper  SAP  10
  T&Y   IT   oper  DST  20
  T&Y   IT   calu  PPP  30
  T&Y   IT   calu  PPP  30
  T&Y   AC   plat  SAP  20
  T&Y   AC   plat  SAP  40
  T&Y   AC   supp  SAP  80
  T&Y   AC   supp  PPP  30

The output:

  dept  div  unit  appl actual
  ----- ---- ----  ---- -------
  T&Y   IT   oper  SAP  10
                   DST  20
             calu  PPP  30
                   PPD  30
        AC   plat  SAP  20
                   SAT  40
             supp  SAP  80
                   PPP  30

Can anybody tell me how to write the VBA code to do that?
Thanks

 

[PCLewis]

If it's a report you are making then you can use the group headers feature to group on dept, div, unit and in the headers place the text control bound to the field. But that qould result in:

  dept  div  unit  appl actual
  ----- ---- ----  ---- -------
  T&Y   
        IT                
             oper  
                   SAP  10
                   DST  20
             calu         
                   PPP  30
                   PPD  30
        AC                
             plat         
                   SAP  20
                   SAT  40
             supp         
                   SAP  80
                   PPP  30

If that is not satisfactory then you move the fields form the group headers into the detail section and remove the group headers. Then you have to write some code like the following:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  Static lastID As String
  Static lastSI3 As String
  Static lastSI6 As String
  If lastID = Me.Detail.Controls("txtID").Value Then
    Me.Detail.Controls("txtID").Visible = False
  Else
    Me.Detail.Controls("txtID").Visible = True
  End If
  
  If lastSI3 = Me.Detail.Controls("txtSubInfo3").Value Then
    Me.Detail.Controls("txtSubInfo3").Visible = False
  Else
    Me.Detail.Controls("txtSubInfo3").Visible = True
  End If
  
  If lastSI6 = Me.Detail.Controls("txtSubInfo6").Value Then
    Me.Detail.Controls("txtSubInfo6").Visible = False
  Else
    Me.Detail.Controls("txtSubInfo6").Visible = True
  End If
  
  lastID = Nz(Me.Detail.Controls("txtID").Value, "")
  lastSI3 = Nz(Me.Detail.Controls("txtSubInfo3").Value, "")
  lastSI6 = Nz(Me.Detail.Controls("txtSubInfo6").Value, "")
  
End Sub

Notice the Static keyword here? This allows your On_Format function to keep track of the previous report line's data so that you can decide whether to display the field or not. Using this technique, your report will come out how you want.

 

[maswien]

PCLewis, Thank you very much! it works like charm.

Another question, if I want insert page break for each department, how to do that?

 

[dhookom]

Why write any code? You should be able to set the text box "Hide Duplicates" property to Yes.

If you choose to use a department header and/or footer, you can set the Force New page property to do what you want.

Also, I think most of the previously posted code can be replaced by the line:
Me.MoveLayout = False
In various header sections.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PCLewis]

I never used the Hide Duplicates much for this sort of thing but I just tried it and yes it's pretty darn good

I guess the only thing in my test data was that I had duplicate 3rd tier data but different 2nd tier data for 2 records, so the display was not quite right. The Hide Duplicates hid the 3rd tier data in the 2nd row. If that's what you wanted then you can use the Hide Sulpicates method for sure.

I also never knew of the MoveLayout property. I looked at it and confirmed that if you create the report like I suggested, using the group headers, then in each group header placed dhookum's suggestion of Me.MoveLayout=False, you will get the correct output as well.

I'm not sure when MoveLayout was added or if it's always been there in Access First time I've used it and I can only guess at how many hours of programming that would have saved me over the years That's the main purpose of helping in forums for me; to learn new things...

 

[dhookom]

That's the main purpose of helping in forums for me; to learn new things...

That's how I learned a lot of what I now know.

To see a unique use of the Me.MoveLayout check out the week-at-a-glance calendar reports at:

http://www.invisibleinc.com/divFiles.cfm?divDivID=4

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PCLewis]

That is elegance and style personified. I've got to take a fresh look at Access reports and try and be a little more creative...

 

[maswien]

excellent! I can learn a lot in this forum! Both of you deserve a star.