hide control in continuous form (1 Viewer)

[Mr.K]

I have a continuous form on which I would like to hide a command button based on the value of a text field ("Notes") within the same record. I'm very new to VBA but so far was able to find the following partial solution:

I have the following happen OnLoad for the form:

Private Sub Form_Load()
    If IsNull(Me.Notes) Then
        Me.my_button.Visible = False
    End If
End Sub

The problem is that once the form loads all the "my_button"(s) in every record become invisible or visible based, I guess, on the value of the "Notes" in the first record.
I found lots of threads speaking about this issue and also visited http://lebans.com/conditionalformatting.htm
The closest solution was the table "ShowHideControls" on the site mentioned above, but due to my lack of knowledge in VBA I wasn't able to translate/understand his code well enough to apply to my example. His table loads text on the button if the checkbox is ON - I guess this could be somehow translated to: "my_button appears if the Notes is Null" in my db.
Can someone please help?

 

[Jibbadiah]

Can you explain why you want to do this?

If it is just a matter of giving them extra information to help them type in notes, or something similar... then you could use an 'if' condition on opening the form and then open a message box...

A little more information may give you a better solution to the one you were thinking of.

 

[Rich]

Your code belongs in the OnCurrent event and even then it's not that satisfactory

 

[Mr.K]

Why do I want to do it..

Each record in my continuous form has a hight of one line. However, the last field which is "notes" in order to make any sens for display purposes should be at least couple of lines high. But very few records actually do have a value in notes so I don't want to adjust the whole layout (increase the row hight) just to accomodate that field. I'd rather not have this field show at all if the value is null and if there is a value then I'd want a control to appear (or better a link "view note") which upon clicking would display the contents of the "notes" in a read only mode.
By the way, that's another chalange which I was going to start a new post for, but I ask here anyway since the solution to the whole issue may possibly incorporate it all: How can I display the content of the notes upon clicking on it? I used a macro to send keys for a zoom preview but that's confusing to the user because once the zoom window opens user can actually type in it even though the field is read only.

To better illustrate my problem I created the following 3 images:

