Solved

MS Access Form Button

Posted on 2009-06-29
21
534 Views
Last Modified: 2013-11-28
how do i get the buttons on a MS Access for to run my VB Scripts, I can run this script fine from my desktop, but when i go to the form and edit the button code and add the code from the script it will no work. Please any suggestions. the version of access is 2003. and i am running it on a windows xp machine.
Private Sub Logoff_Click()

On Error GoTo Err_Logoff_Click

 

    Dim IPport, Infname

    Dim oShell: Set oShell = Wscript.CreateObject("WSCript.shell")

 

    IPport = InputBox("Please Enter IP Address .", sTitle)

    oShell.Run ("c:\putty\putty -l HelpDesk 172.16.32.1 -pw L0ck0ut")

    Wscript.Sleep 3500

    oShell.SendKeys "enable {Enter}"

    Wscript.Sleep 1000

    oShell.SendKeys "L0ck0ut{Enter}"

    Wscript.Sleep 1000

    oShell.SendKeys "vpn-sessiondb logoff ipaddress " & IPport & " noconfirm {Enter}"

    Wscript.Sleep 3000

oShell.SendKeys "exit {Enter}"

 

Exit_Logoff_Click:

    Exit Sub

 

Err_Logoff_Click:

    MsgBox Err.Description

    Resume Exit_Logoff_Click

    

End Sub

 

 

Open in new window

0
Comment
Question by:hoshie329
  • 10
  • 8
  • 2
  • +1
21 Comments
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
Set oShell = Wscript.CreateObject("WSCript.shell")
should be
Set oShell = CreateObject("WSCript.shell")

"the script it will no work"  doesn't tell us anything.
You need to be explicit about the problems or errors athat are occurring.
0
 

Author Comment

by:hoshie329
Comment Utility
I created a form in access 2003, I then put and application button in the form. I edited the code and added the code from my vbscript. When I click the button it pops up a message saying Object Required

Thank you
Option Compare Database

________________________________________________________________________

Private Sub Display_Refresh_Click()

On Error GoTo Err_Display_Refresh_Click
 

    Dim stAppName As String
 

    Dim oShell: Set oShell = Wscript.CreateObject("WSCript.shell")

    

    

    oShell.Run ("c:\putty\putty -l HelpDesk 172.16.32.1 -pw L0ck0ut")

    Wscript.Sleep 4000

    oShell.SendKeys "enable {Enter}"

    Wscript.Sleep 1000

    oShell.SendKeys "L0ck0ut{Enter}"

    Wscript.Sleep 1000

    oShell.SendKeys "sh vpn-sessiondb remote {Enter}"

    Wscript.Sleep 3000

    oShell.SendKeys "exit {Enter}"""

    Call Shell(stAppName, 1)
 

Exit_Display_Refresh_Click:

    Exit Sub
 

Err_Display_Refresh_Click:

    MsgBox Err.Description

    Resume Exit_Display_Refresh_Click

    

End Sub

Open in new window

0
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
I see that you haven't changed the code the way I told you to in my first response.

Do that change and try running it again.
If you get an error, post the error message and say which line is highlighted when you get the error?
0
 

Author Comment

by:hoshie329
Comment Utility
I ran it with the changes, this did change my error.

Compile Error: Sub or Function not Defined

the following line is highlighted yellow with and arrow pointing to it from the left

Private Sub Command_Logoff_Click()

in the following line wscriptCreateObject is highlighted Blue

Dim oShell: Set oShell = wscriptCreateObject("WSCript.shell")
0
 
LVL 27

Expert Comment

by:jjafferr
Comment Utility
you will have to put a reference to the wscript runtime,
in VBA > tools > references > scroll down the list and select "Microsoft scripting Runtime"

then run your code.

Hey Pete, you are doing a wonderful job, so keep it up :)

jaffer
0
 

Author Comment

by:hoshie329
Comment Utility
I ran it with the changes, this did change my error.

Compile Error: Sub or Function not Defined

in the following line wscriptCreateObject is highlighted Blue

Dim oShell: Set oShell = wscriptCreateObject("WSCript.shell")
0
 
LVL 27

Expert Comment

by:jjafferr
Comment Utility
what I posted has nothing to do with code,
it tells MS Access to use WSCript, otherwise Access doesn't know.

