Finding correct form event to Refresh upon and prevent #Deleted

[mdlueck]

My application has read-only multi-record list forms, and add/edit dialog forms to provide edit capabilities.

When the list form launches an add/edit form, it remains open, merely opens the add/edit form which is a modal dialog box.

I am having a difficult time finding an event which will fire when the add/edit dialog form closes and the list form regains focus. I need to preform a Refresh to update the UI and prevent seeing #Deleted when an edit has been made.

I have tried Activate / GotFocus / Dirty event. None have worked thus far.

Of course, pressing F5 refreshes that list form... I would simply like to have the application automatically do that.

Suggestions of which event will fire when a child form closes and the parent form regains focus? Thanks!

 

[mdlueck]

Evidently I was on the correct track...

"Refresh Method [Access 2003 VBA Language Reference]"
http://msdn.microsoft.com/en-us/library/aa221562(v=office.11).aspx

However this is not solving seeing "#Deleted"

Private Sub Form_Activate()
  
  'Update the Form UI with the table contents
  Me.RecordSource = "tmptblqry_auth"
  Me.Refresh

End Sub

Note: For a prior application I found I had to reestablish the RecordSource when calling Refresh, even though the table name was staying the same.

Oh, and I am working with Access 2007.

 

[mdlueck]

As a work-around (hopefully temporary) I added a Refresh button to the read-only record list form:

Private Sub btnRefresh_Click()

  'Update the Form UI with the table contents
  Me.RecordSource = "tmptblqry_auth"
  Me.Refresh

End Sub

Which is a successful band aid for the problem.

It would appear that quite a few others are having similar problems with Access 2007. Suggestions of how to do so without the use of a manual button? Thanks!

 

[speakers_86]

You could use the on timer event.

 

[mdlueck]

I came up with a hack work-around of having the add/edit form call the Refresh button on the parent form after successfully committing the data and just prior to closing / returning to the parent form.

So my Rx is as follows:

Code on commit of the child form:

Private Sub btnCommit_Click()
  On Error GoTo Err_btnCommit_Click

  'Call the Update event of the Validation Object
  If ObjUIValidationAuthTbl.Update(Me) Then
    'Hack to refresh the prior window UI
    Call Forms("adminauth").Refresh_Click

    'Close window "self"
    DoCmd.Close acForm, Me.Name
  End If

Exit_btnCommit_Click:
  
  Exit Sub

Err_btnCommit_Click:
  Call errorhandler_MsgBox("Form: Form_adminauthedit, Subroutine: btnCommit_Click()")
  Resume Exit_btnCommit_Click

End Sub

Code on the button of the form which has the list of records:

'Hack to support UI refresh when add/edit forms make chages to table
'This must be a Public API
Public Sub Refresh_Click()

  'Update the Form UI with the table contents
  Me.RecordSource = "tmptblqry_auth"
  Me.Refresh

End Sub

 

[nicko]

I finally found a solution for this after much trawling of forums so I thought I'd post it

If you have to use a vba sql command to delete records on a sub form because the subform's Recordsource is based on joined tables you can avoid the #deleted records after delete by calling a refresh on the parent form. nothing else seems to work on subform events due to the order they are processed and time lags if deleting from linked sql tables...

Just add this code to the Subform's on delete event:

 Private Sub Form_Delete(Cancel As Integer)
  Dim deleteSQL As String
 Dim deleteSQL2 As String
  
  DoCmd.SetWarnings False
 Me.Dirty = False 'saves the form
 
 deleteSQL = "delete from Table1 where id = " & Me.ID 
 ' deletes records from the first table
 deleteSQL2 = "delete from Table2 where foreignkey = " & Me.ID
'deletes records from the related table 
 DoCmd.RunSQL deleteSQL
DoCmd.RunSQL deleteSQL2
 Cancel = True 'stops access trying to delete the row
  
   DoCmd.SetWarnings True
  Me.Parent.Form.Refresh 'refreshes the whole form like pressing f5
 
End Sub

 

[static]

form.requery

"...because the subform's Recordsource is based on joined tables that aren't joined on primary key fields..."