Check row data before deletion

[Nishikawa]

Hi,

I am trying to create a form that does this:

When a user wish to delete a row of information, the system will detect if that entry was done by him. If so, deletion is allowed. If not, an error message appears and deletion is canceled.

Anyone done this before?

 

[boblarson]

Do you have a field that stores the name of who entered the record?

 

[Nishikawa]

Yup. The row has the name of the person who added the data and my system has also recorded the user who is currently using the database with "=fosname()"

 

[boblarson]

So, you should be able to include a button to delete and in the code use something like:

If Me!YourFieldWithUserName = fOSUserName Then
   If MsgBox("Are you sure you want to delete this record?", vbQuestion + vbYesNo, "Confirm Deletion") = vbYes Then
      DoCmd.RunCommand acCmdDeleteRecord
   End If
End If

You will probably want to create your own menu and toolbars too to keep them from using the built-in delete functions.

 

[Nishikawa]

can I just disable the "allow deletion" from the form or use the "before confirmdel" event?

My YourFieldWithUserName title is "UserName"
Is Me.UserName same as Me!UserName? The reason that I ask if that when I code Me.UserName, the value is from my "=fosname()"

 

[boblarson]

Actually, yes you could disable deletions as the property of the form so that they can't delete with the others (I forgot about that one - it's late)

Whichever field name that has who created the record is the YourFieldWithUserName and you can just use fOSUserName to find out who the current person is.

 

[Nishikawa]

So is YourFieldWithUserName = Forms!formsname!UserName?

UserName is the name of that field.

 

[boblarson]

Since the code it on the form that the code is on you use the ME keyword:

If Me.UserName = fOSUserName Then...

 

[Nishikawa]

Sorry man,

It is not working and I am still trying to find out why.
The Me.UserName does not give me the value of the field in the row that I have selected.

 

[boblarson]

Any chance you can upload the db here so I can take a look?

If you haven't seen this

http://www.access-programmers.co.uk/forums/showthread.php?goto=newpost&t=140587

read it before trying to upload

 

[Nishikawa]

ok. I try to upload it

One thing I notice is that if I put "Me.UserName" on the onclick event of the "Username" field, it show the "UserName" value of that particular row. but If I select the entire row and put "Me.Username" in the before confirmdel event of the form, it does that show the username value in that row.

 

[Nishikawa]

Attached is the database. Thanks for your help Bob! If you go to the comments form, select the second or third row and click delete, you will see that it pops up the username of the first row.

If you able to solve this, then I am able to continue to add in the delete confirm code.

Thanks again!

 

[boblarson]

Okay, simple fix. The problem lay where you neglected to let me know that the user name was on the subform and not on the form where the button was.

So, change the current code to:

MsgBox Me.SPFmCallRestriction2.Form.UserName

and you should be on your way.

 

[Nishikawa]

Thanks Bob!

Sorry to give you so much trouble. To think that you have been teaching me for this entire year and I still make such silly mistake. sigh

 

[boblarson]

Thanks Bob!

Sorry to give you so much trouble. To think that you have been teaching me for this entire year and I still make such silly mistake. sigh

No problem, and that's why I figured I'd better just take a look as it is easy sometimes to just overlook what may be, under other circumstances, obvious. Sometimes the focus is so intense on something that something actually real simple can escape notice. So, no worries and I'm glad we are able to get you moving in the right direction. Good luck with it all and AWF, and ALL of our members, are here to help.

 

[Nishikawa]

sigh.... it stil cannot work! It is deleting the wrong record....

the code is I used it:

If Me.SPFmCallRestriction2.Form.UserName <> User.UserName Then
MsgBox "You can only delete your own entries"
Else
If MsgBox("Are you sure you want to delete this record?", vbQuestion + vbYesNo, "Confirm Deletion") = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
End If
End If

It did not delete the record I selected by the first record.

 

[boblarson]

Okay, now - remember you want to compare to fOSUserName (just let it run the function as it will return the person who is currently doing this. You don't need to retrieve it from any table (also be sure to use the SetWarnings True code after the delete because I'm guessing now that you don't have any warnings, including save messages until you do):

If Me.SPFmCallRestriction2.Form.UserName <> fOSUserName Then
    MsgBox "You can only delete your own entries"
Else
   If MsgBox("Are you sure you want to delete this record?", vbQuestion + vbYesNo, "Confirm Deletion") = vbYes Then
      DoCmd.SetWarnings False
      DoCmd.RunCommand acCmdDeleteRecord
      [color=red]DoCmd.SetWarnings True[/color]
   End If
End If

Be sure to include an error handler and the very first thing in the error handler should be DoCmd.SetWarnings True otherwise if it errors out you won't get any warnings for anything.

And, since you didn't have it in there now, you should put this in a standard module:

Public Function ResetWarnings()
   DoCmd.SetWarnings True
End Function

Then put your cursor in the function and click the RUN button on the debug toolbar. That should reset them if you have accidentally turned them off.

 

[Nishikawa]

It is not about that. Everything is the code works fine. The problem is that Access is deleting the wrong record.

It is deleting the first record shown in the form and not the record highlighted by the record selector.

 

[boblarson]

Okay, I've done some changes (and uploading so you can view everything). There may be other ways, or better, to do this but what I did is essentially this:

1. I added a text box on the main form to hold the ID number of the record selected on the subform.

2. I used the On Current event of the subform to set the value of the text box on the main form to the currently selected record.

3. Then, In the delete code I used a delete query with the text box on the main form being the criteria.

Take a look and see if this will help.