form event not executing

[prasadgov]

Hi,

I have an VBscript which opens a Access db and loads the imported data from two excel sheets and runs few action queries in the frmDone event.
The issue is when i run the script, it executes the excel actions but fails to open the database. But this is random since when i try to run the script again, the database opens and executes the action in frmDone.

Recently, they re-imaged my machine but with the same office setup (Office365). I am not sure if this why this issue is occurring since after repeated tries, the script executes.
correctly.

Set objExcel = Nothing
        Dim db ''As DAO.Database 
        Dim ac ''As Access Application 
        Set ac = CreateObject("Access.Application")
        ac.OpenCurrentDatabase("C:\access\perfdata_fe.accdb") 
        set shl = createobject("wscript.shell")
        shl.sendkeys "^{F4}"
        set db = ac.CurrentDB
        ac.docmd.openform "frmDone"
        'ac.docmd.closeform "frmDone"
        'appAccess.DoCmd.Close 3, cstrReport ' acReport = 3
'appAccess.Quit
'Set appAccess = Nothing

else
    WScript.Echo "Import Aborted."

wscript.quit

End if
    '    ac.docmd.closeform "frmDone"
        'ac.DoCmd.Close 3, cstrReport ' acReport = 3
ac.Quit
Set ac = Nothing
 WScript.Echo "Import Complete."
wscript.quit

TIA

 

[Minty]

Put a Stop or MsgBox command in the VBA in frmDone, and see when and if it fires.

Doesn't ^{F4} send a close command to the active window or am misinterpreting that?

 

[prasadgov]

Put a Stop or MsgBox command in the VBA in frmDone, and see when and if it fires.

Doesn't ^{F4} send a close command to the active window or am misinterpreting that?

I inherited this database and was also curious about ^{F4} command but the script opens the database and the runs the form event correctly.
My issue is, at times, the script fails to open the database but does on repeated tries!

 

[GPGeorge]

I inherited this database and was also curious about ^{F4} command but the script opens the database and the runs the form event correctly.
My issue is, at times, the script fails to open the database but does on repeated tries!

Sendkeys is notoriously unreliable especially when you are opening and closing different applications. There's no guarantee that the application you want to effect (in this case, close) is the one that is currently active when it is executed. I'd look for an alternative way to execute the required action.

 

[sonic8]

My issue is, at times, the script fails to open the database but does on repeated tries!

Long shot:
Before executing your script, check whether there is a hanging instance of MsAccess.exe in Task Manager.
Does the script fail to open the database when there is a hanging instance and succeeds when there is none?

 

[prasadgov]

Long shot:
Before executing your script, check whether there is a hanging instance of MsAccess.exe in Task Manager.
Does the script fail to open the database when there is a hanging instance and succeeds when there is none?

No hanging instances of MsAccess.exe in Task Manager. I checked

 

[prasadgov]

Sendkeys is notoriously unreliable especially when you are opening and closing different applications. There's no guarantee that the application you want to effect (in this case, close) is the one that is currently active when it is executed. I'd look for an alternative way to execute the required action.

hmm. I am thinking to have an alternate way where in it opens the database. I will copy and paste the form event into a module and then a runcode command in a macro. The VBScript can open the form and have a DoCmd to run this macro. Is this the right approach?

 

[Minty]

If the ^{F4} is designed to close the access app after it has "done it's thing" there are much better ways of achieving that.
You could simply put an Application.Quit into the VBA in the Access database.

If not I don't understand it's purpose? Have you tried commenting it out.
I would simply open access with a command-line switch to open the frmDone rather than all the other messing about.

 

[prasadgov]

If the ^{F4} is designed to close the access app after it has "done it's thing" there are much better ways of achieving that.
You could simply put an Application.Quit into the VBA in the Access database.

If not I don't understand it's purpose? Have you tried commenting it out.
I would simply open access with a command-line switch to open the frmDone rather than all the other messing about.

The reason the VBscript is used is because it formats and saves the Excel sheets into xlsx before opening the Access.
I'll try to comment and use the Quit command.

 

[Pat Hartman]

I'd look for an alternative way to execute the required action.

Forms are intended to interact with humans. If you have a batch process, it should not rely on automating a form using send keys. Create a macro to run the shared code. Then if you want to execute the code from a form OR from external program, both paths can run the same procedure. You can open the database with the /m switch so that it runs the macro. The last step of the macro must close the database so it doesn't get left open.