I want to assign following macro to a batten in a form
REM ***** BASIC *****
Sub main
End Sub
Sub renumber()
DIM oConn AS OBJECT
DIM oQueryResultSet AS OBJECT
DIM oQueryUpdate AS OBJECT
DIM qStr AS String
DIM i AS INTEGER
DIM rowNum AS INTEGER
DIM priKeyNo AS INTEGER
oConn = ThisDatabaseDocument.DataSource.getConnection("","")
oQueryResultSet = oConn.CreateStatement()
oQueryUpdate = oConn.CreateStatement()
qStr = "SELECT ""ID"", ""Today_Order"" FROM ""T_Task"" WHERE ""Today_Order"" <= '100.00' ORDER BY ""Today_Order"" ASC"
resultSet = oQueryResultSet.executeQuery(qStr)
i= 0
Do While resultSet.isLast() = False
resultSet.next
rowNum = resultSet.getRow
priKeyNo = resultSet.getString(1)
REM qStr = "UPDATE ""Table1a"" SET ""item_no"" = " & rowNum & " WHERE ""priKey"" = " & priKeyNo & ";"
REM qStr = "UPDATE ""T_Task"" SET ""Today_Order"" = " & i & "WHERE ""Today_Order"" <= '100.00' ;"
qStr = "UPDATE ""T_Task"" SET ""Today_Order"" = " & rowNum & " WHERE ""ID"" = " & priKeyNo & ";"
oQueryUpdate.executeUpdate(qStr)
i = i + 1
Loop
REM qStr = "DROP SEQUENCE ""PYS"" IF EXISTS "
REM qStr = qStr & "CREATE SEQUENCE ""PYS"" AS INTEGER START WITH 1 "
REM qStr = qStr & "UPDATE ""T_Task"" SET ""Today_Order"" = NEXT VALUE FOR ""PYS"" WHERE ""Today_Order"" <= '100.00' "
REM oQueryUpdate.executeUpdate(qStr)
End Sub
I create a push batten in a form and right click on it, then I choose Control then on the Events Tab I choose this macro for the “Key pressed” event and the following text appears in the window in front of “Key pressed” Standard.Test1.renumber (document, Basic)
However, when I press the key nothing happens.
I can run this macro using Tools->Macros → Run macro… → Test Data Base.odb->Standard->Test1->Renumber
Without any problem.
Any suggestions?