Running Access Form from Excel VBA

Question

I am using Excel 2013 and Access 2013. My access query has a form that takes2 inputs, "start date" and "end date", and then runs a macro in access. Is there a way I can use VBA to input "start date" and "end date" from excel and run the form in access? Here is what I have tried, but I get an error saying "The action or method is invalid because the form or report isn't bound to a table or query"

Sub RunAccessQuery()

Dim strDatabasePath As String
Dim appAccess As Access.Application
Dim startDate As Date
Dim endDate As Date

startDate = ThisWorkbook.Sheets("sheet1").Range("B2").Value

strDatabasePath = "access database path"
Set appAccess = New Access.Application
With appAccess
    Application.DisplayAlerts = False
    .OpenCurrentDatabase strDatabasePath
    .DoCmd.OpenForm "inputForm", , , "start =" & startDate
    '.Quit
End With
Set appAccess = Nothing

ThisWorkbook.RefreshAll
MsgBox ("Data has been updated")

End Sub

This is what my form looks like. Click me runs a macro, the first text box holds variable "start" and second one holds variable "end"

Please provide data on the form (record source, which controls are on it, etc). — Erik A
theres 2 text boxes that you fill with a start date and end date (first text box holds variable "start" and second holds variable "end". then theres a button at the button that will run a macro when clicked. what is record source? — J. Doe
I do not get that error. DB opens, form opens, DB closes. I even confirmed with .Visible = True. However, the form is not filtered to the desired record until I change code to: "start = #" & startDate & "#". What is the form's RecordSource - a table, query, SQL statement? — June7
the form just runs a macro. i dont think its attached to a query or table — J. Doe
Then why would you use the OpenForm WHERE CONDITION argument? Remove that from your code. — June7

Erik A Erik A · Accepted Answer · 2017-08-18T19:33:30

Since I assume your form has no recordsource, and the controls are unbound, the following should avoid the error you've encountered:

Sub RunAccessQuery()

Dim strDatabasePath As String
Dim appAccess As Access.Application
Dim startDate As Date
Dim endDate As Date

startDate = ThisWorkbook.Sheets("sheet1").Range("B2").Value

strDatabasePath = "access database path"
Set appAccess = New Access.Application
With appAccess
    Application.DisplayAlerts = False
    .OpenCurrentDatabase strDatabasePath
    .DoCmd.OpenForm "inputForm"
    .Forms("inputForm").start = startDate
    '.Quit
End With
Set appAccess = Nothing

ThisWorkbook.RefreshAll
MsgBox ("Data has been updated")

End Sub

However, this still does nothing more than just open the form and set the start date, and closes Access immediately after.

Running Access Form from Excel VBA

1 Answers