When I try to delete a class that has expired, I still get MsgBox "Do you really want to delete......" That message should not even appear in that case, but instead I should get "Class cannot be deleted...." Actually I get message regardless if the class expired or not. Then I get the msgbox " Class cannot be deleted...." Seems the the code is ignoring the if statement about the date all together. I want to check if the class has expired first. If expired msgbox stating that and then exit sub. If class is not expired the input box "are you sure........" should continue.
Can someone help me out?
Private Sub CmdDeleteEntry_Click()Dim strDelete As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
' connect to the Access database
Set cn = New ADODB.Connection
myConn = TARGET_DB
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open myConn
End With
' open a recordset
Set rs2 = New ADODB.Recordset
src = "SELECT * FROM tblClasses WHERE DateTime = '" & Me.lstClasses.List(Column, 3) & "'"
rs2.Open src, cn, adOpeDynamic, adLockOptimistic
Dim selected As String
Dim answer As Long
Dim RUSure As String
RUSure = InputBox("Do you really want to delete the selected schedule? Enter Y to delete or N to cancel.")
selected = lstClasses.ListIndex
If Not selected = -1 Then
If rs2![expiredDate] < Date Then
MsgBox "That class has expired and cannot be deleted."
Exit Sub
End If
If RUSure = "y" Or RUSure = "Y" Then
answer = lstClasses.List(selected, 0)
strDelete = "Delete * FROM tblRegistered Where ID =" & answer
rs2.Fields("SlotsTaken") = rs2.Fields("SlotsTaken") - 1 'Reduces slots taken by 1 each time a record is deleted.
rs2.Update
cn.Execute strDelete
cn.Close
MsgBox "Class Deleted"
Else
MsgBox "Please select a schedule to delete."
cn.Close
End If
ElseIf RUSure = "N" Or RUSure = "n" Then
MsgBox "Deleting cancelled."
cn.Close
Else
MsgBox "You have entered a wrong value."
' rs.Close
cn.Close
End If
End Sub