Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2002
    Posts
    154

    Question Unanswered: Catching table name in Delete Error

    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.

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    If you are trapping the Errors anyway then you could use:

    Code:
    'If the Error Number is 3200 then.....
    If Err.Number = 3200 then
       'Declare Variables.
       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"
    End if
    .
    Last edited by CyberLynx; 11-20-07 at 21:44.
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Remember to add the lines
    Code:
    On Error GoTo ErrorHandler
    
      <your code>
    
    ErrorHandler:
      <CyberLynx' code>
    George
    Home | Blog

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ...or maybe, just maybe don't allow users direct access to the the tables or design mode.. unless you give your users their own private copy that they can tinker with to whier hearts content

    I know it doesn't help AFTER the event, but its a lesson that we should only have to learn once

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    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!!??

    MTB

Posting Permissions

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