Using a "frozen" recordsert? (1 Viewer)

[rafa]

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.

 

[rafa]

Sorry, when IDNumber = X, I meant IDSample = X

 

[gemma-the-husky]

Use the forms before update event.

Allocate the sample number at that point. Set the number as required, so 0 doesn't work.
You could also include code in the before update event to check the count of samples, and prevent more than 5 readings being entered that way.
If you add these steps, then access will reject the update.

You can use the forms current event to manage/identify the sample id.
Maybe store the last sampleid, and if it changes re-evaluate the current position. (ie number of readings)

 

[arnelgp]

use Transaction.
If you want to cancel "all" even if you "think" that the record was saved, you can Rollback the updates.

 

[rafa]

Use the forms before update event.

Allocate the sample number at that point. Set the number as required, so 0 doesn't work.
You could also include code in the before update event to check the count of samples, and prevent more than 5 readings being entered that way.
If you add these steps, then access will reject the update.

You can use the forms current event to manage/identify the sample id.
Maybe store the last sampleid, and if it changes re-evaluate the current position. (ie number of readings)

Thanks for your reply.
Then you suggest that I must control the SampleNumber in BeforeUpdate event in the form. Whit this I can limit the record source of the form and avoid wrong additions, but I cannot see how to avoid to update the table with the right additions.

 

[jdraw]

Here is an article with example of Transaction logic using Ms Access as mentioned by arnelgp.
A good reference even if you decide on another approach in this instance.

 

[rafa]

arnelgp, thanks for your suggestion of using transactions. I don't know much about it, I am now looking for how to use it. In principle it is about to execute at the same time two SQL queries? (like in a currency transaction). But here I have only one at a time, Insert or Delete. What I do not see is how to store temporary the data inserted in the form before goint to the table.

 

[gemma-the-husky]

Thanks for your reply.
Then you suggest that I must control the SampleNumber in BeforeUpdate event in the form. Whit this I can limit the record source of the form and avoid wrong additions, but I cannot see how to avoid to update the table with the right additions.

Sorry. I don't understand what you are saying.

When a user adds a new record, they will select a sampleid.

You can do something like this in the beforeupdate event for the sampleid (or later in the beforeupdate for the form)
This will stop users entering more than 5 samples.
It's cleaner to do it at the start, otherwise they get the error after entering all the input, which would be irritating.

The exact operation depends on how you have the form working. You might have a readings subform attached to the samples form, in which case you need the following type of code when they try to enter a new reading, but its a similar idea.

recordcount = nz(dcount("*","samplestable","sampleid = " & thissample),))
if recordcount=5 then
  msgbox "Sorry. You have already entered 5 samples. You cannot enter any more."
  cancel=true
  exit sub
end if

 

[rafa]

I am sorry if I did not make myself clear.
That code you wrote is for avoiding to add more than five samples, but that point should be already solved when open the form, with a recordsource of the 5 samples fitting the contitions. There will be no posibility to add a 6th sample.

 

[gemma-the-husky]

Ok - but you have to add code to prevent the sixth being added.
You could put code in the current event, and prevent insertions that way.

Assuming you have two tables, the tables are set as 1-many, then you are restricting the many side to 5 maximum, and you need to add code to apply that restriction.

 

[MajP]

But here I have only one at a time, Insert or Delete. What I do not see is how to store temporary the data inserted in the form before goint to the table.

It can work on a single group of records. In your case you have a group of 5 records and to be valid not only does each record need to get validated but the group has to get validated. So in the transaction if the "group" fails you can roll back.
So in your example

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 TbSampl

Your form would present samples 3,4,5. You enter 3,4,5 and decide to "Cancel" you can roll back everything since the last "saved".

This may, however, be a self inflicted wound on your part by creating the temp records. I am not sure why that is needed. I think you could achieve the same user experience without it and make validation simpler.

I can think of cases where a transaction makes sense. If records 1 and 2 are not valid based on conditions of 3,4,5 then I would need a transaction because I cannot validate each record individually. In your case I do not see that.

 

[MajP]

With that said, @arnelgp did not post his example

Close form without saving record?

