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:
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
RowID = Me.tbID.Text
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
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?
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.
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...