Checking if record exists

[BerkshireGuy]

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(Cancel As Integer)
Dim db As DAO.Database, rs As DAO.Recordset
Dim intnewrec As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset("Select PolicyNumber FROM tblClientSurveys
WHERE Policynumber ='" & Me.txtPolicyNumber & "'")

intnewrec = Me.NewRecord

If rs.NoMatch = False And intnewrec = True Then
MsgBox "Policy Number Already Exists!"
Cancel = True
Me.Undo
End If

End Sub
TIA,
Brian

[Arno R]

"BerkshireGuy" <bd*****@yahoo.com> schreef in bericht news:11*********************@g43g2000cwa.googlegro ups.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(Cancel As Integer)
Dim db As DAO.Database, rs As DAO.Recordset
Dim intnewrec As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset("Select PolicyNumber FROM tblClientSurveys
WHERE Policynumber ='" & Me.txtPolicyNumber & "'")

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)

You need to look for rs.EOF here...

Also I would restructure the code if you want this test for new records only ...
If Me.NewRecord then
'only then open a recordset to look if the value exists
End if

You don't need the Me.undo because the 'cancel = true' 'locks' the user in the field ...
Also: what to do when a user edits an existing record?

Arno R

[Bas Cost Budde]

BerkshireGuy wrote:

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(Cancel As Integer)
Dim db As DAO.Database, rs As DAO.Recordset
Dim intnewrec As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset("Select PolicyNumber FROM tblClientSurveys
WHERE Policynumber ='" & Me.txtPolicyNumber & "'")

intnewrec = Me.NewRecord

If rs.NoMatch = False And intnewrec = True Then
MsgBox "Policy Number Already Exists!"
Cancel = True
Me.Undo
End If

NoMatch becomes valid only after a Find issued to the recordset.

In this case I'd skip the recordset altogether, and try

if dcount("*","tblClientSurveys","policynumber='"&txt policynumber
&"'")>0 then
'your things
end if

Oh, and about the BeforeUpdate thing: would it be wise to forbid edits
on the policynumber when not in a new record? In that case it folds
together nicely:

if newrecord then
if dcount(that thing)>0 then
msgbox "This number is already in use."
cancel = true
end if
else
cancel=true
msgbox "You shall not edit an existing policy number!"
endif
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html

[BerkshireGuy]

Bas,

Ok I have that code, but there is a little issue:

If I go to an existing record and try to change the policy number, it
says "You shall not edit an existing policy number." Ok all good. But
if I now try to add a NEW record, I get the same message.

Any ideas?

-Brian

[David W. Fenton]

"Arno R" <ar***********@tiscali.nl> wrote in
news:42*********************@dreader2.news.tiscali .nl:

"BerkshireGuy" <bd*****@yahoo.com> schreef in bericht
news:11*********************@g43g2000cwa.googlegro ups.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(Cancel As Integer)
Dim db As DAO.Database, rs As DAO.Recordset
Dim intnewrec As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset("Select PolicyNumber FROM
tblClientSurveys WHERE Policynumber ='" & Me.txtPolicyNumber &
"'")

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(Cancel As Integer)
Dim db As DAO.Database, rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("Select PolicyNumber _
FROM tblClientSurveys _
WHERE Policynumber ='" & Me!txtPolicyNumber & "'")

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 tblClientSurveys _
WHERE Policynumber ='" & Me!txtPolicyNumber & "'" _
AND PolicyNumberID <> " & Me!PolicyNumberID

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

[David W. Fenton]

"BerkshireGuy" <bd*****@yahoo.com> wrote in
news:11*********************@g49g2000cwa.googlegro ups.com:

Ok I have that code, but there is a little issue:

If I go to an existing record and try to change the policy number,
it says "You shall not edit an existing policy number." Ok all
good. But if I now try to add a NEW record, I get the same
message.

I have a philosophical problem with allowing you to edit the field
in the first place, only to then pop up a message telling you that
you can't. I would recommend that if you can't change a policy
number that the field be entirely locked when not adding a new
record.

Two possibilities:

If you've got a command button to create a new record, add to it:

Me!txtPolicyNumber.Locked = False

Then, in the form's AfterUpdate event:

Me!txtPolicyNumber.Locked = True

If you don't have a dedicated command button for adding a new
record, then use the form's OnCurrent event to unlock the policy
number.

You may also be able to lock the control in the control's own
AfterUpdate event, or, you could do it in the code for a SAVE RECORD
button.

But I htink it's better design to not allow people the possibility
of editing a policy number in the first place. That way, your
BeforeUpdate code can be much simpler.

Also, see my reply to Arno for some considerations about choosing
your Primary Key.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc