Requery subform after deleting a record

[tatarik]

Hello everyone

-- I haven't been able to figure this one out myself --

I'm trying to execute some lines of code right after deleting a record in a subform, with a right click on the row I want to delete (the idea is that if a record is deleted the other ones should be updated by module1.tblUpdateLatestDocuments).

I've tried putting this code into the events On Delete and After Del Confirm of the subform, none of that worked. :banghead:

Option Compare Database

Private Sub Form_AfterDelConfirm(Status As Integer)
Call Module1.tblUpdateLatestDocuments
Me.Requery
End Sub

Private Sub Form_Delete(Cancel As Integer)
Call Module1.tblUpdateLatestDocuments
Me.Requery
End Sub

Aren't there any events that I could use right AFTER the record has been deleted?

Thanks,
T.

 

[burrina]

Don't know what code is in your module, suspect it is the problem. Do you have code for the delete event and using a Me.Requery at the end?

 

[tatarik]

Don't know what code is in your module, suspect it is the problem. Do you have code for the delete event and using a Me.Requery at the end?

Thanks burrina.
The module 1 code goes like (sorry it's kinda long):

Public Sub tblUpdateLatestDocuments(Optional VesselID As Long, Optional DocumentID As Long)
'------------------------------------
'put a counter to time sub execution
Dim t As Long
t = GetTickCount
'------------------------------------
Dim db As DAO.Database
Dim rs(1) As DAO.Recordset
Dim strSQL(1) As String

'Here is the most complete working recordset
'we might require to work with:
strSQL(0) = "SELECT tblVessels.[#], tblDocumentNames.[#] " & _
            "FROM tblVessels INNER JOIN (tblDocumentNames INNER JOIN tblDocuments ON tblDocumentNames.[#] = tblDocuments.document_ID) ON tblVessels.[#] = tblDocuments.vessel_ID"
'------------------------------------
'The optional arguments are intended to restrict the above working recordset
'to only the specific document set we require.
'Let's narrow down the working recordset, if there are any optional arguments
If IsNull(VesselID) Then
'We have no vessel ID
    If IsNull(DocumentID) Then
        'We have no vessel ID nor document ID.
        'There is no criteria to add to the SQL query,
        'meaning we'll work on the full recordset
        '-------
        'So we need to know how many vessels and document names we have
        'before we can loop through them
        Dim lgNumberOfVessels, lgNumberOfDocumentNames As Long
        lgNumberOfVessels = DMax("[#]", "tblVessels")
        lgNumberOfDocumentNames = DMax("[#]", "tblDocumentNames")
        
        Else
        'We only have a document ID
        strSQL(0) = strSQL(0) & " WHERE (tblDocuments.document_ID)=" & DocumentID & ") "
    End If
    '*************************
    Else
    If IsNull(DocumentID) Then
        'We only have a vessel ID
        strSQL(0) = strSQL(0) & " WHERE (((tblVessels.[#])=" & VesselID
        Else
        'We have both a vessel ID and a document ID
        strSQL(0) = strSQL(0) & " WHERE (((tblVessels.[#])=" & VesselID & _
        ") AND ((tblDocuments.document_ID)=" & DocumentID & ")) "
    End If
End If

Set rs(0) = CurrentDb.OpenRecordset(strSQL(0), dbOpenDynaset)
'Check to see if the 1st recordset actually contains rows
If Not (rs(0).EOF And rs(0).BOF) Then
rs(0).MoveFirst 'Unnecessary in this case, but still a good habit
'************************
'LOOP
Do Until rs(0).EOF = True
    'Let's make sub-recordsets for each vessel/document_name
    '(comparing apple to apple, with a SQL query
    strSQL(1) = "SELECT tblDocuments.vessel_ID, tblDocuments.document_ID, tblDocuments.expiryDate, tblDocuments.latest " & _
    "FROM tblDocuments " & _
    "WHERE (((tblDocuments.vessel_ID) = " & rs(0)![tblVessels.#] & ") And ((tblDocuments.document_ID) = " & rs( _
    0)![tblDocumentNames.#] & ")) ORDER BY tblDocuments.expiryDate DESC, tblDocuments.issuingDate DESC" _
    'We define the latest documents as the document with the latest expiry date
    'or if no expiry date, with the latest issuance date.
    'In any case, this should be the first document in the above SQL query
    
    Set rs(1) = CurrentDb.OpenRecordset(strSQL(1), dbOpenDynaset)
    
    With rs(1)
        'If the recordset is empty, do nothing and move on to the next sub-recordset.
        If .EOF Then
            
            Else
            'Take the first record
            .MoveFirst
            'Check whether the first document has an expiry date
            'If IsNull(![expiryDate]) Then
                .Edit
                ![latest] = True
                .Update
                'Now, update all the next records as 'not latest'
                
                'Move to the next record
                .MoveNext
                'If there is a next record, it is not the latest document
                While .EOF = False
                    .Edit
                    ![latest] = False
                    .Update
                    .MoveNext
                Wend
    
                'Else

            'End If
        End If
        
        .Close
    End With

rs(0).MoveNext
Loop

End If

rs(0).Close

'------------------------------------
Debug.Print "Public Sub tblUpdateLatestDocuments took " & GetTickCount - t; " milliseconds to execute."
'------------------------------------
End Sub

Then the requery refreshes the data underlying the subform, including some data updated by the above routine.

 

[burrina]

Where is this delete code being called from, the subform or the main form?

Call Module1.tblUpdateLatestDocuments
DoCmd.SetWarnings False
Call DoCmd.RunCommand(Command:=acCmdDeleteRecord)
DoCmd.SetWarnings True
Me.Requery

Let me know if this works for you.

 

[tatarik]

Where is this delete code being called from, the subform or the main form?


Let me know if this works for you.

Thanks, I've tried puttin your chunk of code in the AfterDelConfirm event of the subform, ended up stucking the execution at the line Me.Requery....
I had to force stop Access with Windows Task Manager

Sorry, wasn't sure where you were suggesting I put the code you gave

 

[burrina]

I was assuming you had a command button to delete the record.

 

[tatarik]

I was assuming you had a command button to delete the record.

Oh no, I simply want to right click on the row and delete.

 

[burrina]

Then use the OnCurrentEvent or OnChangeEvent.

Good Luck!

 

[spikepl]

You have choosen the most horrible naming convention you could: # is a syntactical element of VBA and you will get into trouble with this - as you alreade have noticed because you have to enlose it in [].

Here is a suggested naming convention: http://www.access-programmers.co.uk/forums/showthread.php?t=225837
And here a list of proscribed words: http://allenbrowne.com/AppIssueBadWord.html

Further, stop this "doesn't work" - business becasue that is not useful for anything, Say what you did and what the system did.

For event sequence google: Sequence of Access events.

I don't know if it has any bearing on the subject but at some stage the current record is no longer the deleted one - that always surprises people.

The way to debug stuff like this is to put a msgbox "hello" so you are sute that the event is run at the time where you want it. Then check your data. Then check, separately, that your sub does what you want it to. First then marry the things.

Know that in DB-context deletions are not used that often, becasue data is often useful for statistics. You'd simply set an Inactive flag for a record and just leave it there. This may or may not be appropriate in your case - you be a better judtge of that.

 

[tatarik]

Thanks, it actually worked with the AfterDelete Confirm event in the end.

 

[tatarik]

Thanks SpikePL, useful comment.