CODEWITHSUNDEEP
excelms-accessvba
J. Doe
J. Doe

Reputation: 199

Running Access Form from Excel VBA

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"

enter image description here

Upvotes: 0

Views: 4053

Answers (1)

Erik A
Erik A

Reputation: 32632

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.

Upvotes: 1

Related Questions