 |
 |
 | |
 | |
 | |
 |
|
|
| |
| |
| |
|
Last updated: before December, 1998
I wasn't aware of the
%INCLUDE "C:\LOTUS\APPROACH\APRLSERR.LSS"
... detailed by Jerry Sikes (see article 'Example LotusScript: Handling Approach Object Errors') but the following script will trap the same error whilst also containing examples of SQL commands and references to databases that are both on and off the current view
Sub Click(Source As Button, X As Long, Y As Long, Flags As Long)
Dim TRANID As String, NOTES As String
Dim DDATE As Variant, CDATE As Variant, RDATE As Variant
Dim AREF As Integer, ANUMBER As Integer, STDREF As Integer, NUMCURR As Integer
If source.transacspanel.Action~ Reference.text="" Then 'ie there is no CURRENT record
Messagebox "To enter a new action, just choose a STD Action Reference",65,"CCS Message"
End
Else 'this section sets all values that will be required throughout the script
Currentwindow.redraw=False 'prevents the screen from re-drawing
TRANID=source.transacspanel.Transaction~ Reference.text
DDATE=source.transacspanel.Diary~ Date.text
CDATE=Today()
RDATE=source.transacspanel.Raised~ Date.text
AREF=source.transacspanel.Action~ Reference.text
ANUMBER=currentview.body.Account~ Number.text
STDREF=source.transacspanel.STDAction.text
' This section is purely used to work out how many transactions (records in CURRENT.DBF) have the same action reference
' therefore whether to remove the note or not
Dim con As New Connection
Dim qry As New Query
Dim rs As New ResultSet
If (con.connectto ("dbase IV")<>False) Then
Set qry.connection = con
qry.tablename = "m:\group\ba\ccs\current.dbf"
Qry.SQL="SELECT * FROM"""& qry.tablename &"""Current WHERE (Current.""Action Reference"" = "& AREF & ")"
Set rs.query = qry
If ((rs.execute)<>False) Then
NUMCURR = (rs.numrows) 'number of records in current with the same action reference
End If
End If
con.disconnect
'End of section for counting transactions
'This section will delete the record from current
If (con.connectto ("dbase IV")<>False) Then
Set qry.connection = con
qry.tablename = "m:\group\ba\ccs\current.dbf"
Qry.SQL="SELECT * FROM"""& qry.tablename &"""Current WHERE (Current.""Unique Transaction Reference"" = '" & TRANID & "')" 'Note the syntax for TE
Set rs.query = qry
If ((rs.execute)<>False) Then
rs.deleterow
rs.updaterow
End If
con.disconnect
Dim newdata(1 To 7) As Variant
newdata(1)=AREF
newdata(2)=ANUMBER
newdata(3)=TRANID
newdata(4)=DDATE
newdata(5)=RDATE
newdata(6)=CDATE
newdata(7)=STDREF
'And now to add a new record in actions
If (con.connectto ("dbase IV")<>False) Then
Set qry.connection = con
qry.tablename="m:\group\ba\ccs\actions.dbf"
Set rs.query = qry
If ((rs.execute)<>False) Then
rs.addrow
For i=1 To Ubound(newdata) 'Used to enter value in each
FIELD NewVAlue=rs.setvalue(i,newdata(i))
Next
rs.updaterow
End If
End If
con.disconnect
End If
'This section creates a new record in the NOTES database and removes
from currnote if required
If (con.connectto ("dbase IV")<>False) Then
Set qry.connection = con
qry.tablename = "m:\group\ba\ccs\currnote.dbf"
Qry.SQL="SELECT * FROM"""& qry.tablename &"""currnote WHERE (currnote.""Action Reference"" =" & AREF &")"
Set rs.query = qry
On Error Goto Skip 'An error will occur when there are no notes against a transaction
If ((rs.execute)<>False) Then
NOTES=rs.getvalue(rs.fieldid("Notes"))
RDATE=rs.getvalue(rs.fieldid("Date")) ' DDATE is Date of the NOTE
If NUMCURR = 1Then
rs.deleterow
rs.updaterow
Else
End If
End If
con.disconnect
Dim newdata2(1 To 5) As Variant
newdata2(1)=ANUMBER
newdata2(2)=TRANID
newdata2(3)=RDATE
newdata2(4)=NOTES
newdata2(5)=AREF
If (con.connectto ("dbase IV")<>False) Then
Set qry.connection = con
qry.tablename="m:\group\ba\ccs\notes.dbf"
Set rs.query = qry
If ((rs.execute)<>False) Then
rs.addrow
For i=1 To Ubound(newdata2)
NewVAlue=rs.setvalue(i,newdata2(i))
Next
rs.updaterow
End If
End If
con.disconnect
End If
End If
Skip : 'If there are no notes the script will continue from here
'This little section is purely to make the screen update and NOT
SHOW deleted records !
currentwindow.redraw=True
CurrentApplication.ApplicationWindow.DoMenuCommand(IDM_REFRESH)
End 'I needed this to prevent a "No Resume" error when there were no
NOTES
End Sub
When a new action is required, the current action is transfered to
ACTNSOLD alongwith any CURRNOTES being transferred into NOTES so that
a history is maintained.
---------------------------------------------------------------------
(c) 1997 Ian Curry - Jist, All rights reserved, permission is granted
for this document to be distributed in electronic form (such as
email, newsgroups, and the WWW) as long as it is posted in its
entirety including this copyright statement, and is not distributed
for financial gain.