Order of events help on code

[sonny]

I have a start and end date fields plus a txt box called wDays.
I'm trying to make sure the user puts in both dates but end cant be earlier than start and vs versa....
If not provide a message as to which is missing....
If alls well do the calulation with the module "GetWorkDays"
I f everything goes bad, dont save the record at all!

I have this behind a cmdButton. Used to unhide the wDays field because it displays #error otherwise...and to do the calculation... with the BeforeUpdate

Private Sub Command8_Click()
    If IsNull(Me.sDate) Then
        MsgBox "Please enter a Start Date"
    ElseIf IsNull(Me.eDate) Then
        MsgBox "Please enter a End Date"
    Else
        If Me.eDate < Me.sDate Then
            MsgBox "End Date cannot be earlier than the Start Date"
            Exit Sub
        End If
    End If
    Me.Requery
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim myDays As Integer
    If IsNull(Me.eDate) Or IsNull(Me.sDate) Then
        Me.wDays.Visible = False
    Else
        Me.wDays.Visible = True
        myDays = GetWorkDays([sDate], [eDate])
        Me.wDays.Value = myDays
    End If
End Sub

Any help would be really appreaciated because I have tried many combonations of code placement to get it working correctly....

 

[wazz]

first and foremost: typically, never store a calculation. sry if you know this already. if you don't, search this site for details - there are plenty of posts.
i'll leave that to you and add a couple of things anyway. i'm not going to test any of this, though.

it displays #error otherwise

do you know why?

Private Sub Command8_Click()
    If IsNull(Me.sDate) Then
        MsgBox "Please enter a Start Date"
    ElseIf IsNull(Me.eDate) Then
        MsgBox "Please enter a End Date"
    Else
        If Me.eDate < Me.sDate Then
            MsgBox "End Date cannot be earlier than the Start Date"
            Exit Sub
        End If
    End If
    Me.Requery
End Sub

- add 'Exit Sub' before ElseIf and before Else (?)
- why Me.Requery? is this for the calculation? if you use and unbound control with a formula behind it, the calculation will be automatic - you won't have to requery.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim myDays As Integer
    If IsNull(Me.eDate) Or IsNull(Me.sDate) Then
        Me.wDays.Visible = False
    Else
        Me.wDays.Visible = True
        myDays = GetWorkDays([sDate], [eDate])
        Me.wDays.Value = myDays
    End If
End Sub

i guess you are storing the calculation, hence putting this on the BeforeUpdate event. (i'm pretty sure now that you don't have to store this. tracking work days...). you could put the .Visible-code (1st 4 lines of the If statement) on the OnCurrent event of the form (stop after '= True'). leave the next two lines.

you might find that this isn't quite right or is incomplete but some ideas hopefully.