Coding "record found" condition

[Guest]

Can anyone tell me how to test the condition "record found" (this is a
primary key) and display a message "record found" and ask the question do you
want to update?

If update, how do I put record in edit mode because I have the Data Entry
set to YES on the form. And can I change the form from edit mode back to
data entry set to YES?

Jube

 

[John Smith]

This is air-code so beware of typos, but I think it will give you what you want:

Private Sub PK_AfterUpdate()
Dim ThisSet As Recordset, SQL As String
On error Goto Error_Trap
SQL = "SELECT count(*) as recCount FROM your_table" _
& " WHERE PK = '" & PK & "'"
Set ThisSet = CurrentDb.OpenRecordset(SQL, DB_OPEN_SNAPSHOT)
With ThisSet
.MoveFirst
If !recCount > 0 Then
if msgbox("Record Exists, Edit it?", vbCritical, "Duplicate Key") _
= vbyes then
SQL = "PK = '" & PK & "'"
Me.Undo
Me.DataEntry = False
Me.Filter = SQL
Me.FilterOn = True
Else
Me.Undo
End If
End If
.Close
End With

Exit_Sub:
Set ThisSet = Nothing
Exit Sub

Error_Trap:
msgbox "PK Update Error, " & Error$
Resume Exit_SSub
End Sub

Private Sub Form_AfterUpdate(Cancel As Integer)
Me.Filter = ""
Me.FilterOn = False
Me.DataEntry = True
End Sub

Substitute the name of your field for PK, and if it is numeric loose the
single quote in the SQL statements.

HTH
John