Running macro in subform returns error

[Zaxxon]

I have a form and displayed in it is a subform. I created a macro to put a datestamp on any record that is changed.

It works fine for records edited using the main form. When I try to run the same macro in the subform I get error 2950; ActionName: SetValue, Arguments: [Date Modified], Date()

Here's my code:

Option Compare Database

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Resp = MsgBox("Do you wish to save the new/edited data?", vbYesNo + vbQuestion, "Unsaved Data")
    If Resp = vbNo Then
       Me.Undo
    Else
       DoCmd.RunMacro "Last Modified"
    End If
End Sub

I have a listbox on the main form so the user can search for records. Ideally it would be nice if the user was asked whether to save or not when they go to change records or close the form. At the moment, each time the user switches between the form and subform, they are prompted to save (if any data is changed).

 

[georgedwilkinson]

My guess is that your macro named "Last Modified" is specific to the data on the main form. You'll need to run code specific to the subform if you want it to work there.

Odd that you would do this in a macro. As such, I can't really help you. But since you're calling it from VB, it seems ironic to me.

 

[Zaxxon]

Ah I think you got it. The main form shows data from Table1 and the subform shows data from Table2. The macro refers to [DateModified] which is a field in Table1. I just assumed it would be ok since the tables were linked. I'll try changing it when I get back to work and post an update.

Also, I chose to do it this way because I didn't know another way to do it :/ I found this on creating the macro: http://office.microsoft.com/en-us/access/HA010345351033.aspx
Then I asked on this forum how to incorporate it and someone provided VB code for me.

 

[georgedwilkinson]

Best not to mix macros and VB. It could cause you some maintenance problems. Here is how you should do it in VBA:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Resp = MsgBox("Do you wish to save the new/edited data?", vbYesNo + vbQuestion, "Unsaved Data")
    If Resp = vbNo Then
       Me.Undo
[COLOR=red]Cancel = True 'You should look into doing this, too.
[/COLOR]    Else
       [COLOR=red]Me.[ControlOnTheFormBoundToYourTimestampColumn] = Now()[/COLOR]
    End If
End Sub