Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2007
    Posts
    74

    Unanswered: Prompt users to confirm file delete

    In my db users can embed files such as Word docs or Excel files. This is done by using a button which prompts them to select a file and then the file is programaticaly embedded using a Bound Object Frame that is bound to an OLE Field in a table.

    When another user pulls up the form they may want to replace the existing embedded file with another file or just delete the file. Since there are instances where this is acceptable and appropriate, I don't want to prevent this action.

    My idea is... When the file is embedded I also write the file owner into the table. So if another user tries to delete the file or attach another file all I have to do is compare the current user to the file owner. If they don't match I pop a msgbox and ask them to confirm that they want to delete the file. If they say yes, I will let them delete the file but I will also write the users id (that deleted the file) and a timestamp in case the file owner wants to know who deleted their file.

    Here is the code i have so far:

    Code:
    Private Sub OLEFile_BeforeUpdate(Cancel As Integer)
    Dim strUID As String, strUserName As String, strFileOwner As String, strFileOwnerName As String, strResponse As String, strsql As String
    Dim RowID As Long
    Dim DeleteTime As String
    Dim db As DAO.Database
     
    Me.tbID.Visible = True
    Me.tbID.SetFocus
    RowID = Me.tbID.Text
    Me.cbNewStatus.SetFocus
    DeleteTime = Now
     
    If RowID <> "" Then
        strUID = CurrentUser
        strUserName = DLookup("[UserName]", "UsysUserType", [UID] = strUID)
        strFileOwner = DLookup("[FileOwner]", "Feedback", [ID] = RowID)
        strFileOwnerName = DLookup("[UserName]", "UsysUserType", [UID] = strFileOwner)
     
        If strFileOwner <> "" Then
            If strUID <> strFileOwner Then
            strResponse = msgbox("This file was attached by " & strUserName & " are you sure you want to delete it?", vbYesNo + vbCritical, "Confirmation Required")
                Select Case strResponse
                    Case Is = vbYes
                        strsql = "Update Feedback SET FileDelete = '" & strUID & "', FileDeleted = '" & DeleteTime & "' WHERE ((ID ='" & RowID & "'));"
                        db.Execute strsql, dbFailOnError
                    Case Is = vbNo
                        Exit Sub
                End Select
            End If
        End If
    End If
     
    End Sub

    The problem is that I get the error that is in the attached picture....

    The error comes when "Me.tbID.SetFocus" is executed. tbID is a textbox hidden on the form, bound to the ID field of the current record that I use to determine which row to use in my sql statement. I have tried using "RowID = Me.tbID.Value" instead and it works as long as there is a value in the field. But if it is a new record and the first action the user is taking is to embed a file, the Before Update event for the OLEFile control will be triggered and there is no rownumber at this point in the table. So it breaks

    I am not sure where to go from here. Any suggestions?
    Attached Thumbnails Attached Thumbnails RT Err 2108.bmp  
    Last edited by Brent Blevins; 08-30-07 at 23:35.

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Brent,

    You neither need to make tbID visible nor set focus to it in order to access its value. Try commenting out those two lines and see what happens.

    Sam

  3. #3
    Join Date
    Jun 2007
    Posts
    74
    That statement is partially true.

    Using "Me.tbID.Value" does not require the focus to be set
    Using "Me.tbID.Text" does require the focus to be set

    Using "Me.tbID.Value" fails when there is no value in the field. This occurs when there is a new row.

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by Brent Blevins
    That statement is partially true.

    Using "Me.tbID.Value" does not require the focus to be set
    Using "Me.tbID.Text" does require the focus to be set

    Using "Me.tbID.Value" fails when there is no value in the field. This occurs when there is a new row.
    Brent,

    Have you tried Nz(Me.tbID, " ") or, if tbID is normally numeric, Nz(Me.tbID, 0)? Access might me more charitable if you leave the final Text/Value property off. Also, I'm not so sure that Nz() works on a control; it may only work on a table variable. You might want to experiment...

    Sam

  5. #5
    Join Date
    Jun 2007
    Posts
    74

    You Rock

    Hey Sam,

    Thanks for the tip. Nz does work perfectly in this scenario.

    Brent Blevins

Posting Permissions

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