Why does contents of a Grid disappear? 2

[terrytype]

I rely on W-XP-Prof OS, Delphi 6 Enterprize and Paradox Tables.

My application has a GUI ["frmMyForm"] relying upon a grid ["grdMyGrid"] and a DBNavigator ["MyNavigator"] both having
DataSource "qryMyItems".

The grid is initally successfully in showing all the records in qryMyItems arising from the following

frmMyForm := TFrmMyForm.Create(nil);
frmMyForm.ShowModal;

But, although the record lines remain, the CONTENTS of the RECORDS "grdMyGrid" disappear AND "MyNavigator" no longer shows the "Visible Buttons" Options when I enter a new record followed by

dmMyDataModule.qryMyItems.Post;
dmMyUpdates.UpdateMyItems;

I might mention that BLANK lines remain for exactly the number of remaining records arising from "qryMyItems".

Seeming to indicate that "grdMyGrid" is still supported by "DataSource:= qryMyItems" but "MyNavigator" IS NOT in that it no longer shows the "Visible Buttons" Options.

In addition examination of the table [using DelphiDesktop] shows that the most recent posting was successfull.

Thanks in advance for suggestions from anyone.

Old Man Delphi

 

[terrytype]

Problem partially solved.

I should have mentioned that UponAmountChange the balance column requires to be updated from top-to-bottom of the table.
Any procedure intended to achieve that would be triggered by UponAmountChange. But this cannot succeed as it would involve iterating on Amount.

To overcome this I developed a seperate .exe application to work in adjunct with the main application which does the job perfectly being fired with the dollowing

WinExec('c:\Program Files\StdAccounting\AccountUpdate.exe', SW_SHOW)

THAT returns the desired results perfectly BUT it is when main application is returned to that my problem arises.
From which it is evident that the above line causes my problem.

The following questions still remaining.
1. WHY that should it have that effect?
2. HOW do I overcome it?

Any ideas anyone?

Old Man Delphi

 

[DjangMan]

You've created a new TFrmMyForm but you don't show the code where you're connecting the datasource controls on the TFrmMyForm to a dataset component.

 

[terrytype]

Thanks for your response Djangman.

I haven't used any special code. The datasource controls [both the grid and the navigator] are connected via the ObjectInspector at DataSource.

I sense my problem arises from triggering another .exe whilst having "MyForm.ShowModal" in the FIRST exe.

Old Man Delphi

 

[DjangMan]

Just to clarify - your grid shows records and your navigator is enabled when the form appears on the screen for the first time?

 

[majlumbo]

Not sure why you start another application just to update the balance column. When the event happens that needs to update the column, why don't you run an UPDATE query to update all the balance amounts, and then refresh the query the DBGrid is dependent on? No need to iterate each record, when you can run SQL on a set.

 

[terrytype]

Thanks DjangMan. Answer is "YES".

Thanks for your interest majlumbo. Whilst an UPDATE query will provide an updated TOTAL of the balance amounts it won't provide the balance at each individual record in the grid.

I have tried refreshing the query the Grid is dependent by repeating the frmMyForm.ShowModal - but Delphi won't permit this. I tried substituting frmMyForm.ShowModal with MyForm.Show which also has no effect.

I still suspect that Delphi does not like WinExec('c:\Program Files\StdAccounting\AccountUpdate.exe', SW_SHOW)in the midst of a frmMyForm.ShowModal.

I like your reference to "refresh". I'll explore the implications and possibilities arising from somehow "closing" [whatever] the effect of ShowModal followed by using a "refresh" command ..... will get back.

Old Man Delphi

 

[majlumbo]

Whilst an UPDATE query will provide an updated TOTAL of the balance amounts it won't provide the balance at each individual record in the grid

What is the formula you use to update the balance amount, and what is the trigger that causes the update to happen?

The beauty of SQL is that it works on "sets." So it is possible to update all the records displayed in the grid in one statement even if the variables in the formula changes depending on the row (as long as the rules are defined i.e. New Balance = (columnAValue * columnBValue) / columnCValue - The values of ColumnA/B and C are for each individual record so each row will get a different amount, but the rule stays the same).

If you are using SHOWMODAL to display the form, then the form itself will need to refresh the contents of the grid, since the main application is blocked until the form is closed.

 

[terrytype]

Thanks for putting me on the right [VERY USEFUL] track maljumbo.

However ...your advice leaves me somwhat confused as it seems you are suggesting the [RUNTIME] code should read as follows

"qryname2 := ('columnBalance = columnAmount * columnBalance)/ columnC');"

As I am only dealing with columnAmount and columnBalance your suggesed "column C" escapes me.

In any event I am hoping to achieve "columnBalance = columnBalance [previous record] + columnAmount [this record]". Leaving your use of "*" and "/" to escape me

Please elaborate.

Thanks in advance.

Old Man Delphi

 

[terrytype]

