Prevent auto save on form (1 Viewer)

[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

 

[Pat Hartman]

The first thing I do when creating an Access form is get rid of all the crap ways that Access tries to sneaky auto save and nobody would expect

@Isaac,
I know I've mentioned this before but, the ONLY place code is necessary is the form's BeforeUpdate event. You can write dozens of lines of code in multiple events but the reality is code is only actually required in the BeforeUpdate event. If the code is not there, you run the risk of a bad or incomplete record from being saved. I once removed 5,000+ lines of code from an application. This person had clearly discovered that his messages were being ignored and bad data was being saved. He had code in 5 control level events for every control on every form. And it still didn't work so he expanded it so that cntl1 checked cntl1, cntl2 checked cntl2 AND cntl1, cntl3 checked cntl3 and cntl2 and cntl1. So you can see how the code accumulates. There was also code in several form level events. The bottom line here is the was NO code in any of the BeforeUpdate events so no matter how much code he wrote he wasn't able to stop the save of bad data.

If you want to use a Save button and ensure that people use it, the process is very simple:
1. Define a public variable. Call it SavePressed or something.
2. In the Current event - SavePressed = False
3. In the cmdSave button's Click event - SavePressed = True
4. In the form's BeforeUpdate event - If SavePressed = True ....
5. In the unload event - add code to cancel if form is dirty. You may need code some other place to trap 2501 if you have a close button.

This allows you to force the user to press your save button.

@tmyers,
You have NO validation code in the BeforeUpdate event. How do you know the changes are valid? That is the code that belongs in the BeforeUpdate event. And you can use the procedure above to force the user to use a Save button if that floats your boat.

You don't have to ask people if they want to save if you validate their entries so you can prevent partial updates or outright errors from being saved. As someone already mentioned, asking gratuitous questions just trains users to click without reading. Instead be parsimonious with your messaging. You want the user to read messages because they are important.

 

[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)

 

[Pat Hartman]

Isaac,
That is why we put validation code in the form's BeforeUpdate event to prevent such a thing from happening. Also, I NEVER leave the AllowZLS property as yes (the current default). If a field is required, a ZLS satisfies that requirement which is why I don't allow them. It makes absolutely no sense to allow a Last name to be "blank", ergo, it should be defined as required and AllowZLS must be set to No.

You have control over what and when, you just have to use the correct events and make sure that required fields are defined as required. That is where RI shines. You would probably also put validation code in the Form's BeforeUpdate event but only to give the user a more friendly error message.

Users should also be trained to use esc or select and delete and never use backspace to "clear" a field because backspace does NOT clear it. It leaves a field as a ZLS. Yet another reason to disallow them.

If the record has no required data then creating an entirely blank row should be OK and the user should not be surprised. Is that 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.

 

[Pat Hartman]

Ahhh....I hear ya', I'd rather just turn all that off and provide a Save button.

You can spend your days arguing with Access or you can work with it. You can switch to some C variant where you can do anything you want and which does nothing for you so you have complete control OR you can work with Access and VBA which is a RAD tool. RAD tools handle the details so that you can handle the unique stuff. I went into Access with over 20 years developing in COBOL on the mainframe so I'd written my million lines of code and didn't need the practice. To me, Access was a breath of fresh air and once I started to understand what it was actually doing, I stopped fighting with it and my life got ever so much better

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

Open the table in design view and click on the field you want to change. Look in the properties and change the one for Allow ZLS from yes to no.

OR If you want to set the property for all STRING fields in all tables in the database, then use this. I didn't write it but I forgot who I got it from so I apologize for not being able to attribute it properly. As always, when running new code, make sure you back up the production copy first so you can go back if you have to.

I've never run this on a database where the tables contain data so I'm not sure what will happen. I would just create a small test table first and make sure it has ZLS data. It may convert it to null or you might get an error.

Function FixZLS()
    Dim db As dao.Database
    Dim tdf As dao.TableDef
    Dim fld As dao.Field
    Dim prp As dao.Property
    Const conPropName = "AllowZeroLength"
    Const conPropValue = False
  
    Set db = CurrentDb()
    For Each tdf In db.TableDefs
        If (tdf.Attributes And dbSystemObject) = 0 Then
            If tdf.Name <> "Switchboard Items" Then
                For Each fld In tdf.Fields
                    If fld.Properties(conPropName) Then
                        Debug.Print tdf.Name & "." & fld.Name
                        fld.Properties(conPropName) = conPropValue
                    End If
                Next
            End If
        End If
    Next
      
    Set prp = Nothing
    Set fld = Nothing
    Set tdf = Nothing
    Set db = Nothing
End Function