Issue with AfterInsert Event on table not firing when inserting record from excel

[selooselm]

Hello!

I'm having an issue where my code for AfterInsert event on my table isn't firing when a new record is inserted from excel.

The event fires and works correctly when the record is inserted from within Access, however.

Has anyone encountered this issue before and can point me in the right direction to get these events to fire?

Thanks!

[June7]

How do you insert record from Excel - what is the code?

I can't find any confirmation but I deduce that programmatically adding record does not trigger the data macro. I will have to do some experimentation later.

[selooselm]

Here's the excel vba to export to the database:

Sub UpdateDB()


Dim db As Database
Dim rs As Recordset
Dim currID As Double
Dim currTech As String
Dim currTK As Single
Dim currVC As Single
Dim currWC As Single
Dim currDoc As Single




Application.Workbooks("temp_" & Format(Date, "mm_dd_yyyy")).Activate


Set db = OpenDatabase(DBpath)
Set rs = db.OpenRecordset("KPI", dbOpenTable)
Set MS = ThisWorkbook.Worksheets("Main")


currID = MS.OLEObjects("cmb_Tech").Object.List(MS.OLEObjects("cmb_Tech").Object.ListIndex, 1)
currTech = MS.OLEObjects("cmb_Tech").Object.List(MS.OLEObjects("cmb_Tech").Object.ListIndex, 0)
currTK = MS.OLEObjects("txt_TK").Object.Value
currVC = MS.OLEObjects("txt_VC").Object.Value
currWC = MS.OLEObjects("txt_WC").Object.Value
currDoc = MS.OLEObjects("txt_Doc").Object.Value


    With rs
        .AddNew
        .Fields("ID") = currID
        .Fields("Tech Name") = currTech
        .Fields("Date Entered") = Date
        .Fields("Technical Knowledge") = currTK
        .Fields("Verbal Communication") = currVC
        .Fields("Written Communication") = currWC
        .Fields("Documentation") = currDoc
        .Update
    
        rs.Close
        Set rs = Nothing
        db.Close
        Set db = Nothing
    End With
    
End Sub

[June7]

My first attempt to build data macro is big failure. Doesn't matter how I enter new record - manually or with code - the data macro does not seem to run.

[selooselm]

Would it help to give you an edited copy of my database?

[June7]

I doubt it. You could do this test which I was trying with my db. Insert a record to table using VBA code within Access:

CurrentDb.Execute "INSERT INTO tablename(fieldname) VALUES('somevalue');

If the AfterInsert event triggers then we know the issue is with the Excel code pushing data to Access.

I am not familiar with the OLEObjects code.

[selooselm]

I'll give it a shot. The OLEObjects is for working with activeX form controls on the worksheet.

[June7]

I've never used ActiveX controls, not even in Excel.

[Missinglinq]

...AfterInsert event on my table isn't firing when a new record is inserted from excel...

...The event fires...when the record is inserted from within Access...

You've actually answered the question yourself. As is true for many events that are associated with Controls, the Form_AfterInsert event does not fire if the Record is inserted through VBA code or Macro. Presumably, you can use the same strategy as we use for Control events, which is to explicitly Call the event, immediately after the Record is inserted.

Linq ;0)>

[June7]

I presumed 'AfterInsert event on my table' to mean a data macro (introduced with Access 2010).

selooselm, let me know if that is wrong.

[selooselm]

Correct. This is a data macro tied to my table for after insertion of a new record.

[June7]

Did you get the test done?

[selooselm]

Not yet. I just switched to a new laptop at work and am wrestling with the IT group to get Access installed again.

[Missinglinq]

I don't have 2010, but I wonder if, perhaps, the same rules apply to the AfteUpdate event of a Table, when a Data Macro is involved, as applies to the AfteUpdate event of a Control, which is to say if data is entered by way of keyboard it fires, but if entered through code (such as is the case when importing data from Excel) it doesn't?

Linq ;0)>

[June7]

That's what I was thinking but I finally got data macro to do something. I ran CurrentDb.Execute code to insert a record and the data macro did trigger. Then I tested importing records with the wizard. The data macro triggered with every record that appended. Then I tested adding record through a recordset in Access VBA, also triggered data macro. Last test was to add new record from Excel VBA. This does not trigger the data macro.

Think that settles the question.