For what its worth I might add that the following code leaves the Navigator bar "dead" AND the grid blank.

qryname2 := ('Balance = Balance + Amount');

Old Man Delphi

 

[majlumbo]

You may want to take a look at an SQL reference.
I have code here that fills in the sql for the query component, but you can drop a query component on your form/datamodule and set its sql property at design time. Then you would just need to Execute (EXECSQL) the query.

* Assuming your Table Name is Order
* Assuming your Balance Column name is Balance
* Assuming your Amount Column name is Amount
then

qryname2.SQL.Text := 'update Order set Balance = Balance + Amount';
//this will update every record in the Order table.  Not just the ones reflected in the DBGrid. 
 
//If you want to do only a subset, then include the "where" clause 
//look at the reference I linked to, to see how.
//qryname2.SQL.Text := 'update Order set Balance = Balance + Amount where columnname = somevalue';

//or if you need to pass a parameter to the query (such as the "somevalue" value in the where clause above then ..
//qryname2.SQL.Text := 'update Order set Balance = Balance + Amount where columnname = :somevalue';//notice ":" before somevalue 
//then follow up with setting parameter
//qryname2.parameterbyname('somevalue').AsInteger or AsString := <The value you want>

qryname2.ExecSQL;//Execute the query --- DON'T OPEN an update query since it does not return back a result set.
grdMyGrid.DataSource.DataSet.Refresh;

* Change the names to the actual values in your database.

Just ask again if you have any questions...

 

[terrytype]

In addition to well-deserved Star Thanks very much. You have been most helpful.

Old Man Delphi

 

[majlumbo]

Actually, I need to update my answer, I didn't read your response as closely as I should have, you say you want to update the current record with the value from the previous record. The question I have is what do you do for the first record since there is no "previous" record?
Anyway, this would make the update query a bit more complicated and may need to be different depending on the database you are using (since I no longer have Paradox I can't test any possible solution.) I did find a few examples on how to use the previous record to update the current record, so you may want to study them to see if you can adapt it to your situation - Anyway, it is possible. Here are a few:

http://dba.stackexchange.com/a/30507

and

http://stackoverflow.com/a/15173126/582118

but barring that, it may just be best to iterate through each record (yes ... I know I steered you away from this!!!) and update them individually...

procedure TFrmMyForm.UpdateBalance;
var
   HoldBalance: Double;
   bk: TBookMark;
begin
   bk := grdMyGrid.DataSource.DataSet.GetBookMark;//save your current location;
   try
      grdMyGrid.DataSource.DataSet.DisableControls;
      try
         grdMyGrid.DataSource.DataSet.First;
         HoldBalance := grdMyGrid.DataSource.DataSet.FieldByName(Balance).AsFloat;
         grdMyGrid.DataSource.DataSet.Next;  //start at 2nd record??  Don't know what to do for first record;
         while Not grdMyGrid.DataSource.DataSet.Eof do
         begin
            grdMyGrid.DataSource.DataSet.Edit;
            try
               grdMyGrid.DataSource.DataSet.FieldByName('Balance').AsFloat := 
                    HoldBalance + 
                    grdMyGrid.DataSource.DataSet.FieldByName('Amount').AsFloat;
                    // (or AsInteger if an integer amount)
            finally
               grdMyGrid.DataSource.DataSet.Post;
            end;
            HoldBalance := grdMyGrid.DataSource.DataSet.FieldByName('Balance').AsFloat;
            grdMyGrid.DataSource.DataSet.Next;
         end;
         grdMyGrid.DataSource.DataSet.GotoBookMark(bk);//go back to that location
      finally
         grdMyGrid.DataSource.DataSet.EnableControls;
      end;
   finally
      grdMyGrid.DataSource.DataSet.FreeBookMark(bk);
   end;
end;

 

[terrytype]

Thanks for your continued interest majlumbo

As it happens this recent suggestion is EXACTLY what I was doing.
HOWEVER the procedure is triggered UponAmountChange.
As a consequence the procedure will attempt to iterate on UponAmountChange ... causing it to hang.
THAT is why I created an adjunct .exe in which UponAmountChange does not arise and THAT .exe works perfectly having triggered it as referred to on 2nd May in this thread.

BUT leaving me with my problem as described there.
I suspect I need to "refresh" [something?] the "ShowModal".

Any ideas on that?

Thanks again for your greatly appreciated interest.


Old Man Delphi

 

[DjangMan]

If you're ending up in a recursive loop you can disable the event handler while you do your processing. Something along the lines of:

procedure TfrmMain.SomeEventHandler(Sender: TObject);
begin
    RunThisProcedureToUpdateTable;
end;

procedure TfrmMain.RunThisProcedureToUpdateTable;
begin
    //First step - disable the event handler while we're updating the table
    MyField.OnChange := nil;

    //Now I can do a bunch of things without getting recursive calls

    //Re-enable the event handler
    MyField.OnChange := SomeEventHandler;
end;