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:

  1. Opens an Access database
  2. Opens a form in this Access database
  3. Fills the form with some data
  4. Triggers a click on "OK" button of the opened form
  5. Closes the form
  6. 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

2 Answers

0
Community On Best Solutions

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

1
Chem1967 On

Thanks to Van Ng and SunKnight0, I have found the problem! As correctly pointed out by SunKnight0, the issue was the Private definition of the click instead of a Public one. It works great now. Thanks for your contribution.