Run SQL Delete Query

[SoggyCashew]

hello, I have a continious form that has a button for each line that
populates in the detail section of the form. This button is to delete all
attendance for the employee. I want to be able to have it ask for the year
that I want to delete. tblInput has a date field named "InputDate" that I
wanted to use. How would I add a pop up that would ask what year I wanted to
delete? Thanks!

Private Sub cmdDeleteInput_Click()
Dim strMsg As String

DoCmd.SetWarnings False '--------------------------Set all Warnings to false
or turns them off

strMsg = "Are you sure you want to delete this employee(s) attendance
entries?" & Chr(13) & _
"" & Chr(13) & _
"" & _
"NOTE: ****THIS CAN NOT BE UNDONE****" & _
"All attendance entries for this employee will be deleted if (Yes)
is selected!"

If MsgBox(strMsg, vbCritical + vbYesNo) = vbYes Then

'//-----------Runs delete query
DoCmd.RunSQL "DELETE * FROM tblInput WHERE UserID =" & Me.txtEmployeeID
DoCmd.SetWarnings True '----------------Sets Warnings back to true
Me.Requery

Else
DoCmd.SetWarnings True '----------------Sets Warnings back to true if no
was selected
End If

End Sub

 

[Douglas J. Steele]

Private Sub cmdDeleteInput_Click()
Dim strMsg As String
Dim strSQL As String
Dim strYear As String

strMsg = "Are you sure you want to delete this employee(s) " & _
"attendance entries?" & Chr(13) & Chr(13) & _
"NOTE: ****THIS CAN NOT BE UNDONE****" & _
"All attendance entries for this employee will be " & _
"deleted if (Yes) is selected!"

If MsgBox(strMsg, vbCritical + vbYesNo) = vbYes Then

strYear = InputBox("For what year do you want the records delete?")
If Len(strYear) > 0 Then
strSQL = "DELETE * FROM tblInput WHERE UserID =" & Me.txtEmployeeID &
_
" AND (InputDate BETWEEN " & Format(DateSerial(strYear, 1, 1),
"\#yyyy\-mm\-dd\#") & _
" AND " & Format(DateSerial(strYear, 12, 31), "\#yyyy\-mm\-dd\#") &
")"
CurrentDb.Execute RunSQL
Me.Requery
End If
End If

End Sub