• Registered: 2020-06-26
  • Posts: 302

Topic: Update a field on an open form

Hi All,
I'm wondering if there is any way to update a calculated field on a form using script?
.
Form A has a calculated field - calcField
Form B performs some data entry that will affect calcField
.
However Form A is OPEN when I open Form B and make the data entry.
So when I close Form B, the calcField has not been updated.
When I close Form A and re-open it the calcField is correct.
.
Is there any way to update calcField on Form A without closing and re-opening it?
.
Thanks
Frank

2 Reply by brian.zaballa (edited by brian.zaballa 2021-08-30 14:01:01)

  • From: Philippines
  • Registered: 2018-10-23
  • Posts: 417

Re: Update a field on an open form

papafrankc wrote:

Hi All,
I'm wondering if there is any way to update a calculated field on a form using script?
.
Form A has a calculated field - calcField
Form B performs some data entry that will affect calcField
.
However Form A is OPEN when I open Form B and make the data entry.
So when I close Form B, the calcField has not been updated.
When I close Form A and re-open it the calcField is correct.
.
Is there any way to update calcField on Form A without closing and re-opening it?
.
Thanks
Frank

Good day Frank,

I can think two way of doing this.

1. You can call the ShowRecord of the Form

FormA.ShowRecord('tblname', [currentid])

If you have a save button on FormA, then you can get its id by using the

FormA.savebtn.dbGeneralTableId

Problem with this approach is if you have a grid on FormA that handles child, then it'll be refreshed and losses its focus. If other problem arises, then use Option 2.
2. Do a sql query that will populate the calculated field(control). You can get your calculated field query from your db manager, then have it in your script. something like

FormA.calculatedControl.Value := SQLExecute('SELECT TOTAL(fld) FROM tbl WHERE id='+IntToStr(FormA.savebtn.dbGeneralTableId));

You can insert your script in the AfterClick event of the Save button of your FormB

brian

3 Reply by papafrankc (edited by papafrankc 2021-08-31 03:32:18)

  • Registered: 2020-06-26
  • Posts: 302

Re: Update a field on an open form

Brian,
Thanks for your suggestions. 
OPTION 1: The ..showrecord doesn't seem to do anything.  Here's what I have so far:
procedure frmService_btnSave_OnClick (Sender: TObject; var Cancel: boolean);
var  TableID :integer;
begin

// FormA.savebtn.dbGeneralTableId  ( GET THE FORM ID )
    TableID := frmEquip.btnSave.dbGeneralTableId;
    showmessage('TableID = '+ intToStr(TableID));

// FormA.ShowRecord('tblname', [currentid])   ( UPDATE THE FORM? )
    frmEquip.ShowRecord('tbl_Equip', TableID);
.
The showmessage shows the ID as 46 which is correct but the calculated Date (NextSvcDate) is not changing.  However when I exit the Equip form and go back in, all is OK.
-------------------------------------------
Option 2 - I can't seem to get the syntax right on this one.  Here's what I've got:
procedure frmService_btnSave_OnAfterClick (Sender: TObject);

var TableID : Integer;
begin
// FormA.savebtn.dbGeneralTableId  ( GET THE FORM ID )
    TableID := frmEquip.btnSave.dbGeneralTableId ;
    showmessage(' AFTERCLICK TableID = '+ intToStr(TableID));

//FormA.calculatedControl.Value := SQLExecute('SELECT TOTAL(fld) FROM tbl WHERE id='+IntToStr(FormA.savebtn.dbGeneralTableId));
   frmEquip.cNextSvcDate.Value := SQLExecute('SELECT TOTAL (calcNextSvcDate) FROM tbl_Equip WHERE id='+ intToStr(TableID));

end;
.
Thanks
Frank

  • From: Philippines
  • Registered: 2018-10-23
  • Posts: 417

Re: Update a field on an open form

Don't copy my sample query, you have to get the query from your calculated field you are trying to get. Can you go to your database and put here the calculated query (calcNextSvcDate) from the table tbl_Equip you are trying to access

brian

5 Reply by papafrankc (edited by papafrankc 2021-08-31 06:32:31)

  • Registered: 2020-06-26
  • Posts: 302

Re: Update a field on an open form

Brian,
A couple of questions: Are you referring to Option 1 or Option 2?
Also do you want me to use the code for the Calculated field as the Query?
And if that's true do you want me to put the complete code into calcNextSvcDate.
.
Sorry, my Pascal skills are still at a pretty basic level.
.
Thanks, Frank

  • From: Philippines
  • Registered: 2018-10-23
  • Posts: 417

Re: Update a field on an open form

papafrankc wrote:

Brian,
A couple of questions: Are you referring to Option 1 or Option 2?
Also do you want me to use the code for the Calculated field as the Query?
And if that's true do you want me to put the complete code into calcNextSvcDate.
.
Sorry, my Pascal skills are still at a pretty basic level.
.
Thanks, Frank

For the Option 2. Yes you need to put the complete code of the calcNextSvcDate, for it to update in the form.

brian
  • From: Philippines
  • Registered: 2018-10-23
  • Posts: 417

Re: Update a field on an open form

For Option 1, try putting your code in the OnAfterClick event of frmService.btnSave

brian
  • Registered: 2020-06-26
  • Posts: 302

Re: Update a field on an open form

Brian,
For Option 1 - putting the code in the OnAfterClick event did the job.  I thought I had tried that before but obviously not. So it's working great now.
.
Thanks, I appreciate your effort and patience
Frank