MS Access Form Button

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

hoshie329Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
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
hoshie329Author Commented:
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
peter57rCommented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

hoshie329Author Commented:
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
jjafferrCommented:
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
hoshie329Author Commented:
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
jjafferrCommented:
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
hoshie329Author Commented:
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
GRayLCommented:
Is the button on the main form or a subform?
0
hoshie329Author Commented:
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
jjafferrCommented:
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
hoshie329Author Commented:
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
jjafferrCommented:
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
hoshie329Author Commented:
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
jjafferrCommented:
>line wo is highlighted
you mean line TWO, if correct, then remove that line.



0
hoshie329Author Commented:
i'm sorry, its been one of those days.

I mean line 40 in the above config.

Thanks
0
jjafferrCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hoshie329Author Commented:
I want to thank you for all the help, this has me on my way. The responses were great.

Again thank you
0
jjafferrCommented:
You are welcome
0
jjafferrCommented:
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
hoshie329Author Commented:
Thank you,

I will look at this
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.