On Mon, 17 Oct 2005 23:12:50 -0400, Shyguy <sh****@aol.com> wrote:
....
I didn't use a function. This is the code I used.
--------
Private Sub Contact_BeforeUpdate(Cancel As Integer)
On Error GoTo Contact_BeforeUpdate_Err
If (Eval("DLookUp(""[Contact]"",""[Contacts]"",""[Contact] =
Form.[Contact] "") Is Not Null")) Then
Beep
MsgBox "The Contact you entered already exists on Database.",
vbInformation, "Duplicate Contact"
DoCmd.CancelEvent
End If
Contact_BeforeUpdate_Exit:
Exit Sub
Contact_BeforeUpdate_Err:
MsgBox Error$
Resume Contact_BeforeUpdate_Exit
End Sub
--------
Can I make this as a function?
The simple answer to your question is "yes".
Next, I'd want to make some style comments
1. DoCmd.CancelEvent is an obsolete style that harkens back to Access 2.0.
That's what the Cancel argument is for - you let Cancel = True to cancel an
event. Of course, that won't matter to us in this case, because to change the
value, we have to -not- cancel the event.
2. In Access 95 and above, there is an Err object with properties, including a
Description property. Using Error$ is another throw-back to Access 2.0.
3. That Eval statement is a bit wierd. It took me a minute to figure out why
it's done that way, but I guess it's a clever way to get "Form.[Contact]" to
be evaluated without writing a lot of extra code. I'd use single quotes
inside the Eval string, though, because it's easier to read and maintain.
Here's what the finished code might look like...
'These 2 statements normally appear at the top of every module.
Option Explicit
Option Compare Database
Private mblnResetContact As Boolean
Private Sub txtContact_BeforeUpdate(Cancel As Integer)
mblnResetContact = False
If Not CheckContactOK() then
mblnResetContact = True
End If
End Sub
Private Sub txtContact_AfterUpdate()
If mblnResetContact then
Me!txtContact = Null
mblnResetContact = False
endif
End Sub
Private Function CheckContactOK() As Boolean
Dim blnContactOK As Boolean
On Error GoTo Err_Catch
Dim strLookupExpr As String
strLookupExpr = "DLookUp('[Contact]','[Contacts]','[Contact] = " & _
"Form.[Contact] ') Is Null"
blnContactOK = Eval(strLookupExpr)
If Not blnContactOK Then
Beep
MsgBox "The Contact you entered already exists on Database.", _
vbInformation+vbOkOnly, _
"Duplicate Contact"
End If
Proc_Final:
CheckContactOK = blnContactOK
Exit Function
Err_Catch:
MsgBox Err.Description, vbExclamation+vbOkOnly
'Don't second guess the user if the code is broken...
blnContactOK = True
Resume Proc_Final
End Sub
' ... Other form code goes here ...
