Access datasheet events not firing

1
votes

I have an Access form that has a datasheet subform that is populated using a table using the code below.

SELECT * INTO temp_filter_tbl FROM (SELECT DISTINCT JobsID, ID, ReferenceDate, Source, Status, NODE, NodeStatus, DocSource, CutOverYear FROM tempTable" & sqlafterwhere & ")"
db.Execute (sql)
Me.CombinationViewDS.SourceObject = "Table.temp_filter_tbl
Me.CombinationViewDS("JobsID").ColumnHidden = True
Me.CombinationViewDS("ID").ColumnHidden = True
Me.CombinationViewDS.Requery

I want to capture the AfterUpdate event so that I apply changes in the temp table to the source tables that the temp_filter_tbl is built from. When I edit a row in the datasheet and click on the little pencil on the right hand side of the datasheet the the corresponding row in the temp_filter_tbl is updated so the update is happening but no events are fired. The AfterUpdate event is not the only event not firing none of the events I try are firing and all of the events are being created in the design view of the data sheet from the properties pane.

Option Compare Database

Private Sub Form_AfterUpdate()
    Debug.Print "Datasheet After Update"
End Sub
Private Sub Form_BeforeUpdate()
    Debug.Print "Datasheet Before Update"
End Sub

Private Sub Form_Click()
    Debug.Print "Datasheet On Click"
End Sub

Private Sub Form_DataChange(ByVal Reason As Long)
    Debug.Print "Datasheet On Data Change"
End Sub

Private Sub Form_DataSetChange()
    Debug.Print "Datasheet On Data Set Change"
End Sub

Private Sub Form_Dirty(Cancel As Integer)
    Debug.Print "Datasheet On Dirty"
End Sub

Private Sub Form_Load()
    Debug.Print "Datasheet On Load"
End Sub
2
ms-accessvbams-access-2007
Many of those events do not work on datasheet forms. Switch to single or continuous form. - Parfait
take a break and rephrase your question please :) - Krish
Why don't want to create a form, based of table and use it as SourceObject for your subform? In this case you will have a place for events, as I can see from your code you are trying to catch events in main for, not in subform module, which doesn't exist for table object. - Sergey S.

2 Answers

3
votes

I have to credit datAdrenaline on the utteraccess.com/forum for the answer to this issue. Access Datasheet Events Not Firing, Access 2007

Me.CombinationViewDS.SourceObject = "Table.temp_filter_tbl"

When the Source object is set on a datasheet access creates a form object on the fly. To use the in memory Form object Access creates you have to set the event property of the form to be a function in public scope.

Me.CombinationViewDS.AfterUpdate = "=SomFunctionCall()"
0
votes

When you use a table as SourceObject, you don't have VBA code module for storing cde for event. Just create a new form, based on table, with default view mode DataSheet and use it as SourceObject for your subform. In this case you can use any event code, it will work correctly. In main form change code to something like this:

sql = "SELECT * INTO temp_filter_tbl FROM (SELECT DISTINCT JobsID, ID, ReferenceDate, Source, Status, NODE, NodeStatus, DocSource, CutOverYear FROM tempTable " & sqlafterwhere & ")"
db.Execute (sql)
Me.CombinationViewDS.Requery

Hide/rename columns in created form.

I'm not sure if you really need to make totally dynamic subform based on table and use workaround from Utteraccess. It will work, but it makes form logic harder to support and unnecessary increases quantity of public functions