Filter Blank Values 1

[samusa]

Could someone tell me how to make labels invisible if value of the field is blank, in a query or Report.

Sam

 

[fneily]

If Len(txt1.value) = 0 then

 

[PHV]

Are you sure the event is triggered ?
How is txt1.Value changed ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[samusa]

That could be the reason. It is the query that filters "IsNUll" condition. No event is triggered on the report. How can I make Labels invisible in that case....

Sam

 

[PHV]

Sorry, I don't know (and don't want to know ...) how DAP may be dynamically formatted by code.

 

[fneily]

You had us chasing our tails. Now, you say the Is Null is in the query. So ONLY records that have that field empty is getting through to the DAP. Correct? If that is so, then just build another DAP with the query as the source minus the fields you want to hide since they will never be seen. Or I'm not understanding.

 

[samusa]

I have "Comments Label1,Comments Label2,Comments Label3" and Memo fields associated with them. For some students all three are blank ,for Some (say)2 are blank and one has comments. I have 2 queries where criteria is" Not IsNull and IsNull".In Report 1 , I deleted "Comments Label1,Comments Label2,Comments Label3" from DAP Report in Design View But in Report 2, I need to make invisble comment labels if memo field is null. In Report 2 students can have comments in one field , two fields or in all three. When there is comments in "Comments Label2/memo field 2) and other 2 are blank.I want to get rid of these labels in a report. when I used VBA code it worked fine.


Sam

 

[fneily]

when I used VBA code it worked fine." Does this mean you figured it out??? No more problems to make things invisible?

 

[samusa]

Labels got invisible in the Report using VBA code. But when I converted it into DAP Report , all these labels became visble. That is why I was trying use VBscript to get rid of them

Sam

 

[fneily]

Read too quickly. You did say VBA. VBA, VBScript my brain crosses up.
The problem I'm having, and it seems like you too, is that there is no "OnCurrent" event for a DAP. If you look at the events under Window in the Script Outline, that's like selecting the form in Access, I don't see any event to program for the current record.
So what to do? You say you "NEED" to hide the fields if they are null. Can you Default the memo field in your tables to something like "No comments for now." This way, the user won't see a blank field but that no comments were entered.
How to avoid them being updated? You can lock the fields. The option is ContentEditable = False.
If they wanted to add comments, you could create a command button, "Add Comments", that when clicked, the code would make them editable - ItemNumber.ContentEditable = True

Just a thought. I'm stuck on how to make the fields invisible when a record is displayed.

If you come up with anything, please post. This obviously could be helpful. Also, I'll keep searching.

 

[samusa]

Yes this is better option for the time being. Thanks

Any idea how to get rid of Expand/Collapse(+)(-) control in DAP Report. I have grouped the data on StudentID and (-) prints on entire report.

Thanks once again for your help and I really appreciate it.


Sam

 

[samusa]

Ok .. I figured it out. Just changed the property of Collapse(-) control to hidden.

Sam

 

[fneily]

I was going to say just click on it in design view and delete it. Hiding it is probably better in case you want to use it again.

 

[samusa]

thanks...

Sam

 

[fneily]

I got it!!
Ok. Go to Microsoft Script Editor. On the bottom tabs, click on SOURCE. On the menu bar click HTML, Script Block, select Client on second menu. In between the Sub and
End Sub, place the following:
<SCRIPT language=vbscript>
Sub FixEnabling
' Hiding/showing a field
If Len(ParcelNumber.value) = 0 Then
ParcelNumber.style.visibility = "hidden"
Else
ParcelNumber.style.visibility = "visible"
End If
End Sub
</SCRIPT>
Obviously change the control name to yours and you can add statements for the label.

Now under Script Outline on the left, scroll down to MSODSC, expand it and double click on CURRENT. Type in FixEnabling between the Sub, End Sub.
Now here's the trick, add (info) to event=Current, so it reads event=Current(info)

Give it a try. As you change records, the control becomes visible or invisible depending on the data.

We did it, Big Sam!

 

[samusa]

Great job! However I couldn't get it resolved yet. "Go to Microsoft Script Editor. On the bottom tabs, click on SOURCE. On the menu bar click HTML, Script Block, select Client on second menu." When I open Microsoft Script Editor, I can see Design Button and DISABLED HTML button on the bottom of the window. I guess disabled HTML button indicate HTML view is active. Anyways, Neither I can see "Sub and End Sub" in the source nor there was "Client" option on the second menu bar.
So I tried following but ended up with script errors. I put following code in script block

<SCRIPT language=vbscript>
<!--
Sub FixEnabling
If Len(txtN1.value) = 0 Then
L5.style.visibility = "hidden"
Else
L5.style.visibility = "visible"
End If
End Sub
-->
</SCRIPT>
Then I clicked on MSODSC->current and put

<SCRIPT language=vbscript for=MSODSC event=current(info)>
<!--
Sub
FixEnabling
End Sub
-->
</SCRIPT>



Sam

 

[fneily]

First, and it's my mistake, for the MSODSC event, take out the SUB and END SUB. It should just be FixEnabling.
Also, I'm working in Access 2000. So you are probably more up to date with your applications and thus we have different view. When you go into the script editor, at the bottom, is there a tab that says HTML OUTLINE? When you click on the HTML button, does HTML appear on the top menu bar? If it does, expand the menu, and see if SCRIPT BLOCK is there. Then see if CLIENT is there.
Right now, I don't have access to any higher version.

 

[samusa]

On the Left side of the window , I have two buttons HTML Outline and ScriptOutline. When I click on HTMLOutline button, It displays page.htm->MSODSC-> <SCRIPT> -> +BODY. But can't see Client there.I double checked with Northwind file as well but could not see Client. Any Idea

Sam

 

[fneily]

After you click HTML Outline, on the MENU BAR do you see HTML? It's a menu. When you click on the name HTML, the menu should expand and you should see Script Block. Also, have you tried it with the delete Sub and End Sub? So
<SCRIPT language=vbscript for=MSODSC event=current(info)>
<!--
FixEnabling
-->
</SCRIPT>

 

[samusa]

I tried it but did not work. It generates script error when I put
<SCRIPT language=vbscript for=MSODSC event=current(info)>
<!--
FixEnabling
-->
</SCRIPT>

I am still trying to figure out how to get HTML Menu bar

Sam

 

[fneily]

I just don't have your version of Access. Also, it's not an HTML menu bar. It's a selection on the menu bar -
File Edit View Debug HTML Table etc.

I don't know what your screen looks like.