For a serie of some limitations among some fields in records (=samples) in a table called TbSamples, I need to insert the samples inside in groups of five, not one at each time, but all the empty samples inside that group. I can select the empty samples in TbSamples in a group of five with some conditions: IDNumber = X and then I use any of the mandatory empty fields: OtherField Is Not Null. I can make a query with these samples and use it as source of a form, then click an "Add" button which launches all the conditions and if everything fit, insert that samples in the table. There is a "Cancel" button that cancel all the samples, even if some of them are already inserted in that form.
The problem comes, as some of you may have guessed, when the user is filling the forms and changes focus to the next record. In that moment, the already filled record was updated in the table, and then conditions are bpassed, and the field OtherField is not Null anymore, so that record is not part of the query anymore, and I cannot cancel the addition of samples based on the query.
What I need is to put those selected registers on hold, that they do not go to the table until they are validated or cancel if the user decides so.
I thought to use a temporary table instead of query, and then update the TbSamples with that TbTmpSamples but I do not how to filled it.
Short sumary: a group of 5 samples is empty. A user opens the form with the five empty samples and fill samples 1 and 2. Click "Add" and these two samples are validated and sent to TbSamples. Later another user wants to insert samples in the same group, opens the form and only samples 3, 4 and 5 appears (for 1 and 2, field OtherField Is Not Null). If user insert sample in position, lets say, 4, and changes his/her mind when focus is already in sample 5, press "Cancel" and samples 3, 4, and 5 are not inserted in TbSample.
If you wonder how I cluster samples in groups of five: In TbSample there are two fields, IDSample and NumberofSample. The number of registers in the table is limited to 50 samples. What I did is to fill in advanced IDSample from 1 to 10 and NumberofSample with numbers from 1 to 5 for each IDSample. So, registers are like:
IDSample, Numberofsample
1, 1
1, 2
1, 3
1, 4
1, 5
2, 1
2, 2
2, 3
2, 4
2, 5
... , ...
10, 1
10, 2
10, 3
10, 4
10, 5
There also a primary key field (from 1 to 50). When a sample comes or goes I use a query update, with IDSample and Numberofsample as conditions coming from forms. So I do not insert new records, I just update existing (for Update I mean delete the rest of fields for one record, or fill them, IDSample and Numberofsample are always there without changes).
Thank you very much in advance for your help.
The problem comes, as some of you may have guessed, when the user is filling the forms and changes focus to the next record. In that moment, the already filled record was updated in the table, and then conditions are bpassed, and the field OtherField is not Null anymore, so that record is not part of the query anymore, and I cannot cancel the addition of samples based on the query.
What I need is to put those selected registers on hold, that they do not go to the table until they are validated or cancel if the user decides so.
I thought to use a temporary table instead of query, and then update the TbSamples with that TbTmpSamples but I do not how to filled it.
Short sumary: a group of 5 samples is empty. A user opens the form with the five empty samples and fill samples 1 and 2. Click "Add" and these two samples are validated and sent to TbSamples. Later another user wants to insert samples in the same group, opens the form and only samples 3, 4 and 5 appears (for 1 and 2, field OtherField Is Not Null). If user insert sample in position, lets say, 4, and changes his/her mind when focus is already in sample 5, press "Cancel" and samples 3, 4, and 5 are not inserted in TbSample.
If you wonder how I cluster samples in groups of five: In TbSample there are two fields, IDSample and NumberofSample. The number of registers in the table is limited to 50 samples. What I did is to fill in advanced IDSample from 1 to 10 and NumberofSample with numbers from 1 to 5 for each IDSample. So, registers are like:
IDSample, Numberofsample
1, 1
1, 2
1, 3
1, 4
1, 5
2, 1
2, 2
2, 3
2, 4
2, 5
... , ...
10, 1
10, 2
10, 3
10, 4
10, 5
There also a primary key field (from 1 to 50). When a sample comes or goes I use a query update, with IDSample and Numberofsample as conditions coming from forms. So I do not insert new records, I just update existing (for Update I mean delete the rest of fields for one record, or fill them, IDSample and Numberofsample are always there without changes).
Thank you very much in advance for your help.