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 · Accepted Answer · 2019-04-25 14:00:46Z

1

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

answered Apr 25, 2019 at 14:00

Van Ng

8212 gold badges8 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chem1967 Over a year ago

Dear Van Ng,I have already tried that before posting the question, but I get the following error message: Run-time error '2465' Application-defined or object-defined error

SunKnight0 Over a year ago

The above code does not "click" the button, it just executes the VBA code behind the button's OnClick event. So either the name/path to the form/button is wrong, or you have not yet assigned VBA code to be executed on the click event of the button. You may also need to change the definition of the Click function from Private to Public, if calling it from outside the form's VBA code.

Chem1967 · Accepted Answer · 2019-04-25 15:48:22Z

1

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.

answered Apr 25, 2019 at 15:48

Chem1967

551 silver badge7 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related