Cancel saving of new record

[missinglinq]

Trying to help a friend with this, so I'm kind of stuck with some of the things here. He wants to goto a new record, enter a PtID then check to see if there's already a record for the data entered. It it already exits, he wants to cancel the new record and go to the existing record; if not he wants to be able to continue adding the new record. PtID is a text datatype.

The code below works fine if it's simply applied to an unbound textbox or when appied to the field itself, but the problem I'm having is cancelling the new record before going to the existing record. I've tried Me.Undo, Cancel = True, SendKeys "{ESC}" and combinations thereof. I've tried putting all of it in the Form_BeforUpdate with no joy. Any ideas would be welcomed. As I said, I'm stuck with the above scenario. IF I were doing it, I'd put a combobox on the form, check for a match and go to it or if no match then got a new record.

Private Sub PtID_LostFocus()
intX = DCount("[PtID]", "ActivePatientsQuery", "[PtID]='" & Me![PtID] & "'")
   If intX > 0 Then
     [b]'Need to cancel record save here[/b]
   
     Set rs = Me.Recordset.Clone
     rs.FindFirst "[PtID] = '" & Me![PtID] & "'"
     Me.Bookmark = rs.Bookmark
   
   End If
End Sub

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[PHV]

Perhaps this ?
Private Sub PtID_LostFocus()
Dim strID As String
strID = Me![PtID]
If DCount("PtID", "ActivePatientsQuery", "PtID='" & strID & "'") > 0 Then
Me.Undo
Me.RecordSet.FindFirst "PtID='" & strID & "'"
End If
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

How are ya missinglinq . . .

Look to the [blue]BeforeUpdate[/blue] event of the field/control where you can make use of the [blue]Cancel[/blue] arguement! . . .

See Ya! . . . . . .

 

[missinglinq]

Aceman1, forgot to say that I'd tried the code there also, without luck. I'll try PHV's.

Thansk!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[missinglinq]

That worked a treat, PHV! Works both in the LostFocus event and the BeforeUpdate event, which is really where I'd prefer to put it. I'd used the LostFocus event because I originally tested the code out on an unbound control.

Thanks to both of you!

Linq

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!