Example LotusScript: Lotus Notes SQL script

[Return to contents]

Example LotusScript: Lotus Notes SQL script

Last updated: before December, 1998

 'Written by Jerry Sikes, 1997
   '(C) Copyright 1997 by Jerry Sikes
   '
   'Permission is granted to freely copy this script in electronic form,
   'or to print for personal use. It may be use in any Approach database,
   'but may not be distributed for profit either by itself or as part of
   'a collection or database.
   '
   'Disclaimer: This script is provided as is without any express or implied
   'warranties. The author assumes no responsibility for errors or omissions,
   'or for damages resulting from the use of the information contained herein

This script is connected to a button for testing. My production order system
exist in Lotus Notes. This script pull values from Notes into an Approach
invoicing application. The final version of this script will bypass the
inputbox and pull all records assigned to the user as a batch rather than
this one at a time version.

Sub Click(Source As Button, X As Long, Y As Long, Flags As Long)      
 
        Dim CON As New CONNECTION
        Dim QRY As New QUERY
        Dim RS As New RESULTSET
        jobcode = Inputbox$("Please Enter Job Number?","Notes Query",,144,144)
        If jobcode = "" Then Goto Finish
        
        If (CON.connectto("Lotus Notes - Workspace", , ,"Production
Orders.nsf")<>False)Then
‘the actual NSF is prod_env.nsf. Approach recognizes the formal database
title as shown if you opened a Lotus Notes Workspace from Approach. This
syntax forces Approach to ask for a log in, using the current user id.
The session remains active until you quit Approach, even though I’m forcing
a disconnect at the end of this script.
                Set QRY.CONNECTION = CON
                QRY.TABLENAME = "SPECIALS" ‘ SPECIALS is a form name. A view name could have been used
                QRY.SQL = "SELECT *  FROM""Production Orders.nsf\SPECIALS"" SPECIALS WHERE (SPECIALS.job_no = '"+jobcode+"')"
                Set rs.query = qry
                If((rs.execute)<>False)Then
                        currentapplication.applicationwindow.domenucommand (IDM_NEWREC)
                        currentview.body.branchno.text = RS.GetValue("branch")
                        If RS.GetValue("order_suffix") = "" Then
                                currentview.body.PONUMBER.text = RS.GetValue("po_number")
                        Else
                                currentview.body.PONUMBER.text = RS.GetValue("po_number") +"-" + RS.GetValue("order_suffix")
                        End If
                        currentview.body.recieved_from.text = RS.GetValue("salesman")
                        currentview.body.SHIP_VIA11.text = RS.GetValue("ship_route")
                        currentview.body.SHIP_TERMS1.text = RS.GetValue("freight_type")
                        currentview.body.comments1.text = Left(RS.GetValue("cnr_card_type") +" "+ RS.GetValue("branch_specs"),110)
                        currentview.body.comments11.text = jobcode + Chr(10) + RS.GetValue("special_ship")
                        currentview.repeatingpanel.ITEMNO.text = RS.GetValue("prod_no")
                        currentview.repeatingpanel.rp_qty.text = RS.GetValue("quantity")
                        currentview.repeatingpanel.rp_sell.text =  RS.GetValue("branch_cost_per_m")
                        currentview.body.req_date.text = RS.GetValue("req_ship_date")
                        currentview.body.ship_name.text = RS.GetValue("ship_name")
                        currentview.body.sn2.text = RS.GetValue("sn2")
                        currentview.body.sn3.text = RS.GetValue("sn3")
                        currentview.body.ship_addrs.text = RS.GetValue("ship_addrs")
                        currentview.body.ship_addrs2.text = RS.GetValue("ship_addrs2")
                        currentview.body.ship_city.text = RS.GetValue("ship_city")
                        currentview.body.ship_st.text = RS.GetValue("ship_st")
                        currentview.body.ship_zip.text = RS.GetValue("ship_zip")
                        currentview.body.ship_attn.text = RS.GetValue("ship_attn")
                        currentview.body.ord_date11.text = RS.GetValue("ord_date")
                        currentview.body.ord_date11.SetFocus
                        currentapplication.applicationwindow.domenucommand (IDM_NEXT)  ‘commits record
                Else
                        Messagebox "The Item was not found. Please Try Again", 0 + 16,"Notes Query"
                End If
                CON.disconnect          
        End If  
Finish:
End Sub

[Return to contents]