DBGrid - Pre-existing and Added records. 1

[HenriM]

This is a little complicated and involves quite a lot of code, so I’ll try to explain in plain language.

On each of my Forms I have a DBGrid

The relevant query selects filtered data from a Table and this is posted to another Table whose Datasource links to the DBGrid.

The ‘filtering’ is done in the usual way in an ADOQuery'Select .... from (tablename) where (select field) = (variable).

Having got that data on the form I now need to Edit the existing records and add new records. Because some of the fields have ‘limited choice’ content, trying to overwrite data in the grid itself I not practicable so to Edit, I make a new selection from the new Table above.

I then create a row of objects, duly aligned under grid, which I have made ‘read only’ so that only the objects can be changed.

Editing these objects works perfectly; as I type into the objects or select from the Comboboxes the changes appear in the right place on the Grid.

There is a Label which I can activate at any time which posts all the records to the main table in a single procedure.

So my User can do whatever work is necessary on this Form and then post the lot to the main database when the work is complete.

Editing existing data, no problem.

The problem comes when we try to add a Record.. I still want to keep the facility whereby data appears in the Grid as it is typed, so I first create a new (‘blank’) record and post this to the grid DataSource. The Record’s ID number is created in code (not Autonumber) and any numeric data is show as zeros.

This record appears in the grid, with the correct RecID and the zeros in the right fields. Likewise, the duplicate objects under the grid, ready to receive data.

BUT

Try to input the data and the following message appears:-

‘Row cannot be located for updated for updating. Some values may have changed since it was last read’ (thinks – Of course they have you cyberdope .. that is what we want to do!!)

I am puzzled as to why is it possible to post edited data to a DataSource for records have been selected from one datasource but not possible for records which have been appended from another?

And before you ask, Yes the Datasource was set to ‘Edit’ after the last ‘Append’ and ‘post..

Likewise, procedures and datasources are defined as ‘Formx.’ Or ‘self,’ wherever they are used.

I am sorry that this has been a rather long explanation but it was necessary to describe exactly what is happening and avoid taking up contributors’ time with points which have already been covered.

Hope someone can help.

 

[Griffyn]

There is an issue with Autonumber fields and Access 97 databases. If the database is Access 97, you'll have to update it to Access 2000 or later, or use some other type of database. The issue results from Autonumbered fields not making their data available upon being added. That is - to post changes to a newly added record containing a Autonumbered field, the autonumber value in Delphi and the database will not match.

 

[HenriM]

Griffyn;
Thanks for your response.
In fact I did mention that I don't use Autonumbers, and increment the record IDs in code.

However,in the meantime I have been able to solve the problem and the solution may help others so I'll explain.

TO RECAP:-
The grid records are selected:-
SelectRecord:= 'Select * from ' + Tablename +'Where [selectField name] = '+inttostr([Selectfield criterion])); **
with ADOQueryx do begin
with SQL do begin
clear;
Add(SelectRecord)
end;
open;
end;
** I find it more convenient to put the criteria to a variable (SelectRecord) and use this in the Add() command than to type the command in full.

These records are then posted to another Table, which we will call TempTablename1.(For reasons of stability, this is not really a temporary table created in code but an empty copy of Tablename, which has been created in my Database and is cleared each time I open the Form.) It now holds only the records selected from TableName.

The grid is tied to this DataSource.

If I now append to this datasource the appended blank record shows, but whereas any records found in the original query can be edited, the appended records cannot. That was my problem.

SOLUTION. Put another ADOQuery on the Form, then:-

RecordSelect :='Select * from '+ TemptableName1'
with ADOQueryz do begin
with SQL do begin
clear;
Add(RecordSelect);
end;
open;
end;

Then ceate the blank record, append and post it to DataSourcez.

then

with ADOQueryx do begin
with SQL do begin
clear;
Add(RecordSelect);
end;
open;
end;

The grid now shows all the records, including the blank which, because it has been selected, not appended can be edited!!!

Hope this helps someone.

HenriM