I am trying to generate a duplicate report to warn the user that they have entered a duplicate record based on at least two parameters after they update a field in a form.

Any help would be greatly appreciated.

Mike

__________________
Mike

Hi Mike,

I'm making two assumptions here:

1. You're using a bound form.
2. By "report" you mean error message dialog box. You'd want your
   user's to have instantaneous notification I suspect.

What I'm thinking is that you can use the form's error event to disply a user-friendly error message dialog when a user tries to write duplicate values to fields defined in a multi-field, unique index (containing the fields that define your "at least two parameters.")

First, you'll need to create the index. Build a multi-field, unique index on the table that includes the fields (as many as you like) which define a duplicate record. This is done through the Indexes dialog in table design view. Just give the index a name, assign your "duplicate-record-defining" fields to it, and set its 'Unique' property to yes.

Second, add this code behind your form:


Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Const errDuplicateIndexValue = 3022
    Dim strMsg As String

    If DataErr = errDuplicateIndexValue Then
        strMsg = "This record cannot be added to the database." & vbCrLf
        strMsg = strMsg & "It would create a duplicate record." & vbCrLf & vbCrLf
        strMsg = strMsg & "Changes were uncuccessful."

        MsgBox "Error Number: " & DataErr & vbCrLf & vbCrLf & strMsg, _
            vbCritical + vbOKOnly, "Duplicate Record."

        Me.txtText1.SetFocus

        Response = acDataErrContinue
    Else
        Response = acDataErrDisplay
    End If

End Sub

This will display a more user friendly error message than the Jet default error string which reads:

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

Hope this is in the vacinity of what you're looking for.

Bob

Thanks for your suggestion. I inserted the coded but it returned the following compile error;

Me.txtText1.SetFocus
"Method or data member not found"

Mike

Hi Mike,

Me.txtText1.SetFocus is simply a reference to one of the textbox controls on the form I used to test the code. The names of the controls on your form will be different. 'Me' is an abbreviated means of referring to the current form, and the SetFocus method moves the focus back to (makes active) the txtText1 textbox control on that form.

You can delete or comment out that line of code if you like, or change txtText1 to the name of one of the controls on your form that you would like focus to return to when you close the error message dialog.

It can be helpful to the user, after they have entered invalid data in a control, to return focus to the control and clear the invalid data through code, as in:

Me.txtText1.SetFocus
Me.txtText1.Text = ""

Since you are using more than one field in your duplicate record definition, it's kind of a crap shoot I guess which control you return focus to.

Let me know if it gives you any other problems.

HTH,

Bob

Thanks a lot! That works great. Can you recommend a good reference book for working with VB for Access?

Mike

Hi Mike,

You're welcome! Glad you got it going.

The two VBA references I've spent the most time in are:

"Access 2000 Developer's Handbook", by Getz, Litwin and Gilbert (2002 ed. available)
"VBA Developer's Handbook", by Getz and Gilbert

The first is Access specific and should be hit first. It covers Access topics not covered by the latter, like Access SQL and ADO/DAO. The latter is about the "universal" VBA language, wherever it shows up, and goes into greater detail on generic language features like string manipulation, class creation, searching and sorting, file I/O, etc. There's defintiely overlap.

For the sake of mentioning it, Access development is done using VBA (Visual Basic for Applications), not VB (strictly speaking) which is the stand-alone programming system that ships with Visual Studio. VBA has the same core components as VB, but lacks the forms engine and compiler (hence, no stand-alone components creation). The language engines of the two are equally powerful in most respects. Its just that VBA can only live as a hosted component in some other COM-based application (like Access).

Enjoy the tomes! :)

Bob