Field to field validation

[Swordfish]

Hello,

I have a main form with number field (Work Type, Work Category, Status,
Client ID, Review Score, etc.

I am trying to prevent the Review Score field from being filled in until the
Status field is filled in. Status is a combo box with four choices -
Complete, Complete - No Chg, Open and OnHold. Complete and Complete - No
Chg. need to be filled in before the Review Score is entered and if not a
msgbox popup requesting to fill in Status before entering a review score. My
current coding is below but it does not seem to be working. Would select
case be an option?

Thanks for the assistance.

Private Sub Review_Score_BeforeUpdate(Cancel As Integer)
Me.Review_Score.Disabled = IsNull(Me.Status) Then
MsgBox "You must select the 'Status' of the change before entering a
score"
Cancel = True
End If

End Sub

 

[Jeanette Cunningham]

Hi Swordfish,
in the form's property sheet, set the enabled property for review score to
No.

In the after update event for Status, have code to enable the review score
text box.
If Not IsNull(Me.Status) Then
Me.Review_Score.Enabled = False
End If

Put a label with "You must select the 'Status' of the change before entering
a
score", just above the review score text box.

You will also need code in the form's current event, to check if a value for
status exists for the current record. If the status is missing, then set
Enabled = False for Review score.
The current event fires every time the form moves to a different or new
record.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Linq Adams via AccessMonster.com]

I know you're 'down under' Jeanette, but shouldn't that be

If Not IsNull(Me.Status) Then
Me.Review_Score.Enabled = True
End If

 

[Swordfish]

I placed the following coding in two different places as directed

Private Sub Status_AfterUpdate()
If Not IsNull(Me.Status) Then
Me.Review_Score.Enabled = True
Else
Me.Review_Score.Enabled = False

End If

End Sub

But help me with Put a label with "You must select the 'Status' of the
change before entering a score", just above the review score text box?

I would like to have some sort of message that says to the user trying to
enter data, you must first select the Status but can I do this with some sort
of msgbox?

Let me know

Thanks

 

[Jeanette Cunningham]

I personally like a label, because the user doesn't need to click anything
like they need to click OK to close a msgbox.
I think ghosted (disabled) controls do suggest to the user that if they do
something on the screen, the ghosted control will spring to life and work
normally.
That's why I like to put a label just above or below the ghosted control -
user spots the ghosted control and reads the label.
You can see this behavior of ghosted controls in outlook 2007 when doing
appointments on the calendar.

When do you want the message box to pop up? Users may not enter data in
the controls by starting at the top and working through in any particular
order, so it is difficult to know when to show the msgbox.

Perhaps you could just lock review score.
When it gets focus, check for a value for status, and if not found, pop up a
message about entering status and after status is entered, unlock review
status.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Jeanette Cunningham]

Good catch, thanks Linq.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[John W. Vinson]

That's why I like to put a label just above or below the ghosted control -
user spots the ghosted control and reads the label.

You're lucky... your users actually READ labels!?

<g, d & r>

 

[Swordfish]

Thank you for responding,

Your second suggestion of Locking the Review Score, get focus with the
msgbox seems to be a good option. Can you give me some direction on this?

Let me know

Thanks

 

[Jeanette Cunningham]

They most certainly do.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Jeanette Cunningham]

Locked and not locked are similar to enabled and disabled, the difference is
that you can't click into a disabled control.
With locked, the user can click into it, but can't edit it.

The code below should be all you need.
Whether review score is locked or not depends entirely on the value for
Status.
You shouldn't need code in the form's current event or the after update
event for status.

Private Sub Review_Score_Enter
If IsNull(Me.Status) Then
Me.Review_Score.Locked = True
Msgbox "The message"
Else
Me.Review_Score.Locked = False
End If
End Sub


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia