Launch a URL from a button in SL pt 2

As I explained in part 1 a client required a means to link an SL AP document to its source location by way of a URL.  I am doing this by adding a button to the SL AP screen that will open the URL location allowing the user to see the source transaction.

The hard part of this (if you don't create VBA customizations for SL every day) is the VBA code required to retrieve and launch the URL.  We are storing the referring document ID in the AP "user" field in SL but we have to reach back to the source database to get the URL of that document.  In part 1 I mentioned that a stored procedure returns the URL for a given document ID.

So we have the document ID on the SL AP screen in a user field.  A button on the screen should open the source document from the provided URL when clicked by the user.

Here is a basic run-down of the steps required in the code:

1. Get the document ID from the value of the user field on the AP screen.
2. Run the stored procedure with the document ID provided to obtain the URL of the source.
3. Open the URL in the default browser.

For step 1 use the GetObjectValue() function in VBA to retrieve the user field value and store it in a variable for use by the stored procedure.

In step 2 we run the stored procedure with the SqlFetch1() function.  This function returns an integer status code we capture that should be zero or else we need to alert the user something went wrong in SQL.  Function parameters for SqlFetch1 include:

• Cursor: An integer variable that is initialized in the Form_Load event code with a "Call SqlCursor" statement.
• SqlStr: The SQL code to execute.  For this instance it is the stored procedure name with the document ID variable concatenated to it with the SParm() function.
• bTable1:  The data structure that receives the results of the stored procedure.
• bTable1Length:  The length of the data structure that receives the results.

The trick of this is to create the data structure use the "Type" construct of VBA to encapsulate the results returned by SQL.  This has to be done in a separate "Module" in the VBA project pane.  I don't know if you can do this directly but I created a .dh file first and did "Import File" on the Module folder (thanks to JanSuan for that little tidbit.)

Also in this Module is the Global declaration of the bTable1 instance of the data structure, as well as the ShellExecute() function required to launch the URL. 

Note that VBA requires that each element of the data structure be explicitly matched to that of the values returned by the stored procedure (same holds true for SQL SELECT from views.)  In our case the URL is of SQL NVARCHAR(MAX) - for which I'm not familiar with a related type in VB.  So my workaround was to CAST the return value down to 200 characters - with the attendant risk that an extra-long URL might get cut off (right now none of the test data URL exceeds 100.)

So if the stored procedure works and we get a good URL back then we launch the URL with the ShellExecute() function.  We use the "open" operation and pass the URL as the "file" parameter in the function.  Up pops the originating document reference in the users browser.

How easy is that?