No.7 Value for DoCmd at vbYesNo condition not working in nested If...Then.

[PierreR]

Access 2002

There are 4 fields on my form, StartDate, StartTime, EndDate and EndTime. EndDate + EndTime should always be larger than StartDate + StartTime. The first message box in the code below takes care of that.

A second message box has to come up as soon as the difference above is 6 hours or more asking if this is okay The box type is vbYesNo in this case.

Everything works fine, except that in the nested if...then clause below, when the user clicks the no-button in that message box, it does not take the focus back to the EndDate field like the first one does.

I think the mistake has to do with the value I use to refer to the clicking of the no-button. Does someone know what I'm doing wrong?

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If EndDate + EndTime < StartDate + StartTime Then
        MsgBox "The end time must be later or the same " _
        & "as the start time", vbInformation, _
            "TIME ENTRY WRONG"
        DoCmd.GoToControl "EndDate"
    ElseIf ((EndDate + EndTime - StartDate - StartTime) * 24) > 6 Then
        MsgBox "Are you sure you spent more than 6 hours " _
            & "at the activity?", vbYesNo
        If vbYesNo = 7 Then
            DoCmd.GoToControl "EndDate"
        End If
    End If
End Sub

 

[RichO]

The problem is that vbYesNo does not hold a conditional value, it is always 4 no matter what. You need to set up your MsgBox differently to determine if yes or no was selected.

    ElseIf ((EndDate + EndTime - StartDate - StartTime) * 24) > 6 Then
        strMsg = MsgBox ("Are you sure you spent more than 6 hours " _
            & "at the activity?", vbYesNo)
        If strMsg = vbNo Then    [COLOR=DarkOliveGreen]' vbNo = 7  &  vbYes = 6[/COLOR]
            DoCmd.GoToControl "EndDate"
        End If
    End If

You will also need to dimension strMsg as Integer at the start of your code.

 

[PierreR]

Thank you, Rich. I have worked on it and came up with something that works, but it is not as elegant as yours. Do you think using Select Case is an OK solution too, or is some of it redundant?

Private Sub Form_BeforeUpdate(Response As Integer)
Dim mbrResponse As VbMsgBoxResult
Dim strMsg As String

    If EndDate + EndTime < StartDate + StartTime Then
        MsgBox "The end time must be later or the same " _
        & "as the start time", vbInformation, _
            "TIME ENTRY WRONG"
        DoCmd.GoToControl "EndDate"

     ElseIf ((EndDate + EndTime - StartDate - StartTime) * 24) > 6 Then
    	strMsg = "Was activity really 6 hours " & _
                "or more?"
    	mbrResponse = MsgBox(strMsg, _
        	vbYesNo + vbQuestion, "This was a very long time")
    
    	Select Case mbrResponse
        	Case vbNo
            		DoCmd.GoToControl "EndDate"
        	Case vbYes
            		Response = acDataErrContinue
           End Select
     End If
End Sub

 

[RichO]

One may consider Select Case to be more than you need because your MsgBox can only have 2 results, so an If..Then..Else would be recommended, but it's just one less line of code. So if what you have works, go with it.

 

[Rich]

Why bother with the Else here anyway?