Hiding fields based on data in other fields

[mkleino]

I have a continuous form that lists Academic Progress for medical students as well as hospital residents. I'd like to make it so that certain fields (such as "Program") only appear as visible when another (Status) field lists one of four things (such as Resident). I want the visibility of the Program field to always reflect the data in the Status field, so that it instantly disappears or reappears depending on what Status is chosen. In this manner I hope to deter people from entering incorrect data, as well as reduce the appearance of blank spaces in the form.

I would greatly appreciate any insight about how to accomplish this.

Thanks very much,
-Mike

 

[Laocon]

Can't you set the AfterUpdate event on the Status control to something like

If Me!Status = "Resident" Then
Me!ProgramTextLabel.Visible = True
Me!ProgramEntryBox.Visible = True
Endif

 

[mkleino]

I tried, but that doesn't work--it just makes it so that everything in the columns disappears after I go to the next line. OnCurrent does the same thing, but when I'm on that record rather than afterwards. I just want it to apply for each line individually (a line being one record in the continuous form).

 

[mkleino]

Ok, I figured out what I needed to do, partially by looking at other posts in the forum. I don't think it's possible to make just one line disappear, and I want the other lines to always be visible, so I just used the Locked property instead of Visible. I made a private subfunction that sets a boolean variable to true or false based on the Status, and sets the Program and a couple other fields' Locked properties to the boolean variable. Then I called that subfunction from the OnCurrent event of the form, as well as the OnChange event of the Status field. Now it works as well as I'm going to get it, I think. Thanks for the suggestions.

-Mike

 

[mkleino]

Alright, the way I just mentioned of doing it ended up having some problems--there were situations where it didn't work. So, I came up with a foolproof solution:

Option Compare Database
Option Explicit
Dim Statuslock As Boolean

Private Sub Program_GotFocus()

CheckStatus
Me.Program.Locked = Statuslock

End Sub

'Modify and reuse the above sub for each
'field you want to lock.

Private Sub CheckStatus()

'Criteria for whether to lock fields
If (Me!StatusKey = 2 Or Me!StatusKey = 3 Or Me!StatusKey = 10 Or Me!StatusKey = 11) Then
Statuslock = False
Else: Statuslock = True
End If

End Sub