CODEWITHSUNDEEP
vbams-access
DHPA
DHPA

Reputation: 55

Run time error when clicking cancel on Input box asking for Date

The end result of this is a time sheet. I put it together initially in excel, but I'm looking to move the payroll information/module into access. When I am in excel the cancel button works fine, but in access it always errors. Some research on line shows this is a problem which I thought I could solve with something akin to 

 If StartDate = "" Then
  GoTo ext:
  End If

This causes

run time error 13.

This occurs I believe because when one hits cancel the StartDate variable is empty and the input box is expecting some type of input. I'll keep looking on line, but perhaps the issue is I should not be using an Input Box, but if that is the case I'm not sure what should be used? The full script is below. Any help is appreciated.

Private Sub CreateTimeSheet_Click()

Dim StartDate As Date

mbox = InputBox("Enter Start Date YYYY/MM/DD", "Enter Start Date")
StartDate = mbox

  If StartDate = "" Then
  GoTo ext:
  End If

FirstDay = Format(CDate(StartDate))
Text76 = Format(CDate(StartDate + 1))
Text77 = Format(CDate(StartDate + 2))
Text78 = Format(CDate(StartDate + 3))
Text79 = Format(CDate(StartDate + 4))
Text80 = Format(CDate(StartDate + 5))
Text81 = Format(CDate(StartDate + 6))
Text82 = Format(CDate(StartDate + 7))
Text83 = Format(CDate(StartDate + 8))
Text84 = Format(CDate(StartDate + 9))
Text85 = Format(CDate(StartDate + 10))
Text86 = Format(CDate(StartDate + 11))
Text87 = Format(CDate(StartDate + 12))
Text88 = Format(CDate(StartDate + 13))

Text61 = Format(CDate(mbox), "dddd")
Text63 = Format(CDate(StartDate + 1), "dddd")
Text64 = Format(CDate(StartDate + 2), "dddd")
Text65 = Format(CDate(StartDate + 3), "dddd")
Text66 = Format(CDate(StartDate + 4), "dddd")
Text67 = Format(CDate(StartDate + 5), "dddd")
Text68 = Format(CDate(StartDate + 6), "dddd")
Text69 = Text61.value
Text70 = Text63.value
Text71 = Text64.value
Text72 = Text65.value
Text73 = Text66.value
Text74 = Text67.value
Text75 = Text68.value

ext:
Exit Sub

End Sub

Upvotes: 0

Views: 650

Answers (1)

braX
braX

Reputation: 11755

Since InputBox returns a String you will want to store the result into a String, not a Date variable. Then you can use IsDate() to check to see if that result text can be converted to a Date. Then and only then continue, otherwise, exit. Keep in mind the user can enter anything they way into the InputBox - You cannot depend on them entering an actual date value.

Dim mbox As String
Dim StartDate As Date

mbox = InputBox("Enter Start Date YYYY/MM/DD", "Enter Start Date")
If IsDate(mbox) Then
  StartDate = CDate(mbox)
Else
  If mbox = "" Then
    ' user entered nothing, so cancel
    Exit Sub
  Else
    ' user entered garbage, so tell them what they did wrong
    MsgBox "That is not a date."
    Exit Sub
  End If
End If

' valid data entered... rest of your code here

Also, if you are adding days to a date value, you will want to use DateAdd, not simple addition.

Upvotes: 1

Related Questions