Solved

MS Access Form Button

Posted on 2009-06-29
21
537 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
ID: 24752191
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
ID: 24755687
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
ID: 24755822
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
ID: 24755910
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
ID: 24756139
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
ID: 24756279
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
ID: 24756367
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
ID: 24756467
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
ID: 24756607
Is the button on the main form or a subform?
0
 

Author Comment

by:hoshie329
ID: 24756641
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 27

Expert Comment

by:jjafferr
ID: 24760457
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
ID: 24763248
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
ID: 24763594
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
ID: 24765837
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
ID: 24765913
>line wo is highlighted
you mean line TWO, if correct, then remove that line.



0
 

Author Comment

by:hoshie329
ID: 24767945
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
ID: 24771398
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
ID: 31598061
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
ID: 24777058
You are welcome
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 24830516
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
ID: 24839440
Thank you,

I will look at this
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show, step by step, how to integrate R code into a R Sweave document
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

910 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

21 Experts available now in Live!

Get 1:1 Help Now