Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    80

    Unanswered: Custom error message?

    How can I create a custom error message box to replace the one that appears by default if a primary key is entered into fields more than twice? I need one to say 'This table has already been booked', and would prefer not to have to use the default Access message.

  2. #2
    Join Date
    Jul 2003
    Posts
    73
    One way of doing this would be to put code in the Form_BeforeInsert event. Check for a duplicate key in the database - show an error - and set Cancel to true (i.e. don't allow update). You may want to do the same on an BeforeUpdate event if you allow the user to update primary key fields.

    Just one possible solution. Any better ones out there?
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  3. #3
    Join Date
    May 2004
    Posts
    80

    Thanks for reply...just not sure how...:D

    Thanks for your response....I'm just not entirely sure how to implement that...I'm assuming it's to do with SQL and I have little experience with that. If you could just tell me where to put it or especially exactly what code to insert and where, it would be much appreciated.

    Thanks.

  4. #4
    Join Date
    Jul 2003
    Posts
    73
    Depends on how you're doing this. Are you entering your data in a form? If you're just using Datasheet View of the table, I don't think you can surpress the Access Error Message.

    If you've got User Input via a form - follow the following:

    1. Open the form in Design Mode.
    2. Edit > Select Form (Ctrl + R)
    3. View > Properties
    4. Click the "Event" Tab
    5. Click the "Before Update" field
    6. Click the Build Button ("...")
    7. Select Code Builder

    This will bring up the following module (add the code as I've added - obviously replacing my placeholders with your actual table etc):

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim rsPKCheck as Recordset
    
      ' Check if Primary Key already in use
      set rsPKCheck = currentDb.OpenRecordset("SELECT * FROM tableName WHERE primaryKeyField = " + primaryKeyFormField.value)
    
      IF NOT rsPKCheck.EOF
        Msgbox "Custom Error Message", vbExclaimation + vbOkOnly, "Error Title"
        Cancel = True
      End If
    
      rsPKCheck.Close
    End Sub
    Last edited by joeldixon66; 05-14-04 at 02:20.
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  5. #5
    Join Date
    May 2004
    Posts
    80

    Thanks

    Thanks very much for that, sounds like it will work. I will be using a form layout for nearly all of my data and so that solution sounds perfect.

    - Grace

Posting Permissions

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