1. line_height_row.jpg (http://www.mkgroup.us/mk_contents/temp/access/line_height_row.jpg)
This is how I have it now, which doesn't really allow to read the "note" without scrolling anoyingly small text box.

2. ugly_long_row.jpg (http://www.mkgroup.us/mk_contents/temp/access/ugly_long_row.jpg)
I could simply increase the row height but since only very few records do have the note, I'm wasting space and the layout becomes uglier.

3. goal.jpg(http://www.mkgroup.us/mk_contents/temp/access/goal.jpg)
If the link or a button could appear for the row where the note does exist and it would open a preview window (if message box how can I format a nice one?) with the note's content - that would be perfect. How can I do that? is my problem. My first post mentiones that I was able to hide/show control based on the note being Null upon the form Load, but unfortunately that shows/hides all the buttons for all rows depending on the value of a single record (I assume the first one listed).


Help is greatly appreciated.

 

[Rich]

Use the built in Zoom box feature to display larger amounts of text

 

[Mr.K]

Use the built in Zoom box feature to display larger amounts of text

I'm not sure if I'm missing something but if you want me to simply press SHIFT+F2 or send those keys through a macro executed on click on Notes, then I already mentioned in my post that the zoom box isn't a good solution because:
upon opening the user can type in the text "thinking" that the note is editable - which is not true since the field is read only. Also, I do not want to display the field Notes if it has a null value.
God someone help me

 

[Bat17]

How about a popup form linked to the original record to display the text?

Peter

 

[AngelsGuardian]

First.. make a button and in the "Conditional formating" make it that if there is a value in the note field..it shows the button.

Second
Link your button to a pop-up window with all info and make all field
Locked = Yes
Enabled = No

 

[Mr.K]

First.. make a button and in the "Conditional formating" make it that if there is a value in the note field..it shows the button.

Second
Link your button to a pop-up window with all info and make all field
Locked = Yes
Enabled = No

Thanks for all the suggestions. However, this still won't work. First of all, if I right click on a button there is no "Conditional Formatting" available (like in the case of a text box). Therefore, I tried to initiate some code but the problem is when and where? As I said in my first post if I do it on the form "active" or "load" then the buttons for all records become visible or invisible based on the value of the "note" of the first record in my continuous form. And I can't call the code on any other individual record instance (e.g. the record gets focus) because I want those buttons to show the minute the form appears and not after the user focused on the record.

 

[Rich]

I've already told you the only event you can use is The FormCurrent event.

 

[Mr.K]

I've already told you the only event you can use is The FormCurrent event.

I tried that as well but the problem stays the same: The button appears or disappears for all records in the continuous form and its visibilty is always based on the first record.
I want the button to appear only for the records that have something in the note, so the user knows there is a note and can click the button to view it if he wishes.

Thanks for all the replies - they all teach me something new. This forum saved me from shooting my screen many times

 

[Mr.K]

new ideas

So I'm still working on this one and figured some workarounds (rather ugly I'd say) based on another post. Maybe it will serve as a springboard for someone to find the final solution to this problem.

New idea:
Instead of a button for every record I created a text field with source: =IIf(IsNull([notes]),"","View Note")

Then styled the text field to look like a link. (I tried setting Hyperlink property to Yes but that did not convert my "View Note" to a link). Then "onClick" event triggers an "openform" with the full content of the note in a new window. However, the following are the issues still:

1. Cursor doesn't change to a hand (like with a real link)
2. The fake "View Note" link still looks editable because if I set Enable to No then I cannot trigger the onClick

Any thoughts?


PS. Thanks to the following thread: http://www.access-programmers.co.uk/forums/showthread.php?t=80773&highlight=change+label+condition

 

[Bat17]

2) I think that you can use condition formating to control Enabled on a text box.

Peter

 

[Jibbadiah]

Put a <space> in hyperlink subaddress.

I use this to give the appearance of a hyperlink, but then create an on-click event instead.

Once the on-click event starts I pop-up a message saying... something like "opening... please wait" (okonly). Then docmd.hourglass false to remove the hand.... and then continue from there.

J.

 

[Mr.K]

Put a <space> in hyperlink subaddress.

I use this to give the appearance of a hyperlink, but then create an on-click event instead.

Once the on-click event starts I pop-up a message saying... something like "opening... please wait" (okonly). Then docmd.hourglass false to remove the hand.... and then continue from there.

J.

Thanks. However, like I said even though I check Is hyperlink property to YES i still don't have a property with hyperlink subaddress. It normally should be under properties, right?

 

[Mr.K]

2) I think that you can use condition formating to control Enabled on a text box.

Peter

Can anyone tell me how? I read various pages on conditional formatting and wasn't able to figure out where to insert the vba.
And ff I try to initiate the code on any event (either oncurrent, onLoad) I'm back to square one where ALL buttons change at once and that doesn't help in my continuous form, since they have to change on per record basis.

 

[SunWuKung]

I've done something similar to this here:
http://www.access-programmers.co.uk/forums/showthread.php?t=91784

hth
SWK

 

[Bat17]

Condition Formating works on field value not VBA. In form design got to your new field, then select conditional formating from the menu.
You then need "Field Value is" "equal to" "View Note"
and to the far right is a button to toggle enabled/disabled

As far as I know you cannot work with buttons individualy on a continous form.

Peter

 

[Jibbadiah]

Sorry Mr K... I realise my mistake.

I use a label instead of a text box.
Using a label gives a property of hyperlinkaddress... put a space in there.

J.

 

[Mr.K]

The problem is that I cannot use a label becasue the only thing I was able to show/hide on a per row basis in a continuous form was a text a textbox.

In the end I think this is the final solution I'll use for the problem posted in my first post:

1. Create a textbox with source: =IIf(IsNull([note]),"","View Note")
2. style the textbox so it looks like a link
3. put a button on top of it so it covers the fake link and make the button transparent
4. Onclik write some vba to open a form with the full content of the note. Add an IF in the vba so nothing happens if the button is clicked accidently on a row that has an empty note.