Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Running parameter query using macro on a form.

Hi, I have to insert record in a Subform by pushing a button on Mainform using following macro. This macro use a query with a parameter ":sid". This parameter value have to be taken from Mainform control "Stud_ID". SUB Insert_Fee DIM oDatabaseFile AS OBJECT DIM oQuery AS OBJECT DIM stQuery AS STRING Dim oForm As Object Dim Result Dim iStudNumber As Integer Dim oStmt As Object oDatabaseFile = ThisComponent.Parent.CurrentController.DataSource oForm = ThisComponent.Drawpage.Forms.getByName("MainForm") iStudNumber = oForm.Columns.getByName("Stud_ID").Value oQuery = oDatabaseFile.getQueryDefinitions() stQuery = oQuery.getByName("Insert_Fee").Command stQuery = Replace(stQuery, ":sid","?") oConnection = Thisdatabasedocument.CurrentController.ActiveConnection oStmt = oConnection.createStatement() oStmt.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_INSENSITIVE oStmt = oConnection.prepareCall(stQuery) oStmt.setInt(1, iStudNumber) oResult = oStmt.execute() oStmt.close() oForm = ThisComponent.Drawpage.Forms.getByName("MainForm") oForm.Filter = "( Stud_ID = " & iStudNumber & ")" oForm.Reload() END SUB The query name is "Insert_Fee" which is following. Subform taken from table "fee_trans". INSERT INTO "fee_trans"("Type","Month","ST_ID","Amount") VALUES ('TF','JUN',(SELECT "ST_ID" FROM "Fee_Define" WHERE "ST_ID" = :sid),(SELECT "Fee" FROM "Fee_Define" WHERE "ST_ID" = :sid)) This macro insert 3 values only but the fourth one remain empty with no error showing. Also form not loading on its previous state. It only shows one record (1 of 1) which was selected at the time of button push. Can you please check the macro code and query and point out where is the problem.

Running parameter query using macro on a form.

Hi, I have to insert record in a Subform by pushing a button on Mainform using following macro. This macro use a query with a parameter ":sid". This parameter value have to be taken from Mainform control "Stud_ID". "Stud_ID".

SUB Insert_Fee
    DIM oDatabaseFile AS OBJECT
    DIM oQuery AS OBJECT
    DIM stQuery AS STRING
    Dim oForm  As Object
    Dim Result
    Dim iStudNumber  As Integer
    Dim oStmt As Object
    oDatabaseFile = ThisComponent.Parent.CurrentController.DataSource
    oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
    iStudNumber = oForm.Columns.getByName("Stud_ID").Value
    oQuery = oDatabaseFile.getQueryDefinitions()
    stQuery = oQuery.getByName("Insert_Fee").Command
    stQuery = Replace(stQuery, ":sid","?")
    oConnection = Thisdatabasedocument.CurrentController.ActiveConnection
    oStmt = oConnection.createStatement()
    oStmt.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_INSENSITIVE
    oStmt = oConnection.prepareCall(stQuery)
    oStmt.setInt(1, iStudNumber)
    oResult = oStmt.execute()
    oStmt.close()
    oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
    oForm.Filter = "( Stud_ID = " & iStudNumber & ")"
    oForm.Reload()
END SUB 
SUB

The query name is "Insert_Fee" which is following. Subform taken from table "fee_trans". "fee_trans".

INSERT INTO "fee_trans"("Type","Month","ST_ID","Amount") VALUES ('TF','JUN',(SELECT "ST_ID" FROM "Fee_Define" WHERE "ST_ID" = :sid),(SELECT "Fee" FROM "Fee_Define" WHERE "ST_ID" = :sid))

This macro insert 3 values only but the fourth one remain empty with no error showing. Also form not loading on its previous state. It only shows one record (1 of 1) which was selected at the time of button push. Can you please check the macro code and query and point out where is the problem.

Running parameter query using macro on a form.

Hi, I have to insert record in a Subform by pushing a button on Mainform using following macro. This macro use a query with a parameter ":sid". This parameter value have to be taken from Mainform control "Stud_ID".

SUB Insert_Fee
    DIM oDatabaseFile AS OBJECT
    DIM oQuery AS OBJECT
    DIM stQuery AS STRING
    Dim oForm  As Object
    Dim Result
    Dim iStudNumber  As Integer
    Dim oStmt As Object
    oDatabaseFile = ThisComponent.Parent.CurrentController.DataSource
    oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
    iStudNumber = oForm.Columns.getByName("Stud_ID").Value
    oQuery = oDatabaseFile.getQueryDefinitions()
    stQuery = oQuery.getByName("Insert_Fee").Command
    stQuery = Replace(stQuery, ":sid","?")
    oConnection = Thisdatabasedocument.CurrentController.ActiveConnection
    oStmt = oConnection.createStatement()
    oStmt.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_INSENSITIVE
    oStmt = oConnection.prepareCall(stQuery)
    oStmt.setInt(1, iStudNumber)
    oResult = oStmt.execute()
    oStmt.close()
    oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
    oForm.Filter = "( Stud_ID = " & iStudNumber & ")"
    oForm.Reload()
END SUB

The query name is "Insert_Fee" which is following. Subform taken from table "fee_trans".

INSERT INTO "fee_trans"("Type","Month","ST_ID","Amount") VALUES ('TF','JUN',(SELECT "ST_ID" FROM "Fee_Define" WHERE "ST_ID" = :sid),(SELECT "Fee" FROM "Fee_Define" WHERE "ST_ID" = :sid))

This macro insert 3 values only but the fourth one remain empty with no error showing. Also form not loading I am using LO version 6.2 on its previous state. It only shows one record (1 of 1) which was selected at the time of button push. Windows 10. Database is local HSQLDB. Can you please check the macro code and query and point out where is the problem.