Check if code already exist in a different table

[jeanulrich00]

Hi

I have a table "TblIem" containing many field and one field is named
"Code" This field is indexed with no duplicate

I also have another table named "TblOut" which contain the same fields
then the "TblItem"

I have a form "FrmDataEntry" based on the table "TblItem"

What I want is this :

When the user enter data in the text field "Code" a script that I will
put on the after updated even that will check if this code already
exist in the table "TblOut"

So if the code exist a the data will be erased automaticaly and a
message will pop telling the user that he can not use such code

Thanks

 

[Ken Sheridan]

Use the Code control's BeforeUpdate event procedure as this accepts a Cancel
argument. The code would be:

Const conMESSAGE = "Code already in use."
Dim ctrl As Control
Dim strCriteria As String

Set ctrl = Me.ActiveControl

If Not IsNull(ctrl) Then
strCriteria = "Code = " & ctrl
If Not IsNull(DLookup("Code", "TblOut", strCriteria)) Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
ctrl.Undo
Cancel = True
End If
End If

The above assumes that the Code field is a number data type. If its text
data type amend the code to:

strCriteria = "Code = """ & ctrl & """"

Ken Sheridan
Stafford, England