Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

access save data

I have a form that saves data after leaving the last field on the form. I understand that access will automatically save the data if the form is "dirty". I don't want this to happen. I want the user to decide to save the data by pressing the "save" button on my form.

This only happens when I either 'tab' out of the field or hit enter which in essense is tabbing out.

Can I set the field control to on dirty from true to false on exit from the field. If so, what if the user wants to cancel out and return to another form by pressing another button on the form? Also do I need to set this for each field on the form? Can I set it globaly? That is, I do not want any records saved on any form until the user presses the save button on that form.

Your help would be appreciated.

Thanks
Al
0
allfredeneuman
Asked:
allfredeneuman
  • 4
  • 3
  • 2
  • +3
3 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I don't want this to happen. I want the user to decide to save the data by pressing the "save" button on my form.
No can do dude, unless you write a bunch of code to...
(1)  Copy your table into another 'temporary' like table.
(2)  Change your form so that the 'temporary' like table is the Record Source
(3)  Let the user edit that table as long as they want, then...
(4)  When user hits Save button write VBA code that ...
   (a)  Deletes the copied rows from original table
   (b)  Inserts the edited rows from the 'temporary' table into the original table.

Access saves records after the user navigates away from them, and afaik does not have any funcitonality that saves multiple records as a batch or transaction.

Good luck.
Jim
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<slight change to above>
(1a)  Create a copy of your original table as another 'temporary' table.
(1b)  Make sure there are no records in it.
(1c)  When your form opens, INSERT whatever records the user is going to manipulate from the original table to the 'temporary' table.
0
 
Dale FyeCommented:
Change the Cycle property on the Other tab of the Properties dialog from "All Records" to "Current Record".

Then, instead of going to the next record after you tab out of the last field, the focus will go back to the first field of the current record.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Dale FyeCommented:
When you modify the Cycle property, it now gives you the ability to force the user to use Cancel and Close buttons to save or cancel updates to the current record.  You can use a Cancel button and the docmd.Undo method to reverse any changes to the current record, or a Save button, along with a me.Dirty statement to force Access to write the current record.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
One method is this:

In the form's code module,, at the very top of the module (in the General Declarations section), add this:

Private fSave As Boolean

In the Form's BeforeUpdate  event, do this:

If Not fSave Then
  Cancel = True
  Msgbox 'You can't save now"
End If

Now in your Save button's click event:

Sub Yourbutton_Click()
  fSave = True
End If

Finally, in the Current event of the form, reset the value of fSave:

Sub Form_Current()
  fSave = False
End If
0
 
datAdrenalineCommented:
You can indeed perform the task you seek to do (require user to press a "Save" button).  LSM provided a way, but I personally have used a method that requires less code ...

In the Form's BeforeUpdate  event, do this:

Private Form_BeforeUpdate(Cancel As Integer)
    Cancel = True
    MsgBox "You must save your changes with the Save button!"
End Sub

Open in new window

The BeforeUpdate event code above will prevent ANY Saving from occurring (except through your button with some fancy code :) ) ....

Now in your Save buttons on click code ...

Private Sub btnSaveRecord_Click()

    'Disable the BeforeUpdate event
    Me.BeforeUpdate = ""

    'Save the record
    If Me.Dirty Then Me.Dirty = False

    'Enable the BeforeUpdate event
    Me.BeforeUpdate = "[Event Procedure]"

End Sub

Open in new window



Hope this helps!
0
 
allfredeneumanAuthor Commented:
Thank you for the answers.

fyed's answer works except when I loose focus on the form - switch to another form without pressing the "save record" button. The record will still be saved.

I'm thinking either datAdrenaline or LSMconsulting might be the answer to this issue I haven't tried them yet.

maybe I can do something in the form "on lost focus"?

=Al
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
" except when I loose focus on the form - switch to another form without pressing the "save record" button. The record will still be saved."

Why?  Are you sure?
That really should not happen - since the form isn't really moving to a new record. It should remain in the current state (Dirty if an edit has occurred).

Also, I think Before Update scheme above would be problematic IF ... you need to do any kind of final validation (quite common), in which case you will need the Form BU event in tact.

Basically, you need (typically) 3 buttons on the form and the code to control them:
Save, Cancel, New Record (or Add Record).

mx
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
FWIW I've used the method I suggested in hundreds of projects, and it works every time. The only time the record is saved is if the user explicitly clicks the Save button. Otherwise, the user either (a) cannot leave the form or (b) must Undo (i.e. Esc) the changes before leaving the form.
0
 
datAdrenalineCommented:
validation is not an issue with either method that i or lsm presented.  in both cases the validation can be placed in the code behind the save button.

also, in support of lsmconsulting, i too have used the technique he presented, as well as the technique i presented many many times.  both techniques are viable, i have just developed a preference for the method i proposed :)
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Well, I wasn't really challenging either approach. Beyond that  re:

 "except when I loose focus on the form - switch to another form without pressing the "save record" button. The record will still be saved."

There is no reason 'the record will be saved' just because you happen to click on another open form. You can easily prove this to your self.  So, setting the Cycle property as fyed noted should ... take care of the 'tabbing out' issue ... UNLESS ... something else is going on.

mx
0
 
Dale FyeCommented:
To be honest, I use a combination of the technique I mentioned, and the LSMs.  The only reason the technique I mentioned, by itself might not work, is if the "form" in question is actually a subform.  As soon as you move the focus off the subform, the record will be written, which is why I also use the technique that LSM suggested.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Correct. Based on the OP's description, I assumed no subforms.
0
 
Dale FyeCommented:
as did I.
0
 
allfredeneumanAuthor Commented:
Thank you for your suggestions.

I think I have what I need to know for now. I'm going to use fyed's solution and probably LSM consulting or datAdrenaline. If I cannot get it working I'll open another question.

AEN
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

  • 4
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now