Validation to avoid Duplicates in Access

[Sandaseeli]

Hello,

My work is a data base in acess 2000. In a form for a combo box I have set the rule 'No duplicates' to avoid duplicates as the primary field of the form is a differrnt one. However as the form carries many fields after this thers is no warning to know that the field is duplicated until we proceed to the next record after completing the record. This is really ineffecient.

I want to set a validation for this combo box so that as soon as the tab leaves the combo box after selecting a vlaue if the field is duplicated a warning message comes. I tried several validation tricks under validation control of the combo box property settings, but failed.

Please help me.

[~s.o.s~]

It would be better if you post a concrete code sample to get help.

And btw if anyways your combo box is picking data from a column of a table which always has distinct values, where does the duplication come in...?

[PirateTUX]

I think I get what you're trying to do, so I came up with a little bit of code that might help you out. This example was written for a text field, but it should work for any type of field.

Private Sub field1_Exit(Cancel As Integer)
    Dim rs As Recordset
 
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM [table1] WHERE [table1].[field1]='" & Me![field1].Value & "';")
 
    If Not rs.EOF Then
        MsgBox "Value in field1 is not unique.  Please enter a unique value!"
        Cancel = True
 
        Me![field1].Value = Null
        Me![field1].SetFocus
    End If
 
    rs.Close
 
End Sub

Basically what this does is execute a quick query on the table you're going to be modifying to check to see if whatever value you typed in already exists in the column that the text box is bound to. If the value already exists, the field is cleared and focus is returned to that field so the user can try entering something new.

Good luck!

[Sandaseeli]

Thank you so much for the reply and code provided. However I seem to have a problem with the part highlighted in red below. A debug message comes and mark this line. Clarification is highly appreciated.

Private Sub field1_Exit(Cancel As Integer)
    Dim rs As Recordset
 
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM [table1] WHERE [table1].[field1]='" & Me![field1].Value & "';")
 
    If Not rs.EOF Then
        MsgBox "Value in field1 is not unique.  Please enter a unique value!"
        Cancel = True
 
        Me![field1].Value = Null
        Me![field1].SetFocus
    End If
 
    rs.Close
 
End Sub

[~s.o.s~]

The example posted by PirateTUX was meant to be taken as a template and not copied word to word. You have to modify the example to include the database name, the table name and to create a connection.

If you are a beginner you can start your VB quest here.

[PirateTUX]

That is correct. I'm sorry... I should have specified that. This code snippet was only intended to give you a good idea of how to handle the problem. The names used in this snippet are not specific to your situation.

I'm hoping that from looking at the code you get the idea of how it works. Basically it traps an event that cooincides with the cursor leaving the field you want to check, which it be by way of the tab key or the mouse. When this happens, it executes a quick query on the table being effected by the form, looking for the same entry that the user just typed in the field you're wanting to watch. If it finds it in the table, it raises a dialog box telling the user, and then returns the cursor to the watched field. If it doesn't find anything, it does nothing.

I hope this helps you out! Good luck!!!