Im very new so this could be a dumb question but here goes. I have a form which adds records to a database of donors. On the form i have a close form button with the code that follows - DoCmd.Close acForm, "Add Donor Form", acSaveNo whenever the close button is pressed it saves the record to...

www.access-programmers.co.uk

This shows rolling back a group of records on a continuous form.
As I said, I am not convinced that you need to do this, but it would work and be easy to implement on your form. This will allow you to enter 3,4,5 and roll all back.

 

[rafa]

Thanks MajP.
I read the chat "Close form without record?" As far as I understood, the BeforeUpdate event is used to validate only a record.
Regarding the transaction method, well, I agree with you, it could work but I would like to try something easier, even reducing the features for the user (if it can't be done, it can't be done, at least with my Access knowdlege).

My need to try to validate several records come from the fact that life will be easier for users. That form will have also a button for each record to duplicate last record in next record (samples has ca. 15 fields and some samples has all the same except, e.g. serial number). Except last records, of course. Tha would be extremly useful for users. Saves time and avoids mistakes. If I add one sample after another, that would be impossible.

I was thinking also to make a table to be used as temporary table, with only five records, select in some way the right records (3, 4, and 5) make them a source of the form to be filled and then update the main table with this. But I was blocked on how to "transfer" the right records to that temporary table. Would it be possible?

 

[MajP]

You can do this with a temporary table or even an unbound form. The temp table would probably be easier than an unbound form. I do not think it would be easier than wrapping in a transaction. If you look at that example it is done done with one line of code.

If you went with a temp table. You would select a sample. You would figure out how many records already entered and load the remaining "empty rows". In the example you would see 3,4,5. Then the user hits save and it does an insert query of all records if all are validated. If one or more is invalid it forces the user to put in valid inputs.

However, I still think you may have made this harder then it is needed. I do not think you are gaining anything. If you just made this a simple main form (sample) and subform (sample readings) and did not preload the records, what do you lose?

Assume it is not preloaded.
You mention a user entering 2 records, then later coming back and entering 3. That tells me that all 5 do not have to be entered at one time. It also tells me you are not validating the "group".

With a standard main form subform you can:
1. add a reading.
2. You could duplicate a reading.
3. You can show completed readings and
4. you can validate each record as you enter.
5. You can alert the user if less than 5 records are entered when leaving the main form and can force the user to do something.

Saves time and avoids mistakes. If I add one sample after another, that would be impossible.

That is not true. Why do you think that. Duplicating a record is pretty common.

I may have missed it, but I still am not seeing if you really need to validate the records as a group or are you really needing to validate each record before entering it.

If you were an experienced Access developer then weighing the pros and cons for using temp tables or transactions, it might be worth it. If not, you may be causing a lot of extra work for no real benefit. I am so far not really seeing the benefit. Not discouraging you because this is very doable, but cautioning. The problem is with down stream effects. You make the UI not the traditional Access way, which works nice but more complicated. That may make doing follow on things also more complicated.

 

[rafa]

Thank you very much for your comprehensive reply, I really appreciate the time you have taken.

I had no preconceived ideas on how to fix this. I considered that solution because I thought the ones you mention (form with subform) wouldn't give the desired result (= I did not know how to do it, see below). But now that you mention it, of course I'm open to it if people with much more experience say it's possible and easier.

You mention a user entering 2 records, then later coming back and entering 3. That tells me that all 5 do not have to be entered at one time. It also tells me you are not validating the "group".

Yes, validation rules will be there, no matter if user left some time between inputs. For example, if the five samples cannot have more than 25 Kg all together, weights of samples 1 and 2 still count when trying to insert samples 3, 4, 5, which will be stopped if any of the new samples exceeds that limit, and so on. I should explain that all five positions for samples do not have to be occupied, this is not a rule (think, for example, of boxes on a shelf in a warehouse, each shelf can contain 0 to 5 boxes).

If you just made this a simple main form (sample) and subform (sample readings) and did not preload the records, what do you lose?

