How not to save a whole record until Save is clicked?

[BrazilTechie]

Hi all:

1) Can you tell me if there is a way to save a record and make my record # (my occurence #) be captured only after the user entered all fields and is completely satisfied with it?

I am between setting the occurence # as a number and setting it as an autonumber. As I stated above, the only problem I think I would have with the autonumber is not be able to stop it from jumping up when the form is opened as a new record.

2) Is there a way not to save a record when the user goes from a field to another? I believe this is the default. I mentioned on question one that I'd like to have the record saved only when the Save button is clicked on (sorry if I sound redundant in my questions).

Please help.
Thanks.

 

[Bry12345]

The record is NOT saved when you move between fields, unless you have entered some code to make Access do so. The record is, however, saved when you move between records.

After you make the first entry on the form, the record status becomes 'dirty' (look at the status icon on the left side of the form), until is is explicitly saved or the form is closed.

The easiest way to do what you want to do is to add code to to Before Update to present a message box asking if the user wants to save the record. - - - -

Bryan

 

[BrazilTechie]

Thanks for your reply Bryan.

I guess the only problem I would still have it the autonumbering.
When I first change any of the fields, my autonumber field (occurence #) goes up one.
Are you saying that if I cancelled out without saving that record the autonumber would actually go back to what it was?

Thanks again for your help.

 

[930driver]

No, it won't go back to what it was.

You may be mis-using the autonumber. It's really there for use in establishing relationships between tables and should never be seen by your end users.

If you need sequential numbers that have meaning to your users you need to create your own function that returns the next available number and assigns it to that record as needed. "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[BrazilTechie]

Hi all. I got it. I will be using the following code to allow the user to decide on the saving or not when clicking the CLOSE (or SAVE) button.

Private Sub Command258_Click()
Dim ConfirmMsg As String
ConfirmMsg = "Click Yes to Save this record. Press No to undo changes."

If Me.Dirty = True Then
If MsgBox(ConfirmMsg, vbYesNo, "Save Changes&quot = vbNo Then
DoCmd.CancelEvent
Me.Undo
End If
End If
DoCmd.Close
End Sub

I have tested and found that the autonumber does not move up if the user cancels out by clicking on No.

Thank you all for the replies.

 

[Bry12345]

That'll work . . but just to reinforce what 930driver advised:

If you absolutely cannot have the record # (i.e. occurance #) increment by more than one, then you should not be using autonumber. While the autonumber may not increment when you cancel as in your code above, you'll run into other issues in situations such as:

1 - A user adds a record that needs to be deleted.
2 - You add 'test' records as part of your programming
3 - A whole bunch more situations that don't immediately come to mind.

Just my opinion . . . - - - -

Bryan

 

[KornGeek]

Adding to Bryan's #3:
If you ever make this database multiuser, new possibilities arise:
3a) Two users try to add a record at the exact same time, and it gives them both the same autonumber.
3b) UserA gets a number, then UserB gets a number. UserA then cancels, and there is a gap before UserB's number.
3c) Other possibilities that don't immediately come to mind.

The rule of thumb is never use autonumber if you expect it to have any meaning to the user other than a simple unique identifier.

From the perspective of somebody who has had to revamp a program and remove autonumber, it's easier if you just do it in the beginning.

 

[BrazilTechie]

Hi Bryan:
Thanks for your last comments.
I will keep them in mind.

For now, I am OK.
Thanks again.

 

[BrazilTechie]

Hi KornGreek:

You posted your response before I was replying to Bryan's last post.

Yes, I intend to make this a multiuser application. How else could I keep track of the occurence number? Making it a global variable?

You are right. I am at the beginning. I should try and start it on the right track.

I will be waiting for your reply.

Thanks.

 

[KornGeek]

