How to trigger a click in Access database form from Excel VBA code

Question

I'm trying to trigger a click on "OK" button of a form in Access database from a VBA code in Excel.

The main idea is as follows. A VBA code in Excel goes through the following steps:

Opens an Access database
Opens a form in this Access database
Fills the form with some data
Triggers a click on "OK" button of the opened form
Closes the form
Closes the Access database

As for now, the code is able to open the database, to open the form, to fill it with data, but I'm unable to force the click on the "OK" button. I tried several different approaches of type:

.Forms("Subscriptions_Redemptions").Controls("OK").Click

but neither works...I would appreciate any help. To make things clear, clicking on "OK" button activate an access VBA code that dispatches the information from the form in the database using "Event Procedure" On Click.

Here is the present version of the code:

Private Sub CommandButton4_Click()
    Dim appAccess As Object

    'create new access object
    Set appAccess = CreateObject("Access.Application")

    'open the acces project
    Call appAccess.OpenCurrentDatabase( _
    "H:\PROD\HIGH_YIELD\HY_LUX\Database\HY_LUX.mdb")
    appAccess.Visible = True

    With appAccess
        Application.DisplayAlerts = False
       .DoCmd.OpenForm "Subscriptions_Redemptions"
       .Forms("Subscriptions_Redemptions").DateVal = "04/12/2019"
       .Forms("Subscriptions_Redemptions").Controls("OK").Click
    End With

    Set appAccess = Nothing
End Sub

Van Ng Van Ng · Accepted Answer · 2019-04-25T14:00:46

You should call form's object model, that means following syntax:

.Forms![Subscriptions_Redemptions].ButtonName_Click

Where ButtonName is an button control object name, that can be specified through properties window, or looking at event handler

How to trigger a click in Access database form from Excel VBA code

2 Answers