NEWBIE Q - Amending Standard Messages

[KellyGroom]

HelloI have a database that has a table that contains addresses which can be associated with various individuals, i.e. 1 address can be shared by many individuals. I have created a form that provides a user with a function to delete addresses that are no longer used. However, as expected when a user tries to delete an address that is still associated to a user, the standard message below appears.I would like to amend the standard message to make it more user friendly so that the user understands what it means and if possible to also remove the option of being able to delete the record until any associations have been removed first?I have managed to create a message however, it appears after this standard message. I have jotted below my VB on click script but would be very grateful if somebody could advise me how to prevent the standard message from appearing so that the user only receives my intended message. Private Sub delete_Click()On Error GoTo Err_delete_Click DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70 DoCmd.Close DoCmd.CloseExit_delete_Click: Exit SubErr_delete_Click: MsgBox "This address cannot be deleted as it has people associated with it. You must first select another address for the individual before you can return to this menu and delete the address." Resume Exit_delete_Click End SubIf anyone can help or point me in the right direction I would be very grateful.Thank you.

 

[evanscamman]

Try the Form's OnError event. Not sure if it even triggers with this error message, but if it does you should be able to cancel it, and place your own error message there.

Evan

 

[KellyGroom]

Thank you Evan for your suggestion. I have tried it but it doesn't seem to make a difference, unless I am missing something??

Kelly

 

[evanscamman]

First of all, is the event triggering?

Go to design view of your form. Look in properties, and see if the event is listed as a Event Procedure.

Click on the ... to see the code behind the event.
If you have made it that far, toggle a breakpoint in the procedure.

Then run your form and make the error message pop up.
If it pops up the VBA window, this means that the event triggered.

Then you just need to cancel the error message, and substitute your own.
If the Event doesn't fire, then either this is a bad solution, or you did something wrong.

Perhaps not as helpful as I could be, but I'm away home.

Evan

 

[BrettM]

This might be simplistic but...

Private Sub delete_Click()
On Error GoTo Err_delete_Click
    ' test yourself to see if it is associated with a user
    If test = no Then
        DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
        DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
        DoCmd.Close DoCmd.Close
    Else
        MsgBox "This address cannot be deleted as it has people associated with it. You must first select another address for the individual before you can return to this menu and delete the address"
    End If
Exit_delete_Click:
    Exit Sub

Regards Brett

 

[Rich]

It's worth remembering that DoMenuItem has been obsolete for years and should be replaced with one of the RunCommand constants

 

[KellyGroom]

Hello againThank you all for trying to help me. I am still struggling to get this to work for me, despite trying your suggestions and code. It may be that I am doing this wrong as I really am a complete VB newbie.There are no event procedures at all for the form. What I have is a form based on my Address Table which has a combo box to allow the user to select the address they wish to modify or delete. I then have a command button to delete the selected record that they choose.The original bit of VB I posted works fine on the command button's on click event to bring up the message I desire however I still receive the same standard message prior to this but I can't see where it is pulling this message from in order for me to change it.I tried replacing my code with Brett's (many thanks) but received a compile error which threw me. Tried making an few amendments but to no avail.I tried amending the DoMenuItem to the relevant RunCommands (acCmdDelete Record and acCmdSelect Record) but I must be doing something incorrectly as I recieved a compile, invalid argument error, so may need to look into this a bit more.If anyone can shed any further light after my essay update I would be very grateful.Many thanks again.Kelly

 

[MSAccessRookie]

You have not posted your code as of this time, but the warning message in the JPG that you posted looks a lot like the type you get when you append or delete records. I usually use a SetWarnings Function call when I want to suppress those, perhaps it will work for this message as well.

[B]Somewhere towards the Beginning of the Function:[/B]
 
    [COLOR=blue][B]DoCmd.SetWarnings False ' To Set Warnings Off[/B][/COLOR]
 
[B]Somewhere towards the End of the Function:[/B]
 
    [COLOR=blue][B]DoCmd.SetWarnings True ' To Set Warnings On[/B][/COLOR]

 

[KellyGroom]

I have now managed to replace the DoMenuItem for the Run Commands. )Aplogies for forgetting to add my code. Here it is .... Option Compare DatabasePrivate Sub Combo19_BeforeUpdate(Cancel As Integer)_________________________________________________________________End SubPrivate Sub delete_Click()On Error GoTo Err_delete_Click RunCommand acCmdSelectRecord RunCommand acCmdDeleteRecord DoCmd.CloseExit_delete_Click: Exit Sub Err_delete_Click: MsgBox "This Address cannot be deleted as it has a User associated with it. You must first remove this address from any User's that use it before you can return to this menu and delete the Address." Resume Exit_delete_Click: End SubThank you for the warning suggestion. I have tried the code you posted in numerous places of my code but have found that either it makes no difference or it closes my form completely with no warnings or questions... obviously.Thanks again, Kelly

 

