What is wrong with this code

[Surinder Kumar Arora ITS GM EAST MTNL Delhi]

I want code in a form for update event for a field named telephone having
eight character number property to search for all records telephone field
and return no action if no record is having that value
and if any other record is having that value it should go to that
record(latest record if more than one records are having that value)
I have written the code as follows
It accepts duplicate values and does not go to the duplicate record

Private Sub TEL_AfterUpdate()

Dim rst As Object

Set rst = Me.RecordsetClone

rst.FindFirst "[Tel] = ' " & Me.TEL & " ' "

If rst.NoMatch Then
Me.COMPLAINT = COMPLAINT
Else
'immediately go to that record
Me.Bookmark = rst.Bookmark
End If

rst.Close

End Sub

regards
arora sk

 

[Surinder Kumar Arora ITS GM EAST MTNL Delhi]

Dear Sir,
Thank you very much for the correct and uptodate reply which has sorted out
my problem
regards
arora sk

I want code in a form for update event for a field named telephone
having eight character number property to search for all records
telephone field and return no action if no record is having that value
and if any other record is having that value it should go to that
record(latest record if more than one records are having that value)
I have written the code as follows
It accepts duplicate values and does not go to the duplicate record

Private Sub TEL_AfterUpdate()

Dim rst As Object

Set rst = Me.RecordsetClone

rst.FindFirst "[Tel] = ' " & Me.TEL & " ' "

If rst.NoMatch Then
Me.COMPLAINT = COMPLAINT
Else
'immediately go to that record
Me.Bookmark = rst.Bookmark
End If

rst.Close

End Sub

regards
arora sk

I see a couple of problems. First, your code as posted is adding two
extra spaces on the beginning and end of the telephone number entered in
the Tel control, so probably it will never find a match. Also, as
Turtle has pointed out, if Tel is defined as a number field, as opposed
to text, you shouldn't have the apostrophes at all. However, I'm going
to assume that this is actually a text field.

A second possible problem is that, if the control named Tel is bound to
the Tel field in the forms recordsource, you'll need to undo the update
to this control and to the form before moving to a new record using the
Bookmark property. Otherwise the record you just typed into will be
saved automatically, if there's nothing to stop it. So if Tel is a
bound control, you should modify that part of your code like this:

If rst.NoMatch Then
Me.COMPLAINT = COMPLAINT
Else
' Cancel changes to this record
Me.Tel.Undo
Me.Undo
' Go to the matching record
Me.Bookmark = rst.Bookmark
End If

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)