Results 1 to 8 of 8
  1. #1
    Letad is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    4

    How to execute a batch file & return output from a Sub Form

    TASK: check for specific set of software on employee's assigned laptop using a batch file within Access.



    Problem: found a solution that creates a function to run the batch file and call that function in a "Run code" Macro. However, I don't know how to capture the value in the field/parameter from the Access sub form "Hardware" that the batch file will use for it's output.

    Here is a snapshot of the batch file - checkSoftware.bat

    @echo off
    IF exist "\\%1\C$\Program Files\Internet Explorer\Iexplore.exe" goto ONE
    goto OFFLINE

    :ONE
    IF exist "\\%1\C$\Program Files\Altova\XMLSpy2016\xmlspy.exe" Echo %1% Has XMLSpy 2016 Installed
    IF exist "\\%1\C$\Program Files\Altova\XMLSpy2015\xmlspy.exe" Echo %1% Has XMLSpy 2015 Installed
    IF exist "\\%1\C$\Program Files\Altova\XMLSpy2014\xmlspy.exe" Echo %1% Has XMLSpy 2014 Installed
    IF exist "\\%1\C$\Program Files\Altova\XMLSpy2013\xmlspy.exe" Echo %1% Has XMLSpy 2013 Installed
    goto NOXML

    :NOXML
    Rem Echo %1% NO XMLSPY Installed
    goto OTHER

    :OFFLINE
    Echo %1% is not online in Building, cannot check for XMLSPY
    goto OTHER

    :OTHER
    IF exist "\\%1\C$\Program Files (x86)\TechSmith\Snagit 9\Snagit32.exe" Echo %1% Has SNAGIT Installed
    IF exist "\\%1\C$\Program Files (x86)\Microsoft Office\Office14\visio.exe" Echo %1% Has Visio Installed
    IF exist "\\%1\C$\Program Files (x86)\Microsoft Office\Office14\winproj.exe" Echo %1% Has Project Installed
    IF exist "\\%1\C$\Program Files\SmartBear\ReadyAPI-1.5.0\bin\ReadyAPI-1.5.0.exe" Echo %1% Has Soap Installed
    IF exist "\\%1\C$\Program Files (x86)\Teradata\Client\14.10\bin\bteqwin.exe" Echo %1% Has TeraData Installed
    IF exist "\\%1\C$\Program Files (x86)\HP\Unified Functional Testing\bin\Analyzer.exe" Echo %1% Has UFT Installed
    IF exist "\\%1\C$\Program Files (x86)\SSH Communications Security\SSH Tectia\SSH Tectia Client\ssh-client-g3.exe" Echo %1% Has Tectia Installed
    IF exist "\\%1\C$\Program Files\TextPad 7\Textpad.exe" Echo %1% Has Textpad Installed
    goto END

    :END

    Here is a snapshot of the function in a module that I found online - RunMyBatch

    Option Compare Database
    Public Function RunMyBatch()
    Dim input1 As String
    input1 = Forms![EmployeeDetails]!SubForm![Hardware]![ComputerName]
    CallShell("c:\DSS\DS\Inventory\InventoryDatabase\c hecksoft.bat")
    End Function

    'Check Software macro will check the software on anemployee's computer from their hardware record
    'run the macro from the [Hardware] subform in [EmployeeDetails] form


    Here is what I have created thus far for CheckSW Macro - Check Software is a control button on the Hardware subform which runs this macro

    SelectObject
    Object Type - Form
    Object Name - Hardware subform
    In Database Window - yes
    If [Forms]![Hardware subform]![Type].[Text]="Laptop"
    GoToControl
    Control Name - ComputerName
    RunCode
    Function Name - RunMyBatch
    End If
    *This is where I'm stuck...the macro runs a OnClick event whenever the "Check Software" button is clicked, but this is the error I get



    • "Microsoft Access cannot find the referenced form "Hardware subform"


    Hopefully, I explained enough detail, but if not just let me know what I left out. thank you!

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I'll start with: If [Hardware subform] is truly a SubForm then it will not be in the Forms Collection.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Cannot find "hardware subform? Is it not named [Hardware] ??
    Forms![EmployeeDetails]!SubForm![Hardware]![ComputerName]
    Aside from that, not sure about the reference to it.
    Forms![EmployeeDetails]!Controls.SubformControlName.[Hardware]![ComputerName]
    Put a break there and in the immediate window type ?Forms![EmployeeDetails]!Controls.SubformControlName.[Hardware]![ComputerName].Name and hit enter. Play with the reference until you get a valid response.
    Another aside: why dim and assign input1 and then not use it?
    Where is your Option Explicit statement?
    Why not use VB script to return values to your function? I know of no way to pass results to a function by way of a batch file, which returns nothing AFAIK. Maybe you'd have to create a csv file and read from that, but again, it seems like the more difficult route to take. You can access, read/write to and delete files via vb, so why bring command files into the picture? Maybe take a look here http://www.devhut.net/2013/10/23/det...gram-vbscript/

    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Letad is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    4
    I didn't know which technique to apply Macro vs. VB script, hence the batch file in the Run Code macro seemed appropriate as I considered my options. I am not proficient with VB code that's why I leaned more to the Macro option, but I've only worked with macros in Excel. Now that you mention that a VB script would be a better route, I'll see what code I can find that will assist me with developing a VB script. I'll start with the link you provided.

    In response to your questions:

    Put a break there and in the immediate window type ?Forms![EmployeeDetails]!Controls.SubformControlName.[Hardware]![ComputerName].Name and hit enter. Play with the reference until you get a valid response. I had not thought of the Subform as a control. Thank you, I will give this a try.

    Another aside: why dim and assign input1 and then not use it? That's just it, I don't know how to use it. This is where I'm stuck at

    Where is your Option Explicit statement? Didn't know I needed to put this statement in there, but I know now.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Note that vb script and vba (vb) are not the same thing. If you use vb script you need a reference to vbscripting runtime. Also, the subform is not a control - it is contained in the subform control.
    Post back if you need more help. You should be able to find a vb or vb script solution to your issue.

  6. #6
    Letad is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    4
    So the Visual Basic editor in Access is not the same as VBA? Is VBA a programming language? To write a macro in Access is totally different than a writing one in Excel, what language is the macro using? Can I find vb scripts in this forum?

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Visual Basic for Applications (VBA) is an Object Oriented Programming language. Parts of the language is common between Office programs, but each has their own object model. VBE as some call it, is the language Editor. You can write code and create a standard module using Notepad if you want, saving it with a .bas extension, but I suspect it is seldom done as you'd lose all of the editor functionality and I doubt you can do that for form or report modules (other than to import it into the form module as text). My point is that like HTML, one can write code for some things outside of the editor.

    Visual Basic is not VBA, but there are some similarities. You can create a form with VB (in fact, if you want one, you must) but AFAIK, you cannot do so with VBA.

    VBScript is a lightweight variation of VBA based on the Component Object Module and that is what I meant by it not being the same as VBA. If you use the FileSystemObject to access system files, you are actually using VBScript, but I doubt MS bothers to make that distinction on some of their web help pages. In fact, to create a file system object which you would use to work with system files, the syntax includes the word Scripting: Set fs = CreateObject("Scripting.FileSystemObject"), and you must have a reference to the VBScript runtime dll otherwise you will raise errors. As for finding scripts, I think the functionality is so ingrained in most Access people's VBA psyche as to be one and the same. Really all you need is a problem to solve, and Google for a solution. If it involves scripting, an astute poster will say that a reference to the scripting library is required and then you'll know what you're dealing with. In fact, invoking a method or creating an object based on that dll is assimilated into the vba code in such a way that you'd never notice it if you didn't recognize it.

    You don't write Macros, you build them with the Macro builder. I'll always remember this tag line from a forum poster somewhere:
    Macros are for Weenies; code is cool!
    So you will elevate your coolness by not referring to them that way! Macros have limitations, but they're the automatic transmission of the Access 'drivers' and many would be helpless without them. I cannot remember the last time I used anything other than an AutoExec macro, unless it was to help someone here.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Letad is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    4
    Thank you so much for such a thorough explanation Now it is time for me to get my research on...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Open and log in to database through batch file
    By spacekowboy in forum Access
    Replies: 9
    Last Post: 11-02-2015, 03:22 PM
  2. Exporting Form to PDF - Output File Name
    By thexxvi in forum Access
    Replies: 13
    Last Post: 05-26-2015, 07:25 PM
  3. Replies: 3
    Last Post: 05-02-2011, 07:34 AM
  4. Run batch file
    By shay in forum Access
    Replies: 3
    Last Post: 12-15-2010, 09:47 AM
  5. Running Batch File
    By NoiCe in forum Programming
    Replies: 2
    Last Post: 03-21-2010, 07:05 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums