BeforeUpdate

[Klo]

I'm trying to use a SQL statement to validate input in a form. I need to make sure that the number entered in not already used. ( Access 2000) This is what I tried:

Private Sub Pin_BeforeUpdate(Cancel As Integer)
On Error GoTo Pin_Err
Dim CurDB As Database, JobInputTB As Recordset, SQLStmt As String

Set CurDB = CurrentDb
SQLStmt = "SELECT Pin_Number FROM Job_input_table WHERE Pin_Number = """ & Me![Pin] & """"
Set JobInputTB = CurDB.OpenRecordset(SQLStmt)
If Not JobInputTB.EOF Then
MsgBox "This Pin Number has already been used in the current run. Please choose another.", vbCritical, "Duplicate Entry"
Cance = True
End If
JobInputTB.Close
Set JobInputTB = Nothing
Exit Sub
Pin_Err:
MsgBox " Error is " & Err.Description & " entering new Pin Number", vbCritical, ""
Exit Sub



End Sub

When I put it to the test I get "Error is too few parameters Expected 1. I'm am totally stuck! Help!!
Mike

 

[zevw]

You don't need a Select Statement for this:

Do the Following:

Dim Find_Pin

Find_Pin = DLookup("Pin_Number","Job_input", "Pin_Number = '" & Me![Pin] & "'" )

If Not IsNull(Find_Pin) Then
MsgBox "This Pin Number has already been used in the current run. Please choose another.", vbCritical, "Duplicate Entry"
Cancel = True
End If

 

[Klo]

This is what I get when I run it:
The object dosen't contain the Automation object 'Pin_number"

?

 

[zevw]

Sorry I did not realize your Table Name is Job_input_table

Here it is!

Find_Pin = DLookup("Pin_Number","Job_input_table", "Pin_Number = '" & Me![Pin] & "'" )