I have a report that has a couple of fields that can grow if it gets too big. How can I set up the remaining fields to grow if these other fields grow? I'm trying to get all fields the same height instead of it looking like [censored].
Jeff
Full Version: CanGrow Problem
G’day Jeff
If I understand correctly you can set the border style to transparent and draw lines around them at run time.
Little A97 demo attached.
Regards,
Chris.
If I understand correctly you can set the border style to transparent and draw lines around them at run time.
Little A97 demo attached.
Regards,
Chris.
Chris,
Thanks for the example but I am having trouble making it work. Here is a copy of my report. Can you take a look at it and make some suggestions?
Jeff
Thanks for the example but I am having trouble making it work. Here is a copy of my report. Can you take a look at it and make some suggestions?
Jeff
G’day Jeff
That might require a bit of work but without knowing all the details you may be able to achieve something like the above attachment.
The areas circled in blue may be extended, it just depends on what they are and if they are conditional on anything. The areas circled in red could be more difficult if you wanted to center the text in the available space, if not then should be easy.
Having said that, I am assuming quite a lot so please take it with a grain of salt.
To go any further I would require the report, sample data, any query the report might be using and in A97 please.
Hope that helps.
Regards,
Chris.
That might require a bit of work but without knowing all the details you may be able to achieve something like the above attachment.
The areas circled in blue may be extended, it just depends on what they are and if they are conditional on anything. The areas circled in red could be more difficult if you wanted to center the text in the available space, if not then should be easy.
Having said that, I am assuming quite a lot so please take it with a grain of salt.
To go any further I would require the report, sample data, any query the report might be using and in A97 please.
Hope that helps.
Regards,
Chris.
Chris,
Yeah, that is exactly what I need the report to do.
The area in blue does have conditional formatting for each of the shaded cells. If nothing is there then its grey, red text if "D" or green text if "U". That's about it. The areas in red are fine. The data does not need to line up in the middle.
Any ideas on how to go about it?
Jeff
Yeah, that is exactly what I need the report to do.
The area in blue does have conditional formatting for each of the shaded cells. If nothing is there then its grey, red text if "D" or green text if "U". That's about it. The areas in red are fine. The data does not need to line up in the middle.
Any ideas on how to go about it?
Jeff
G’day Jeff
Well the way I would go about it is the same way I did in the first demo I posted.
To help see what is going on in that demo try setting the border style to solid in each of the four text boxes. Also note that the horizontal line at the bottom is pushed down by the tallest text box. Another thing to note is that all other lines are drawn at run time and their height is fixed at 30,000 twips but the variable height of the detail section overrides that setting.
The conditional vertical bars could be handled by conditionally switching the visibility of the text boxes and conditionally drawing thick vertical lines in their place.
It seems to be possible but you will probably need to lookup the Line method in online help.
It’s going to be a bit of a fiddle and that’s why I said I would need a demo if possible.
Hope that helps.
Regards,
Chris.
Well the way I would go about it is the same way I did in the first demo I posted.
To help see what is going on in that demo try setting the border style to solid in each of the four text boxes. Also note that the horizontal line at the bottom is pushed down by the tallest text box. Another thing to note is that all other lines are drawn at run time and their height is fixed at 30,000 twips but the variable height of the detail section overrides that setting.
The conditional vertical bars could be handled by conditionally switching the visibility of the text boxes and conditionally drawing thick vertical lines in their place.
It seems to be possible but you will probably need to lookup the Line method in online help.
It’s going to be a bit of a fiddle and that’s why I said I would need a demo if possible.
Hope that helps.
Regards,
Chris.
G’day Jeff.
Just noticed that the people you work for may not allow uploading a sample to site so here is some other information that might help.
The very first thing is to make sure that any and all reports are stable with regards to holding such things as paper size and margins.
Not all '‘out of the box’ versions of Access were stable, so do a search on this site if that is a problem.
Personal preference…
Turn off snap to grid. Jog object position (<Ctrl> + <arrow keys>) rather than dragging with mouse.
Better still position objects in code to the twip resolution or copy/paste dimensions from a ‘control’ object to relative subordinate objects.
I prefer code because it’s easier to change.
Avoid high contrast (black) thin (one pixel) lines. If you miss by one pixel then you miss by 100% and it shows.
If possible, reduce contrast (light gray), thicken lines (5 pixels) and if you then miss by one pixel you miss by 20% in low contrast and it’s not so easy to see.
Oh! and one other thing… I’m standing on my head on the other side of the planet so there is an inherent delay in replies ‘while the old girl spins’. So post at your pleasure because others will have valid suggestions and this is not a ‘Chris-fest’.
That’s about all I can think of at the moment but please let us know if you still have problems.
Regards,
Chris.
Just noticed that the people you work for may not allow uploading a sample to site so here is some other information that might help.
The very first thing is to make sure that any and all reports are stable with regards to holding such things as paper size and margins.
Not all '‘out of the box’ versions of Access were stable, so do a search on this site if that is a problem.
Personal preference…
Turn off snap to grid. Jog object position (<Ctrl> + <arrow keys>) rather than dragging with mouse.
Better still position objects in code to the twip resolution or copy/paste dimensions from a ‘control’ object to relative subordinate objects.
I prefer code because it’s easier to change.
Avoid high contrast (black) thin (one pixel) lines. If you miss by one pixel then you miss by 100% and it shows.
If possible, reduce contrast (light gray), thicken lines (5 pixels) and if you then miss by one pixel you miss by 20% in low contrast and it’s not so easy to see.
Oh! and one other thing… I’m standing on my head on the other side of the planet so there is an inherent delay in replies ‘while the old girl spins’. So post at your pleasure because others will have valid suggestions and this is not a ‘Chris-fest’.
That’s about all I can think of at the moment but please let us know if you still have problems.
Regards,
Chris.
Chris,
I hope this will help.
Jeff
I hope this will help.
Jeff
G’day Jeff.
It was a bit of a fiddle but seems OK.
The attached demo is in A97 and does not use conditional formatting but should still work in A2K.
I removed everything I did not need but did not change the names of anything so you should be able to simply import the report to your application.
Hope that goes close to what you require.
Regards,
Chris.
It was a bit of a fiddle but seems OK.
The attached demo is in A97 and does not use conditional formatting but should still work in A2K.
I removed everything I did not need but did not change the names of anything so you should be able to simply import the report to your application.
Hope that goes close to what you require.
Regards,
Chris.
G’day Jeff.
Personal preference but swapping very minor things around I think we get something easier on the eye and it seems to draw attention to the data in the report rather than the formatting of the report.
To my eye it also partially overcomes the slight ‘pixel bleed’ at the bottom of the section but I haven’t printed it, no colour ink in the printer, so it will depend on how it looks on paper.
But I do like the layout of the report, whatever it means.
Little A97 demo attached.
Regards,
Chris.
Personal preference but swapping very minor things around I think we get something easier on the eye and it seems to draw attention to the data in the report rather than the formatting of the report.
To my eye it also partially overcomes the slight ‘pixel bleed’ at the bottom of the section but I haven’t printed it, no colour ink in the printer, so it will depend on how it looks on paper.
But I do like the layout of the report, whatever it means.
Little A97 demo attached.
Regards,
Chris.
Chris,
Thanks for the hard work. I really appreciate it. Unfortunately, I need to have the conditional formatting in the report. I've attached a pic of what it now looks like with the conditional formatting. Any way to fix it?
Jeff
Thanks for the hard work. I really appreciate it. Unfortunately, I need to have the conditional formatting in the report. I've attached a pic of what it now looks like with the conditional formatting. Any way to fix it?
Jeff
I’m not sure what you mean by you need conditional formatting in the report.
If you need it then simply apply it to the report I sent you after converting it to Access 2000. In other words, what conditional formatting do you need that can’t be applied to the report I sent you?
It also looks like you set the border style back to normal in the boxes containing the “D” “U” status but it need to remain transparent.
Chris.
If you need it then simply apply it to the report I sent you after converting it to Access 2000. In other words, what conditional formatting do you need that can’t be applied to the report I sent you?
It also looks like you set the border style back to normal in the boxes containing the “D” “U” status but it need to remain transparent.
Chris.
Chris,
I sort of see how you did the color for the U's and D's in your report. Can the Status, EOC, Doc, and Remarks blocks be completed the same way? Here is the conditional formatting I'm using now. It is all dependent on the Status Code.
FMC=1
PMCM=2
PMCS=3
NMCM=4
NMCS=5
[intstatusnumber] =1 then font dark green
[intstatusnumber] between 2 and 3 then font dark blue
[intstatusnumber] between 4 and 5 then font red
Any ideas on how to incorporate it?
Jeff
I sort of see how you did the color for the U's and D's in your report. Can the Status, EOC, Doc, and Remarks blocks be completed the same way? Here is the conditional formatting I'm using now. It is all dependent on the Status Code.
FMC=1
PMCM=2
PMCS=3
NMCM=4
NMCS=5
[intstatusnumber] =1 then font dark green
[intstatusnumber] between 2 and 3 then font dark blue
[intstatusnumber] between 4 and 5 then font red
Any ideas on how to incorporate it?
Jeff
G’day Jeff.
I think you could have used conditional formatting for that but here it is in code.
Little A97 demo attached and hope that is what you require.
Regards,
Chris.
I think you could have used conditional formatting for that but here it is in code.
CODE
Option Explicit
Option Compare Text
Private Const conLineColor As Long = 14540253
Private Const conBlockColor As Long = 12632256
Dim lngStatusColor As Long
Sub DrawLines()
Dim lngLeft As Long
Dim lngColumnLeft(28) As Long
Dim ctl As Control
lngColumnLeft(0) = 780
lngColumnLeft(1) = 780
lngColumnLeft(2) = 1320
lngColumnLeft(3) = 1560
lngColumnLeft(4) = 1800
lngColumnLeft(5) = 2040
lngColumnLeft(6) = 2280
lngColumnLeft(7) = 2520
lngColumnLeft(8) = 2760
lngColumnLeft(9) = 3000
lngColumnLeft(10) = 3240
lngColumnLeft(11) = 3480
lngColumnLeft(12) = 3720
lngColumnLeft(13) = 3960
lngColumnLeft(14) = 4200
lngColumnLeft(15) = 4440
lngColumnLeft(16) = 4680
lngColumnLeft(17) = 4920
lngColumnLeft(18) = 5160
lngColumnLeft(19) = 5760
lngColumnLeft(20) = 7740
lngColumnLeft(21) = 9120
lngColumnLeft(22) = 11520
lngColumnLeft(23) = 12240
lngColumnLeft(24) = 12960
lngColumnLeft(25) = 13673
lngColumnLeft(26) = 14400
lngColumnLeft(27) = 15180
For lngLeft = LBound(lngColumnLeft()) To UBound(lngColumnLeft())
Me.Line (lngColumnLeft(lngLeft), 0)-(lngColumnLeft(lngLeft), 30000), conLineColor
Next lngLeft
For Each ctl In Me
With ctl
If .Tag = "CheckColor" Then
If IsNull(.Value) Then
For lngLeft = .Left + 15 To .Left + .Width - 15 Step 10
Me.Line (lngLeft, .Top + 15)-(lngLeft, 30000), conBlockColor
Next lngLeft
Else
.ForeColor = IIf(.Value = "D", vbRed, vbGreen)
End If
End If
End With
Next ctl
Select Case UCase(Me.chrCodeName)
Case "FMC"
lngStatusColor = 4227072 [color="green"]' dark green[/color]
Case "PMCM", "PMCS"
lngStatusColor = 10485760 [color="green"]' dark blue[/color]
Case "NMCM", "NMCS"
lngStatusColor = vbRed
Case Else
lngStatusColor = vbBlack
End Select
Me.chrCodeName.ForeColor = lngStatusColor
Me.chrSystemReason.ForeColor = lngStatusColor
Me.chrDocumentNumbers.ForeColor = lngStatusColor
Me.chrComments.ForeColor = lngStatusColor
End Sub
Private Sub GroupHeader1_Print(Cancel As Integer, PrintCount As Integer)
DrawLines
End Sub
Private Sub Report_Open(Cancel As Integer)
Dim lngSuffix As Long
Me.Line130.BorderColor = conLineColor [color="green"]' If you have trouble[/color]
Me.Line131.BorderColor = conLineColor [color="green"]' finding any[/color]
Me.Line132.BorderColor = conLineColor [color="green"]' of these lines[/color]
Me.Line133.BorderColor = conLineColor [color="green"]' change colour[/color]
Me.Line135.BorderColor = conLineColor [color="green"]' to vbRed[/color]
Me.Line136.BorderColor = conLineColor [color="green"]' and look again.[/color]
For lngSuffix = 1 To 31
Me("chr" & CStr(lngSuffix)).BorderColor = conLineColor
Next lngSuffix
End Sub
Option Compare Text
Private Const conLineColor As Long = 14540253
Private Const conBlockColor As Long = 12632256
Dim lngStatusColor As Long
Sub DrawLines()
Dim lngLeft As Long
Dim lngColumnLeft(28) As Long
Dim ctl As Control
lngColumnLeft(0) = 780
lngColumnLeft(1) = 780
lngColumnLeft(2) = 1320
lngColumnLeft(3) = 1560
lngColumnLeft(4) = 1800
lngColumnLeft(5) = 2040
lngColumnLeft(6) = 2280
lngColumnLeft(7) = 2520
lngColumnLeft(8) = 2760
lngColumnLeft(9) = 3000
lngColumnLeft(10) = 3240
lngColumnLeft(11) = 3480
lngColumnLeft(12) = 3720
lngColumnLeft(13) = 3960
lngColumnLeft(14) = 4200
lngColumnLeft(15) = 4440
lngColumnLeft(16) = 4680
lngColumnLeft(17) = 4920
lngColumnLeft(18) = 5160
lngColumnLeft(19) = 5760
lngColumnLeft(20) = 7740
lngColumnLeft(21) = 9120
lngColumnLeft(22) = 11520
lngColumnLeft(23) = 12240
lngColumnLeft(24) = 12960
lngColumnLeft(25) = 13673
lngColumnLeft(26) = 14400
lngColumnLeft(27) = 15180
For lngLeft = LBound(lngColumnLeft()) To UBound(lngColumnLeft())
Me.Line (lngColumnLeft(lngLeft), 0)-(lngColumnLeft(lngLeft), 30000), conLineColor
Next lngLeft
For Each ctl In Me
With ctl
If .Tag = "CheckColor" Then
If IsNull(.Value) Then
For lngLeft = .Left + 15 To .Left + .Width - 15 Step 10
Me.Line (lngLeft, .Top + 15)-(lngLeft, 30000), conBlockColor
Next lngLeft
Else
.ForeColor = IIf(.Value = "D", vbRed, vbGreen)
End If
End If
End With
Next ctl
Select Case UCase(Me.chrCodeName)
Case "FMC"
lngStatusColor = 4227072 [color="green"]' dark green[/color]
Case "PMCM", "PMCS"
lngStatusColor = 10485760 [color="green"]' dark blue[/color]
Case "NMCM", "NMCS"
lngStatusColor = vbRed
Case Else
lngStatusColor = vbBlack
End Select
Me.chrCodeName.ForeColor = lngStatusColor
Me.chrSystemReason.ForeColor = lngStatusColor
Me.chrDocumentNumbers.ForeColor = lngStatusColor
Me.chrComments.ForeColor = lngStatusColor
End Sub
Private Sub GroupHeader1_Print(Cancel As Integer, PrintCount As Integer)
DrawLines
End Sub
Private Sub Report_Open(Cancel As Integer)
Dim lngSuffix As Long
Me.Line130.BorderColor = conLineColor [color="green"]' If you have trouble[/color]
Me.Line131.BorderColor = conLineColor [color="green"]' finding any[/color]
Me.Line132.BorderColor = conLineColor [color="green"]' of these lines[/color]
Me.Line133.BorderColor = conLineColor [color="green"]' change colour[/color]
Me.Line135.BorderColor = conLineColor [color="green"]' to vbRed[/color]
Me.Line136.BorderColor = conLineColor [color="green"]' and look again.[/color]
For lngSuffix = 1 To 31
Me("chr" & CStr(lngSuffix)).BorderColor = conLineColor
Next lngSuffix
End Sub
Little A97 demo attached and hope that is what you require.
Regards,
Chris.
Chris,
I just returned to work and downloaded your attachment. It looks great. Thank YOU very much!!!! I really appreciate it.
Jeff
I just returned to work and downloaded your attachment. It looks great. Thank YOU very much!!!! I really appreciate it.
Jeff
G’day Jeff and you’re very welcome.
I like the report, seems very business like without being overly flashy.
(Any chance of a Navy pension???
)
Regards,
Chris.
I like the report, seems very business like without being overly flashy.
(Any chance of a Navy pension???
)Regards,
Chris.
Hey Chris,
I hope so. I'm at 19 years right now and I can go to 24 as a chief but if things don't clear up for me soon I just may get out at 20.
Anyway, I see that you set the border colors with number codes. Where can I find those codes if I want to make the borders and cells a little darker?
Thanks,
Jeff
I hope so. I'm at 19 years right now and I can go to 24 as a chief but if things don't clear up for me soon I just may get out at 20.
Anyway, I see that you set the border colors with number codes. Where can I find those codes if I want to make the borders and cells a little darker?
Thanks,
Jeff
G’day Jeff.
The way I do it is to create a line or rectangle, which is visible on the report, and manually change the border colour. After getting the colour I require simply copy the colour number to the constant value.
When finished don’t forget to set the test object to invisible or simply delete it.
There may be better ways but hope that helps.
Regards,
Chris.
The way I do it is to create a line or rectangle, which is visible on the report, and manually change the border colour. After getting the colour I require simply copy the colour number to the constant value.
When finished don’t forget to set the test object to invisible or simply delete it.
There may be better ways but hope that helps.
Regards,
Chris.
Chris,
Thanks.
Jeff
Thanks.
Jeff
I'm a new Access user and am looking for the CanGrow function. Instructions say to go to the Design View, click on the chosen field, go into Properties and change CanGrow to "Yes" - however, CanGrow does not appear in Properties. What next?
G’day Mary and welcome to Utter Access.
SubHunter must be off chasing bigger fish. (I know, bad pun but best I could do at such short notice.
)
The Can Grow property is illustrated in the attached image.
Just make sure you’re on the correct Tab and also that you have selected the correct control.
(Tip: always select the All tab, that way you won’t miss a property and you will find properties you haven’t used before.)
Regards,
Chris.
SubHunter must be off chasing bigger fish. (I know, bad pun but best I could do at such short notice.
)The Can Grow property is illustrated in the attached image.
Just make sure you’re on the correct Tab and also that you have selected the correct control.
(Tip: always select the All tab, that way you won’t miss a property and you will find properties you haven’t used before.)
Regards,
Chris.
Mary,
What kind of field is it? I believe only data fields can grow. Label fields do not.
Jeff
What kind of field is it? I believe only data fields can grow. Label fields do not.
Jeff
LOL,
Yeah Chris, I've been busy all day working on my project. Now its the end of day and I'm on my way out of the door. I didn't even see your reply to her before I answered.
Anyway, I hope both of our replies helped her.
Later,
Jeff
Yeah Chris, I've been busy all day working on my project. Now its the end of day and I'm on my way out of the door. I didn't even see your reply to her before I answered.
Anyway, I hope both of our replies helped her.
Later,
Jeff
G’day Jeff.
I was just playing with some dummy data for the report and found a potential problem.
The four memo fields will not display more than 255 characters.
Why I do not know but the fix is to remove all upper case formatting from the table and report, rename the textboxes that contain the memo fields, change the code to the new textbox names and format the textboxes using the Ucase() function.
New, version 3, demo attached.
Regards,
Chris.
I was just playing with some dummy data for the report and found a potential problem.
The four memo fields will not display more than 255 characters.
Why I do not know but the fix is to remove all upper case formatting from the table and report, rename the textboxes that contain the memo fields, change the code to the new textbox names and format the textboxes using the Ucase() function.
New, version 3, demo attached.
Regards,
Chris.
Morning Chris,
I don't think the inputs from the squadrons will be anywhere near 255 characters so I think we're good but I will keep this new one handy incase I do run into a problem.
Speaking of problems, I need another favor. If you look at the report you will see the section labeled "AIP" with four sections below it. I need to add another block under there. If you look in the Aircraft Table you will see chrSLAM/CATM. This needs to be added between Survivability and AIMS. I tried to add it to your previous version that I'm using but the format is all off. For furture reference, how would I adjust the VBA code to account for new blocks if I need to add some later?
Anyway, I'll check back later today before heading home but I doubt I will catch you before Monday. So, have a great weekend and thanks for all your help. I am getting great reviews from all the work we're doing.
Later,
Jeff
I don't think the inputs from the squadrons will be anywhere near 255 characters so I think we're good but I will keep this new one handy incase I do run into a problem.
Speaking of problems, I need another favor. If you look at the report you will see the section labeled "AIP" with four sections below it. I need to add another block under there. If you look in the Aircraft Table you will see chrSLAM/CATM. This needs to be added between Survivability and AIMS. I tried to add it to your previous version that I'm using but the format is all off. For furture reference, how would I adjust the VBA code to account for new blocks if I need to add some later?
Anyway, I'll check back later today before heading home but I doubt I will catch you before Monday. So, have a great weekend and thanks for all your help. I am getting great reviews from all the work we're doing.
Later,
Jeff
G’day Jeff.
Well I know you don’t think you will ever need 255 characters in a field but the chrSystemReason field was already truncated by 10 characters. No real point in using a memo field if it gets truncated to 255 characters because you could use a text field for that.
So this version adds the changes you require and I have removed the 255 display limitation from the memo fields. I’ve added a couple of comments to the code to show you what was needed to add the extra vertical line.
Hope that helps.
Regards,
Chris.
Well I know you don’t think you will ever need 255 characters in a field but the chrSystemReason field was already truncated by 10 characters. No real point in using a memo field if it gets truncated to 255 characters because you could use a text field for that.
So this version adds the changes you require and I have removed the 255 display limitation from the memo fields. I’ve added a couple of comments to the code to show you what was needed to add the extra vertical line.
Hope that helps.
Regards,
Chris.
Morning Chris,
Thanks again for the help. Where do those left column numbers come from?
Jeff
Thanks again for the help. Where do those left column numbers come from?
Jeff
G’day Jeff and sorry but I’ve been busy.
“Where do those left column numbers come from?”
From my calculator and I know no other way to do it.
They are twips, 567 to the centimeter, 1440 to the inch, and just for the Australian Navy (if they are watching)…103,680 to the fathom. (Please don’t park that destroyer in front of my aircraft carrier you twip. {Black humor I know but it was a long time ago.})
Regards,
Chris.
“Where do those left column numbers come from?”
From my calculator and I know no other way to do it.
They are twips, 567 to the centimeter, 1440 to the inch, and just for the Australian Navy (if they are watching)…103,680 to the fathom. (Please don’t park that destroyer in front of my aircraft carrier you twip. {Black humor I know but it was a long time ago.})
Regards,
Chris.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.