As I said I discard this possibility, form with subform (continuous?), because I did not know how to solve some related problems which I did not explain previously, sorry:
- Some fields are compulsory, as usual (Name, Dimensions, Weight, SNumber, etc) and others not (eg. Remarks). I was not sure how to make this works. When a record is abandoned? But the user can leave one record before completing it, jumping to another one to copy/paste the same field in some records, it is pretty common. With my approach, this will be made with the OK button. Here...honestly, I dont know.
- I wanted to fill the field SampleDate (date/time when sample is inserted) automatically, inserting in some action query Now(), easy with an OK button. Again, whit the form/subform approach I don't know at what point to launch this.

I would also prefer to preload available empty records. I think it is better to tell the user how many samples are available, rather than limiting the addition if the limit is exceeded.

With a standard main form subform you can:
4. you can validate each record as you enter.

Even in a continous subform? Jumping among the samples/records?

Saves time and avoids mistakes. If I add one sample after another, that would be impossible.

That is not true. Why do you think that. Duplicating a record is pretty common.

Sorry, I meant in a form with only one sample (yes, one at a time, not very practical, but safe).

I may have missed it, but I still am not seeing if you really need to validate the records as a group or are you really needing to validate each record before entering it.

Humm....yes, you are right. Validation rules apply to each group of five (like weight) but are, or can be, checked when a sample is entered.

Duplicating a record is pretty common.

I've done this, a button at the end of each record in the continuois form, not using acNewRec but acNext (remember that in my pop-up form the empty samples of a group are already there, no additions allow).
I'm still struggling to make the last record button disable. In principle it would be easy: if this is the last record of recordset, CmdDuplicateRecord.Visible = False. No way, I can have all or none visibles. Maybe it would be better to stop the event/sub when an error arrives (=when last record wants to go to next record).

Again, thak you very much for your help.

 

[MajP]

Can you explain why someone would enter only readings for say 1, 3, 5 vs entering that data as just 1,2,3? Why have empty records? What is the meaning?

I do not know your skillset. If you were experienced developer then doing with a temp table could give you a lot of flexibility but there will be significant code involved.
If not i would do everything i could to do this in a traditional method. A main form with subform and do not preload any records. Then you can add most of the functionality you describe.

What might be difficult would be to start entering a reading and then leave it without filling out the required fields.

 

[gemma-the-husky]

If you think about excel, you can have all your readings in a single spreadsheet table, and filter the stop row of the spreadsheet to select a single sample and the associated readings.

In access you are doing the same thing, but you avoid the repeating group of general information about the sample, and end up with two tables

samples - with the general information
readings

You arrange your form to show the sample, and for the current sample, the readings. As you move from sample to sample the database automatically applies the filter so that you only see the results for a the selected sample. You do not need any code to do this. All you now need to do is add a little piece of code to limit the readings entered to a maximum number per sample.

You can now easily extract for each sample
-the total number of readings
-the average reading
-samples with the full 5 readings
-samples with incomplete reading counts.
-samples on a given date
-anything at all

it's a different way of considering the data. You hardly ever view all the data. You just view the particular subset that interests you each time.

 

[gemma-the-husky]

Have a look at this sample. The data is linked automatically, but there is some code to improve the functionality.
I hope it helps. When you start, it will open at the main Samples form. There is code in the database, so you might get a warning.

The code does this.

a) - not allow readings to be entered if there is no active sample
this gets retested as you move from sample row to row.

b) the test and notes are both text.

c) the readings allow you to enter a date/time of the reading, and a decimal number.
The time of reading is entered as valid date space valid time (optional)
So 13/3/21 12:06:34 is a valid entry.
It's actually tricky to enter date and time, but this is an example.

d) - when you enter a reading it stores the time entered and user name - the yellowed fields


USE

