avoiding entering a contact twice

[Paul]

I have a subform that lists contacts, where the primary key field is
ContactID. The user adds new contacts to the subform by making a selection
in the ContactID combo box.

The compound primary key in the table underlying the subform recordset will
not allow duplicate entries, but the error message it produces about
duplicate primary keys is only meaningful to developers. Most users would
have no idea what it's trying to tell them.

I'd like to put some code in the Before Update event of the combo box that
will check to see if the selected ContactID already exists in the subform
recordset, so I can display a Message Box that tells the user the contact
already exists in plain English, and then cancels the entry. (Is that
Cancel = Yes)?

What VBA code can I use to check to see if the ContactID already exists in
the recordset?

Thanks in advance.

Paul

 

[Douglas J. Steele]

Private Sub Form_BeforeUpdate(Cancel As Integer)

If DCount("*", "MyTable", "ContactID = " & Me.cboContactID & _
" And Field2 = " & Me.txtSomeField) > 0 Then
MsgBox "Data already exists."
Cancel = True
End If

End Sub

 

[Bob Quintal]

I have a subform that lists contacts, where the primary key field
is ContactID. The user adds new contacts to the subform by making
a selection in the ContactID combo box.

The compound primary key in the table underlying the subform
recordset will not allow duplicate entries, but the error message
it produces about duplicate primary keys is only meaningful to
developers. Most users would have no idea what it's trying to
tell them.

I'd like to put some code in the Before Update event of the combo
box that will check to see if the selected ContactID already
exists in the subform recordset, so I can display a Message Box
that tells the user the contact already exists in plain English,
and then cancels the entry. (Is that Cancel = Yes)?

What VBA code can I use to check to see if the ContactID already
exists in the recordset?

Thanks in advance.

Paul

I'd use a simple dCount() call to determine if the contact name
there. If the value is not 0 then a record exists.

Just make the criteria for the dCount equal to the compound key in
the table/query that feeds the subform.

Cancel = True. Sometimes a plain yes works but I don't trust it.

 

[Paul]

Thanks to Doug and Bob for the helpful suggestions.

In using the DCount() function as you suggested, I ran into some interesting
things.

First I noticed that I had to replace the periods with exclamation points in
Me.Name and Me.ProjectID because with the periods I got the "Run-time error
'2001': You canceled the previous operation." With the exclamation points,
that message went away.

The next thing I noticed was that Cancel = True by itself doesn't clear the
duplicate contact entry. In fact, after the BeforeUpdate event is finished
running, Access displays another error message saying:

"The value in the field or record violates the validation rule for the
record or field . . ." etc. etc.

But if I add a SendKeys command following Cancel = True, then the entry is
cleared (as desired) and the error message doesn't appear.

I also noticed is that is seems to run fine without the Cancel = True
command, as long as I have the SendKeys line in there. The error message
goes away, and the entry is removed from the subform. The only noticeable
thing that happens if I keep the Cancel = True line in there is that the
computer beeps at you after you click OK in the message box.

The code I am using is:

Private Sub Name_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblProjectContact", "ContactID = " & Me!Name & " And
ProjectID = " & Me!ProjectID) > 0 Then
MsgBox "That contact that has already been added to this project."
Cancel = True
SendKeys "{esc}{esc}{esc}", False
End If
End Sub

(The syntax in the DCount statement may appear flawed, but Me!Name actually
evaluates to a number, and the expression does work).

I've noticed in the past when I mention the SendKeys command some people
cringe, so if you have a better way to get the duplicate entry cleared, I'd
be happy to try it, but so far this is the only way I've found to eliminate
the error message and also clear the duplicate entry.

At this point I'm wondering what the purpose of the Cancel = True command
is. In this Sub, it doesn't seem to do anything (other than beep at you),
since the code runs just fine without it.

I'd appreciate any comments you may have about this.

Thanks for pointing me in the right direction with the DCount function,
because it seems to be working just fine.

Paul

 

[Stuart McCall]

Thanks to Doug and Bob for the helpful suggestions.

In using the DCount() function as you suggested, I ran into some
interesting things.

First I noticed that I had to replace the periods with exclamation points
in Me.Name and Me.ProjectID because with the periods I got the "Run-time
error '2001': You canceled the previous operation." With the exclamation
points, that message went away.

The next thing I noticed was that Cancel = True by itself doesn't clear
the duplicate contact entry. In fact, after the BeforeUpdate event is
finished running, Access displays another error message saying:

"The value in the field or record violates the validation rule for the
record or field . . ." etc. etc.

But if I add a SendKeys command following Cancel = True, then the entry is
cleared (as desired) and the error message doesn't appear.

I also noticed is that is seems to run fine without the Cancel = True
command, as long as I have the SendKeys line in there. The error message
goes away, and the entry is removed from the subform. The only noticeable
thing that happens if I keep the Cancel = True line in there is that the
computer beeps at you after you click OK in the message box.

The code I am using is:

Private Sub Name_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblProjectContact", "ContactID = " & Me!Name & " And
ProjectID = " & Me!ProjectID) > 0 Then
MsgBox "That contact that has already been added to this project."
Cancel = True
SendKeys "{esc}{esc}{esc}", False
End If
End Sub

(The syntax in the DCount statement may appear flawed, but Me!Name
actually evaluates to a number, and the expression does work).

I've noticed in the past when I mention the SendKeys command some people
cringe, so if you have a better way to get the duplicate entry cleared,
I'd be happy to try it, but so far this is the only way I've found to
eliminate the error message and also clear the duplicate entry.

At this point I'm wondering what the purpose of the Cancel = True command
is. In this Sub, it doesn't seem to do anything (other than beep at you),
since the code runs just fine without it.

I'd appreciate any comments you may have about this.

Thanks for pointing me in the right direction with the DCount function,
because it seems to be working just fine.

Paul

Pressing Esc undoes any changes made in a form. So what you need is a method
to do the same thing. As luck would have it, there's one built in to Access.
Replace your SendKeys line with this one:

Me.Undo

 

[Douglas J. Steele]

Name is a reserved word, and shouldn't be used for your own purposes. Since
Name is a property of the form, Me.Name refers to the form's Name, not your
control named Name. For a list of names to avoid, see what Allen Browne has
at http://www.allenbrowne.com/AppIssueBadWord.html

 

[Paul]

Doug, Bob and Stuart,

Thank you, gentlemen, for your help and insights into the use of DCount(),
Me.Undo and avoiding reserved words.

I'm using the procedure in several subforms, and it works great.

Paul

 

[Bob Quintal]

Doug, Bob and Stuart,

Thank you, gentlemen, for your help and insights into the use of
DCount(), Me.Undo and avoiding reserved words.

I'm using the procedure in several subforms, and it works great.

Paul

You are most welcome.

Now that you've changed the offending reserved word 'name' your code
should work with the dot(.) as well as the bang(!)