Prevent auto save on form

[tmyers]

@tmyers Glad you got it working.

A couple of observations. I have never once used a transaction with roll back . It's quite a specialised thing. Normal users wouldn't really need it. A clearing bank or investment house might. I wouldn't use single data types. For money values, I would use currency (much the best) or doubles.

Is the rollback not required? I assumed it was needed in the sense that you either commit or rollback and that you couldn't begin a transaction without closing it in some manner.

 

[gemma-the-husky]

Got it. I will ask the few people I am building this for and see if they want that extra line of defense or not, but you all made valid points for getting rid of it.

Don't ask them. Some will say yes, some will say no. Don't put it in when doing a normal edit. Just say it's standard behaviour. As Minty said, only do this where it's absolutely necessary. It's like the HitchHikers Guide to the Galaxy game where the game wouldn't let you swallow the babel-fish, until you asked repeatedly. (it wasn't the babel-fish, but it was something early on, on the Vogon space ship.

 

[tmyers]

Don't ask them. Some will say yes, some will say no. Don't put it in when doing a normal edit. Just say it's standard behaviour. As Minty said, only do this where it's absolutely necessary. It's like the HitchHikers Guide to the Galaxy game where the game wouldn't let you swallow the babel-fish, until you asked repeatedly. (it wasn't the babel-fish, but it was something early on, on the Vogon space ship.

FYI that movie is my all time favorite movie. I love both it and the book.

 

[gemma-the-husky]

Is the rollback not required? I assumed it was needed in the sense that you either commit or rollback and that you couldn't begin a transaction without closing it in some manner.

Well a transaction is like transferring £1m from one bank account to another. You want to update both the receiving account and downdate the sending account. You don't want half to get lost. So you need a roll back in case of a calamity. Just completing a normal process isn't as critical. You have to bear in mind that adding transaction commits and rollbacks is rather like record locking. It can conceivably stymie other users from doing some things until your transaction is completed. You probably do not want to offer the option of a rollback. The rollback should be automatic. If the user doesn't know how to respond, and leaves the "commit y/n" message on the screen, you may be blocking all other users from the database. The idea is to make the record locking/transaction management to last for absolutely as short a time as possible. It's there to protect the integrity of the data.

see the answers to this search.
can a database transaction cause a deadlock - Bing

What you would really do is initiate the transaction AFTER the user presses Y to commit the entry. Then if the save fails, the transaction rolls back. But this isn't a transaction really, A transaction is a sequence of processes that all need to be completed to complete the transaction. You are more likely just saving a single record entry.

 

[tmyers]

Well a transaction is like transferring £1m from one bank account to another. You want to update both the receiving account and downdate the sending account. You don't want half to get lost. So you need a roll back in case of a calamity. Just completing a normal process isn't as critical. You have to bear in mind that adding transaction commits and rollbacks is rather like record locking. It can conceivably stymie other users from doing some things until your transaction is completed. You probably do not want to offer the option of a rollback. The rollback should be automatic. If the user doesn't know how to respond, and leaves the "commit y/n" message on the screen, you may be blocking all other users from the database.

see the answers to this search.
can a database transaction cause a deadlock - Bing

That makes sense. I went ahead and removed it.

Also, do you have any idea why I am having an issue with the decimal/percent values I mentioned in post #13?

 

[gemma-the-husky]

Are you calculating the percentages each time or storing them?
How do you display them?
How is the display control for the percentage bound?

 

[tmyers]

Are you calculating the percentages each time or storing them?
How do you display them?
How is the display control for the percentage bound?

The both the start and end fields themselves are numbers with the format to percent. So I am storing it.
The control is a textbox bound to the first field and is formatted as percent.
When the code runs, the variables are showing the correct value in decimal (in this test, 0.06) but the destination table either doesn't get updated or something is going wrong as it still shows as 0.

 

[gemma-the-husky]

I am not following. What values do you have in start and end fields.
If the start field is 0.06, then are you expecting 6%, or 0.06%. If it's formatted as percent, it ought to be displaying with a % sign. It might be that the precision of your percent displays are causing the values to display as 0%.

 

[tmyers]

The value that is showing in the field is 6.00%, same as the control. The variable is showing 0.06 as its value and the end destination is showing 0.00%

I would like to clarify that actually entering the field shows the 6.00% value and not the 0.06

 

[gemma-the-husky]

I don't really understand. Are all these fields in one table?
Can you confirm what the field names types and values are, and what they should be?

This may be an issue with storing a calculated figure. If the underlying data changes, you have to re-calculate and update the stored figure.
Are you doing this on a form? If you are then temporarily add a record selectors to the form so you can see when it is edited.

 

[tmyers]

I don't really understand. Are all these fields in one table?
Can you confirm what the field names types and values are, and what they should be?

This may be an issue with storing a calculated figure. If the underlying data changes, you have to re-calculate and update the stored figure.
Are you doing this on a form? If you are then temporarily add a record selectors to the form so you can see when it is edited.

The data originates from [tblProducts] field [GP] which is set as a number and formatted to percent. The control on the form is bound to this field and also formatted as percent (just to test I switched it to general number and it does show it as 0.06). I am then trying to take the old value and new value and insert them into the table [tblChangeHistory] that holds the changes done to the record (shows both original value and the new value). There are no calculations being done anywhere in regards to this field/control with the only thing have been done is changing its format to display as a percent.

EDIT:
It does appear to be working, as I added in the code to do me.gptxt * 100 (which made it simply 6 obviously). Then when the code ran and I checked the table, it showed 600%. So I am confused as to why it wont show 6% when the value is 0.06.

 

[gemma-the-husky]

I would step through the code and display the old value and new value in a message box, probably.
I am not sure but overwriting the value in code may not set the old value in the way that would happen if you edit the control manually.
You can lock a field, but still change the value in code. Maybe that's what's going on.

 

[Isaac]

I meant to add this earlier

In case you do decide to go the route of only allowing saves through your button and eliminate all auto-save type of activity, this may help

How does Access handle the updates on my Forms?

Does anyone have some nice breakdown on how Access handles/passes data between the form/query/tables? I've picked up a bit from reading different threads, etc. But, I would like to make sure I understand how data is moved/udpated so that I can better control it through my program. Let me use...

www.access-programmers.co.uk

 

[Isaac]

I was just working in Access on a linked Sharepoint list with zero records and was reminded how much I dislike Access trying to constantly save everything - just how over-the-top it really is.

Here is what I did:

- Open the linked table object in datasheet mode
- Put my cursor in the new record, first column
- Type a single space, then backspace, then close the table. POOF - a new record was added to my Sharepoint list, complete with .... Nothing, except a ZLS. Uhh
Show me a user who would expect THAT! (Now someone is going to go find one...LOL)

 

[Isaac]

Ahhh....I hear ya', I'd rather just turn all that off and provide a Save button.
But honestly (sincerely), it's good to read what others do ... I appreciate the input. Always learning.

 

[tmyers]

How do you disallow ZLS? I have never noticed that option and would actually help deal with some errors I have had trouble managing.

 

[gemma-the-husky]

At the field, make the field "required" and disallow zls
And/or in the form's beforeupdate validation

if nz(somefield,"")="" then
'cancel the update, and give a warning.

Do both really. Protect the data at the table level, and make the app user-friendly as well. if you don't test in the before update the user will get a pretty unfriendly error message.

 

[Isaac]

You can spend your days arguing with Access or you can work with it.

I'm sorry, but I think it is MUCH easier to spend 3 minutes doing what it takes to disallow all the auto saves and then provide a button. That is about what it takes me.

My rule is I always strive to create an interface that either meets user's expectations or creates them for them. When your interface defies expectations, that causes irritable users. I don't really care what Microsoft thinks it should be, I care about my users.

 

[Minty]

I'm sorry, but I think it is MUCH easier to spend 3 minutes doing what it takes to disallow all the auto saves and then provide a button. That is about what it takes me.

My rule is I always strive to create an interface that either meets user's expectations or creates them for them. When your interface defies expectations, that causes irritable users. I don't really care what Microsoft thinks it should be, I care about my users.

How do you block right-clicking and select form close in the form header, unless you have completely disabled right-clicking or shortcut menus?
Or Alt-f4

I can't believe all of these are blocked in 3 minutes of code.
(Just being argumentative hear by the way)

 

[tmyers]

I can't believe all of these are blocked in 3 minutes of code.
(Just being argumentative hear by the way)

Black voodoo magic.