I have a form attached to a table that allows users to enter client names and addys and such. Some wiseguy, I can't get anyone to fess up about it, has tried to be helpful by deleting all the fields in Clients that he or she thinks we don't use anymore--probably thinking that this is the same as deleting the record. I have had to made the client name required with allow zero length string no to stop this but a couple of things would be nice. This is what I figure happened--the person tried to erase one of these parent table entries and it gave a message like: "The record cannot be deleted or changed because table 'child table name' includes related records." This is form error 3200, I already know that. Does anyone know of a way to capture the child table's name short of parsing the error string for stuff between two single quotes so that I can tailor the error message to something like: "You cannot delete this client because there are quotes by this client. Please contact the database administrator to solve this problem." There are several possibilities for child table name that could have this ClientID in them so I wanted to just be nice and explain to whoever is doing this in the future why this can't be done by them and it seems to me that giving up table names to users is not a good idea. I thank you in advance for your time and your responses.
If you are trapping the Errors anyway then you could use:
'If the Error Number is 3200 then.....
If Err.Number = 3200 then
Dim ErrMsg As String
Dim StartPos as Integer
Dim TableName As String
'Place the Error Message into the ErrMsg String variable.
ErrMSg = Err.Description
'Get the starting possition of where the Table name may be
'in the character string of the ErrMsg variable. This wil be
'used to parse out the Table name from the Error Message.
StartPos = (InStr(1, ErrMsg, "'") + 1)
'Parse out the Table name from the Error Message now held
'in the ErrMsg variable and place it into the TableName variable.
TableName = Mid$(ErrMsg, StartPos, InStr(StartPos, ErrMsg, "'") - StartPos)
'Fire off a Message to User.
MsgBox = "You cannot delete this client because there are quotes" & vbCr & _
"by this client located in a Table named ' & TableName & "'." vbCr & _
"Please contact the Database Administrator to solve this" & vbCr & _
"problem.", vbExclamation, "Deletion Not Permitted"
Last edited by CyberLynx; 11-20-07 at 20:44.
Self Taught In ALL Environments.....And It Shows!
I usually look (DLookup() or DCount()) to see if the 'many' table(s) has related record, and, if so, write my own (very polite!) message and cancel the delete. This way you can say what you like in the message!!??