Conditional formatting inside a form

[DataRick]

I am trying to set a conditional format for a required field based on the value of another field from the same table. I am trying to do this from the form that inputs the data in to the table.



In other words, if the value of field A = 2, I want the value of the required field to be "yes" or "no" (this is a user input). If the value of field A is not 2, then the value of the required field is null.

I've been experimenting with Iif statements for several hours and I'm at a loss. I am familiar with the basics of SQL but this seems to be beyond my expertise.

Any help would be greatly appreciated!!

[alansidman]

Here is MS tutorial on how to do conditional formatting in a form.

http://office.microsoft.com/en-us/ac...010208133.aspx

Alan

[June7]

Wait! That is not conditional formatting, it is conditional value. This is a data validation issue. It will require VBA code to control the user input or if user is even allowed to do input. Is the required field a text or yes/no type?

[DataRick]

Thank you but that only applies to changing the color of the field. I need to force the user to enter a specified value of yes or no in the required field if the value of field A is 2.

[DataRick]

Yes. The required field is a text field and I want to force the user to enter "yes" or "no" if the value of field A = 2 (field A is not the required field but another field in the same table).

[June7]

First I would control availability of the conditional field. Set the bound control as Enabled (or Visible) No. The in AfterUpdate event of FieldA:

If Me.FieldA = 2 Then
Me.controlname.Enabled = True
Me.controlname.SetFocus
End If

Also use Validation property of the control: "Yes" Or "No"
Once the control has focus, must enter Yes or No. Could be a combobox.

Is this form in Single, Continuous, or Datasheet view? Can users navigate to other records?

[DataRick]

The form is single and users can navigate to other records. I know very little about VBA. I like your idea of controlling the availability of the conditional field though. Can you point me to some step by step instructions for this? I think I might know but, like I said, VBA is relatively new to me.

[June7]

Review this article http://office.microsoft.com/en-us/ac...010341717.aspx

Will also need my suggested code in the form Current event so it will reset the Enabled property when moving to another record.

If Me.FieldA = 2 Then
Me.controlname.Enabled = True
Me.controlname.SetFocus
Else
Me.controlname.Enabled = False
End If

[DataRick]

Thank you June7!! I will work on this this afternoon and let you know how I make out.

[alansidman]

Good catch June. I have been sick this weekend and I must have lost focus. Easy to do. I guess I should stay out of the forums until this passes so I don't mess anyone else up.

[June7]

Hey Alan, is okay, I had to read it 3 times before it sunk in what the issue really was.

[DataRick]

I get this error when moving from one record to the next:
Run-time error '2110':
Rerun Tracker can't move focus to the control PJMfault.

This is my code:
Private Sub Form_Current()
If Me.IMRRPBRR = 2 Then
Me.PJMfault.Enabled = True
Me.PJMfault.SetFocus
Else
Me.PJMfault.Enabled = False
End If
End Sub

Private Sub PJMfault_AfterUpdate()
If Me.IMRRPBRR = 2 Then
Me.PJMfault.Enabled = True
Me.PJMfault.SetFocus
End If
End Sub

Here is what the terminology means:
Rerun Tracker is the name of the database system

IMRRPBRR is a number field in the table. On the form, I have an option box where the user must make 1 of 2 choices. The choices enter a value of 1 or 2 in the IMRRPBRR field. The default value is 1. I previously referred to this as FieldA.

PJMfault is a text field name in the same table. I referred to this as the required field. If the value of IMRRPBRR is 2, I want to require the user to enter a value of "Yes" or "No" in this field.

I'm thinking that I need to add a line in the else statement that sets the focus back to IMRRPBRR. Am I on the right track here?

[June7]

That could be. If the focus was still on that control when moving to new record, it will try to stay there but it has been disabled, hence the error. Try it. Set focus before disabling.

Also, might want to include the Else in the AfterUpdate so that if user unselects 2 the control will again be disabled.

[DataRick]

I'm almost there. Many, many thanks to June7 for all your help!!!

**This determines if PJMfault is visible on an existing record**
Private Sub Form_Current()
If Me.IMRRPBRR = 2 Then
Me.PJMfault.Visible = True
Else
Me.IMRRPBRR = 1
Me.PJMfault.Visible = False
End If
End Sub

**This makes PJMfault visible if IMRRPBRR is changed from 1 to 2 when entering a new record**
Private Sub Frame7_AfterUpdate()
If Me.IMRRPBRR = 2 Then
Me.PJMfault.Visible = True
Me.PJMfault.SetFocus
End If
End Sub

The last thing I need to get working is to force the user to enter yes or no in the PJMfault field when it is visible on the form.

[June7]

Set Validation property to: Yes Or No. Unfortunately, validation probably won't be enforced if user doesn't type in the field. Try code in the LostFocus event of this control.
If Me.IMRRPBRR = 2 And Me.PJMfault & "" = "" Then
MsgBox "Must select Yes or No."
Me.PJMfault.SetFocus
End If