So just enter some text in the first sample record, (both fields are text, and click the pencil in the left margin to save the record. As soon as you have entered the first sample record, the readings subform will be activated.
You can enter more samples by using the navigation control at the bottom. The * button lets you enter a new record.

Now it doesn't actually show you a reading ID at all, it displays the readings in order based on the date/time of the reading.

If you try to enter a new reading, it first checks the number of readings, and stops you entering more than 5.
This is normal behaviour for databases. It's much easier for databases to work on evaluating a set of data, and counting how many.

If you manually entered rows, 1,2,3, then deleted row 2, and entered row 4, you now have rows 1,2,4
If you enter then row 5, delete row 2, and enter row 6, you now have rows 1,4,5,6
You only have 4 rows, but the highest number is already 6. If you sort them in a different order, you might get 4,6,1,5

So, instead of having to control row numbers, databases are better at evaluating the set as a whole - so just 4 rows entered so far, and ignore row numbers.

 

[rafa]

Can you explain why someone would enter only readings for say 1, 3, 5 vs entering that data as just 1,2,3? Why have empty records? What is the meaning?

Because it is real. These numbers correspond to real places, and it is necessary to know whether it is full or empty. The samples are identical, but you have to be able to distinguish between the one at 1 or at 3. That the samples are identical is not so strange, if you think of any mass production process (machine parts, for example, or more simply, screws). Samples cannot be labelled or marked, that's what the positions in each group are for. And more generally... because that's what I've been asked to do, and I have to adapt the database to the work of my colleagues, not the other way around.

Ok, after all your comments I went to the temporary table option during the weekend. I am aware this is not the prettiest and fastest method, but so far it works:

- A new table, TbTmpSamples, with an IDTmpSample, which I filled with numbers 1 to 5. The rest of fields are the same like TbSamples and are empty.
- A Select Query to select the five samples which correspond to a position, which is selected in another form (combo).
- An Update query, QueTmpAddSamples, to update TbTmpSamples from the Select query, linked by Numberof Sample. Why I used a intermediate query? Because it was impossible to get it directly between the two tables. I could not write the right Criteria to get it. I try to simplify this in the future.
- Another Update Query, QueTmpDeleteSamples, which change to Null all the samples in TbTmpSamples, except the NumberofSample (1 to 5).
- A third UpdateQuery, QueTmpMoveSamples, passing samples between TmpSamples to TbSamples.
Now, in the main form a button which execute QueTmpAddSamples (position selected from a combo), and open a form:
- The form has the Temp Table as record source, here I insert the condition "mandatoryfield" Is Null, in this way I get only the empty samples (e.g. 1, 4, 5). The user fill all the mandatory fields (still to fine tune moving inside the recorsetClone), button to duplicate record, etc. There are two bottons: Cancel: execute QueTmpDeleteSamples (tmp table is empty) and close form. OK: execute QueTmpMoveSamples (samples are moved from temp to TbSamples), then execute QueTmpDeleteSamples (tmp table is empty, ready for the next update) and close form.

Uf...yes, I know, a mess. Four queries and a table (and some small code).

@Gemma, thank you very much for your database example. I surely will use some of yout ideas, thanks!
I was not aware of the possibility of using Environ("USERNAME"). Is it safe? What I usually use is a dialogbox at the beginning to enter username and password, if it is ok, I make the dialogbox not visible and use that info in the database.

One last and direct question: what is better for the database (fast, clean, etc): to build queries and executed from the code? Or write directly the SQL code and not build the query? Or is it the same?

Many thanks! At least it works and your comments help me to clarify it.

 

[gemma-the-husky]

environ is safe, although it can be got around. If you already have a login system then use yours. I just added it for the demo. There are about 50 environ settings.

for x = 1 to 50
msgbox "environ  " & x & "   " & Environ(x)
next

queries - I think most of us use stored queries, and develop them with the interactive query design. If you right click the header, and select SQL, you can see what is actually being produced, and use that as a learning tool

I think most of us would still do it this way to control the total of 5 samples, and let users add their own internal sample number to manage numbers 1 to 5. We would not normally create empty place holders for 5 readings. You can do, but it's not necessary.

But if you want to pre-load 5 spaces, then when you insert a new sample do something like this
(in the afterupdate event, I think)

'only do it once!
if me.newrecord then
      for x = 1 to 5
          create a sql insert statement for sampleid, with a reading value of x, with empty/default fields
          run the sql insert statement
          'or execute a stored query for the value of x
     next
     requery the subform
end if

Now you need to change your dbs to prevent the users adding or deleting the 5 sample rows.