Validating a form when adding a record

[shaz123]

I have a form which allows an individual to add a cylinder record, i would like to validate the form in the sense that if the cylinder has already been added into the table it cannot be added again. So once the cylindernumber has been typed within the text field and the add button is clicked, the cylinder number typed is checked against the table if it has already been added a meesage should appear not allowing the user to enter a clyinder that is already added. I ahve tried this with the following code (the coding in red was already generated by adding a command button "add" to the form. The message i am recieving when the cylinder number in the textbox matches the cylinder number within the table is "you cant go to the specific record". I think my coding must be in the wrong order and IDEAS!!!!!

MyCode:
Private Sub Command39_Click()
On Error GoTo Err_Command39_Click

DoCmd.GoToRecord , , acNewRec


Exit_Command39_Click:
Exit Sub

Err_Command39_Click:
MsgBox Err.Description
Resume Exit_Command39_Click

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim StrSQl As String

Set db = CurrentDb

Set rs = db.OpenRecordset("SELECT * From tbl_CylinderMaster Where [Cylinder Serial Number] = '" & Me![Cylinder Serial Number] & "'")

If Me![Cylinder Serial Number] = [Cylinder Serial Number] Then
MsgBox ("This Cylinder Number has already been added please add another")

End If



End Sub

 

[MStef]

Instead of your code, make an INDEX on Cylindernumber field,
with UNIQUE = YES, or make this field as a PRIMARY KEY.

 

[shaz123]

Thankyou for that it was so basic, i was just going the long way round