|  | Set Can Grow Property in module allowed by Access? | | |
| | | Kelii |  |
|  Posted: Thu Aug 21, 2008 4:23 am Post subject: Set Can Grow Property in module allowed by Access? |  |
I'll be brief.
I have a form that allows users to select a variety of options for a
specific report (e.g., filter, sort). I want to set word wrap as one
option available to the user.
Based on my tests, it does not appear that Access will allow me to set
the report's fields can grow property to true in the form's module.
Does this sound right?
Sample code:
Private Sub cmdPrintPreview()
DoCmd.OpenReport "rptTest", acPreview
If Me.fraWordWrap = 2 Then
Reports!rptTest!FieldTest.CanGrow = True
End If
End Sub
Assuming the above is accurate, can the reports properties be set if I
open in design mode (hidden) before opening in preview mode? Further
assuming that some users will be using Access Runtime, will this
solution work for all my users?
Thanks in advance for any help or direction.
Best,
Kelii |
| |
| | | Allen Browne | |
| | Posted: Thu Aug 21, 2008 4:23 am Post subject: Re: Set Can Grow Property in module allowed by Access? | |
| |  | |
Kelii, set it after the report is open will be too late. It's already done
its thing.
Try setting it in the report's Open event procedure instead of in the form's
code. This kind of thing:
Private Sub Report_Open(Cancel As Integer)
If CurrentProject.AllForms("Form1").IsLoaded Then
If Forms("Form1")!fraWordWrap = 2 Then
Me.FieldTest.CanGrow = True
End If
End If
End Sub
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - LINK
Reply to group, rather than allenbrowne at mvps dot org.
"Kelii" <keliie@yahoo.com> wrote in message
news:1d73288a-4fe6-420f-a5a5-cd3e2a57a8c7@m73g2000hsh.googlegroups.com...
| Quote: | I'll be brief.
I have a form that allows users to select a variety of options for a
specific report (e.g., filter, sort). I want to set word wrap as one
option available to the user.
Based on my tests, it does not appear that Access will allow me to set
the report's fields can grow property to true in the form's module.
Does this sound right?
Sample code:
Private Sub cmdPrintPreview()
DoCmd.OpenReport "rptTest", acPreview
If Me.fraWordWrap = 2 Then
Reports!rptTest!FieldTest.CanGrow = True
End If
End Sub
Assuming the above is accurate, can the reports properties be set if I
open in design mode (hidden) before opening in preview mode? Further
assuming that some users will be using Access Runtime, will this
solution work for all my users?
Thanks in advance for any help or direction.
Best,
Kelii |
|
| |
| | | Kelii | |
| | Posted: Thu Aug 21, 2008 8:01 pm Post subject: Re: Set Can Grow Property in module allowed by Access? | |
Allen,
Thanks for the suggestion.
So I gave it a shot, and I'm still getting the same error (2448: You
can't assign a value to this object) on the command where I attempt to
set the CanGrow property to True.
If you have any other suggestions or workarounds I would be open to
it.
Thanks Kelii |
| |
| | | Kelii | |
| | Posted: Thu Aug 21, 2008 8:25 pm Post subject: Re: Set Can Grow Property in module allowed by Access? | |
Allen,
Just another note, that I implemented a slight change to the code
which goes as follows:
DoCmd.OpenReport "rptSKUListingSimple", acViewDesign, , ,
acHidden
If Forms!frmSKUReportOptions!fraWordWrapOptions = 1 Then
'No wrap
Reports!rptSKUListingSimple!Vendor_Name.CanGrow =
False
etc....
ElseIf Forms!frmSKUReportOptions!fraWordWrapOptions = 2
Then 'All wrap
Reports!rptSKUListingSimple!Vendor_Name.CanGrow = True
etc....
End If
DoCmd.Close acReport, "rptSKUListingSimple", acSaveYes
DoCmd.OpenReport "rptSKUListingSimple", acPreview
This opens the report in design view (hidden) first, reformats,
closes, and reopens the report in print preview mode.
Anyhow, this works. The only snag is that I don't think it will work
for users running the app with Access Runtime. Is this assumption
correct?
Thanks again,
Kelii |
| |
| | | Allen Browne | |
| | Posted: Fri Aug 22, 2008 12:35 am Post subject: Re: Set Can Grow Property in module allowed by Access? | |
| | | |
You are still trying to do this in the original event. You can't (except in
desgin view, which (as you say) is highly undesirable.) Re-read the previous
reply.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - LINK
Reply to group, rather than allenbrowne at mvps dot org.
"Kelii" <keliie@yahoo.com> wrote in message
news:9e043961-3212-4833-9dae-8d146778fe1d@x35g2000hsb.googlegroups.com...
| Quote: | Allen,
Just another note, that I implemented a slight change to the code
which goes as follows:
DoCmd.OpenReport "rptSKUListingSimple", acViewDesign, , ,
acHidden
If Forms!frmSKUReportOptions!fraWordWrapOptions = 1 Then
'No wrap
Reports!rptSKUListingSimple!Vendor_Name.CanGrow =
False
etc....
ElseIf Forms!frmSKUReportOptions!fraWordWrapOptions = 2
Then 'All wrap
Reports!rptSKUListingSimple!Vendor_Name.CanGrow = True
etc....
End If
DoCmd.Close acReport, "rptSKUListingSimple", acSaveYes
DoCmd.OpenReport "rptSKUListingSimple", acPreview
This opens the report in design view (hidden) first, reformats,
closes, and reopens the report in print preview mode.
Anyhow, this works. The only snag is that I don't think it will work
for users running the app with Access Runtime. Is this assumption
correct?
Thanks again,
Kelii |
|
| |
| | | Kelii | |
| | Posted: Fri Aug 22, 2008 5:24 pm Post subject: Re: Set Can Grow Property in module allowed by Access? | |
| | | |
Hi Allen,
I understand what you are saying ... put the code in the Report_Open
event, not the cmdPreviewReport event.
Sorry I wasn't being specific in my posts. I did relocated the code
into the Report_Open event (shown below in all its glory). And, as I
mentioned above, it returns the stated error. I did some reading in
Access Developer's Handbook and VBA Developer's Handbook; there isn't
anything specific about my question, but the text suggests that report
modifications are limited...
One other option I'm considering is to make several versions of the
report with the various word wrap options. Its kind of inelegant IMO,
but I haven't been able to find any other options for my Access Run
Time users.
Private Sub Report_Open(Cancel As Integer)
On Error GoTo Error_Handler
'This code generates an error, unable to set property
If Forms!frmSKUReportOptions!fraWordWrapOptions = 1 Then 'No wrap
'Do nothing, default format to no wrap
ElseIf Forms!frmSKUReportOptions!fraWordWrapOptions = 2 Then 'All
wrap
Me.Vendor_Name.CanGrow = True
Me.Item_Category.CanGrow = True
Me.Item_Type.CanGrow = True
Me.Item_Location.CanGrow = True
Me.Item_Description_ID.CanGrow = True
Me.Item_Unit_of_Measure.CanGrow = True
Me.SKU.CanGrow = True
Me.SKU_Memo.CanGrow = True
ElseIf Forms!frmSKUReportOptions!fraWordWrapOptions = 3 Then
'Wrap memo only
Me.SKU_Memo.CanGrow = True
End If
Exit_Procedure:
On Error Resume Next
Exit Sub
Error_Handler:
Select Case Err
Case Else
MsgBox "Error: " & Err.Number & vbCr & Err.Description
Resume Exit_Procedure
End Select
End Sub
Thanks again,
Kelii |
| |
| | | Allen Browne | |
| | Posted: Sat Aug 23, 2008 2:48 am Post subject: Re: Set Can Grow Property in module allowed by Access? | |
| | | |
Okay, I just looked in help (from the VBA window) under CanGrow property. It
includes this note:
This property setting is read-only in a macro or Visual Basic in any
view but Design view.
So attempting to set it (other than in design view) yields error 2448:
You can't assign a value to this object.
A possible workaround might be to change the ControlSource of the text boxes
in Report_Open so they don't wrap:
- Set CanGrow to Yes at design time.
- Rename the controls so they are different from the field names, e.g. set
the Name property of Vendor_Name to:
txtVendor_Name
- Use Report_Open to limit the number of characters in the field if you
don't want it growing. Aircode example:
Private Sub Report_Open(Cancel as Integer)
Const strcForm = "frmSKUReportOptions"
If CurrentProject.AllForms(strcForm).IsLoaded Then
If Forms(strcForm)!fraWordWrapOptions = 2 Then
Me.txtVendor_Name.ControlSource = "=Left([Vendor_Name], 10)"
'etc.
End If
End If
End Sub
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - LINK
Reply to group, rather than allenbrowne at mvps dot org.
"Kelii" <keliie@yahoo.com> wrote in message
news:b9cd4a1d-6ce0-46f9-9412-f0869e12e621@26g2000hsk.googlegroups.com...
| Quote: |
I understand what you are saying ... put the code in the Report_Open
event, not the cmdPreviewReport event.
Sorry I wasn't being specific in my posts. I did relocated the code
into the Report_Open event (shown below in all its glory). And, as I
mentioned above, it returns the stated error. I did some reading in
Access Developer's Handbook and VBA Developer's Handbook; there isn't
anything specific about my question, but the text suggests that report
modifications are limited...
One other option I'm considering is to make several versions of the
report with the various word wrap options. Its kind of inelegant IMO,
but I haven't been able to find any other options for my Access Run
Time users. |
|
| |
| | | Kelii | |
| | Posted: Sat Aug 23, 2008 7:54 pm Post subject: Re: Set Can Grow Property in module allowed by Access? | |
Allen,
Fair enough, I should have read the help more closely. I rarely find
the answer I need in help, and I tend to dismiss it with a brief read.
Anyhow, lesson learned.
I have to say that your suggestion is quite good, and in fact I'll be
using it as shown.
Thanks a ton, I appreciate it.
Kelii |
| |
|
|
