Going Online To The Client's Web Site

Here's a good one: your contacts data probably includes a field that contains the web site address of your client, such as " www.vif.com/users/cgreaves ". You are about to telephone the client, but would like to review their web site to collect information.

We will develop the code that launches your browser to the client web site as soon as you click in the client's web site field.

We will include a procedure for checking that we are on line (some of us use dial-up connections), and if we are not online, we will say so. I am indebted to Andrew Cronnolly of Woody's Lounge for this tip.

We will also check that there is data in the web page field, before launching the browser to an empty link!

First, let's write the VBA code to test if we are on-line. We will write the procedure, and we will write a procedure to test our procedure(!), and we will test our procedure.

Close down your form temporarily, and from the database window choose the Modules tab and elect to start a New Module. You will be returned to the VBE in a module empty except for these lines:

Option Compare Database
Option Explicit

Choose File, Save, and you will be prompted for a name for this module. We will use the name "Utilities". In a later course we will show you how to develop libraries of utility procedures in Word, Excel, Access and PowerPoint.

Into your module "Utilities" enter the following code:

Private Declare Function InternetGetConnectedState Lib "wininet.dll" (ByRef lpSFlags As Long, ByVal dwReserved As Long) As Long
Public Function blnOnline() As Boolean
blnOnline = InternetGetConnectedState(0, 0)
End Function

The line " Private Declare" must be near the top of the module, before any Function or Sub procedures. This line tells VBA that we will be making use of a file called "wininet.dll", and you may want to check that you have that file on your system. In Windows Explorer choose Tools, Find, and enter "C:Winnet.Dll" in the text box. There should be a copy, and only one copy, in your C:\Windows\System\ folder.

The " Public Function blnOnline" is a utility procedure. It is Public (so we will be able to call it from almost anywhere else, very handy!), and it is a Function (so it can return a value as a result of its work); it's name is prefaced with "bln" to remind us humans that it returns a value that is Boolean (on/off, yes/no) in nature.

The function merely places a call to another function in winnet.dll and passes on that value. Nothing to it, really.

Now we will write a short test macro. We call it a macro because it is a special type of SUBroutine - a subroutine with no parameters can be called directly by the end user. (If you have been recording macros in Word or Excel, go take a look at them. I bet they are all SUBroutines with no parameters in the parentheses!)

Sub TESTblnOnline()
MsgBox blnOnline
End Sub

This little macro merely calls our new procedure and pops up a message box with either "true" or "false"; the answer will be "True" if you are connected to the internet.

Run the macro by making sure that the insertion point (text cursor) is within the body of your subroutine, and then tapping the F5 function key on your keyboard.

Easy!

Once I have tested a procedure and found it to work satisfactorily, I usually comment out the TEST subroutine by placing a single-quote at the head of each line, and dragging the commented block inside the procedure.

Public Function blnOnline() As Boolean
blnOnline = InternetGetConnectedState(0, 0)
' Sub TESTblnOnline()
'     MsgBox blnOnline
' End Sub
End Function

We are now in a position to write the VBA code for the event that occurs when the user clicks in the WEBPAGE field of our form.

Reenter design mode for the form frmClient, select the WEBPAGE text box, and in the properties window look for the "On Click" event.

You will generate a skeletal procedure by choosing the triple-period button:

Private Sub WEBPAGE_Click()
End Sub

We now flesh out this skeleton.

Private Sub WEBPAGE_Click()
If blnOnline Then
Dim retval As Double
Dim strDest As String
strDest = Me!WEBPAGE
retval = Shell("C:\Program Files\Mozilla\mozilla.exe" & strDest)
Else
MsgBox "NOT online"
End If
End Sub

The first line calls our new utility procedure to check if we actually have a connection established. If we do no, a message box will pop up with "Not nine" displayed. We ought to establish a dial-up connection before clicking the web-page text box.

If a connection is already established, we will proceed to declare a dummy variable to receive the result of our SHELL call; we will declare a string variable to hold the web page name; we will load the value of the WEBPAGE field to our string variable, and we will "shell out" to our browser program, passing to it the name of the client web site (held in the string variable).

Now, you will need to replace my string for the Mozilla browser with the appropriate string for your browser, be it Opera, Netscape, Internet Explorer or whatever.

If the web page is empty, we want to bypass this entire process, and we can do that by augmenting our Click event procedure thus:

Private Sub WEBPAGE_Click()
If Trim(Me!WEBPAGE) <> "" Then
If blnOnline Then
Dim retval As Double
Dim strDest As String
strDest = Me!WEBPAGE
retval = Shell("C:\Program Files\Mozilla\mozilla.exe" & strDest)
Else
MsgBox "NOT online"
End If
Else
End If
End Sub

We have included another If-Then-Else statement to test if the WEBPAGE field, stripped of all leading and trailing spaces, has any value whatsoever. If the field is empty, we do nothing else, just dropping out through the bottom of the If statement.

By the way, since one If statement is nested inside another, we say that this is a Nested-If structure. Complicated it isn't.

Search