I would create a unique record identifier (there is a good example in one of the Access FAQ's) similar to autonumber. This gets around the problem of two users getting the same number. This will, however, leave gaps.

If you must remove gaps, then I would create a tool (I would use a function, but you might be able to do it with a query) to go through and number the records in a separate field. You will have to run this tool periodically to handle new records being added, and potentially renumber everything if a record is ever deleted.

Is there a particular reason you can't have any gaps in the sequence?

 

[BrazilTechie]

Hi KornGeek:

What I am trying to do is to keep a tracking number for occurences.
Gaps should be avoided.

Thanks.

 

[MichaelRed]

I hesitate to leap into this quagmire. It has already been reasonably and clearly stated that the use of autonumber and similar approaches for incrementing will (in all pratical applications) generate 'gaps' in the sequence.

This will occur in different scenarios, however it will occur. The ONLY way to 'prevent' this is to periodically (every occurance of the possability of a record addition OR deletion) renumber the recordset.

The concept of an ordinal counter w/o gaps is somewhat foregin to the concept of a relational databse. The former is a serial and unit concept/perspective while the latter is orientated towards the use of sets of objects. In the set concept, sequence is, generally. Autonumber, as previously noted is not intended to 'sequentially' track the number or sequence of record additions to a data set (table), but simply to provide a simplistic method (in SINGLE USER data base applications) of generating a UNIQUE key for a recordset.

As CLEARLY stated by others in previous posts, you cannot rely on a simple 'counter' to generate OR MAINTAIN a sequential counter, except in very limited and special circumstances. What ever the rationale for the attempt, your need to seriously re-consider it.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[BrazilTechie]

Hi all:

This is going to be my first multiuser application. Please bear with me here.

I was thinking if I could just set my occurence number as type number in the tblOccurences.

Then every time a user opened a form, the following would take place.

1. Open the table (tblOccurences) as the current table
2. Go to the last last and capture the its occurence number.
3. Display that number and tell user a "new" number will be created in this record he is about to create, based on his response.
4. If he answers YES, the Occurence number field would be incremented by 1.
5. After this point he would no longer have the chance to UNDO it.

I don't think there would be a chance of a record number being duplicated, right?

Please let me know how this sound and how to do it.

Thanks a bunch and sorry for not responding faster.

 

[KornGeek]

Actually, there are a couple of issues with what you describe.

1) When you think about a database, don't think about the records being in any particular order, because they may not be. Instead of the "last" record, you would want to get the maximum value for the field (DMax is a great function).

2) If UserA goes to start a record, and the highest value in the table is 5, UserA will get the value 6. If UserB goes to start a record before UserA finishes, the highest value in the table will still be 5, so UserB will get the value 6. If both UserA and UserB attempt to save, they will both be saving with the value 6.

3) If you use some other method to get the value so that UserA would get the value 6, and then UserB would get the value 7, then if UserA cancels and UserB saves, you will have a gap in the data.

One possibility (and hopefully someone with more experience can comment on this) would be to wait until the user hit the save button to get the ID value. This would still have the potential problem of two users saving at the same time, but I believe (with very little certainty) that this should reduce the probability of that.

However, if a user ever deletes a record, you are back to having gaps. In my opionion, it isn't worth the effort to try to have a sequence number in a record. If you date and time stamp the records and/or use a variation on autonumber such as the one in the FAQs (faq700-184 I believe), then you can sort the records for display purposes. The autonumber variation will give you a unique record number.

By the way, the faq I mentioned was written by the much esteemed MichaelRed who commented above. I have implemented a variation of this code, and it works quite well.

 

[MichaelRed]

KornGeek,

thank you, thank you, thank you, ...

Kudos, compliments, stars and information re employment opportunity accepted with as much grace as I am able to muster at any moment.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[BrazilTechie]

Hi KornGeek:

About the probability of a user deleting a record, we can discard that. They won't be able to (it's not part of the project).

And about the article from MichaelRed, it's pretty good.

Let me take some time and see if I can come with a solution and I will post back in a couple of days or so.

Thanks very much for the time being.

You guys have given me good and innovating ideas.