so please follow my steps.

jaffer
0
 

Author Comment

by:hoshie329
Comment Utility
I went in to the VBA > tools > references > scroll down the list and select "Microsoft scripting Runtime" and placed the check mark there. when i ran the code i got the error still.

Compile Error: Sub or Function not Defined

in the following line wscriptCreateObject is highlighted Blue

Dim oShell: Set oShell = wscriptCreateObject("WSCript.shell")

0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Is the button on the main form or a subform?
0
 

Author Comment

by:hoshie329
Comment Utility
I believe it is on the main form, form 1. i have attached the database so you can see what i am doing.
Floria-VPN-Remote.mdb
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 27

Expert Comment

by:jjafferr
Comment Utility
A few issues here:
1. You had a module, and a Sub using the same name,
    I changed the Sub name to logoff_user
2. You did not define your Sub as Private or Public,
    So I defined it as Public
3. Shell is a reserved name, you should using reserve system/function names,
    I changed it to Shell2
4. The corrections recommended by Peter57r have been implemented.
5. I removed about 7 References you made (including the 1 I recommended), as they are not required.
6. From the Form, I changed the call to the Sub as
    Call logoff_user

jaffer
Public Sub logoff_user()

'On Error Resume Next
 

'this should either be a public Sub or a Public Function

'you cannot call it Sub only
 

 Dim cIPport, Shell, strCommand, ReturnCode

 Dim oShell: Set oShell = CreateObject("WSCript.shell")
 

   cIPport = InputBox("Please Enter IP Address .")

 

     oShell.Run ("c:\putty\putty -l HelpDesk 172.16.32.1 -pw L0ck0ut")

       oShell.Sleep 3500

     oShell.SendKeys "enable {Enter}"

       oShell.Sleep 1000

     oShell.SendKeys "L0ck0ut{Enter}"

       oShell.Sleep 1000

     oShell.SendKeys "vpn-sessiondb logoff ipaddress " & cIPport & " noconfirm {Enter}"

       oShell.Sleep 3000

     oShell.SendKeys "exit {Enter}"
 

         'Create shell object

           Dim Shell2                   '<== Shell is a reserverd name, I changed it

           Set Shell2 = CreateObject("wscript.shell")
 

         'What to run in command line

           strCommand = "ping -n 3 -w 1000 " & cIPport
 

         'Run command and get return code

           ReturnCode = Shell2.Run(strCommand, 0, True)
 

         '0 = pingable, 1 = no response

              If ReturnCode = 0 Then

                 Shell2.Echo cIPport & " is still Connected"

              Else

                 Shell2.Echo cIPport & " is Disconnected"

              End If
 

End Sub

Open in new window

Floria-VPN-Remote.mdb
0
 

Author Comment

by:hoshie329
Comment Utility
this got the command started, it does open a input box asking for the IP, But then sends and error

Run time error '438'
Object doesn't support this property or method.

It does open putty and logs in but then nothing else happens.

when i click the the debug button it shows line 13 in the above code highlighted yellow.

Thanks
0
 
LVL 27

Expert Comment

by:jjafferr
Comment Utility
Here you go,
we had to
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

so please delete the code in the whole module and replace it with the attached code.
We have to remove the object ref before the sleep command, which I did in the attached code too.

jaffer
Option Compare Database

Option Explicit
 

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
 
 

Public Sub logoff_user()

'On Error GoTo Resume_Next
 

'this should either be a public Sub or a Public Function

'you cannot call it Sub only
 

 Dim cIPport, strCommand, ReturnCode

 Dim oShell: Set oShell = CreateObject("WSCript.shell")
 

   cIPport = InputBox("Please Enter IP Address .")

 

     oShell.Run ("c:\putty\putty -l HelpDesk 172.16.32.1 -pw L0ck0ut")

       Sleep 3500

     oShell.SendKeys "enable {Enter}"

       Sleep 1000

     oShell.SendKeys "L0ck0ut{Enter}"

       Sleep 1000

     oShell.SendKeys "vpn-sessiondb logoff ipaddress " & cIPport & " noconfirm {Enter}"

       Sleep 3000

     oShell.SendKeys "exit {Enter}"
 

         'Create shell object

           Dim Shell2                   '<== Shell is a reserverd name, I changed it

           Set Shell2 = CreateObject("wscript.shell")
 

         'What to run in command line

           strCommand = "ping -n 3 -w 1000 " & cIPport
 

         'Run command and get return code

           ReturnCode = Shell2.Run(strCommand, 0, True)
 

         '0 = pingable, 1 = no response

              If ReturnCode = 0 Then

                 Shell2.Echo cIPport & " is still Connected"

              Else

                 Shell2.Echo cIPport & " is Disconnected"

              End If
 

