Hello
I have a master detail table setup
Master Table has the following fields
(id integer not null, <- part of Primary key
nr integer not null, <- part of Primary key
......
Detail Table has
(fk_id integer not null,
fk_nr integer not null,
nr integer not null,
...
The first two fields are used in a foreign key relationship to master table
and the nr in the detail table is to enforce uniqueness within this table.
The form has 2 TMSQuery components linked via the mastersource.
When i insert a new record in the master table and immediately commence inserting records in the detail table, each time i insert a record i run a function from the queries newrecord method which basically executes select max(nr) from detail table where fk_id=?? and fk_nr=?? and then rerturns retrieved value + 1.
The problem is that the function cannot see any records for the detail table at this stage and therefore always returns 1 which causes a violation of the primary key. Is there a setting that i should be setting to resolve this or is there a problem with my approach.
By the way i ensure that any updates to the master table have been applied (using applyupdates) before executing the statements in new record method
Regards
GuyB