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
– Erik A, Commented Aug 18, 2017 at 18:55
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
– J. Doe, Commented Aug 18, 2017 at 19:13
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
– June7, Commented Aug 18, 2017 at 19:25
the form just runs a macro. i dont think its attached to a query or table — J. Doe
– J. Doe, Commented Aug 18, 2017 at 19:30
Then why would you use the OpenForm WHERE CONDITION argument? Remove that from your code. — June7
– June7, Commented Aug 18, 2017 at 19:31

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

1

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.

answered Aug 18, 2017 at 19:33

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

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

1 Comment

J. Doe Over a year ago

" .Forms("inputForm").start = startDate " Thats what i was looking for. I just put a .DoCmd.RunMacro "macro" afterward to run the macro that the form should run. I just didnt know how to set the variables in the form. Thank you!

Collectives™ on Stack Overflow

Running Access Form from Excel VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related