Field Validation

I have 2 fields one completed date and the other a combo box with 4
headings, completed is one of them

If completed is selected and the date hasnt been filled in I would
like to have a message box saying that if "Completed" is selected then
a date must be filled in, and go back to that control.

I have tried:

Private Sub Status_Exit(Cancel As Integer)
If Status = ("Completed") And CompletedDate = IsNull Then
Beep
MsgBox "Completed date must be filled in"
DoCmd.GoToControl (CompletedDate)
Exit Sub
End If
End Sub

But it just chucks up error messages, what am I missing here, keep
changing it around and getting no where.

Please help - thanks

Elaine

Re: Field Validation

Hi Elainie,

It sounds like you are selecting something from a combobox and if a
textbox called "CompletedDate" is empty then you want to notify the user
and have the user fill in the textbox with a date. Anyway, my example
will be based on this scenario. I will also use naming conventions for
the controls. A textbox name is usually preceded by txt
as in txtCompletedDate and combobxes are preceded by cbo
as in cboTest

In the AfterUpdate event of the combobox you can do something like this:

Private Sub cboTest_AfterUpdate()
If txtStatus = "Completed" And txtCompletedDate = "" Then
txtCompletedDate.SetFocus
MsgBox "Completed date must be filled in"
End If
End Sub

The combobox AfterUpdate event occurs after a user selects something
from the combobox. Note that I also removed the parentheses from
txtStatus = "Completed". This is the correct syntax. And I set the
focus on txtCompleted before the MsgBox. It doesn't matter if you
SetFocus before or after. I just usually do it before the MsgBox.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Field Validation

On Nov 13, 6:04 pm, Rich P wrote:
> Hi Elainie,
>
> It sounds like you are selecting something from a combobox and if a
> textbox called "CompletedDate" is empty then you want to notify the user
> and have the user fill in the textbox with a date. Anyway, my example
> will be based on this scenario. I will also use naming conventions for
> the controls. A textbox name is usually preceded by txt
> as in txtCompletedDate and combobxes are preceded by cbo
> as in cboTest
>
> In the AfterUpdate event of the combobox you can do something like this:
>
> Private Sub cboTest_AfterUpdate()
> If txtStatus = "Completed" And txtCompletedDate = "" Then
> txtCompletedDate.SetFocus
> MsgBox "Completed date must be filled in"
> End If
> End Sub
>
> The combobox AfterUpdate event occurs after a user selects something
> from the combobox. Note that I also removed the parentheses from
> txtStatus = "Completed". This is the correct syntax. And I set the
> focus on txtCompleted before the MsgBox. It doesn't matter if you
> .SetFocus before or after. I just usually do it before the MsgBox.
>
> Rich
>
> *** Sent via Developersdexhttp://www.developersdex.com***

Yes - that is right - but tried your code and it doesnt seem to work -
in fact it does nothing - not even an error message now. I changed
the completed date from a date/time field into a text box and this
didnt work either. Sorry, is there something else I have to do to make
it work. I will do a refresh macro to update it all.

Re: Field Validation

Elainie wrote:
> On Nov 13, 6:04 pm, Rich P wrote:
>
>>Hi Elainie,
>>
>>It sounds like you are selecting something from a combobox and if a
>>textbox called "CompletedDate" is empty then you want to notify the user
>>and have the user fill in the textbox with a date. Anyway, my example
>>will be based on this scenario. I will also use naming conventions for
>>the controls. A textbox name is usually preceded by txt
>>as in txtCompletedDate and combobxes are preceded by cbo
>>as in cboTest
>>
>>In the AfterUpdate event of the combobox you can do something like this:
>>
>>Private Sub cboTest_AfterUpdate()
>> If txtStatus = "Completed" And txtCompletedDate = "" Then
>> txtCompletedDate.SetFocus
>> MsgBox "Completed date must be filled in"
>> End If
>>End Sub
>>
>>The combobox AfterUpdate event occurs after a user selects something
>>from the combobox. Note that I also removed the parentheses from
>>txtStatus = "Completed". This is the correct syntax. And I set the
>>focus on txtCompleted before the MsgBox. It doesn't matter if you
>>.SetFocus before or after. I just usually do it before the MsgBox.
>>
>>Rich
>>
>>*** Sent via Developersdexhttp://www.developersdex.com***
>
>
> Yes - that is right - but tried your code and it doesnt seem to work -
> in fact it does nothing - not even an error message now. I changed
> the completed date from a date/time field into a text box and this
> didnt work either. Sorry, is there something else I have to do to make
> it work. I will do a refresh macro to update it all.
>
What if txtCompletedDate is Null? You have txtCompletedDate = "" and a
"" is not a Null.

What you can try is
And NZ(txtCompletedDate,"") = ""
and this will convert a Null to the value, in this case "", to the value
you set.

In your initial post you had
CompletedDate = IsNull
and that should return a Null. The better syntax is
If IsNull(Me.CompletedDate) And...


Perhaps
http://www.youtube.com/watch?v=hXhkOpzoVrM