Solved

how do I set the number of columns in Access 2007 Report via vba

Posted on 2012-12-28
21
351 Views
Last Modified: 2013-02-26
I need to determine the number of columns to be used for a report, the number of columns will vary between Managers.  I am attempting to set the column widths, and number of columns to appear on a report (Org Chart) - to prevent multiple pages.

Is there a way to determine the number of columns needed and via code set the number of columns needed and the column width?  Some of my mangers have 9 columns and some may have as many as 11 and I would like to fit it on to one page (11x17 - tabloid).  Also set the margins.

K
0
Comment
Question by:Karen Schaefer
  • 11
  • 6
  • 4
21 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 38727984
When you say "columns" are you referring to the "Fields", or are you speaking about the "Columns" setting in the report Page Layout?

If you are referring to "fields", then this is not easy.
When you create a report, the columns are hardcoded in the recordsource.

You can create "dynamic" reports, but this gets messy and complex.
Here is an example:
http://support.microsoft.com/kb/328320


If this for the "Report Columns", then this may be even more complex to do *everything* you are asking for...

I am sure it is certainly theoretically possible, but it may require a significant investment it time to get all of your requirements working.

So let's see what other experts post...

Jeff
0
 

Author Comment

by:Karen Schaefer
ID: 38727990
I am talking about Columns from Page Layout on the Report.

I am still attempting to get the necessary columns to print out on 1 page.

K
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38728005
Karen,

I admire your incorrigibility with this project...
;-)

I know I sound like a broken record, ...but are you absolutely sure this is the only way to get the Org chart you need...?

Jeff
0
 

Author Comment

by:Karen Schaefer
ID: 38728008
How would to get the data from an database into an ORG chart that must have the layout that I have showed you?
  The user is current using just an Excel sheet and is manually updating it - My database will cut the time to create it from hour to just seconds.

Karen
0
 

Author Comment

by:Karen Schaefer
ID: 38728010
I thought about using VISIO - but the Visio chart is limiting.

K
0
 

Author Comment

by:Karen Schaefer
ID: 38728184
What do you know about Report.PrtMip Property?

I found this code on the online help of MS.  Note I modified the code to include another variable to determine the number of columns needed.  The original code did not include the integer.

Private Type str_PRTMIP
    strRGB As String * 28
End Type

Private Type type_PRTMIP
    xLeftMargin As Long
    yTopMargin As Long
    xRightMargin As Long
    yBotMargin As Long
    fDataOnly As Long
    xWidth As Long
    yHeight As Long
    fDefaultSize As Long
    cxColumns As Long
    yColumnSpacing As Long
    xRowSpacing As Long
    rItemLayout As Long
    fFastPrint As Long
    fDatasheet As Long
End Type

Public Sub PrtMipCols(ByVal strName As String, nCT as integer)

    Dim PrtMipString As str_PRTMIP
    Dim PM As type_PRTMIP
    Dim rpt As Report
    Const PM_HORIZONTALCOLS = 1953
    Const PM_VERTICALCOLS = 1954
    
    ' Open the report.
    DoCmd.OpenReport strName, acDesign
    Set rpt = Reports(strName)
    PrtMipString.strRGB = rpt.PrtMip
    LSet PM = PrtMipString
    
    ' Create two columns.
    PM.cxColumns = 2
    
    ' Set 0.25 inch between rows.
    PM.xRowSpacing = 0.25 * 1440
    
    ' Set 0.5 inch between columns.
    PM.yColumnSpacing = 0.5 * 1440
    PM.rItemLayout = PM_HORIZONTALCOLS
    
    ' Update property.
    LSet PrtMipString = PM
    rpt.PrtMip = PrtMipString.strRGB
    
    Set rpt = Nothing
    
End Sub 

Open in new window


Here is how I am calling the code - I just want to pass a variable for the column numbers for each time the report is generated - need help with syntax?  Note nct is set as integer.
 
            If QueryExists("qryOrgChartData") = True Then
                Set qdf = curDB.QueryDefs("qryOrgChartData")
                qdf.SQL = strsql
                qdf.Close
              End If
            strsql = "Select Count(Mgr_Org) as ColCt from qryOrgChartData"
            Set rs = curDB.OpenRecordset(strsql)
            nct = rs.Fields("colct").value
           rs.Close
           
           PrtMipCols("rptOrgChart_template", nct)
            DoCmd.OutputTo acOutputReport, "rptOrgChart_template", acFormatPDF, nrptName, True, , 0, acExportQualityScreen

Open in new window

K
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38728251
Only that it was used for somethings *before* the Printer object was fully exposed in office 2003.
0
 

Author Comment

by:Karen Schaefer
ID: 38728256
ok what does that mean for what I want to do?

K
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38728276
Not entirely sure.

I'll see if I can raise JimD...
0
 

Author Comment

by:Karen Schaefer
ID: 38728323
Need help with syntax on setting the variables and then calling them.
        PrtMipCols (stdocname, nct)
It currently does not like my syntax.

