"Arno R" <ar***********@ tiscali.nl> wrote in
news:42******** *************@d reader2.news.ti scali.nl:
"BerkshireG uy" <bd*****@yahoo. com> schreef in bericht
news:11******** *************@g 43g2000cwa.goog legroups.com... Hello everyone,
I have a bond form that a user uses to enter data.
One of my fields, is PolicyNumber. I added some code on the
Before Update event of txtPolicyNumber that checks to see if that
policy number is in the system when entering a new record. If it
is, I want it to display a message and go back to the policy
number field.
I've tried this code on a policy number that is not in the table
and
it keeps informing me that it is in the system.
Here is the code:
Private Sub txtPolicyNumber _BeforeUpdate(C ancel As Integer)
Dim db As DAO.Database, rs As DAO.Recordset
Dim intnewrec As Integer
Set db = CurrentDb()
Set rs = db.OpenRecordse t("Select PolicyNumber FROM
tblClientSurvey s WHERE Policynumber ='" & Me.txtPolicyNum ber &
"'")
intnewrec = Me.NewRecord
If rs.NoMatch = False And intnewrec = True Then
MsgBox "Policy Number Already Exists!"
Cancel = True
Me.Undo
End If
End Sub
NoSeek = NoFind = NoMatch ???? (Look up NoMatch in Help)
Why not just check rs.RecordCount. If it's 0 then there aren't any
rcords, and you can cance:
Private Sub txtPolicyNumber _BeforeUpdate(C ancel As Integer)
Dim db As DAO.Database, rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordse t("Select PolicyNumber _
FROM tblClientSurvey s _
WHERE Policynumber ='" & Me!txtPolicyNum ber & "'")
If rs.RecordCount <> 0 And Me.Newrecord Then
MsgBox "Policy Number Already Exists!"
Cancel = True
Me.Undo
End If
End Sub
In fact, what I'd do is enable the editing of txtPolicyNumber only
when creating the new record, then you don't have to check the
value.
Of course, if you want to check the value, and the policy number is
the primary key, then you're stuck checking for Me.NewRecord.
However, I would definitely argue against using the policy number as
the primary key. If you use an Autonumber that the user never sees,
you then could use this as your test SQL:
SELECT PolicyNumberID _
FROM tblClientSurvey s _
WHERE Policynumber ='" & Me!txtPolicyNum ber & "'" _
AND PolicyNumberID <> " & Me!PolicyNumber ID
That would exclude the current record from your test for cases where
you're changing the policy number of an existing record.
If the policy number can be changed, then I think that's yet another
reason why you should not use it as a PK.
--
David W. Fenton
http://www.bway.net/~dfenton
dfenton at bway dot net
http://www.bway.net/~dfassoc 
