Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Unanswered: Automatic saving of data in MS-Access

    I created a form using form wizard and assoicated the form with a table. Then I created a save button in the form which will save the data into the table.

    Problem I have is, I would like to avoid automatic saving of data in form unless Save command button is pressed. For this, in the Form Unload event, I am checking if form is Dirtry (Me.Dirty) if so I am trying to undo the action. Me.Undo which is not working. I also tried:

    DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

    I want all the changes that are made to database without pressing save button to be lost.

    Can anybody suggest the solution. I would greatly appreciate that.

    - Rama

  2. #2
    Join Date
    Feb 2004
    Posts
    4
    I am in particular looking at the event that fires just before automatic saving of data.

    Form unload event is not the one as data is already saved before unloading. Close is not the one.

    If I can find the Event then I can either undo the save or Make Me.Dirty as false.

    Looking forward to the reply.

    Thanks in advance.

  3. #3
    Join Date
    Nov 2003
    Location
    Riga, Latvia
    Posts
    36
    There are two ways of doing this.

    First, is you can make the controls unbound and write the data into the db by using vb code under the button onclick event.

    Second, you can try to play with events, and what you're looking for is beforeinsert event (for new records only) or beforeupdate event (for both current and new records). Sequence was something like <beforeinsert> beforeupdate current afterupdate <afterinsert> where events in <> occur only for new records, accordingly.

    Then, you could set the button to change some variable, like, for example

    dim CanUpdate as boolean

    which is false by default, and then in the beforeupdate check

    if not varCanUpdate then docmd.cancelevent

    There might be better ways to do this, but this should work.
    Replies based on my configuration: Windows XP / Microsoft Office Access 2003 unless specified otherwise.

  4. #4
    Join Date
    Feb 2004
    Posts
    4
    Thanx Virgo_Libra. Actually I already tried that.

    The first choice is ruled out for me, as I am modifying already existing database which has huge set of forms and tables. I cannot afford to change those settings by designing new forms.

    Second choice did not work actually. I tried both before and after updates and inserts. When I tried to undo before insert/update, if saving is not through command button, it is ignoring the undo. Same is the case with After insert/update.

    I suspect MS-Access is using a buffer and writing its contents to the buffer and then worry about After/Before Inserts/Updates. When I Undo the data it is not undoing the things it has written to buffer. I am not sure this is the reason why it didn't work. If anybody can shed some more light on this.

    So I am back at the same question, are there any alternative solutions. I would greatly appreciate your help.

    Once again thanks Virgo

  5. #5
    Join Date
    Nov 2003
    Location
    Riga, Latvia
    Posts
    36

    Attention please

    Seems that you've overlooked the docmd.cancelevent that I was suggesting to use. Try this: I have a form with two buttons, btnOK and btnCancel, and the following code for the form:

    Dim varCanSave as Boolean

    Private Sub btnCancel_Click()
    On Error GoTo Err_btnCancel_Click

    If Me.Dirty Then DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
    DoCmd.Close

    Exit_btnCancel_Click:
    Exit Sub

    Err_btnCancel_Click:
    ErrorBox Err.Description, "Form control Event/Function", "frmAccount.btnCancel_Click", Err.Description
    Resume Exit_btnCancel_Click

    End Sub

    Private Sub btnOK_Click()
    On Error GoTo Err_btnOK_Click

    If Me.Dirty Then
    varCanSave = True
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    End If

    DoCmd.Close
    Forms!frmCustomer!sfrmList.Form.Requery

    Exit_btnOK_Click:
    Exit Sub

    Err_btnOK_Click:
    ErrorBox Err.Description, "Form control Event/Function", "frmAccount.btnOK_Click", Err.Description
    Resume Exit_btnOK_Click

    End Sub

    Private Sub Form_BeforeUpdate(Cancel As Integer)

    If Not varCanSave Then
    DoCmd.CancelEvent
    MsgBox "Cannot save record!"
    End If

    End Sub

    Private Sub Form_Current()

    varCanSave = False

    End Sub
    With this, if I try to use anything other than the OK button -- i.e. Shift+Enter, "Save record" from the menu, closing the form -- it will give out a message "Cannot save record!" and not write anything into the table, since I am cancelling the Update event. And, clicking the Cancel button just undoes the changes that I've made.
    Replies based on my configuration: Windows XP / Microsoft Office Access 2003 unless specified otherwise.

  6. #6
    Join Date
    Feb 2004
    Posts
    4
    Thanx Virgo-Libra. I havn't tried that. I will try that now. Thanx once again.

  7. #7
    Join Date
    Dec 2003
    Posts
    268

    Changes

    I actaually came across this same type of problem. I am working with a DB where any change to any field is logged into an event log for the DB. I couldn't make the logevent fire on the after update event because if the value changed from x to y then from y to x it would create some redundancy in the information logged.

    Luckily this was during the development stage and am using RSs and Ctl values to make the event logging easier.

    My question is that if this is a pre-existing form, why don't you just turn it into a unbound form and manually feed the RS information into the form. if any other forms reference this form as long at the Ctl names don't change it shouldn't have any problems.

  8. #8
    Join Date
    Jan 2004
    Posts
    184

    Re: Changes

    If you are using DAO and recordsets why don't you call Recordset.CancelUpdate before closing the form? Also there is a DAO Data control that does that located here:

    http://www.scirocco.ca/downloads.html
    In abundance of water only the fool is thirsty. Bob Marley.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •