Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Record is NOT delete though Status=acDeleteOK ... WHY ?

  1. #1
    ksor's Avatar
    ksor is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    82

    Record is NOT delete though Status=acDeleteOK ... WHY ?


    I have a subform with some records and to each of these records is a file stored file on the HD.

    When I want to delete a RECORD and can choose if the related file on the HD should be moved to a "Deleted"-folder or not.

    The record IS IN FACT DELETED to see in the form, but if I close Down the form and open it Again - the record is stille there !

    The related file is moved or nor depending of my choice - no problem here.

    But it's a problem the record is NOT deleted !

    Why is it not deleted ?

    here is my code: ?????? why can't I paste the code ???????????

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    5,714
    is it a parent or child record? (some relations will now allow delete)

  3. #3
    ksor's Avatar
    ksor is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    82
    It is in a subform but it's a parent record - if I understand that term correct.

    If I try the same procedure Again and skip my code for moving/deleting the related file - then it WILL delete the record.

    So it could be very nice if I could show you my code here .... I can paste it into Word but not here ... WHY ????

    Anyway I beleave the record SHOULD be deleted when the Status is zero and first then I start my moving/deleting of the file ... note AFTER the record was (I thought !) deleted !

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    11,827
    Not sure why you can't paste vba or sql code, or attach a zip file of the database (or a copy with enough data to show the issue).
    Please try again, and tell us what you did if you can not post vba or sql code.

  5. #5
    ksor's Avatar
    ksor is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    82
    Quote Originally Posted by orange View Post
    Not sure why you can't paste vba or sql code, or attach a zip file of the database (or a copy with enough data to show the issue).
    Please try again, and tell us what you did if you can not post vba or sql code.
    I just tried to past some code copied from my Access module - plain and simple !

    When I insert here the cursor just jumps a Little ... but no code appearing !

    If I right after starts up Word and try pasting ... no problems ... the code is showing up !

    ... has I to do with me running the Edge-browser ?

    The DB is way too big to upload here.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,638
    This is a test of copy/paste VBA code in Edge browser.

    Code:
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If Me!SampleType = "Independent Assurance" Then
        'no field data, hide dividing line
        Me.Line256.Visible = True
    End If
    End Sub
    If you want to provide db, follow instructions at bottom of my post. Or upload to a fileshare site and post link.


    Why 'move' files? Just set a value in field that identifies record as 'Archived' then apply filter to exclude those records.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  7. #7
    ksor's Avatar
    ksor is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    82
    OK, I restarted my machine and try to paste Again !

    It will NOT paste code from MS Access modules !

    I can take some text from anywhere else and it pastes nicely !!!!!!!!!!!!!

    WTF is wrong here !

  8. #8
    ksor's Avatar
    ksor is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    82
    I copied the code to a Word document and shut Down Access - then copied from the Word document to here - and it worked !!!!!!

    Code:
    Private Sub Form_AfterDelConfirm(Status As Integer)
        If anyString<> "" Then
            If (Status =acDeleteOK) Then
                IfVeto("Skal selve filen: '" & anyString & "' overførestil mappen 'SLET_docs' ?", vbDefaultButton1) = vbYes Then
                   FileCopy TrimFrom("Right", CurrentDb.Name, "\")& "EVENT_docs\" & anyString, _
                            TrimFrom("Right", CurrentDb.Name, "\") &"SLET_docs\" & anyString
                   DeleteFile TrimFrom("Right", CurrentDb.Name, "\")& "EVENT_docs\" & anyString
                End If
            End If
        End If
    End Sub
    
    
    Private Sub Form_Delete(Cancel As Integer)
        If Me.SelHeight> 1 Then
            Cancel = True
            selRecordCount= selRecordCount + 1
            IfselRecordCount = 1 Then
                FejlMeld "Du kan kun slette 1record af gangen aht. behandling af de tilknyttede filer !"
            End If
        Else
            anyString =""
            If NotIsNull(Me.[Sti til fil]) Then anyString = Me.[Sti til fil]    ' bruges for at slette selve FILEN
            selRecordCount= 0
        End If
    End Sub

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    11,827
    ??? I have no idea.

    I just copied the code below from a module and pasted it; then highlighted and clicked the hash/octothorpe (#) in the header to create code tags.

    Code:
    Public Function ISO_WorkdayDiff( _
      ByVal datDateFrom As Date, _
      ByVal datDateTo As Date) _
      As Long
    
          ' Purpose: Calculate number of working days between dates datDateFrom and datDateTo.
          ' Assumes: 5 or 6 working days per week. Weekend is (Saturday and) Sunday.
          '
          ' Limitation: *** Does not account for public holidays.***
          '
          ' May be freely used and distributed.
          ' 1999-04-23. Gustav Brock, Cactus Data ApS, Copenhagen ' 2000-10-03. Constants added.
          '             Option for 5 or 6 working days per week added.
    
            Const cbytWorkdaysOfWeek  As Byte = 5
    
            Dim bytSunday             As Byte
            Dim intWeekdayDateFrom    As Integer
            Dim intWeekdayDateTo      As Integer
            Dim lngDays               As Long
            Dim datDateTemp           As Date
            
            ' Reverse dates if these have been input reversed.
    10       On Error GoTo ISO_WorkdayDiff_Error
    
    20      If datDateFrom > datDateTo Then
    30        datDateTemp = datDateFrom
    40        datDateFrom = datDateTo
    50        datDateTo = datDateTemp
    60      End If
            
            ' Find ISO weekday for Sunday.
    70      bytSunday = WeekDay(vbSunday, vbMonday)
            
            ' Find weekdays for the dates.
    80      intWeekdayDateFrom = WeekDay(datDateFrom, vbMonday)
    90      intWeekdayDateTo = WeekDay(datDateTo, vbMonday)
            
            ' Compensate weekdays' value for non-working days (weekends).
    100     intWeekdayDateFrom = intWeekdayDateFrom + (intWeekdayDateFrom = bytSunday)
    110     intWeekdayDateTo = intWeekdayDateTo + (intWeekdayDateTo = bytSunday)
            
            ' Calculate number of working days between the two weekdays, ignoring number of weeks.
    120     lngDays = intWeekdayDateTo - intWeekdayDateFrom - (cbytWorkdaysOfWeek * (intWeekdayDateTo < intWeekdayDateFrom))
            ' Add number of working days between the weeks of the two dates.
    130     lngDays = lngDays + (cbytWorkdaysOfWeek * DateDiff("w", datDateFrom, datDateTo, vbMonday, vbFirstFourDays))
            
    140     ISO_WorkdayDiff = lngDays
    
    150      On Error GoTo 0
    160      Exit Function
    
    ISO_WorkdayDiff_Error:
    
    170       MsgBox "Error " & err.number & " (" & err.Description & ") in procedure ISO_WorkdayDiff of Module Module5"
    
    End Function
    This site will accept an attached file in zip format.

  10. #10
    ksor's Avatar
    ksor is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    82
    Iwill try to document my problem a little further – and to refresh: the problemis a record is NOT deleted when I try to delete it. It seems like it is deletedin the form, but when I take a look in the underlaying table the record isstill there.

    Let’sfirst see the underlaying tables and relations:

    Click image for larger version. 

Name:	ER1.jpg 
Views:	13 
Size:	76.1 KB 
ID:	31553



    Thetables involved in this operation is: ”PERSON”, ”PERS_SOCHÆN” and ”DELTOG_I”and in short they keep track of my persons, which social events theyparticipate in and with whom.

    RelationA is defined like this:
    Click image for larger version. 

Name:	Relation A.jpg 
Views:	12 
Size:	37.3 KB 
ID:	31554



    RelationB is defined like this:
    Click image for larger version. 

Name:	Relation B.jpg 
Views:	12 
Size:	37.4 KB 
ID:	31555


    Theform I use to maintain the tables look like this:
    Click image for larger version. 

Name:	PERSON-form.jpg 
Views:	12 
Size:	274.3 KB 
ID:	31556


    AndI have placed a label to show where the data comes from in the main- andsub-forms.

    Therecord in the subform ”PER_SOCHÆN” is selected to be deleted and when I pressthe Delete-key I get these warnings from Access and I just let it delete – andit LOOKS like all is OK in the form here, BUT …

    IfI take a look in the underlaying tables the record in the table ”DELTOG_I” isdeleted (OK!) but the record in the table ”PER_SOCHÆN” is still there – NOT OK!

    Thefunctionality around the related file – 26-409.docx - on the harddrive isworking OK, so never mind that.

    Hereis my code:

    Code:
    PrivateSub Form_AfterDelConfirm(Status As Integer)
        If anyString <> "" Then
            If (Status = acDeleteOK) Then
                If Veto("Skal selve filen:'" & anyString & "' overføres til mappen 'SLET_docs' ?",vbDefaultButton1) = vbYes Then
                    FileCopyTrimFrom("Right", CurrentDb.Name, "\") & "EVENT_docs\"& anyString, _
                            TrimFrom("Right", CurrentDb.Name, "\") &"SLET_docs\" & anyString
                    DeleteFileTrimFrom("Right", CurrentDb.Name, "\") &"EVENT_docs\" & anyString
                End If
            End If
        End If
    EndSub
    
    
    PrivateSub Form_Delete(Cancel As Integer)
        If Me.SelHeight > 1 Then
            Cancel = True
            selRecordCount = selRecordCount + 1
            If selRecordCount = 1 Then
                FejlMeld "Du kan kun slette 1record af gangen aht. behandling af de tilknyttede filer !"
            End If
        Else
            anyString = ""
            If Not IsNull(Me.[Sti til fil]) ThenanyString = Me.[Sti til fil]    ' brugesfor at slette selve FILEN
            selRecordCount = 0
        End If
    EndSub
    IfI try to comment out my code for the managing the related file, here26-409.docx, it’s the same result – in my ealier posting I wrongly said itworked – but it didn’t – it gives the same result.

    Whatis wrong and how can I fix it ?

    (Still I can't paste from Access - this is done from a Word document and I can see that a lot of SPACES are left out they are in the original text - very strange, I think !)
    Last edited by ksor; 12-07-2017 at 11:34 AM.

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,638
    What happens if you manually delete record directly from table?

    I don't see anything in structure or code that would cause your process to fail.

    I have a db that does not rely on relationships to manage data integrity. I prevent use of keyboard Delete key and require users to use my button. Code deletes dependent records then deletes the parent record. Here is how I capture and cancel the keyboard Delete.
    Code:
    Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
     'suppress default Delete Confirm dialog box.
     Response = acDataErrContinue
     'cancel the automatic delete operation
     Cancel = True
     MsgBox "Must click Remove Test button to delete test from sample." & vbCrLf & _
            "If sample is in closed accounting period, Delete/Remove test not permitted.", , "Delete"
     End Sub
    Just marking record as 'Archived' and applying filter not acceptable approach?
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  12. #12
    ksor's Avatar
    ksor is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    82
    Quote Originally Posted by June7 View Post
    What happens if you manually delete record directly from table?

    I don't see anything in structure or code that would cause your process to fail.

    I have a db that does not rely on relationships to manage data integrity. I prevent use of keyboard Delete key and require users to use my button. Code deletes dependent records then deletes the parent record. Here is how I capture and cancel the keyboard Delete.
    Code:
    Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
     'suppress default Delete Confirm dialog box.
     Response = acDataErrContinue
     'cancel the automatic delete operation
     Cancel = True
     MsgBox "Must click Remove Test button to delete test from sample." & vbCrLf & _
            "If sample is in closed accounting period, Delete/Remove test not permitted.", , "Delete"
     End Sub
    Just marking record as 'Archived' and applying filter not acceptable approach?

    I don't like that 'Archived' solution ... in the long run it will mess up my tables, I think.

    If I manually delete the record in the table "PER_SOCHÆN" it deletes it AND warn me of the deleting of the 'child-record' in the "DELTOG_I" table - and that's just "by the book" and OK - that's What I expected my code would do ;-(( ... but didn't !

    I would very much like to know What is wrong in my design and/or code.

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,638
    Well, I can't see it from what you posted so would have to examine database. But then the language of the db will probably be a barrier.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  14. #14
    ksor's Avatar
    ksor is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    82
    Quote Originally Posted by June7 View Post
    Well, I can't see it from what you posted so would have to examine database. But then the language of the db will probably be a barrier.
    Yeah, I think so too ...

    On the other hand I think I just "designed and coded by the book" so I think it's a Little wired that it fails ;-((

    But thx for your time anyway !

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,638
    Where is selRecordCount declared? Is this declared in module header? I am not sure why you need this variable and the conditional code using it.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Resetting form to original status on record change
    By Thompyt in forum Programming
    Replies: 8
    Last Post: 11-18-2014, 07:09 PM
  2. Check if record exist and update the status
    By JustinC in forum Access
    Replies: 4
    Last Post: 08-17-2014, 12:39 PM
  3. Replies: 4
    Last Post: 06-20-2013, 09:26 PM
  4. Auto status a record
    By yes sir in forum Database Design
    Replies: 6
    Last Post: 06-11-2012, 07:46 PM
  5. Trying to Delete record using delete query
    By MooseOTL in forum Access
    Replies: 13
    Last Post: 10-04-2011, 01:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums