Move Fields on Report Based on Value

[doodle]

windows xp
access 97

I have a report that lists the customer address in the header section.

CustName
AddressLine1
AddressLine2
AddressLine3
City,State,Zip
Country

The problem with these fields is that AddressLine2 and AddressLine3 are
not always populated. I want to move the other fields up, if that is
the case.

Here's my pseudo, i just can't seem to finish it. no matter how i try
to check the value or check to see if it is null, it returns an error.

Private Sub Report_Open(Cancel As Integer)

'Check to see if address 2 is null
'If it is not, set fields:
Me.tblCustomer_Address2.top = 2880
Me.Address3.top = 3120
Me.City.top = 3360
Me.Country.top = 3600
'If it is, set fields:
Me.City.top = 2880
Me.Country.top = 3120
Me.Address3.top = 3360
Me.tblCustomer_Address2.top = 3600

End Sub


-doodle

 

[strive4peace]

Hi Doodle,

the Report Header section can grow... so can controls within it...

so, since you suspect that AddressLine2 and/or AddressLine3 may not
always be filled out... make their Height property (Format tab)
something small, like 0.02, set CanGrow (also Format tab) --> Yes... and
tighten up your vertical spacing

I am assuming that AddressLine2 and AddressLine3 are under Address1

anyway, you can't check this stuff on the ReportOpen event because every
record may be different... this is easier anyway -- no code

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[doodle]

Thanks so much! That's perfect!

-doodle

 

[doodle]

wait..... maybe not so perfect....

the can grow messes up my other code which adds the lines to the
report. it looks like i am going to have to move the fields after all.
any idea how i can do that?


here is my code for the lines:

Sub DrawLine()
Dim rpt As Report, lngColor As Long
Dim sngTop As Single, sngLeft As Single
Dim sngWidth As Single, sngHeight As Single

' Rectangle #1
Set rpt = Reports!rptCommercialInvoice
rpt.ScaleMode = 3
sngTop = rpt.ScaleTop + 0 ' Left
sngLeft = rpt.ScaleLeft + 2095 'Top
sngWidth = rpt.ScaleWidth - 4530 'Right
sngHeight = rpt.ScaleHeight - 900 'Bottom
rpt.Line (sngTop, sngLeft)-(sngWidth, sngHeight), lngColor, B

' Rectangle #2
Set rpt = Reports!rptCommercialInvoice
rpt.ScaleMode = 3
sngTop = rpt.ScaleTop + 2322 ' Left
sngLeft = rpt.ScaleLeft + 2095 'Top
sngWidth = rpt.ScaleWidth - 1875 'Right
sngHeight = rpt.ScaleHeight - 900 'Bottom
rpt.Line (sngTop, sngLeft)-(sngWidth, sngHeight), lngColor, B

' Rectangle #3
Set rpt = Reports!rptCommercialInvoice
rpt.ScaleMode = 3
sngTop = rpt.ScaleTop + 3543 ' Left
sngLeft = rpt.ScaleLeft + 2095 'Top
sngWidth = rpt.ScaleWidth - 30 'Right
sngHeight = rpt.ScaleHeight - 900 'Bottom
rpt.Line (sngTop, sngLeft)-(sngWidth, sngHeight), lngColor, B

End Sub

Private Sub Report_Page()
Call DrawLine
End Sub

 

[doodle]

I solved it with this: (I'm sure there is a better way, but it works.

Private Sub Report_Open(Cancel As Integer)

If (IsNull(Forms!frmOrderEntry.MachineSN)) Then
Me.MachineSN.Visible = False
Me.lblMachineSN.Visible = False
End If

If (IsNull(Forms!frmOrderEntry.CustPO)) Then
Me.CustPO.Visible = False
End If

Dim myCk As String

myCk = Forms!frmOrderEntry.ckShipTo.Value

DoCmd.Maximize

Select Case myCk
Case Is = "Yes"
Me.lblShipTo.Visible = True
Me.txtShipTo1.Visible = True
Me.txtShipTo2.Visible = True
Me.txtShipTo3.Visible = True
Me.txtShipTo4.Visible = True
Me.txtShipTo5.Visible = True
Me.txtShipTo6.Visible = True
'check to see if the third address line is null
If
(IsNull(Forms!frmOrderEntry.sfrmCustomer.Form.txtAddress3ST)) Then
'check to see if second line is null also
If
(IsNull(Forms!frmOrderEntry.sfrmCustomer.Form.txtAddress2ST)) Then
Me.txtShipTo5.top = 720
Me.txtShipTo6.top = 960
Me.txtShipTo3.top = 1200
Me.txtShipTo4.top = 1440
Else
Me.txtShipTo3.top = 720
Me.txtShipTo5.top = 960
Me.txtShipTo6.top = 1200
Me.txtShipTo4.top = 1440
End If
Else
Me.txtShipTo3.top = 720
Me.txtShipTo4.top = 960
Me.txtShipTo5.top = 1200
Me.txtShipTo6.top = 1440
End If
Case Is = "no"
Me.lblShipTo.Visible = False
Me.txtShipTo1.Visible = False
Me.txtShipTo2.Visible = False
Me.txtShipTo3.Visible = False
Me.txtShipTo4.Visible = False
Me.txtShipTo5.Visible = False
Me.txtShipTo6.Visible = False
End Select

'check to see if the third address line is null
If (IsNull(Forms!frmOrderEntry.sfrmCustomer.Form.txtAddress3)) Then
'check to see if second line is null also
If (IsNull(Forms!frmOrderEntry.sfrmCustomer.Form.txtAddress2)) Then
Me.City.top = 2880
Me.Country.top = 3120
Me.tblCustomer_Address2.top = 3360
Me.Address3.top = 3600
Else
Me.tblCustomer_Address2.top = 2880
Me.City.top = 3120
Me.Country.top = 3360
Me.Address3.top = 3600
End If
Else
Me.tblCustomer_Address2.top = 2880
Me.Address3.top = 3120
Me.City.top = 3360
Me.Country.top = 3600
End If


End Sub

 

[strive4peace]

Hi doodle,

glad you got it! Sorry I didn't write back sooner, have been out of town.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*