syncing different queries between forms
lazarus / 16-Databases

----------------------------------------------------------------------
codifies · 2012-09-13 16:46

The solution I have works, but I'm just not so happy with it, it
doesn't seem particularly elegant for a start!

For a number of reasons I've decided to have a dbgrid on the main form
be a read only table of an sql which contains a join (any where or
multiple tables in a sql statement seems to make it read only in any
case?)

When the user either moves up or down with the cursor key or clicks on
a row a little form pops up with the fields of that record this uses a
different query that is read / write, supported by an additional query
to feed a dblookup (hence the join on the main grid)

However as I mentioned the implementation seem to leave a little to be
desired.
I'll detail it here and if anyone can make suggestions I would really
appreciate feedback

If I can make it both elegant and robust, I'll make a mini tutorial of
it (on the wiki? )

anyhow this is what I'm doing

DBGrid1CellClick calls PeopleQueryAfterScroll
which checks a boolean that is set after form2 has been created
assuming form2 is created it does
Code: [Select]     
form2.f2peopleQuery.Locate('id',DataSet.FieldByName('id').AsString,o);
      form2.Show;   

when form 2 is created I have to set the cursor or the dropdown isn't
showing anything if the first record is selected
Code: [Select]  form2.f2peopleQuery.Locate('id',form1.PeopleQuery.Fiel
dByName('id').AsString,o);
  form1.form2created:=True; {yuck}
  form2.hide;  

there is a button to commit any edits done on the second form
this has to refresh the main forms query and then after the refresh
restory its position
Code: [Select]  f2peopleQuery.ApplyUpdates;
  form1.PeopleQuery.Refresh;
  form1.peoplequery.Locate('id',f2peopleQuery.FieldByName('id').AsStri
ng,o);
  form2.hide;  

it all seems like it could be done better, but how?!

----------------------------------------------------------------------
codifies · 2012-09-13 19:38

Code: [Select]  if (form2<>nil) then
    if (form2.f2peopleQuery<>nil) then 
removes the need for the horrid global boolean!!

and refreshing the lookup replaces the initial sync of id's

its getting better!

----------------------------------------------------------------------
teos · 2012-09-13 19:55

might be a very stupid solution but I would have created a datamodule,
put one TDataset component for every table needed on the form, add
Datasources and set them up in master-detail (mastersource and
masterfield properties).

if you do that right, you have read-write datasets. If you put
datasources on form1 and form2, pointing to the datasets you want to
edit and use data aware components on form2, all you need is to show
form 2 to have data ready for editing. In the formclose of form2 or
under the save button of form2 you can post the data, under the cancel
button cancel edits.

this approach takes the trouble out of the readonly datasets and gives
a datamodule with datasets which are widely useable.

is this something useable?

----------------------------------------------------------------------
codifies · 2012-09-13 21:42

yeah data module may well be the way to go, I'll check it out later
once I get chance! thx

----------------------------------------------------------------------
codifies · 2012-09-14 02:58

turns out a data module was just the trick! great suggestion thanks

I wonder would something like this be worth turning into a mini
tutorial for the wiki ?