Txt boxes on a report to hide when nothing is in them

[manix]

Hi,

I have a line of text boxes on a report that report values via a query. Now sometimes the report may not have any values to display, but the report must still be produced and printed.

I have 5 text boxes that, if the query does not return any results, I want these txt boxes to not be visible and instead, a message is displayed along the lines of "No items to report this month".

The txt boxes are contained within the detail section of the report, and this is continuous. At present I just get 5 blank text boxes in one row when the query does not return any data.

Can anyone help?

I have tried this on the detail event "on format"

If IsNull(TextBoxThatReceivesValue) Then
Me!TextBoxThatReceivesValue.Visible = False
Else
Me!TextBoxThatReceivesValue.Visible = True
End If

but doesn't seem to work and i don't know where else to put it?!!

Thanks,

 

[Rabbie]

Try this

If IsNull(TextBoxThatReceivesValue) Or TextBoxThatReceivesValue = ""  Then
Me!TextBoxThatReceivesValue.Visible = False
Else
Me!TextBoxThatReceivesValue.Visible = True
End If

 

[manix]

Try this

If IsNull(TextBoxThatReceivesValue) Or TextBoxThatReceivesValue = ""  Then
Me!TextBoxThatReceivesValue.Visible = False
Else
Me!TextBoxThatReceivesValue.Visible = True
End If

Thanks Rabbie, but if I use this code, I get an error that says:

Run time error '2427': You entered and expression that has no value

The debugger highlights the first line of the code If IsNull(TextBoxThatReceivesValue) Or TextBoxThatReceivesValue = "" Then

 

[Rabbie]

Hi

I have just tested this and it is working for me as posted. It hides the text box if it is empty but displays it when it has data.

try this change

If IsNull(me!TextBoxThatReceivesValue) Or ME!TextBoxThatReceivesValue = ""  Then
Me!TextBoxThatReceivesValue.Visible = False
Else
Me!TextBoxThatReceivesValue.Visible = True
End If

If you still have problems it could be worth posting a cut down version of your DB so we can look at it

 

[manix]

Nope, the code just doesn't seem to see whether the text box contains any data or not. It can see the txt box, because if I simply enter code that says on open, txtbox.visible = False it disapears on open!!! Just does not seem to be able to tell if it contains data or not.

I think it is best to tackle it at the query level, but that is beyond me, I.e. how do I build an expression that says if this filed returns no data, hide the text box on the report??

Another way out, could be to say, if the query returns no results, show a label on the report that says so. But I do not now what the code would be to look at the query results and when this should be looked at (I.e. on the report open event?)



I will try and cut down a version, but it is all a bit complex, when one things chopped out it may lose something you guys would need to know!!

 

[Rabbie]

The code I posted works - I have tested it. The error message you got suggests you may have a typo when altering the textbox name to make it fit. Can you post what you have put into the On Format event.

 

[manix]

I have a number of text boxes that need to be hidden if they don't contain data and I have tried it on all of them, they all return the same error!

The code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(Me!Concern_Details) Or Me!Concern_Details = "" Then
Me!Concern_Details.Visible = False
Else
Me!Concern_Details.Visible = True
End If
End Sub

 

[Rabbie]

Please post this part of your DB so I can see why it doesn't work for you. Your approach should work

 

[gemma-the-husky]

if your data is coming from a table join showing all items from one side, and only where equal (left join?) you may not have a null or a zls - you may have nothing at all - hence your message


so you can error trap this (and write your test a bit more efficientlym by using nz function)

try this

     on error goto skip
     textBoxThatReceivesValue.visible= nz("TextBoxThatReceivesValue",vbnullstring)

exithere:
    exit sub 'might be exit function

skip:
    'trap the error
    'display the err if you are interested
    resume exithere

 

[manix]

if your data is coming from a table join showing all items from one side, and only where equal (left join?) you may not have a null or a zls - you may have nothing at all - hence your message


so you can error trap this (and write your test a bit more efficientlym by using nz function)

try this

     on error goto skip
     textBoxThatReceivesValue.visible= nz("TextBoxThatReceivesValue",vbnullstring)

exithere:
    exit sub 'might be exit function

skip:
    'trap the error
    'display the err if you are interested
    resume exithere

Thanks for all your input, i have still not managed to define this problem, I think it maybe as gemma as said, no data or null values are appearing in the text, I am afraid I don't quite understand the above code. I am not 100% up to speed on VB sorry!!!

I have tried breaking down what I can, but whatever I do renders the DB useless, there is so many dependancies in there!!!!!!!

 

[chergh]

Try something like:

dim strRecSource as string

strRecSource = me.recordsource

if currentdb.OpenRecordset(strRecSource).RecordCount = 0 then
   Me!Concern_Details.Visible = False
Else
   Me!Concern_Details.Visible = True
end if

You might also get what you are wanting by using the canshrink property.

 

[manix]

Try something like:

dim strRecSource as string

strRecSource = me.recordsource

if currentdb.OpenRecordset(strRecSource).RecordCount = 0 then
   Me!Concern_Details.Visible = False
Else
   Me!Concern_Details.Visible = True
end if

You might also get what you are wanting by using the canshrink property.

Chergh, you are legend! Thank you.

That worked straight off and I understand why, so thanks Rabbie, Gemma and Chergh! Excellent. I did try the Can Shrink property, and that worked but it meant that all the different text boxes would shrink to the size of the text contained and it looked untidy!!!

The code that now works:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim strRecSource As String

strRecSource = Me.RecordSource

If CurrentDb.OpenRecordset(strRecSource).RecordCount = 0 Then
   Me!Concern_Details.Visible = False
Else
   Me!Concern_Details.Visible = True
End If
If CurrentDb.OpenRecordset(strRecSource).RecordCount = 0 Then
   Me!Supplier.Visible = False
Else
   Me!Supplier.Visible = True
End If
If CurrentDb.OpenRecordset(strRecSource).RecordCount = 0 Then
   Me!Part_Number.Visible = False
Else
   Me!Part_Number.Visible = True
End If
If CurrentDb.OpenRecordset(strRecSource).RecordCount = 0 Then
   Me!Month.Visible = False
Else
   Me!Month.Visible = True
End If
If CurrentDb.OpenRecordset(strRecSource).RecordCount = 0 Then
   Me!lbltext.Visible = True
Else
   Me!lbltext.Visible = False
End If
End Sub

PS - Chergh, you're not the same Chergh that used to frequent the Elsmar Cove Forums are you? Just a thought!

 

[chergh]

Glad it worked and yep I'm the same chergh