Search for field name in access table then update relevant fields names with values delphi 7

Question

I have an Access database table named ReceiptTable with the following field names: item name, buying price, selling price, goods total, cash, change. I am using an Adoquery and datasource to connect to the access database. When I want to update records to receiptTable, I use the following code to locate an item name from the database then update all the records with similar item name in the database with the values from edit box field values:

procedure TReceiptForm.BitBtn1Click(Sender: TObject);
begin
with ADOQuery1 do
ADOQuery1.Open;
ADOQuery1.Locate('item name',Edit1.Text,[]) ;
ADOQuery1.edit;
ADOQuery1.FieldValues['goods total']:=edit3.Text;
ADOQuery1.FieldValues['cash']:=edit4.Text;
ADOQuery1.FieldValues['change']:=edit5.Text;
ADOQuery1.Post;
end;

The problem I have is that only one row with the item name is updated but the other rows with similar item name are not updated. What code should I add above so that all the rows which have similar item names are updated with values from edit boxes?

Answer 1

This simple code answers your question:

procedure TReceiptForm.BitBtn1Click(Sender: TObject);
var
    itemname, goodstotal, cash, change: string;
begin
    // Execute query
    try
        ADOQuery1.Open;
    except
        on E: Exception do begin
            ShowMessage(E.Message);
            Exit;
        end{on};
    end{try};
    // Values
    itemname   := Edit1.Text;
    goodstotal := Edit3.Text;
    cash       := Edit4.Text;
    change     := Edit5.Text;
    // Find first matching record, then go to the end of resultset.
    try
        ADOQuery1.DisableControls;
        if ADOQuery1.Locate('item name', itemname, []) then begin
            while not ADOQuery1.Eof do begin
                if ADOQuery1.FieldByName('item name').AsString = itemname then begin
                    ADOQuery1.Edit;
                    ADOQuery1.FieldValues['goods total'] := goodstotal;
                    ADOQuery1.FieldValues['cash']        := cash;
                    ADOQuery1.FieldValues['change']      := change;
                    ADOQuery1.Post;
                end{if};    
                ADOQuery1.Next;
            end{while};
        end{if};
    finally
        ADOQuery1.EnableControls;
    end{try};
end;

This will work, but you can consider using one SQL statement for updating the table, or if it is possible order your query by 'item name' and use this:

...
// Find first matching record, then update while next record matches too.
if ADOQuery1.Locate('item name', itemname, []) then begin
    while (not ADOQuery1.Eof) and 
          (ADOQuery1.FieldByName('item name').AsString = itemname) do begin
        ADOQuery1.Edit;
        ADOQuery1.FieldValues['goods total'] := goodstotal;
        ADOQuery1.FieldValues['cash']        := cash;
        ADOQuery1.FieldValues['change']      := change;
        ADOQuery1.Post;
        ADOQuery1.Next;
    end{while};
end{if};
...