trouble with Report variable (1 Viewer)

[Ziggy1]

I set up a report that is opened from a form that has Unbound check boxes. The checks are used in my code to hide or show the relative controls on the report.

Also I coded the Detail section "On Format" event to calculate the spacing for when a Control is not selected to be shown... essentially I do all the math to alter the size and position of the controls that will be shown ( it is a group of 4).

The problem is I have to keep the form Open in order to have it print, I want it to be a popup and then close as report opens.... I can do it, but when I actually want to print I get an error rendering the report.

Should I have a variable or function separate from each to hold the values? Can I force the form to close after the report opens, via a Report event?

**Note I am formatting in the Page Header and Detail sections

this is an example I use in the code to pass the variable, I tried placing it in the report Open event and putting the variable declaration at the top ( below Option explicit), but no luck.

Frm_std20 = Forms!frmPrintMenu.chk20std

Just need a nudge ( or kick if you preferr in the right direction.

 

[vbaInet]

We need more information.

* What is Frm_std20?
* What is the point of using a variable if the control on the form already has the value?
* How will you manage re-aligning the controls on the form when certain controls are hidden? That' is, you will need to move controls left.

 

[Ziggy1]

The code below works like a charm, but getting it to work with the form closed that is a problem. You see I want the form to be popup make the choice then print report and had off the choices to the report.

I currently have Popup turned off (so it drops behind the report), but it causes my main form to resize then I open the Print form..

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)


' Ziggy Stouten
'Nov 2011

' I also tried by declaring outside this procedure 

Dim Frm_std20 As Boolean
Dim Frm_std40 As Boolean
Dim Frm_HC40 As Boolean
Dim Frm_HC45 As Boolean
Dim Frm_ttDays As Boolean

Dim ColCnt As Integer  ' number of controls to show
Dim colwid As Double  ' control width
Dim LeftPos As Double ' control left position


Dim cCont As Control


ColCnt = 0
colwid = 8
'LeftPos = 11.217
LeftPos = 11.238


    ' converts the LeftPos to inches and then TWIPS
    ' 1 Inch = 1440 TWIPS

' also tried in the on open

' stores the state of the checkbox
Frm_std20 = Forms!frmPrintMenu.chk20std
Frm_std40 = Forms!frmPrintMenu.chk40std
Frm_HC40 = Forms!frmPrintMenu.chk40hc
Frm_HC45 = Forms!frmPrintMenu.chk45HC
Frm_ttDays = Forms!frmPrintMenu.chkTranDays

' ensures report starts with controls visible

Me.txt20std.Visible = True
Me.txt40HC.Visible = True
Me.txt40std.Visible = True
Me.txt45hc.Visible = True
Me.txtTran.Visible = True

' makes control invisible based on checkmark on form

        If Frm_std20 = False Then
            Me.txt20std.Visible = False
        End If

        If Frm_std40 = False Then
            Me.txt40std.Visible = False
        End If

        If Frm_HC40 = False Then
            Me.txt40HC.Visible = False
        End If

        If Frm_HC45 = False Then
            Me.txt45hc.Visible = False
        End If
        
        If Frm_ttDays = False Then
            Me.txtTran.Visible = False
            colwid = 10
            LeftPos = 9.238
        End If
                
                
                LeftPos = LeftPos / 2.54 * 1440
        
         '  count columns visible
    For Each cCont In Me.Controls
    
        If cCont.Visible = True And cCont.Tag = "FrtChrg" Then
              ColCnt = ColCnt + 1
        End If
    
    Next cCont
    
    'divides the column count into the width of the section
    colwid = colwid / ColCnt
    
    ' converts the colwidth to inches and then TWIPS
    ' 1 Inch = 1440 TWIPS
    colwid = colwid / 2.54 * 1440
    
    
    ' position controls
        For Each cCont In Me.Controls
    
            If cCont.Visible = True And cCont.Tag = "FrtChrg" Then
            
           
                    cCont.Left = LeftPos
                    cCont.Width = colwid
                    
                    ' increments the position
                    LeftPos = LeftPos + colwid
        
            
            End If
    
        Next cCont
    
    
End Sub

 

[vbaInet]

So declare the variables as global variables. That is put them in a Standard Module.

 

[Ziggy1]

So declare the variables as global variables. That is put them in a Standard Module.

that is where I am stuck, I know what you mean, but I'm not able to get the value to the report, I get "Variable not defined". I know how to code (not a pro) but get lost in the scope sometimes, tend to stick with what I know.

Module:

Option Compare Database

Option Explicit
Dim Frm_std20 As Boolean
Dim Frm_std40 As Boolean
Dim Frm_HC40 As Boolean
Dim Frm_HC45 As Boolean
Dim Frm_ttDays As Boolean


Public Sub chkboxs()

Frm_std20 = Forms!frmPrintMenu.chk20std
Frm_std40 = Forms!frmPrintMenu.chk40std
Frm_HC40 = Forms!frmPrintMenu.chk40hc
Frm_HC45 = Forms!frmPrintMenu.chk45HC
Frm_ttDays = Forms!frmPrintMenu.chkTranDays
 DoCmd.OpenReport "rptQuoteChrgFCL2d", acViewPreview, "", "", acNormal


End Sub

 

[Ziggy1]

OK I see what I missed, I need to declare as "Public"

Public Frm_std20 As Boolean
Public Frm_std40 As Boolean
Public Frm_HC40 As Boolean
Public Frm_HC45 As Boolean
Public Frm_ttDays As Boolean

 

[vbaInet]

Do you know what a Standard Module is? You also need to declare the variables as Public.

When I say Standard Module I'm not talking about the Module of a form or report.

 

[Ziggy1]

Do you know what a Standard Module is? You also need to declare the variables as Public.

When I say Standard Module I'm not talking about the Module of a form or report.

Yes,

I just don't often need to declare a Public Variable.


Anyways, I got it working with a Module, but still get an error if I close the form

 

[Ziggy1]

hang on, I'm just reviewing the code, I see a reference to the form I forgot to replace

 

[vbaInet]

I don't know what's causing the error but I know it's not related to your OP.

 

[Ziggy1]

Thanks vbaInet, you gave me the nudge I needed! I had to get out of that "box" I was in (think outside), ultimately it was using the "Public" to declare the variable that solved it. then I found I forgot to replace one of the form references.

So thanks for the help.

 

[vbaInet]

You're welcome!