Update a table after delete

I am trying to update a certain table after I delete a record in another table.
Both tables have a field "MED_ID" in common.
So when I delete a selected record in table2 (the table I do the deleting from)
I would like to update Table1 so that a field in this table ,called "Available" changes
its value from No to Yes.
I tried afterdelete of the table2:

Var
CurrentMed_ID:string;
begin
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Text:= 'update Table1 set Available =''Yes'' where Med_ID ='+QuotedStr(CurrentMed_ID);
ADOQuery1.ExecSQL;
end;

database is access 2003 and MED_ID is the text field.
but it dont seem to work.
Any way to do it?
BosanacAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BosanacAuthor Commented:
CurrentMed_ID := wwDBGrid1.Datasource.Dataset.FieldbyName('Med_ID').value;
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Text:= 'update TABLE1 set AVAILABLE =''YES'' where Med_ID ='+QuotedStrCurrentMed_ID);
ADOQuery1.ExecSQL;

dont seem to work either..I get the error 'cant convert variant of type null into string'
but both fields are text !???
0
ee_rleeCommented:
what is the value of QuotedStrCurrentMed_ID?
0
BosanacAuthor Commented:
QuotedStrCurrentMed_ID ...text field...containing numbers and letters...
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

diniludCommented:
>>can't convert variant of type null into string

  then wwDBGrid1.Datasource.Dataset.FieldbyName('Med_ID').value is null.

  -----------------------------------------

CurrentMed_ID := wwDBGrid1.Datasource.Dataset.FieldbyName('Med_ID').asstring;
showmessage(CurrentMed_ID);
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Text:= 'update TABLE1 set AVAILABLE =''YES'' where Med_ID ='+QuotedStrCurrentMed_ID);
ADOQuery1.ExecSQL;


0
ee_rleeCommented:
try this
ADOQuery1.SQL.Text:= 'update Table1 set Available = ' + QuotedStr('Yes') + ' where Med_ID ='+QuotedStr(CurrentMed_ID);

Open in new window

0
BosanacAuthor Commented:
do not work either...
0
ee_rleeCommented:
can you elaborate on 'do not work'? does it produce an error?
0
diniludCommented:
What was your CurrentMed_ID value?
0
bokistCommented:
try with parameters

ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Text:= 'update TABLE1 set AVAILABLE = :p1 where Med_ID = :p2');
ADOQuery1.Parameters.Parambyname('p1').value := 'Yes';
ADOQuery1.Parameters.Parambyname('p2').value := CurrentMed_ID;
ADOQuery1.ExecSQL;

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ziolkoCommented:
>>QuotedStrCurrentMed_ID ...text field...containing numbers and letters...

what type is >>Med_ID<< field in your database?

if Med_ID is string type then try this:

var currentmedid: string;

currentmedid := ... assign your value here


if Length(currentmedid) = 0 then
  ADOQuery1.SQL.Text := Format('update TABLE1 set AVAILABLE = %s where Med_ID is null', [QuotedStr('YES')])
else
  ADOQuery1.SQL.Text := Format('update TABLE1 set AVAILABLE = %s where Med_ID = %s', [QuotedStr('YES'), QuotedStr(currentmedid)]);

ziolko.
0
ziolkoCommented:
also you can try this

uses Clipbrd;

and afetr you set SQL.Text
use:

Clipboard.AsText := ADOQuery1.SQL.Text;

after execution simply Ctrl+V to your db editor and run it from there to get access generic error.

but by the name >>Med_ID<< i would guess that's numeric field not string, in this case you need StrToInt() instead of QuotedStr()

ziolko.
0
BosanacAuthor Commented:
Why this does not work I do not know:

procedure TForm3.ADOTable3AfterDelete(DataSet: TDataSet);
var currentmed_id: string;
begin
CurrentMed_ID := wwDBGrid1.Datasource.Dataset.FieldbyName('Med_ID').asstring;
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Text:= 'update TABLE1 set AVAILABLE= :p1 where Med_ID = :p2';
ADOQuery1.Parameters.Parambyname('p1').value := 'YES';
ADOQuery1.Parameters.Parambyname('p2').value := CurrentMed_ID;
ADOQuery1.ExecSQL;
 end;
any ideas?

Med_ID is text field in the DB (in both tables)
0
bokistCommented:
check length and value of the fields.
also you can check sql command :
CurrentMed_ID := wwDBGrid1.Datasource.Dataset.FieldbyName('Med_ID').asstring;
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Text:= 'update TABLE1 set AVAILABLE= :p1 where Med_ID = :p2';
ADOQuery1.Parameters.Parambyname('p1').value := 'YES';
ADOQuery1.Parameters.Parambyname('p2').value := CurrentMed_ID;
          showmessage(ADOQuery1.SQL.Text);
ADOQuery1.ExecSQL;

0
bokistCommented:
Another idea: does table contains records meet your criteria??
CurrentMed_ID := wwDBGrid1.Datasource.Dataset.FieldbyName('Med_ID').asstring;
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Text:= 'select * from TABLE1 where Med_ID = :p2';
ADOQuery1.Parameters.Parambyname('p2').value := CurrentMed_ID;
ADOQuery1.SQL.Open;
if ADOQuery1.EOF   --> no records
0
ziolkoCommented:
copy you SQL statement to clipboard and try to run it from access, if you have errors post here error messages

ziolko.
0
BosanacAuthor Commented:
MED_ID contains numbers and letters (example: A101)
They are both text fields in their respective tables with same lenght.

I do a perfect update with (after insert) :
ADOQuery1.Close;
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Text:=' update Table1 set Available =''No'' where MED_ID = :d1 ';
  ADOQuery1.Parameters.ParamByName('d1').Value :=cxLookUpComboBox1.Text;
  ADOQuery1.ExecSQL;
but when I select a record in the grid (in whisch the insert is visible) and delete it the update like I mentioned does not work.
Any ideas?
0
bokistCommented:
perhaps save   CurrentMed_ID   before deleting record
CurrentMed_ID := wwDBGrid1.Datasource.Dataset.FieldbyName('Med_ID').asstring

0
ziolkoCommented:
bokist has a point, if you delete record and try to read value from dbaware component you'll get null instead of expected value

ziolko.
0
BosanacAuthor Commented:
you are perfectly right zoiko ...stupid me...
I was putting the code on afterdelete event while it should have gone  on beforedelete..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.