Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003

    Unanswered: begginers question

    im completely new to writing VB code so you will prob think im stupid. however the problem i have is this..

    i want to run a macro that makes a connection to the internet. then i want to open a form which requires the internet to be present when it opens, but what is happening is that the form is saying connection not present because the macro hasnt completed yet..ho can i delay to open form action so that the macro can run..heres the code below

    DoCmd.RunMacro Connect

    DoCmd.OpenForm "order", acNormal, , , acFormEdit
    DoCmd.GoToRecord , , acNewRec

    as i said earlier im a newbie... so its probably someting simple... i was wondering if it was the "if ... then" statement i need.. but not sure how to use it.

    would be very greatful for some ass..

    thanks lee

  2. #2
    Join Date
    Nov 2003
    Connecting to the Internet is obviously the easy part....The difficult part is knowing that you are actually connected. Because there are different ways to connect to the Internet (i.e.: through a Modem, through a LAN, etc.) you want to be able to cover all the basis within your application in order to reduce any overhead with User Required Configuration.

    To carry out what you want to accomplish, we will need to detect whether or not an Internet connection has been achieved.

    There are basically three ways to do this:

    1) By reading the Registry. Certain flags (System\CurrentControlSet\Services\RemoteAccess from HKEY_LOCAL_MACHINE) are set within the Registry when a Internet connection is either made or broken. The trouble is, this method will ONLY work for a Modem connection but NOT through a LAN. In other words, if the computer running the application is in fact the computer that contains the Modem, this will work. But, if the computer running the application is connecting to the Internet via Modem through a Local Area Network (LAN), this will not work. Do you know what I mean? So, we check another way....because who knows what the User has for connecting to the Internet.

    2) By using the InternetGetConnectedStateEx function from the wininet.dll library. By using this function you can additionally receive some more information about your connection. For example: Type of connection (LAN/Modem), Using of Proxy, RAS installing, OnLine/OffLine. It's work fine, but there is one problem. If your computer is in a Local Area Network (LAN) but you are connecting to Internet via modem, this method always informs you that you are connected to the internet whether you are connected or not. Soooo...on to the next one.

    3) By Pinging a website (preferably one that will never die ). We do this by sending a direct ping to some well established Internet address (URL) and checking for connection errors. Today we're going to use http:/ (only cause my mail goes there - heh). It's takes more time to determine whether or not a Internet connection is made, but this method is the most reliable out of all three. Slow but reliable...hmmmm.

    Because, I'm sure, a lot of people out there are interested in knowing how to do this, I will post all three methods as though your application has a User Configuration (setup) form or table.

    User Config:

    0 will be considered as Registry Check (method 1)
    1 will be considered as API function check (method 2)
    2 will be considered as a Ping check (method 3)
    Place whichever the selection happens to be into a Integer variable named UserNetConnect.

    Place the following code into the event where you will be opening your Orders form.

    Whoops.....because this post was longer than the allowable 10000 characters, I've been forced to place it into a TWO Posts. Hope ya all don't mind. (ya ya, I know I could have Zip'ed it)

  3. #3
    Join Date
    Nov 2003
    See The Post Above First:

    Place the following code into the event where you will be opening your Orders form.

    Dim ImConnected As Boolean
    Dim TimesUp 
    Select Case UserNetConnect 
       Case 0
                ImConnected = NetConnectMethod0()
       Case 1
                ImConnected = NetConnectMethod1()
       Case 2
                ImConnected = NetConnectMethod2()
    End Select
    ‘You may not need this… it simply ensures that the Net Check has 
    ‘time to complete. Note: Do you see how we used the DateDiff function.
    ‘You can create a SLEEP timer this way as well. 
    TimesUp = Time
    Do Until IsConnected
          ‘If the Do/Loop has been going at it for 10 seconds
          ‘then get out of the Loop
          If DateDiff("S", TimesUp, Time) > 10 Then Exit Do:
    'If we detect no Internet connection then exit the Sub(routine)
    If IsConnected = False Then
        MsgBox”No Internet Connection…@@” & “We can not access the Order “ & _
        “Form unless a Internet Connection is made. Please connect to the Internet”
        Exit Sub
    End if
    'Everything appears OK and a Internet connection was detected
    'so lets open the form and goto a new record.
    DoCmd.OpenForm "order", acNormal, , , acFormEdit
    DoCmd.GoToRecord , , acNewRec

    Now....Copy and paste the code below into Declarations section of a database Module. Call your module...oh....something like "NetCheck":

    'Working with registry declarations and constants
    Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long
    Private Declare Function RegOpenKey Lib "advapi32.dll" Alias "RegOpenKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, phkResult As Long) As Long
    Private Declare Function RegQueryValueEx Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal lpReserved As Long, lpType As Long, lpData As Any, lpcbData As Long) As Long
    Private Const ERROR_SUCCESS = 0&
    Private Const APINULL = 0&
    Private Const HKEY_LOCAL_MACHINE = &H80000002
    'Working with wininet.dll declarations and constants
    Private Declare Function InternetGetConnectedStateEx Lib "wininet.dll" Alias _ "InternetGetConnectedStateExA" (ByRef lpdwFlags As Long, ByVal _
     lpszConnectionName As String, ByVal dwNameLen As Long, ByVal _
    dwReserved As Long) As Long 
    'Private Declare Function InternetGetConnectedState Lib "wininet.dll" (ByRef lpdwFlags _
    As Long, ByVal dwReserved As Long) As Long 'used this with IE4
    'Declares for direct ping
    Private Declare Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" (ByVal _
    lpszAgent As String, ByVal dwAccessType As Long, ByVal lpszProxyName As String, _
    ByVal lpszProxyBypass As String, ByVal dwFlags As Long) As Long
    Private Declare Function InternetOpenUrl Lib "wininet.dll" Alias "InternetOpenUrlA" _
    (ByVal hInet As Long, ByVal lpszUrl As String, ByVal lpszHeaders As String, ByVal _
    dwHeadersLength As Long, ByVal dwFlags As Long, ByVal dwContext As Long) As Long
    Private Declare Function InternetCloseHandle Lib "wininet.dll" (ByVal hInet As Long) As Long
    Const INTERNET_FLAG_RELOAD = &H80000000
    Public Funtion NetConnectMethod0() As Boolean
       Dim ReturnCode As Long, hKey As Long
       Dim lpSubKey As String, phkResult As Long
       Dim lpValueName As String, lpReserved As Long
       Dim lpType As Long, lpData As Long
       Dim lpcbData As Long
       lpSubKey = "System\CurrentControlSet\Services\RemoteAccess" & Chr$(0)
       ReturnCode = RegOpenKey(HKEY_LOCAL_MACHINE, lpSubKey, phkResult)
       If ReturnCode = ERROR_SUCCESS Then
           hKey = phkResult
           lpValueName = "Remote Connection"
           lpReserved = APINULL
           lpType = APINULL
           lpData = APINULL
           lpcbData = APINULL
           ReturnCode = RegQueryValueEx(hKey, lpValueName, lpReserved, lpType, ByVal _
           lpData, lpcbData)
           lpcbData = Len(lpData)
           ReturnCode = RegQueryValueEx(hKey, lpValueName, lpReserved, _
           lpType, lpData, lpcbData)
           If ReturnCode = ERROR_SUCCESS Then
               If lpData = 0 Then
                    NetConnectMethod0 = False
                    NetConnectMethod0 = True
               End If
               NetConnectMethod0 = False
           End If
    End If
    RegCloseKey (hKey)
    End Function
    Public Function NetConnectMethod1(Optional ByRef ConnectionInfo As Long, _
    Optional ByRef sConnectionName As String) As Boolean
       Dim dwFlags As Long
       Dim sNameBuf As String, msg As String
       Dim lPos As Long
       sNameBuf = String$(513, 0)
       If InternetGetConnectedStateEx(dwFlags, sNameBuf, 512, 0&) Then
           lPos = InStr(sNameBuf, vbNullChar)
           If lPos > 0 Then
              sConnectionName = Left$(sNameBuf, lPos - 1)
              sConnectionName = ""
           End If
           NetConnectMethod1 = True
           'You really don't need all the msg code below. It is simply here
           'so that you can display the type of connection.
           msg = "Your computer is connected to Internet" & vbCrLf & "Connection Name: " & sConnectionName
           If (dwFlags And INTERNET_CONNECTION_LAN) Then
               msg = msg & vbCrLf & "Connection use LAN"
           ElseIf lFlags And INTERNET_CONNECTION_MODEM Then
               msg = msg & vbCrLf & "Connection use modem"
           End If
           If lFlags And INTERNET_CONNECTION_PROXY Then msg = msg & vbCrLf & "Connection use Proxy"
           If lFlags And INTERNET_RAS_INSTALLED Then
              msg = msg & vbCrLf & "RAS INSTALLED"
              msg = msg & vbCrLf & "RAS NOT INSTALLED"
           End If
              msg = msg & vbCrLf & "You are OFFLINE"
              NetConnectMethod1 = False
              msg = msg & vbCrLf & "You are ONLINE"
              NetConnectMethod1 = True
           End If
              msg = msg & vbCrLf & "Your connection is Configured"
              msg = msg & vbCrLf & "Your connection is not Configured"
           End If
           NetConnectMethod1 = False
           msg = "Your computer is NOT connected to Internet"
        End If
        'You can un-Rem the line below if you want to display a
        'message box showing the type of connection. 
        'MsgBox msg, vbInformation, "Checking connection"
    End Function
    Public Function NetConnectMethod2() As Boolean
       Dim hInet As Long
       Dim hUrl As Long
       Dim Flags As Long
       Dim url As Variant
       hInet = InternetOpen(App.Title, INTERNET_OPEN_TYPE_PRECONFIG, vbNullString, vbNullString, 0&)
       'Checking connection with
       If hInet Then
          hUrl = InternetOpenUrl(hInet, "", vbNullString, 0, Flags, 0)
          If hUrl Then
             NetConnectMethod2 = True
             Call InternetCloseHandle(hUrl)
             NetConnectMethod2 = False
          End If
       End If
       Call InternetCloseHandle(hInet)
    End Sub
    That should just about do it. I know it appears quite long winded (so to speak - sorry about that) but it really is'nt. I hope this helps you, and any others out there.

    sheesh...I spent way to much time on this post.


  4. #4
    Join Date
    Dec 2003
    thanks for your time.. but this seems a bit over my head.. i dont know much about programming but am keen to learn.. do you any good books. im reading a "sams teach yourself VB" at the moment but i think i need some sort of reference book so i can see some code in action..

    anyway back to my problem. i should have been a bit more specific..
    basically when the cmd button is pressed to open my form i want it to run the macro "Connect" which maps my ftp site to the Z drive on my local machine so that the table which is stored there can be retreived by the form...

    but whats happening is that the for tries to open before the drive has been mapped so i get a "cant locate table error" or something on those lines..i need some sort of delay or check to makesure the form doesnt open until the drive has been mapped..

    as i said before im very new to VB and was wondering if i could use the IF THEN... eg

    IF (macro complete) THEN open form....(dont laugh)

    something like that anyway...

    thanks for your time


  5. #5
    Join Date
    Nov 2003
    W H A T !!!! All that and not even on Topic!!! Just kidding.

    Try the code below:

    To use this function Place the following code into the event where you will be opening your Orders form:

    If CheckNetwork("\\network_server\drive") = True Then
       DoCmd.OpenForm "order", acNormal, , , acFormEdit
       DoCmd.GoToRecord , , acNewRec
    End IF
    Place the code below into a database module:

    Private Declare Function prn_WNetGetConnection Lib "mpr.dll" _
              Alias "WNetGetConnectionA" (ByVal LocalName$, ByVal RemoteName$, _
              cbRemoteName&) As Long
    Private Declare Function prn_WNetAddConnection Lib "mpr.dll" Alias _
              "WNetAddConnectionA" (ByVal NetPath$, Password, LocalName&) As Long
    Const ERROR_NO_ERROR = 0
    Const ERROR_BAD_NET_NAME = 67
    Const ERROR_MORE_DATA = 234
    Const ERROR_BAD_DEVICE = 1200
    Const ERROR_NO_NET_OR_BAD_PATH = 1203
    Const ERROR_NO_NETWORK = 1222
    Const ERROR_NOT_CONNECTED = 2250
    Public Function CheckNetwork(DriveOrPrinter$) As Boolean
    'Purpose:  To check to see if a drive or printer on the network is available
    'Accepts:   Drive as "C:","LPT3:" or "\\network_server\drive"
    'Returns:   True (-1) on Success, False (0) on Failure
    On Error GoTo Err_CN
        Dim dwError&
        Dim RemoteNamesz&
        Dim RemoteName$
        CheckNetwork = True
        If Instr(DriveOrPrinter, "\\") < 1 Then  'local named resource
            RemoteName = String(255, 0)
            RemoteNamesz = Len(RemoteName)
            dwError = prn_WNetGetConnection(DriveorPrinter, RemoteName, RemoteNamesz)
            If dwError = ERROR_CONNECTION_UNAVAIL or _
                          dwError = ERROR_NOT_CONNECTED Or _
                          dwError = ERROR_NO_NETWORK Then
                CheckNetwork = 0
                GoTo NetMsg
            End If
        Else   'a network address is supplied to the function
                'we supply a null password, which may be required & a null connection name 
                'since we're not actually connecting, just checking the connection
                'will return ERROR_DEVICE_AREADY_REMEMBERED if available
            dwError = prn_WNetAddConnection(DriveOrPrinter, Null, 0&)
            If dwError = ERROR_NO_NETWORK Or _
                         dwError = ERROR_NOT_CONNECTED Or _
                         dwError = ERROR_CONNECTION_UNAVAIL Or_
                         dwError = ERROR_NO_NET_OR_BAD_PATH Or _
                         dwError = ERROR_ACCESS_DENIED Or _
                         dwError = ERROR_BAD_NET_NAME Then
                CheckNetwork = 0
                GoTo NetMsg
            End If
        End If
        GoTo Exit_CN
        MsgBox "The required network device is not currently available.", 16, "Check Network"
        Exit Function
        MsgBox "Error: " & Err & " " & Error$, 16, "Check Network"
        Resume Exit_CN
    End Function
    Note: If you want to actually create a network connection using WNetAddConnection, change the last parameter call in the declarations to ByVal LocalName as String, rather than a long or integer, and pass your local drive or port connection like "c:".

    whew...hope I'm on some sort of topic now

  6. #6
    Join Date
    Dec 2003

    Wink cheers

    cheers mate works great.. thanks.. for the help

  7. #7
    Join Date
    Oct 2003


    Here's another way...

    The best way to know whether or not you're connected is to try something that will only succeed if you are connected, and that will raise an error (on error goto ...) if not.

    Arrange to suppress any warning combo-boxes that would appear; prompts and so-forth. You don't want to be asking the user to connect; you want the operation to fail and to throw an error. (And be sure to restore the warning status to the way it was when you're done.)

    Obviously this "thing that you attempt" should be a fast do-nothing operation. And you should still put up a message and an hourglass because any attempt might take a second or so the first time you try it even when it succeeds. (It might take a lot longer on the user's slow machine than on your development speedo.)

    But I think that this approach would be a lot cleaner and more flexible than diddling with the registry, because when some new way of connecting to the Internet inevitably comes about, the registry-related code would "break" while the try-it approach would continue to operate.

    (There will always be a new and creative reason for things not to work.)

    Plus, by examining the exact error-code you received, you can tell the user a lot more about what to do. Anything from "the necessary software is not installed" to "please plug the cable back in" could be dealt with the same way.

    ChimneySweep(R): fast, automatic
    table repair at a click of the

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts