Required fields

[realtallgal]

How do I write a validation that says
"If the status = "completed", then the completed date must be populated"?

Thanks
diana

 

[Remou]

What have you tried?

http://lessthandot.com

 

[realtallgal]

I have tried using the expression builder
IIf[taskers].[status] ="completed", [taskers].[completed date] <> NULL.

 

[Remou]

Are you using a form? It will not be possible to do this kind of validation without a form. Next, you will need to pick an event, the Before Update event of the form is usually good. Then you will need a little code for the event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.Status="Completed" And IsNull(Me.[Completed date]) Then
       MsgBox "You must fill in the completed date."
       Cancel=True
    End If
End Sub

As an aside, it is not a good idea to have spaces in control names or field names, you will save yourself a lot of troble if you get rid of them. It is usually best to rename the controls so they do not have the same name as the fields they contain (eg txtTextbox, cboCombo). Pick a naming convention that suits and use it.

http://lessthandot.com

 

[realtallgal]

Yes it is a form.. also. Sorry for these silly questions but I am new to the coding stuff, I usually used macros. What does this mean "Next, you will need to pick an event, the Before Update event of the form is usually good"?

 

[Remou]

When you open a form in design view, you can choose View->Properties, or you can double-click anywhere on the form to open the Property Sheet. Each control and each part of the form has its own property sheet, which you can view by clicking on the relevant item or by selecting it from the drop-down list on the Form Design toolbar. The property sheet has various tabs, one of which is events, if you select [event procedure] for any of these events and click the three little dots ... to the right, it will open the code window with a couple of lines filled in, for example:

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
End Sub

You can fill in your code between these two lines, for example:

Private Sub Form_BeforeUpdate(Cancel As Integer)
     MsgBox "Hello!"
End Sub

After that, things get a little more complicated, but it is worth learning, if you intend to carry on using MS products, unless you wish to switch to VSTO (Visual Studio Tools for Office).

http://lessthandot.com