Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Feb 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    2 Events in 'before update' (Access 2002)

    I have a database where I need to remind my users to save they're info, but
    also need to have an invisible time/user/date stamp. Below is what I have so
    far, only I'm getting the following error:
    The LinkMasterFields property setting has produced this error: 'Ambiguous
    name detected: Form_BeforeUpdate'

    I'm really new to VBA so I need this spelled out to me (please):
    I have three questions:

    1) If I want to log the last modified information (ie: date/ time etc.),
    shouldn't it be in the After Update of the form instead of the Before Update?

    2) What am I doing wrong with the code to get this error?

    3) Is there a way I could have my code to prompt the user to save changes, and
    if the user said "yes" then it would save the changes and create the time
    stamp.. if the user said "no", then it would just undo the changes.
    Can this be done? (This would be the preferred method) If so, how?

    Any help would be greatly appreciated.

    barkaroo


    Private Sub Form_BeforeUpdate(Cancel As Integer)

    ' This procedure checks to see if the data on the form has
    ' changed. If the data has changed, the procedure prompts the
    ' user to continue the save operation or cancel it. Then the
    ' action that triggered the BeforeUpdate event is completed.

    On Error GoTo Err_BeforeUpdate

    ' The Dirty property is True if the record has been changed.
    If Me.Dirty Then
    ' Prompt to confirm the save operation.
    If MsgBox("Do you want to save?", vbYesNo + vbQuestion, _
    "Save Record") = vbNo Then
    Me.Undo
    End If
    End If

    Exit_BeforeUpdate:
    Exit Sub

    Err_BeforeUpdate:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_BeforeUpdate
    End Sub
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    On Error GoTo BeforeUpdate_Err

    ' Set bound controls to system date, time, and user.
    DateModified = Date
    TimeModified = Time()

    BeforeUpdate_End:
    Exit Sub

    BeforeUpdate_Err:
    MsgBox Err.Description, vbCritical & vbOKOnly, _
    "Error Number " & Err.Number & " Occurred"
    Resume BeforeUpdate_End
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: 2 Events in 'before update' (Access 2002)

    Welcome to Woody's Lounge!

    The Before Update event is the one to use here, but you can have only one Form_BeforeUpdate procedure for a form, not two as in the code you posted. So you must merge the two procedures into one. It isn't necessary to test if the form is 'Dirty': the Before Update event only occurs if the record has been modified. When you undo the changes, you must also set the Cancel argument of the event procedure to True, to let Access know that the record shouldn't be saved. Here is a version that merges the two procedures:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    ' This procedure checks to see if the data on the form has
    ' changed. If the data has changed, the procedure prompts the
    ' user to continue the save operation or cancel it. Then the
    ' action that triggered the BeforeUpdate event is completed.

    On Error GoTo Err_BeforeUpdate

    ' Prompt to confirm the save operation.
    If MsgBox("Do you want to save?", vbYesNo + vbQuestion, "Save Record") = vbNo Then
    Me.Undo
    Cancel = True
    Else
    Me.DateModified = Date
    Me.TimeModified = Time
    End If

    Exit_BeforeUpdate:
    Exit Sub

    Err_BeforeUpdate:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_BeforeUpdate
    End Sub

  3. #3
    New Lounger
    Join Date
    Feb 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2 Events in 'before update' (Access 2002)

    Wow Hans Thanks! That's exactly what I was looking for. Just one more thing... is there any way to put the "user" in there too? I have the field set up in my table as "User".

    I really appreciate your help.
    Thanks again.
    Barkaroo.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: 2 Events in 'before update' (Access 2002)

    If you want to store the network login name, you can put the following code in a standard module:

    First, put this declaration at the top of a module, after Option Compare etc., but before all Subs and Functions:

    Private Declare Function WNetGetUserA Lib "mpr.dll" _
    (ByVal lpszLocalName As String, ByVal lpszUserName As String, lpcchBuffer As Long) As Long

    Put the following function somewhere below it:

    Public Function GetNetUser() As String
    Dim lpUserName As String, lpnLength As Long, lResult As Long
    'Create a buffer
    lpUserName = String(256, Chr$(0))
    'Get the network user
    lResult = WNetGetUserA(vbNullString, lpUserName, 256)
    If lResult = 0 Then
    GetNetUser = Left$(lpUserName, InStr(1, lpUserName, Chr$(0)) - 1)
    Else
    GetNetUser = "-unknown-"
    End If
    End Function

    In the Before Update event procedure of your form, add the following line below Me.TimeModified = Time:

    Me.User = GetNetUser

    If you have applied user-level security to your database, i.e. if users have to log into the database with a username and password, you can use CurrentUser instead of GetNetUser. (Without user-level security, CurrentUser will always return Admin).

  5. #5
    New Lounger
    Join Date
    Feb 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2 Events in 'before update' (Access 2002)

    Hans,

    Thanks a million! You're awesome!

Posting Permissions

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