Primary Keys

[Guest]

Is there any way to set up a form such that when data is entered on the
primary key field, the user will be told at that point that a duplicate
record already exists, or to have Access automatically pull up the existing
record? Right now, if a duplicate record is entered on the primary key, the
user does not know this until all information has been input and the user
tries to move out of the record.

Thanks!

Lisa

 

[GasMan]

On leaving the primary key field, execute a query on that value. if it
returns a record, give them an error message and set focus back.

You are really better letting access handle a primary key field
(called surrogate key) and just index your field with no duplicates.

Is there any way to set up a form such that when data is entered on the
primary key field, the user will be told at that point that a duplicate
record already exists, or to have Access automatically pull up the existing
record? Right now, if a duplicate record is entered on the primary key, the
user does not know this until all information has been input and the user
tries to move out of the record.

Thanks!

Lisa

Please remove obvious from email address if emailing.

 

[John Vinson]

Is there any way to set up a form such that when data is entered on the
primary key field, the user will be told at that point that a duplicate
record already exists, or to have Access automatically pull up the existing
record? Right now, if a duplicate record is entered on the primary key, the
user does not know this until all information has been input and the user
tries to move out of the record.

Thanks!

Lisa

You can use the BeforeUpdate event of the primary key textbox to
search for the entered value. Try code like:

Private Sub txtPK_BeforeUpdate(Cancel as Integer)
Dim rs As DAO.Recordset
Dim iAns As Integer
Set rs = Me.RecordsetClone ' assuming the form is not filtered
rs.FindFirst "[PK] = " & Me!txtPK
' or rs.FindFirst "[PK] = '" & Me!txtPK & "'" if it's Text
If Not rs.NoMatch Then ' found a dup
Cancel = True ' cancel this field in either case
iAns = MsgBox("This PK exists! Click OK to go to it, Cancel" _
& " to try again:", vbOKCancel)
If iAns = vbOK Then ' clear this record, go to the found one
Me.Undo
Me.Bookmark = rs.Bookmark
Else ' just let the user try again
Me!txtPK.Undo
End If
End If
Set rs = Nothing
End Sub

John W. Vinson[MVP]