Solved

MS Access Form Button

Posted on 2009-06-29
21
544 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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
 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

734 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