A simpler message box?? (1 Viewer)

[jegenes]

I've got an index in a table that keeps users from entering identical records. It works fine. Upon entering an indentical record the Access message box comes up with the warning.

I'd like to replace that message box with one of my own. How do I do that? Thanks a lot.

John

 

[Alexandre]

I could choose one of these two approaches:

Error trap and display your custom message (Error code is. I beleive 2146. Not sure)


Using the Before Update event, perform a Dlookup (or DCount) to find if there is already a record with the same id, if so display a custom message and cancel the event.

Private Sub txtPrimaryKey_BeforeUpdate()
If Not IsNull(Dlookup("PrimaryKey", "tblName", "PrimaryKey = " & txtPrimaryKey.Value)
' Then the PK already exists
Else
' Then you are OK
End IF
End Sub

Best to my opinion is error trapping.

 

[David R]

Alexandre is correct that BeforeUpdate is the field to use to verify that your Primary Key is ok, BEFORE you get through entering all your fields and attempt to save the record. The trick is to put your code behind the BeforeUpdate of the field, not the entire form. You can also use Dcount which I believe is faster than Dlookup for this purpose.

    If DCount("[PrimaryKeyField]", "tableName", "[PrimaryKeyField]= '" & Me![PrimaryKeyField] & "'") = 1 Then
        MsgBox "This is a duplicate <primary key>."
        Me.Undo
        Cancel = True
    End If

HTH,
David R

 

[jegenes]

I forgot to mention that the table prevents duplicate records by comparing 2 fields in each record. Neither of these fields is unique, so each can be duplicated by itself.

In other words, if FieldA = 123, and FieldB = 500, you can only have one occurance of that combination. You can have several records with FieldA = 123, but only one that also has FieldB = 500.

Neither field is a primary or secondary key.

The index works fine for this, but I hate the error box that comes up.

Thanks,

John

 

[Alexandre]

Both approaches are still valid.

I don t have off hand the error code for a duplicate entry in an unique index, so I would suggest to adapt the DLookup method to your case:

Private Sub txtPrimaryKey_BeforeUpdate()
If Not IsNull(Dlookup(WahteverFieldNameHere, "TableName", "FieldA = " & txtFieldA.Value & " AND FieldB = " & txtFieldB.Value)
' Then the PK already exists
Else
' Then you are OK
End IF
End Sub