Help in acheving to hide fields if null or 0

[matwalker30]

Good evening,

In a quest to make life a little easier for myself I have found Access 2010 !

There starts my problem...

Having never used Access before I have managed to get the data from my excel spread sheet into access. I have then managed to get this into a report which looks exactly as I need it to appear. (as below)

PVM 1-3
Location:Comms room
Function:Controlled access into the comms room
Locking:Abloy EL56
Equipment:1 No. RP3 HID readers
1 No. Door Contact
1 No. Request to Exit
0
1 No. Tamper Relays
EgressVia RTE
Sounder:Local Door Alarm
Control:Controller 1-2
Notes: 0

(it appears better in access) However as you can see there are "0" where there isn't any data. Now using conditional formatting I can get rid of this but im left then with a gap. (as below).

PVM 1-3
Location:Comms room
Function:Controlled access into the comms room
Locking:Abloy EL56
Equipment:1 No. RP3 HID readers
1 No. Door Contact
1 No. Request to Exit

1 No. Tamper Relays
EgressVia RTE
Sounder:Local Door Alarm
Control:Controller 1-2
Notes:


Does anybody know a way of removing the gaps based on whether there is a value in the cell may be either "0" or blank.

I'm a complete newbie at this so baby steps would be appreciated.

Thank you in advance

 

[arnelgp]

you use events on the form. first you need to save the original height of the detail section of the report and the height of the control that you wish to hide, including the height of its label. then based on conditions if it is blank or 0 you can set the controls height to 0 and adjust the height of the detail section.

Dim lngLblHeight As Long
Dim lngTxtHeight As Long
Dim lngDetailHeight As Long

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If (Me.yourControlName & "" = "" Or Me.yourControlName & "" = "0") Then
' adjust height to 0
Me.yourControlLabel.Height = 0
Me.yourControlName.Height = 0
'adjust the height of the detail section
Me.Detail.Height = lngDetailHeight - lngTxtHeight
Else
' reset the height of the detail section
Me.Detail.Height = lngDetailHeight
' reset the height of your controls
Me.yourControlLabel.Height = lngLblHeight
Me.yourControlName.Height = lngTxtHeight

End If
End Sub

Private Sub Report_Load()
' save the heights of detail section and controls
lngLblHeight = Me.yourControlLabel.Height
lngTxtHeight = Me.yourControlName.Height
lngDetailHeight = Me.Detail.Height
End Sub

 

[matwalker30]

Hi Arnelgp,

Thank you for taking the time to reply.

So reading you code makes complete sense but where in events do I add this too ? Do I need to create a new event or do I add it to one of the ones in there already ?

The ones that are in the item don't seem to be relevant

On Click
On Got Focus
On Lost Focus
On Dbl Click
On Mouse Down
On Mouse Up
On Mouse Move
On Key Down
On Key Up
On Key Press
On Enter
On Exit

Or do I goto another property ?


Thanks for your time in answering this.

 

[arnelgp]

its on your report. edit your report in design view. on property sheet, click any event to enter into VBE then delete whatever code is in there except:

Option Compare Database
Option Explicit

then copy and paste the code that i gave you, remember to replace "yourControlLabel" and "yourControlName" with the correct name of the label and textbox control that you want to hide.