This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.
Course Of The Month
7 days, 1 hour left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
how do I set the number of columns in Access 2007 Report via vba
Posted on 2012-12-28
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
11
6
4
21 Comments
Active today
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
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
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
I am still attempting to get the necessary columns to print out on 1 page.
K
Active today
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
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
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
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
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.
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.
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
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
Active today
Only that it was used for somethings *before* the Printer object was fully exposed in office 2003.
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)
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
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.
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
Active today
<<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.
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.
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.
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.
Active today
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
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
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
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
Active today
<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
Well, we all just got a bottle of Champagne from our boss,...
...So I'll leave this to Jim...
;-)
Happy New Year All!
;-)
Jeff
Active today
<<...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.
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.
Active today
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.
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.
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.
Thanks.
Featured Post
ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.
One of a set of tools we're offering as a way to say thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| ACESS 2010 Query Criteria | 23 | 55 | |
| Query Dilema in Access 2010 | 3 | 37 | |
| SQL Server 2005 running VERY slow on Virtual Machine | 18 | 65 | |
| MS Access - Autofill field with item description | 6 | 51 |
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents. The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications. You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Suggested Courses
Course of the Month7 days, 1 hour left to enroll
734 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.