Solved

Update second table based on values being saved in first

Posted on 2000-04-04
12
164 Views
Last Modified: 2011-09-20
Hey all,

Maybe the title is kinda cryptic ....

But, what I want to do is this:

I am saving a record into table1. Based on this, I will need to save some values into table2. On inserting another record within table1, I want to update and edit (actually modify) the mentioned record in table2.

How do I do this. I am confusing myself into a corner here. Please help?

fats
0
Comment
Question by:ffelaar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +5
12 Comments
 

Expert Comment

by:ShadowFax
ID: 2683425
What DB are you using?
0
 
LVL 10

Expert Comment

by:Lischke
ID: 2683430
Hi Fats,

actually, this should not be a problem, although it depends obviously on the way you want to react. TTable has got an OnAfterInsert event. You could write an event handler for this event and modify your second table there. If you want to make it entirly database driven then you need a trigger like:

create trigger test for YourTable active after insert position 0

as
begin
  insert into Table2 set ID = New.ID
end

This trigger takes the newly inserted ID (suppose there would be one) and inserts a record with that ID into the second table (also provided there is an ID column).

Ciao, Mike
0
 

Author Comment

by:ffelaar
ID: 2683488
Hey everyone,

Thanx for the quick response. I am using an Access 97 database.

Did you notice 200 points ;-)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:kretzschmar
ID: 2683717
hi ffelaar,

use the beforepost-event from table1 for the update on table2, you can use for this a second table or a query.

explain a bit more

meikl
0
 
LVL 13

Expert Comment

by:Epsylon
ID: 2683735
You can use TdataSet.Locate('someField', somevalue, []) to locate the record in Table2 and then edit the record in the AfterInsert event:

procedure Table1AfterInsert(...);
begin
with Table2 do
begin
  if Locate('someindexField', somevalue, []) then
    Edit
  else
    Insert;
  FieldByName('fieldtomodify').AsInteger := somevalue;
  Post;
end;
end;


Regards,

Epsylon.

0
 
LVL 2

Expert Comment

by:mullet_attack
ID: 2686218
Hi Fats,

One way to do it...

1. add the record to the first table
2. add record to second table - make sure this table has AutoIncrement Primary key, and no other indices or filters
3. add another record to first table
4. 'last' on second table will return you the record added in step 2. You can now edit it.

This assumes you do this in a proc of some sort. If you want atomatic actions, you can use AfterPost event of table1 to check for the record in table2, add it if not there, or else edit it.

Mark
0
 

Author Comment

by:ffelaar
ID: 2686507
Not quite guys ....

Okay, I have two tables. Table1 and Table2 say.

On showing a particular form, I position myself onto the last record of table1.
I copy it and append it as a new record - ready for editing.

On saving of this record ... there will (trust me) be a record in Table2 to
be updated with some calculated values from table1.

If I use your advice of using AfterInsert on Table1 ... then I will run into a
problem ... because on making a duplicate of the last record as stated above ... it
will get called before I really have something to update it with. The record must
still be edited by the user ....

Just thought I'd make the issue clearer ....

Thanx so far
Fats
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 2686537
hi ffelaar,

as i said, use the beforepost-event
and check if your table1 is in insert mode like

if table1.state = dsinsert then
//do your update stuff

meikl
0
 
LVL 2

Expert Comment

by:Felixin
ID: 2689945
Here goes an example on an AFTERPOST event to update a second table when one is modified:

procedure TDM.AfterPost(DataSet: TDataSet);

  function Existe (Tabla : TTable) : Boolean;
  begin
    with QSelect do
    try
      Close;
      SQL.Clear;
      SQL.Add ('SELECT Count (*) AS Cuenta FROM Actualizaciones');
      SQL.Add ('WHERE UPPER (Tabla)=' + QuotedStr (UpperCase (Tabla.TableName)));
      Open;
      Result := (FieldValues ['Cuenta'] >= 1);
      Close;
    except
      raise;
    end;
  end;

  procedure Actualiza (Tabla : TTable);
  begin
    with QUpdate do
    begin
      try
        Close;
        SQL.Clear;
        SQL.Add ('UPDATE Actualizaciones');
        SQL.Add ('SET Tabla=' + QuotedStr (Tabla.TableName) + ',');
        SQL.Add ('    Hora='  + QuotedStr (FormatDateTime('mm/dd/yyyy h:nn:ss', Now)));
        SQL.Add ('WHERE UPPER (Tabla)=' + QuotedStr (UpperCase (Tabla.TableName)));
        ExecSql;
      except
        raise;
      end;
    end;
  end;

  procedure Inserta (Tabla : TTable);
  begin
    with QInsert do
    begin
      try
        Close;
        SQL.Clear;
        SQL.Add ('INSERT Actualizaciones (Tabla, Hora) VALUES');
        SQL.Add ('  (' + QuotedStr (Tabla.TableName) + ',');
        SQL.Add ('   ' + QuotedStr (FormatDateTime('mm/dd/yyyy h:nn:ss', Now)) + ')');
        ExecSql;
      except
        raise;
      end;
    end;
  end;

begin
  try
    DBAux.StartTransaction;
    if Existe (TTable (DataSet)) then // FIRST, I look for one record to be updated on the second table with the corresponding key
      Actualiza (TTable (DataSet)) // Record already exists then UPDATE

    else
      Inserta (TTable (DataSet)); // Record does not exist then INSERT
    DBAux.Commit;
  except
    DBAux.Rollback;
    raise;
  end;
end;
0
 

Accepted Solution

by:
ffelaar earned 200 total points
ID: 2696902
The solution I went with is as follows:

___________________________________

with qryDayAverages do
begin
 Close;
 Params.ParamByName('company').AsInteger := gCompanyID;
:
:
:
  Open;
end;

effortHours := VAL_Duration.AsInteger div 60;
effortMinutes := VAL_Duration.AsInteger mod 60;

with tblDay do
begin
  Open;
   Locate('TripCoIDFKey; TripVesselIDFKey; TripStartDate; DayDate',
            VarArrayOf([gCompanyID, gVesselID, gTripStartDate, gDragDate]),
            [loPartialKey]);
    Edit;
    if VAL_DragCount.AsInteger <= 1 then
    begin
      FieldByName('DayEffortHours').AsInteger := effortHours ;
      FieldByName('DayEffortMinutes').AsInteger := effortMinutes;
:
:
:
    tblDay.Post;
  end
  else
  begin
    FieldByName('DayAverageSpeed').AsFloat := VAL_AvgSpeed.Value;
:
:
:
      tblDay.Post;
    end;
  end;
0
 
LVL 13

Expert Comment

by:Epsylon
ID: 2696913
This goes wrong when Locate can't locate the right record. So I suggest to add an if statement:

If Locate(....) then
begin
  <your stuff>
end;
0
 
LVL 3

Expert Comment

by:darinw
ID: 2697118
Hello all,

ffelaar has requested this question be deleted. Rather than lose the work done, I am saving the question to the PAQ.

darinw
Customer Service
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question