Results 1 to 5 of 5
  1. #1
    Join Date
    May 2002
    Posts
    157

    Unanswered: Multiple error messages being given

    Hi there,
    I am using Access 2000.

    I have Supervisor Table which has a combination index where no duplicates are allowed.

    This means that when a record is created with the SiteID field and the CompanyID field and the SupervisorName field being the same as a previous record, then the current record cannot save and error messages are given.

    I have created a Supervisor Details Form which allows entry of details into the Supervisor Table.

    On the Supervisor Details Form I have created the following code based on the ON ERROR Event of the form's properties.

    ********************************
    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    On Error GoTo Form_Error_Err

    Select Case MsgBox("The record you are trying to create will create a duplicate record." _
    & vbCrLf & "" _
    & vbCrLf & "Records cannot be created where the SiteID, CompanyID and Supervisor Full Name is identical to a previously created record." _
    & vbCrLf & "" _
    & vbCrLf & "Click on the YES button below to clear the record then press the ESC key (escape key on your keyboard) to cancel all messages." _
    & vbCrLf & "" _
    & vbCrLf & "OR" _
    & vbCrLf & "" _
    & vbCrLf & "Click on the NO button below to clear only the Supervisor Full Name ready for input of new Supervisor Full Name." _
    & vbCrLf & " " _
    & vbCrLf & "" _
    , vbYesNo Or vbExclamation Or vbDefaultButton1, "DUPLICATE RECORD NOT ALLOWED")

    Case vbYes
    DoCmd****nCommand acCmdUndo
    Me![SupervisorID].SetFocus

    Case vbNo
    Forms![usysbeEmployer4SiteSupervisors].[SupervisorsFullName].Value = Null
    Me![SupervisorsFullName].SetFocus


    End Select

    Form_Error_Exit:
    Exit Sub

    Form_Error_Err:
    MsgBox Error$

    Resume Form_Error_Exit

    End Sub
    ********************************

    When I test the form and deliberately cause an error the following occurs:

    1. On clicking the close window (X) I get the error message I created above.
    2. The actions I created under Case YES or Case NO occur depending on which button I click.
    3. Another (Access generated) message appears:
    "The changes you requested to the table were not
    successful because they would create duplicate values
    in the index, primary key or relationship. ....................."
    I have no choice other than to click the OK button for this message.
    4. Then the error message I created above, reappears.
    Again I can choose either YES or NO and the actions will occur depending
    on which button I click.
    5. Another (Access generated) message appears:
    "You cant save this record at this time". Microsoft Access may have
    encountered an error whilst trying to save this record. ..................
    I am given a YES or NO button to click on.

    Is there some way I can stop all these additional error messages from occuring? I have tried all sorts of things and nothing I am doing seems to solve this problem and in some cases makes more errors of the same ilk generate.

    Hoping you can give me some advise.

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    the problem isn't your code but your sequence of events - the ms error messages occur - and will always occur - because the field is indexed/no duplicates and the user is attempting to leave the record with a duplicate....thus you must prevent that from occuring.

    consider a look-ahead method that seeks a duplicate in the beforeupdate event - that generates a message and assures correction of the situation before leaving the record...thus the ms error will never get triggered....

  3. #3
    Join Date
    May 2002
    Posts
    157

    Further help required

    I understand what you are suggesting but just don't know how to carry through with the code.

    So I need to write some code that is put in to the SupervisorForm in the beforeupdate event which looks up my Supervisor Table to check to see if the following fields (in combination) has a duplicate entry to the data that has been entered in the current form.
    -SiteID
    -CompanyID
    -SupervisorName

    In combination means that when a record is created with the SiteID field and the CompanyID field and the SupervisorName field being the same as a previous record, then the current record cannot save and error messages are given.

    I can then use my error code to trap if a duplicate record is about to be created and if so then cancel the update.

    The only problem is that I am not sure on how to write the code to do this.

    Can anyone please help with this?

    Thanks heaps

  4. #4
    Join Date
    Oct 2009
    Posts
    340
    After those fields have been entered (in the AfterUpdate event of the 3rd field entered) - trigger a query of the table - with those values as the criteria of the query.

    If the query returns a record - then these values are duplicates.
    If the query returns no record - then these values are not duplicates.

    Count the records of the query's record set to determine which is the answer.
    You will want to research the DCount method for this task.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    There's a few different approaches to this problem (I've faced this problem also):

    1. Control the value of the required fields using the AfterUpdate event of each required field (or don't set any of the fields to null but instead to default records (ie. Blank Name).)
    ie.
    Forms![usysbeEmployer4SiteSupervisors].[SupervisorsFullName].Value = "Johnson".

    Otherwise, in the Afterupdate event, you would test if the value isnull.
    ex:
    sub procedure SupervisorsFullName_AfterUpdate()
    if isnull(me!SupervisorsFullName) then
    msgbox "Value cannot be blank."
    me!SupervisorsFullName.undo
    'or me!SupervisorsFullName = "Blank Name"
    end if
    pro's = quick, easy fix. con's = you have a 'dummy' record so to speak in the database and/or you need to control each AfterUpdate event of each required field.

    2. Design your form so it doesn't refresh (also change your form's record cycle property to cycle on the same record) and then control the refreshing in a Save or Close type button (note: pay attention to the tab order and when the autonumber field get's generated.)
    pro's = works great if you can set it up correctly. con's - can be tricky to setup especially if you have it cycling to a new record in the tab order.

    3. Design a 'separate' form just for adding records (so you can control the events differently - I'll sometimes do this).
    pro's = you can control events specific to adding a new record (ie. prompting to delete if fields are blank.) con's = you then have 2 forms to maintain for design changes.

    4. Design the whole form unbound. This involves removing the sourceobject of every field and populated the values to/from the form to/from the table.
    pro's = fast form, full control over events. con's = time-consuming to design.

    5. Create a Function to delete the record if it doesn't meet the criteria (or ask the user if they want to delete the record.)
    pro's = works nicely, especially prompting the user to delete. con's = designing the function correctly. Ex (in ADO) - note: you would call this in your event which is closing the form/refreshing the records (I usually call it when closing the form after testing for blank values):

    Function DelMyTableRec(RecID as variant)
    if isnull(RecID) then
    msgbox "ERROR: RecordID passed to DelMyTableRec is blank."
    exit function
    end if
    dim QI as integer
    QI = msgbox("Do you want to delete this record?",vbyesno)
    if QI = vbno then exit function
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from MyTableName where RecordID = " & RecID & ""
    rs.open strSQL, currentproject.connection, adopendynamic, adlockoptimistic
    if rs.eof and rs.bof then goto noRecs
    rs.delete
    noRecs:
    rs.close
    set rs = nothing
    exit function
    Last edited by pkstormy; 11-13-09 at 19:24.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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