Exit Sub

Resume_Next:
 

    If Err.Number = -2147024894 Then

        'ignore

    Else

        Debug.Print Err.Number & vbCrLf & Err.Description

        MsgBox Err.Number & vbCrLf & Err.Description

    End If

    Resume Next

End Sub

Open in new window

0
 

Author Comment

by:hoshie329
Comment Utility
that corrected that problem, i think this one will be tha last

Run time error '438'
Object doesn't support this property or method.


line wo is highlighted
0
 
LVL 27

Expert Comment

by:jjafferr
Comment Utility
>line wo is highlighted
you mean line TWO, if correct, then remove that line.



0
 

Author Comment

by:hoshie329
Comment Utility
i'm sorry, its been one of those days.

I mean line 40 in the above config.

Thanks
0
 
LVL 27

Accepted Solution

by:
jjafferr earned 500 total points
Comment Utility
Initially we tried to execute your code without looking at what Access can do natively,
so in this modification, I am using some Access built in commands.
instead of Echo, which is a way to represent a message to the user, I used the Access status bar for the message.


jaffer
Option Compare Database

Option Explicit
 

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
 
 

Public Sub logoff_user()

On Error GoTo Resume_Next
 

'this should either be a public Sub or a Public Function

'you cannot call it Sub only
 

 Dim cIPport, strCommand, ReturnCode

 Dim oShell: Set oShell = CreateObject("WSCript.shell")
 

   cIPport = InputBox("Please Enter IP Address .")

 

     oShell.Run ("c:\putty\putty -l HelpDesk 172.16.32.1 -pw L0ck0ut")

       Sleep 3500

     SendKeys "enable {Enter}"

       Sleep 1000

     SendKeys "L0ck0ut{Enter}"

       Sleep 1000

     SendKeys "vpn-sessiondb logoff ipaddress " & cIPport & " noconfirm {Enter}"

       Sleep 3000

     SendKeys "exit {Enter}"
 

         'Create shell object

           Dim Shell2                   '<== Shell is a reserverd name, I changed it

           Set Shell2 = CreateObject("wscript.shell")
 

         'What to run in command line

           strCommand = "ping -n 3 -w 1000 " & cIPport
 

         'Run command and get return code

           ReturnCode = Shell2.Run(strCommand, 0, True)
 

         '0 = pingable, 1 = no response

              If ReturnCode = 0 Then

                 'Echo cIPport & " is still Connected"

                 SysCmd acSysCmdSetStatus, cIPport & " is still Connected"
 

              Else

                 'Echo cIPport & " is Disconnected"

                 SysCmd acSysCmdSetStatus, cIPport & " is Disconnected"

              End If
 

Exit Sub

Resume_Next:
 

    If Err.Number = -2147024894 Then

        'ignore

    Else

        Debug.Print Err.Number & vbCrLf & Err.Description

        MsgBox Err.Number & vbCrLf & Err.Description

    End If

    Resume Next

End Sub

Open in new window

Floria-VPN-Remote.mdb
0
 

Author Closing Comment

by:hoshie329
Comment Utility
I want to thank you for all the help, this has me on my way. The responses were great.

Again thank you
0
 
LVL 27

Expert Comment

by:jjafferr
Comment Utility
You are welcome
0
 
LVL 27

Expert Comment

by:jjafferr
Comment Utility
A piece of advise:
Vista and Windows server 2008, due to security, they don't support "SendKeys", thus your code won't work.

As an alternative, you can use the Free
http://www.autoitscript.com/autoit3/

It contains a good sample program for combining Windows Script and AutoIt.

jaffer
0
 

Author Comment

by:hoshie329
Comment Utility
Thank you,

I will look at this
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now