Consulting

Results 1 to 4 of 4

Thread: Expired date criteria

  1. #1
    VBAX Newbie
    Joined
    Jul 2012
    Posts
    3
    Location

    Expired date criteria

    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


  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,083
    Location
    If you want to evaluate the date first why not place the test code before the inputbox line of code?
    You also do not have any Error Trapping in your code, so if you do get an error you may not aware of it.

  3. #3
    VBAX Newbie
    Joined
    Jul 2012
    Posts
    3
    Location
    I placed the code before and still get the same results. I am new to this and trying my best to work through. Thanks for you response.

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,083
    Location
    Then the first thing to check is the value of the string selected and the value of Rs2![ExpiredDate] with a message box.
    If your code needs them to be a certain value to work then you have to check that they meet your criteria.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •