Hide fields or disable edits 1

[iamtrying]

Hello, I as using a sub-form to capture historical data. There are five fields I am dealing with
Last_Biennial, Next_Biennial, Recertification, Re-Eval, and Letter_Sent. If the Next_Biennial field is populated with a date, then I would like for the fields Recertification and Re-Eval to be hidden or editing disabled. The idea is to prompt the user to add a new record for Recertification or Re-Eval. The same is true if the Recertification field is populated with a date; The Last_Biennial, Next_Biennial, Re-Eval fields should be hidden or editing disabled. Here is some code which is working correctly for Last_Biennial & Next_Biennial. The code for Recertification and Re-Eval works when I place the cursor in each field and enter data, but if I navigate from the record and return, all of the fields are visible and can be edited.

I amtrying

Option Compare Database
Private Sub Form_Current()
Recertification.Visible = True
Recertification_Label.Visible = True
Re_Eval.Visible = True
Re_Eval_Label.Visible = True
If Next_Biennial > #1/1/2001# Then
Next_Biennial.Visible = True
Label19.Visible = True
Last_Biennial.Visible = True
Label18.Visible = True
Recertification.Visible = False
Recertification_Label.Visible = False
Re_Eval.Visible = False
Re_Eval_Label.Visible = False
Combo22.Visible = True
Time1_Label.Visible = True
End If
End Sub

Private Sub Form_Load()

End Sub

Private Sub Last_Biennial_AfterUpdate()
Next_Biennial = DateAdd("yyyy", 2, Last_Biennial)
End Sub

Private Sub Re_Eval_AfterUpdate()
If Re_Eval > #1/1/2001# Then
Recertification.Visible = False
Recertification_Label.Visible = False
Last_Biennial.Visible = False
Label18.Visible = False
Next_Biennial.Visible = False
Label19.Visible = False
End If
End Sub

Private Sub Re_Eval_GotFocus()
If Re_Eval > #1/1/2001# Then
Recertification.Visible = False
Recertification_Label.Visible = False
Last_Biennial.Visible = False
Label18.Visible = False
Next_Biennial.Visible = False
Label19.Visible = False
End If
End Sub

Private Sub Recertification_AfterUpdate()
If Recertification > #1/1/2001# Then
Re_Eval.Visible = False
Re_Eval_Label.Visible = False
Last_Biennial.Visible = False
Label18.Visible = False
Next_Biennial.Visible = False
Label19.Visible = False
End If
End Sub

Private Sub Recertification_GotFocus()
If Recertification > #1/1/2001# Then
Re_Eval.Visible = False
Re_Eval_Label.Visible = False
Last_Biennial.Visible = False
Label18.Visible = False
Next_Biennial.Visible = False
Label19.Visible = False
End If
End Sub

 

[Remou]

I think you must look at Conditional Formatting, in particular, Expression. It will allow you to disable controls based on the value of another control.

 

[iamtrying]

Remou

Conditional formatting does not allow me to achieve the goal through Expression. Could you give an example.

 

[Remou]

It seems to me, from what you have said, that you have a continuous form. If this is true, conditional formatting is what you need. If you select, say, the Re_Eval control and enter as 'Expression Is':

[tt][Recertification]>#2007/12/01#[/tt]

And then choose the enabled/disabled option, you should find that it is not possible to enter data in the Re_Eval control where the Recertification field meets the above criterion. (It is likely that Access will re-order the date, but the above format makes dates simpler to enter.)

 

[iamtrying]

Remou

I followed your instructions and the form is working as planned. It is not allowing the user to edit existing populated fields which will prompt them to enter a new record. I understand that they can change the data in the fields which will remove the conditional formatting, but it is always good to have a way out.

Very handy

 

[Remou]

I will just add that some bright spark will assume that the prompt is to change the date, not to add a new record. It is best to tell the user when they try to edit the date that they should create a new record.

 

[iamtrying]

You are correct. I have included your exact thoughts in the user instructions.