Before update not working

[Bee]

Hi,

I used a before Update procedure to check if the date of a textbox is later than another text box. However, nothing seem to happen when I enter the wrong date.

Here is my code:

Private Sub txtDateOfCompletionForHandover_BeforeUpdate(Cancel As Integer)
    Dim anticipatedCompletionDate As Date
    Dim dateCompletionHandover As Date
    
    'assign vars values
    anticipatedCompletionDate = txtAnticipatedCompletionDate.Value
    dateCompletionHandover = txtDateOfCompletionForHandover.Value
    
    'If date1 refers to a later point in time than date2, the DateDiff function returns a negative number
    If DateDiff("d", anticipatedCompletionDate, dateCompletionHandover) < 0 Then
        MsgBox "You are trying to Handover house before the house is finished!" & vbCrLf & vbLf & _
        "Please enter a later date than Anicipated Completion Date above.", , "House Handover"
        
        'empty textbox and keep focus
        txtDateOfCompletionForHandover.Text = ""
        txtDateOfCompletionForHandover.SetFocus
    End If
End Sub

Any help will be very much appreciated.
B

 

[jkl0]

Since you are not saving the record yet, the After Update event may be more appropriate.

Hope this helps?

 

[ejstefl]

I think the problem is that you are using ".value", which will refer to the comitted value of the control. Since this is beforeupdate, you are not getting the compare you want.

Try this:

'assign vars values
anticipatedCompletionDate = txtAnticipatedCompletionDate
dateCompletionHandover = txtDateOfCompletionForHandover

Also, there is no need to use the datediff. You can compare the dates directly. ie:

If anticipatedCompletionDate > dateCompletionHandover

Although there is nothing wrong with using DateDiff.

 

[Bee]

No, still does't work.

 

[ejstefl]

Have you tried putting a breakpoint in your code and stepping through it to see what the problem is?

 

[boblarson]

You're just missing the key ingredient in your IF Statement:

Cancel = True

 

[Bee]

Have you tried putting a breakpoint in your code and stepping through it to see what the problem is?

I have tried that, it did not return any error and everything worked. However, it did not detect it when i entered the wrong date.

 

[Bee]

You're just missing the key ingredient in your IF Statement:

Cancel = True

I don't understand your reply boblarson.

 

[ejstefl]

I have tried that, it did not return any error and everything worked. However, it did not detect it when i entered the wrong date.

I'm sorry, I don't understand. You say everything worked, then you say it did not detect that you entered a wrong date.... which is it?

When you stepped through the code, did your if statement evaluate to true or false? If it evaluated to False, you need to check the values in your variables and find out why they are not what they should be.

 

[Bee]

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.txtAnticipatedCompletionDate > Me.txtDateOfCompletionForHandover Then
        MsgBox "You are trying to Handover house before the house is finished!" & vbCrLf & vbLf & _
        "Please enter a later date than Anicipated Completion Date above.", , "House Handover"        
        Cancel = True
        Me.txtDateOfCompletionForHandover.Undo
        Me.txtDateOfCompletionForHandover.SetFocus
    End If
End Sub

1. There is no reason to use variables or the DateDiff() to compare two date fields.
2. Whenever an edit involves checking a field for null or comparing values of two or more fields, that edit must be done in the FORM's BeforeUpdate event to be effective.
3. When an edit fails, cancel the update.
4. I rarely .undo a value. Usually the user only needs to retype a single character. When you remove the enitre value, he can't see what he entered and you force him to re-enter the entire value.
5. You were using the control property but not the "Me." quailfier so you weren't taking advantage of intellisense.
6. The .text property is ONLY available when the control has the focus. It is rarely used except in the change event. That is usually the only time you want to see a partial value.

That's perfect. Thank you very much.