adding date if value is selected

[Robster]

I want make a date control compulsory if a value is selected from a combo list on the same form.

I have Combo List, status, which can be 25%, 50%, 75%, WON or Lost. If the value is WON or LOST I want the date control, Date Project Closed, to be compulsory. I cant set it to insert today's date as it may have been closed up to a week before.
If 25%, 50% or 75% are selected then the date should not be compulsory.

 

[pr2-eugin]

Try the BeforeUpdate event of the Form.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If ((Me.[COLOR=Blue]comboBoxName [/COLOR]= "WON" Or Me.[COLOR=Blue]comboBoxName [/COLOR]= "Lost") And _
        Len(Me.[COLOR=Blue]dateControlName [/COLOR]& vbNullString) = 0) Then
        MsgBox "You need to enter the Date in the Date Field", vbCritical, "Date missing"
        Cancel = True
        Me.[COLOR=Blue]dateControlName[/COLOR].SetFocus
    End If
End Sub

 

[Robster]

Thanks.

Just to confirm, is the ME.control the form name or the table name?
The form is Edit Records.

 

[pr2-eugin]

Since it is validating the current form Me. is a default way of representing the Current Object. So you only have to worry about the control names (as highlighted).

 

[Robster]

OK. didnt know that.

This is what I've got but it's not working.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If ((Me.Cmb_ProposalStatus = "WON" Or Me.Cmb_ProposalStatus = "Lost") And _
Len(Me.DateProjectClosed & vbNullString) = 0) Then
MsgBox "You need to enter the Date in the Date Field", vbCritical, "Date missing"
Cancel = True
Me.DateProjectClosed.SetFocus
End If


End Sub

 

[pr2-eugin]

Explain what you mean by..

but it's not working.

 

[Robster]

if i set the status to Won but dont enter a date, I can still exit the form.

 

[pr2-eugin]

What is the RowSource, BoundColumn, ColumnCount of the combo box - Cmb_ProposalStatus.

Try walking through the code.

 

[Robster]

It's ok, I've got it to work.

Is there a way to get it to run before closing the form?
There is a macro that runs on close which throws up an error saying that 'you cant save this record at this time' obviously as the date field is missing.

 

[pr2-eugin]

So you chose to set the Field in the table to Required ? WoW ! Good going !

The Close would be the last event, in this order; Unload -> Deactivate -> Close