conditional control formatting

[xzyragon]

Hey guys,

I researched around to try and find out how to do this, but I coudln't find anywhere, and I just started using VBA.

Anyways, I'm making a form for a database for patients, and I made 3 controls: Biopsy Date 1, Biopsy Date 2, and Biopsy Date 3. I'm making the form so that the entire hospital staff can enter data into it, and I wanted to make it look more streamlined (impress the boss...) and make is so that Biopsy Date 2 and Biopsy Date 3 didn't show up if Biopsy Date 1 was a null value.

Here's the code I have so far, but it's not working... (I'm just trying to get 2 to be hidden... Once I get this down, I'm sure adding Biopsy Date 3 will be a piece of cake).



Option Compare Database
Public Sub Biopsy_Multi_Hide()
If Nz(Me![Biopsy Date #1], [ValueIfNull]) = [] Then
Me![Biopsy Date #2].Visible = False
Else
Me![Biopsy Date #2].Visible = True
End Sub

Thanks for your help!

[orange]

If IsNull(Me![Biopsy Date #1] Then
Me![Biopsy Date #2].Visible = False
Me![Biopsy Date #3].Visible = False
....

How do you plan on running this sub?

If this is a single form, then you could use the OnCurrent event (I think UNTESTED)

You should rethink the names of your fields/controls.
You should adopt a naming convention with no spaces and no special characters in field and object names.

[xzyragon]

If IsNull(Me![Biopsy Date #1] Then
Me![Biopsy Date #2].Visible = False
Me![Biopsy Date #3].Visible = False
....

How do you plan on running this sub?

If this is a single form, then you could use the OnCurrent event (I think UNTESTED)

You should rethink the names of your fields/controls.
You should adopt a naming convention with no spaces and no special characters in field and object names.

Yeah, I realized the names were really weird, especially with the # symbol. I can have the control have a different name than what appears on the actual form, right? I was just keeping the name exactly as it appears on the form because this is my first time using VBA for access, and I was just trying to diagnose why it was working. I think part of my problem is that I don't know how to run the sub. If I have this written in a module, does it not automatically run?

[orange]

No it doesn't run automatically.

You should research vba procedure and access events.
Here are some links
http://www.accessmvp.com/strive4peace/
http://www.functionx.com/

[xzyragon]

thanks for the links. I read through them, and I got this to work


Private Sub Biopsy_Date__2_AfterUpdate()
Option Compare Database
Private Sub Form_Load()
If IsNull(Me![Biopsy Date #1]) Then
Me![Biopsy Date #2].Visible = False
Me![Biopsy Source #2].Visible = False
Me![Biopsy Result #2].Visible = False
Me![Biopsy Date #3].Visible = False
Me![Biopsy Source #3].Visible = False
Me![Biopsy Result #3].Visible = False
ElseIf IsNull(Me![Biopsy Date #2]) Then
Me![Biopsy Date #3].Visible = False
Me![Biopsy Source #3].Visible = False
Me![Biopsy Result #3].Visible = False
Else
Me![Biopsy Date #2].Visible = True
Me![Biopsy Source #2].Visible = True
Me![Biopsy Result #2].Visible = True
Me![Biopsy Date #3].Visible = True
Me![Biopsy Source #3].Visible = True
Me![Biopsy Result #3].Visible = True
End If
End Sub


My only question is that this applies the settings to the entire form. Is there any way to make it person-specific? Like if patient1 has a biopsy date 1 and 2, and patient2 has only biopsy date 1, I don't need the superfluous stuff showing for patient2...

[geekgirlau]

Rather than adding the code to the OnLoad event, add it to OnCurrent so it runs every time you move to a new patient.

By the way, this data is not normalised. It would be much better to have a separate table containing the patient ID, and the biopsy date. This way you're not restricted to any specific number of biopsies.