Solved

Update second table based on values being saved in first

Posted on 2000-04-04
12
162 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
  • 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

911 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now