Help writing a Function

[k983111]

Can someone please help me out with writing a function.
I have a pair of text boxes on a form that enter data into a table (Table1).these text boxes together form a PK. However before the data is entered i need the data in these fields to be checked against another table(2), to see if this record has already been entered into that table, if so i need a msg box to pop up and prompt me.
I have very little experience with writing functions so as much explanation as possible would be greatly appreciated.

 

[Tim K.]

Try the code below.

Private Sub TextBox2_BeforeUpdate(Cancel As Integer)
If DCount("[AnyFieldNameInTable2]", "Table2", "[Field1] = '" & Me.TextBox1 & "' And [Field2] = '" & Me.TextBox2 & "'") = 1 Then
MsgBox "Duplicated. You have to find a new id"
Cancel = True
Me.Undo
Exit Sub
Else
'Do nothing
End If

End Sub


I use the code on BeforeUpdate event of the text box 2. And I assume that both fields are Text.

 

[Fornatian]

This also works by letting Access identify the error and respond accordingly without having to manually erk it out.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Const RecordAlreadyExists = 3022

If DataErr = RecordAlreadyExists Then
MsgBox "Already a record"
Response = acDataErrAdded
Me.Undo
Me.Field1.SetFocus
End If
End Sub

 

[k983111]

Been working on that for a while, many thanks.