CODEWITHSUNDEEP
ms-accessvbams-access-2010
bgmCoder
bgmCoder

Reputation: 6370

Action on Form Save

We have an Access 2010 database that acts as a front-end to a MS SQL database. When we edit data in the form there is a procedure that needs to run in order to properly save certain data back to SQL.

Our DB programmer added a "Save Button" to do this. But that causes another problem - there are multiple ways in Access by which to save a form -

Is there any way to attach a procedure the actual save action so that no matter how a person moves to a next form that the procedure gets run?

[update]

Here is the code behind the scenes: the first sub is attached to the "Save" Button. Of course, the second is attached to the form BeforeUpdate.

Private Sub SaveRecord_Click()
'From NAME form
Form_BeforeUpdate False
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
'used by NAME form
    [Last_Update] = Now
    '*********************
    Save_Record
    '*********************
    MName_ID = Me.Name_ID
    Me.Undo
    Cancel = True
    If Not IsNull(MName_ID) Then
        Jump_to_Name_ID MName_ID, True
    Else
    End If
End Sub

I guess I just don't understand what the button is for.

Upvotes: 0

Views: 1611

Answers (2)

bgmCoder
bgmCoder

Reputation: 6370

Well, I was not able to use Mr. Sommer's solution because it was not possible to add an event handler to a linked table on account of their being read-only. But, I did work out a simple procedure that seems to work well enough.

So, I was actually already using the BeforeUpdate event, so I'm catching the right event here - this is the event that traps the save, whether it be on change of navigation or the save-record bar on the left. However, there were a few issues that resulted from using Application.Echo False to keep Access from posting back the old data to the control whilst the Me.Undo takes place.

So we use cancel=true to prevent the BeforeUpdate event from doing its normal processing, and we use Me.Undo to prevent Access from trying to save data to the linked tables.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Cancel = True

    [Last_Update] = Now
    '*********************
    Save_Record '-->This will save the data back to SQL
    '*********************
    MName_ID = Me.Name_ID
    Application.Echo False  'don't show the undo in the controls
    Me.Undo

    If Not IsNull(MName_ID) Then    'used for the navigation buttons
        Jump_to_Name_ID MName_ID, True
    Else
    End If

    Application.Echo True   'turn the control refresh back on
    Me.Repaint
End Sub

Upvotes: 0

Jan Sommer
Jan Sommer

Reputation: 3808

So I installed an MS Access 2010 trial and finally managed to figure out a way to solve your problem. It includes data macros and a hidden gem that took me quite a while to find.

Here's how you run VBA when a table changes:

  1. Create an ordinary module (haven't tried class modules) with public functions:

Module name: EventHandlers

Public Function InsertEvent(ByVal id As Integer)
    MsgBox "inserted: " + CStr(id)
End Function

  1. Open the table that, when modified, should run VBA and go to "Table" in the ribbon.

  2. Click on "After Insert"

  3. In the "Add New Action"-select box, choose SetLocalVar (or select it from the Action Catalog).

  4. In the Name-field, insert the name of the module (in this case, EventHandlers, as we created earlier)

  5. In the Expression-field, write the name of the function: InsertEvent([id]) (where [id] is an actual column in the table you're adding a data macro for)

  6. Save and close

Whenever something is inserted to the table, a messagebox will be shown with the id.

You could do the same with the update event. The function could be something like this:

Public Function UpdateEvent(ByVal oldValue As String, ByVal newValue As String)
    MsgBox oldValue + " changed to: " + newValue
End Function

and the data macro would be

Action: SetLocalVar

Name: EventHandlers

Expression: UpdateEvent([Old].[your_column_name];[your_column_name])

Note: Executing DoCmd.RunSQL with update, insert or delete will execute data macros and THEN ask the user if he or she actually WANTS to update/insert/delete the row. If the user clicks cancel, nothing is changed but your data macro executed anyway. If you haven't already, you should probably disable this check before implementing data macros.

Upvotes: 3

Related Questions