DAta Validation in a field based on the contents of another field

[Guest]

I have what I suspect is a very simple problem. On my form is a pick list
containing 3 values (set up as a row source type of Value List). The values
are Pending, Open and Closed. What I need to be able to do is make sure that
if the status is Closed that the Date Closed field is not null. How do I do
this ??

 

[Graham Mandeno]

Hi Jo-Anne

Use the Form_BeforeUpdate event procedure to check the values of the fields
and, if necessary, cancel the update event and ask for the required data.
Something like this:

If cboStatus = "Closed" and IsNull(DateClosed) Then
Cancel = True
DateClosed.SetFocus
MsgBox "Please specify the date closed"
Exit Sub
End If

 

[Guest]

Thanks much Graham,

I'll give that a try right now !!!

 

[Guest]

Graham,

I tried the routine you suggested and there is a small problem. The
debugger won't let me use the setfocus command until after the recod has been
saved and if I remove the set focus command, I can't leave the Status
control. How do I tell the code to Tab once (which will set the focus on the
DateClosed control).

 

[Guest]

Graham,

Obviously my reading comprehension skills need work. I did not notice the
Form_before update vs Control_before update. My apologies !!

I do, however have another question - if, on the same control, the status is
not closed and the date closed is not null (lets say someone revised the
status after previously closing), what code do I need to delete the contents
of the date closed field, and where in the previous If statement does it
belong ??

 

[Guest]

Graham,

My apologies for my previous replies - I did not read properly the words
"Form_beforeUpdate" and I was attempting the code you suggested in the
Control itself. - Not good, not good !!

I have now rectified this and it works fine - thanks much. I have another
question though (which I promise to read the response to more carefully). If
a user changes the status from Closed to either Open or Pending, I need to be
able to remove the Date Closed info as it is no longer valid - how do I do
this ?? If I need to add it to the Form_beforeUpdate code, where in the
previous If statement does it belong ??

 

[Graham Mandeno]

Hi Jo-Anne

My apologies for my previous replies - I did not read properly the words
"Form_beforeUpdate" and I was attempting the code you suggested in the
Control itself. - Not good, not good !!

No worries! It's easy to miss things on the first reading - everyone does
it sometimes

I have now rectified this and it works fine - thanks much. I have another
question though (which I promise to read the response to more carefully).
If
a user changes the status from Closed to either Open or Pending, I need to
be
able to remove the Date Closed info as it is no longer valid - how do I do
this ?? If I need to add it to the Form_beforeUpdate code, where in the
previous If statement does it belong ??

For this you should use the AfterUpdate event of the status combobox:
If cboStatus<>"Closed" then DateClosed = Null

 

[Guest]

Graham,

Thanks much - that was too easy !!!

Graham,

I tried the routine you suggested and there is a small problem. The
debugger won't let me use the setfocus command until after the recod has been
saved and if I remove the set focus command, I can't leave the Status
control. How do I tell the code to Tab once (which will set the focus on the
DateClosed control).