[MSAccessRookie]

I have now managed to replace the DoMenuItem for the Run Commands. )Aplogies for forgetting to add my code. Here it is .... Option Compare DatabasePrivate Sub Combo19_BeforeUpdate(Cancel As Integer)_________________________________________________________________End SubPrivate Sub delete_Click()On Error GoTo Err_delete_Click RunCommand acCmdSelectRecord RunCommand acCmdDeleteRecord DoCmd.CloseExit_delete_Click: Exit Sub Err_delete_Click: MsgBox "This Address cannot be deleted as it has a User associated with it. You must first remove this address from any User's that use it before you can return to this menu and delete the Address." Resume Exit_delete_Click: End SubThank you for the warning suggestion. I have tried the code you posted in numerous places of my code but have found that either it makes no difference or it closes my form completely with no warnings or questions... obviously.Thanks again, Kelly

I have reformatted your code to make it readable. To do this in the future, you can use the [ code ] and [ /code ] block markers (without the spaces) to surround your code. Anything inside the Block will not be reformatted when you post it. Add the code marked in Red and see what happens.

[FONT=Times New Roman][SIZE=3]Private Sub delete_Click()[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]   On Error GoTo Err_delete_Click [/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=red][B]   DoCmd.SetWarning False[/B][/COLOR][/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]   RunCommand acCmdSelectRecord [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   RunCommand acCmdDeleteRecord [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   DoCmd.Close[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Exit_delete_Click: [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=red][B]   [FONT=Times New Roman][SIZE=3]DoCmd.SetWarning True[/SIZE][/FONT][/B][/COLOR]
    Exit Sub [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Err_delete_Click: [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   MsgBox "This Address cannot be deleted as it has a User associated with it. You must first remove this address from any User's that use it before you can return to this menu and delete the Address." [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   Resume Exit_delete_Click: [/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]End Sub[/SIZE][/FONT]

 

[KellyGroom]

I added the code in red but when I try out the command button on my form it jumps straight back into VB with a Compile error: Method or data member not found. Set Warning is highlighted on the DoCmd.Set Warning False line.Thank you for the tip also.

 

[KellyGroom]

Ignore my last message, I just needed to be the letter 's' at the end of Set Warning.With the SetWarning commands it now deletes the selected record and closes the form. Although the standard Access message no longer appears, nor does my tailored message and it also now takes away the prevention of them contining to delete a record that it is still associated with a user.Thank you though for the suggestion, it was definitely worth a try and I have certainly learnt something new. Possibly back to the drawing board, unless there is a work around.

 

[MSAccessRookie]

Don't give up quite yet. The condition might exist because the Warning/Error no longer occurs when the Warnings are off. Try letting the error occur before you shut the message down. Moving the Set Statements to the Opposite side of the MsgBox may or may not prove to be more to your liking.

 

[Rich]

You can of course post your own message to the user something like

Private Sub Cmd11_Click()
On Error GoTo Err_Cmd11_Click
Dim rs As Recordset
Dim Msg, Style, Title, Response, MyString
Set rs = Me.RecordsetClone
With rs
.FindFirst "[fldAudID] = " & txtAuditID.Value ' find the current in recordset
.MoveNext 'move to the next record
If Not .EOF Then
Msg = "If you delete this record the following " & " " & .RecordCount - .AbsolutePosition & " " & "records will also be deleted are you sure you wish to continue?"
Style = vbYesNo + vbExclamation + vbDefaultButton1
Title = "Confirm Multiple Record Deletion!"
Response = MsgBox(Msg, Style, Title) ' if not at end of file
If Response = vbNo Then
Exit Sub
Else: Dim stDocName As String

DoCmd.RunSQL "DELETE tbAccAudits.fldAudId, tbAccAudits.fldAuditDate, tbAccAudits.fldAuditAmnt, tbAccAudits.AccountTypeID, tbAccAudits.CdID FROM tbAccAudits WHERE (((tbAccAudits.fldAudId)>=[Forms]![frmPerAudits]![txtAuditID]) AND ((tbAccAudits.AccountTypeID)=[Forms]![frmPerAudits]![AccountTypeID]));"

DoCmd.Close
End If

Else
Msg = "You are about to delete this record are you sure you wish to continue?"
Style = vbYesNo + vbExclamation + vbDefaultButton1
Title = "Confirm Record Deletion"
Response = MsgBox(Msg, Style, Title)
If Response = vbNo Then
Exit Sub
Else
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE tbAccAudits.fldAudId, tbAccAudits.fldAuditDate, tbAccAudits.fldAuditAmnt, tbAccAudits.AccountTypeID, tbAccAudits.CdID FROM tbAccAudits WHERE (((tbAccAudits.fldAudId)>=[Forms]![frmPerAudits]![txtAuditID]) AND ((tbAccAudits.AccountTypeID)=[Forms]![frmPerAudits]![AccountTypeID]));"
DoCmd.SetWarnings True
DoCmd.Close
End If
End If
.Close

End With
Exit_Cmd11_Click:
Exit Sub
Err_Cmd11_Click:
MsgBox Err.Description
Resume Exit_Cmd11_Click

End Sub

 

[BrettM]

Kelly,

My suggestion was not meant to be a literal copy - only a suggestion, and it still stands. If, as you say, you are happy with the way your code works but just want to do away with the system error then...

Private Sub delete_Click()
On Error GoTo Err_delete_Click
 
   [COLOR=green]' test for yourself here to see if the address is associated with a user[/COLOR]
 
   If "your test result" = False Then [COLOR=green]' Not Associated! Replace "your test result"  with your own figures[/COLOR]
       RunCommand acCmdSelectRecord 
       RunCommand acCmdDeleteRecord 
       DoCmd.Close
   Else [COLOR=green]' IS associated so error message[/COLOR]
        MsgBox "This address cannot be deleted as it has people associated with it. You must first select another address for the individual before you can return to this menu and delete the address"
    End If
Exit_delete_Click:
    Exit Sub

This will only delete if the address is not associated with a user so that system error will never appear. AND it is short. The only issue is working out your own test.

Regards Brett

 

[jdraw]

Kelly,

You should do some searching on database integrity. Specifically, referential integrity and foreign keys. What you are asking can be accomplished by setting up a referential integrity constraint between tables.

http://databases.about.com/cs/administration/g/refintegrity.htm is one I found but there will be many others. Good luck.

 

[editolis]

The right direction is the following:

First you have to DROP INDEXES.

Try this Code before you delete:
========================================
Dim str
str = "DROP INDEX [YourField] ON [yourTable]"
CurrentDb.Execute str, dbFailOnError
========================================

The [YourField] field is your primary key of your table.

The STANDARD ACCESS Message now is disappeared.

Then follow the delete method.

 

[KellyGroom]

Thank you all for your replies. Sorry for not responding sooner but have been side tracked. I will be looking again at this problem of mine shortly. Thanks again.

 

[gemma-the-husky]

its

docmd.setwarnings with an S on the end.
intellisense shoiuld guide you on this, as you enter the command, but you wouldnt see that if you just cut and paste.
I dont think this is the problem anyway, though.

Also you dont need the selectrecord line, i dont think, as you will already have sleected the line(s) you want to delete. Note, I am not showing code, just explaining what is going on. Incidentally if you sleect multiple lines, access will run your code for each line - so some may delete, and others not

2 alternatives

1 - test the deletion with your own check

in this case, the method you want is

"before delete confirm"

in there you need to examine the record you want to delete - if you decide you dont want to delete the record, then cancel the event

the "on delete" event doesnt give you the cancel facility - it just runs the delete - so you still get the error message if the delete fails


2 - use the "on error " event

Alternatively, you can just try the delete anyway, and get the error message, which is what you are doing at present. (note that you are getting the error message because you have set relational integrity up - which may be deliberate, or accidental)

in this case you need to use the "on error" event to intercept the error number, and replace the error message with your own. This needs a bit of work, as you need to know the error number, but access doesnt tell you the error number as standard. This is useful if you dont want to allow multiple deletions


Summary

Note, that these are different techniques, but they both require a reasonable understanding of what access is doing, to manage the behaviour as you want.

Personally, I tend to validate things first, so I would be inclined to use the first method.
There is no reason not to use both methods as belt and braces, but its a matter of taste.

You can even do other things, like not allowing direct deletion of items (just cancel the delete event in the "before delete confirm") - and instead give the user a command called "delete" to do exactly what you want. Probably other things you could do as well.

 

[KellyGroom]

Thank you all for you help! I have now managed to resolve my problem by looking more closer at my relationships and how delete changes cascade works. By removing the 'delete cascading records' option between only the relevant linked tables my rewritten error message now appears when any associations are found and prevents the user from deleting the record.

With all your comments I have managed to learnt a few things about basic coding in access and database design, so thank you all.