How to run access event procedure from vb script after form open

Question

The following vb code opens up a database form at a specific record. I would then like to run the event procedure that is attached to a button on the form. I have tried creating a Macro that runs the procedure, but when running the vb script, it says the procedure cannot be found. I have even tried changing the event procedure from private to public with no avail. I have also even tried just putting the name of the procedure instead of the Macro1 and still a no go. What could I possibly be missing.

Here is the vb Script:

Set accessApp = CreateObject("Access.Application")
accessApp.OpenCurrentDataBase "M:\Shared Documents\Job Cost Analysis\TRAINING JOBCOST ANALYSIS DATABASE\Job_Cost_Analysis_Tracking_Training.accdb", false
accessApp.visible = true
accessApp.UserControl = true


accessApp.DoCmd.OpenForm "frmClients", , , "Clients!client_id= 100005 ", , acWindowNormal, True
accessApp.Forms("frmClients").status_ID=3
accessApp.Run "cmdImportItemStmt_Click"
accessApp.Forms("frmClients").status_ID=34
accessApp.Save

Here is the procedure in access:

Private Sub cmdImportItemStmt_Click()
On Error GoTo Problems

Dim filename As String

'***** CHECK TO MAKE SURE STATUS IS IN ITEMIZED STATEMENT IMPORT ***********
If Me.status_ID.Column(1) = 3 Then

    If IsNull(Me.frmItemizedStmtTotals("AMT DISPUTED").Value) = True Then

        filename = (copyQueue & "CLIENT_" & CStr(Me.client_ID.Value) & "\Client_" & CStr(Me.client_ID.Value) & ".xlsx")

        DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12, "Sheet1", filename, True

        DoCmd.SetWarnings (WarningsOff)
        '**********APPEND ITEMIZED STATEMENT***********
        DoCmd.OpenQuery "append_itemized_stmt_import"

        '**********UPDATE ITEMIZED STATEMENT W/ REASON CODES***********

        DoCmd.OpenQuery "Update_reason_codes_by_desc_null_revcodes"
        DoCmd.OpenQuery "Update_reason_codes_by_desc"

        '************** If facility is OHSU remove non-billable items per contract**********

        If Me.facility_ID = 102 Then
        DoCmd.OpenQuery "qryOHSU_nonbillable"
        End If

        '**************************************************************

        Me.frmISAmtsByRevenueCodes.Requery
        Me.frmItemizedStmtTotals.Requery

        DoCmd.DeleteObject acTable, "Sheet1"

        DoCmd.SetWarnings (WarningsOn)

    Else

        MsgBox "UNABLE TO IMPORT IS RECORDS BECAUSE THE ITEMIZED" & vbCrLf & _
            "STATEMENT'S TOTAL BILLED CHARGES ALREADY HAS A" & vbCrLf & _
            "BALANCE > THAN ZERO INDICATING A PREVIOUS IMPORT", vbOKOnly, "UNABLE TO IMPORT IS RECORDS"

    End If

Else

    MsgBox "INACCURATE CLAIM REVIEW STATUS" & vbCrLf & _
        "FOR ITEMIZED STATEMENT IMPORT", vbOKOnly, "INACCURATE CLAIM REVIEW STATUS"

End If

RecalculateTotals
Exit Sub

Problems:
Err.Clear
Resume Next

End Sub

accessApp.Run "Macro1" you trying to run proc Macro1 not yours cmdImportItemStmt_Click() — Zam
– Zam, Commented Dec 6, 2016 at 16:19
can you make simple test procedure and try to run it? without opening any other forms? leave in your VBS script only following commands: open MS Access and try to run TEST proc. — Zam
– Zam, Commented Dec 6, 2016 at 16:20

Zam · Accepted Answer · 2016-12-06 16:24:31Z

2

VBS script for test (based on yours):

Set accessApp = CreateObject("Access.Application")
accessApp.OpenCurrentDataBase "C:\Temp\Database1.mdb"
accessApp.UserControl = true

accessApp.Run "Test1"

and test code in MS Access db.

can you run it without issue?

answered Dec 6, 2016 at 16:24

Zam

2,9301 gold badge20 silver badges34 bronze badges

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

1 Comment

SikRikDaRula Over a year ago

yes this works, but still does not work after opening form

SikRikDaRula · Accepted Answer · 2016-12-06 19:23:36Z

By changing Me. to Forms!frmClients! in the procedure I was able to get it working. I guess the vb script did not like Me. since the script is run outside of access.

Public Sub ImportItemStmt()
On Error GoTo Problems

Dim filename As String


'***** CHECK TO MAKE SURE STATUS IS IN ITEMIZED STATEMENT IMPORT ***********
If Forms!frmClients!status_ID.Column(1) = 3 Then

    If IsNull(Forms!frmClients!frmItemizedStmtTotals("AMT DISPUTED").Value) = True Then

        filename = (copyQueue & "CLIENT_" & CStr([Forms]![frmClients]![client_ID].Value) & "\Client_" & CStr([Forms]![frmClients]![client_ID].Value) & ".xlsx")

        DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12, "Sheet1", filename, True

        DoCmd.SetWarnings (WarningsOff)
        '**********APPEND ITEMIZED STATEMENT***********
        DoCmd.OpenQuery "append_itemized_stmt_import"

        '**********UPDATE ITEMIZED STATEMENT W/ REASON CODES***********

        DoCmd.OpenQuery "Update_reason_codes_by_desc_null_revcodes"
        DoCmd.OpenQuery "Update_reason_codes_by_desc"

        '************** If facility is OHSU remove non-billable items per contract**********

        If Forms!frmClients!facility_ID = 102 Then
        DoCmd.OpenQuery "qryOHSU_nonbillable"
        End If

        '**************************************************************

        Forms!frmClients!frmISAmtsByRevenueCodes.Requery
        Forms!frmClients!frmItemizedStmtTotals.Requery

        DoCmd.DeleteObject acTable, "Sheet1"

        DoCmd.SetWarnings (WarningsOn)

    Else

        MsgBox "UNABLE TO IMPORT IS RECORDS BECAUSE THE ITEMIZED" & vbCrLf & _
            "STATEMENT'S TOTAL BILLED CHARGES ALREADY HAS A" & vbCrLf & _
            "BALANCE > THAN ZERO INDICATING A PREVIOUS IMPORT", vbOKOnly, "UNABLE TO IMPORT IS RECORDS"

    End If

Else

    MsgBox "INACCURATE CLAIM REVIEW STATUS" & vbCrLf & _
        "FOR ITEMIZED STATEMENT IMPORT", vbOKOnly, "INACCURATE CLAIM REVIEW STATUS"

End If

'RecalculateTotals
Exit Sub

Problems:
Err.Clear
Resume Next
End Sub

Collectives™ on Stack Overflow

How to run access event procedure from vb script after form open

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related