Public Sub PrtMipCols(ByVal strName As String, nCT As Integer)

            If QueryExists("qryOrgChartData") = True Then
                Set qdf = curDB.QueryDefs("qryOrgChartData")
                qdf.SQL = strsql
                qdf.Close
              End If
           rs.Close
            strsql = "SELECT Count(A.MgrOrg) AS CountOfMgrOrg" & _
                        " FROM" & _
                        " (SELECT MgrOrg" & _
                        " FROM qryOrgChartData" & _
                        " GROUP BY MgrOrg) as A"
                        
            Set rs = curDB.OpenRecordset(strsql)
            nCT = rs.Fields("CountOfMgrOrg").value
            Debug.Print nCT
            
           rs.Close
           Dim stdocname As String
           stdocname = "rptOrgChart_template"
        PrtMipCols (stdocname, nct)
            DoCmd.OutputTo acOutputReport, "rptOrgChart_template", acFormatPDF, nrptName, True, , 0, acExportQualityScreen

Open in new window

0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:Karen Schaefer
ID: 38728378
Well I am getting closer - I am able to change the number of columns displayed and change the column width on demand, except now I am having an issue with the columns breaking the correct place.  

Do you know the syntax to add the column break on the correct control, in my case on the grouping of the MgrOrg - should all be in separate columns on the report.

Private Type str_PRTMIP
    strRGB As String * 28
End Type

Private Type type_PRTMIP
    xLeftMargin As Long
    yTopMargin As Long
    xRightMargin As Long
    yBotMargin As Long
    fDataOnly As Long
    xWidth As Long
    yHeight As Long
    fDefaultSize As Long
    cxColumns As Long
    yColumnSpacing As Long
    xRowSpacing As Long
    rItemLayout As Long
    fFastPrint As Long
    fDatasheet As Long
End Type

Public Sub PrtMipCols(ByVal strName As String)

    Dim PrtMipString As str_PRTMIP
    Dim PM As type_PRTMIP
    Dim rpt As Report
    Const PM_HORIZONTALCOLS = 1953
    Const PM_VERTICALCOLS = 1954
    
    ' Open the report.
    DoCmd.OpenReport strName, acDesign
    Set rpt = Reports(strName)
    PrtMipString.strRGB = rpt.PrtMip
    LSet PM = PrtMipString
    
    ' Create two columns.
    PM.cxColumns = nCT
    If nCT < 11 Then
        PM.xWidth = 1.75 * 1440
    Else
        PM.xWidth = 1.65 * 1440
    End If
    ' Set 0.25 inch between rows.
   ' PM.xRowSpacing = 0.25 * 1440
    
    ' Set 0.5 inch between columns.
   ' PM.yColumnSpacing = 0.5 * 1440
    PM.rItemLayout = PM_HORIZONTALCOLS
    
    ' Update property.
    LSet PrtMipString = PM
    rpt.PrtMip = PrtMipString.strRGB
    
    Set rpt = Nothing
    
End Sub

Open in new window

0
 
LVL 57
ID: 38732498
<<Do you know the syntax to add the column break on the correct control, in my case on the grouping of the MgrOrg - should all be in separate columns on the report.>>

 The Access report engine works it's way down the page formatting as it goes.

 You can determine where it is via the .Top property and what column it's in via the .Left property.

When it reaches the bottom of column 1, it moves to the next column and keeps doing that till it runs out of space on the entire page.

 WIthin that, it responds to the normal settings, such as Keep together.   As for the column break, look at the New Row or Column property.

 Sounds like your almost there!

Jim.
0
 

Author Comment

by:Karen Schaefer
ID: 38733146
Thanks Jim & Jeff,

Do you guys know the correct syntax for setting the Column Layout in the Page setup?  I have not been able to locate what the correct syntax is for setting the Column Layout to left to right.

Thanks,

K

Nevermind - just found the line of code the previous code post.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38733198
Hey Karen,

If you get this all working, you should tighten up the syntax, add comments and write this up  as an "Article" here.

It'd be a great way to start the new year...

;-)

Jeff
0
 

Author Comment

by:Karen Schaefer
ID: 38733212
If I have the time - as you can see I am working on the holiday.

Only 1 minor issue left - I am now getting 2 pages - the header appearing on page 2 with no other data.  I have played with the margins, the field sizes etc. but can't seem to resolve the 2 page printout.

I hate it when things come together only to have something else rear its ugly head.

K
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38733285
<If I have the time - as you can see I am working on the holiday.>
Well, we all just got a bottle of Champagne from our boss,...
...So I'll leave this to Jim...
;-)

Happy New Year All!

;-)

Jeff
0
 
LVL 57
ID: 38738025
<<...So I'll leave this to Jim...>>

Orphand headers can be tough to track down.  Make sure all you bands are snugged up to the bottom of the last control (unless you really want the white space)

Jim.
0
 

Author Comment

by:Karen Schaefer
ID: 38738034
????Make sure all you bands are snugged up to the ????  Not sure what you mean?

K
0
 
LVL 57
ID: 38738059
Figured you might not<g> - I should have explained a bit more.

The bands in a report layout are the report header/footer, page header/footer, detail, etc.

For each section, the bottom edge can be moved up/down leaving white space or no white space under the last control.  

Move the edge up as much as possible unless you really need the space and if a band is not used (like the report footer) but it appears in the design surface, make sure it's visible property is false.

Jim.
0
 

Author Comment

by:Karen Schaefer
ID: 38738083
Well thanks for the suggestion - all my sections of the report are as short/narrow as possible with the can grow/shrink = yes.  Supposedly to prevent the erroneous white space.  I am not going to spend much more time on this I beleive my customer can live with it as is.  

Thanks.
0
 
LVL 57
ID: 38738164
Don't rely on can shrink....there are a number of reasons why it may not work.

Jim.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now