Finding Matches or a feature that doesn't allow duplicates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi -
I'm working with Access 97 and I was wondering if anyone knows of a feature
in Access that doesn't allow duplicates or finds matches for a record? If
so, can you teach me?
 
Nani said:
I'm working with Access 97 and I was wondering if anyone knows of a
feature
in Access that doesn't allow duplicates or finds matches for a record? If
so, can you teach me?
Click to expand...

If you want to prevent any two Records in a Table from having the same value
in a particular Field, in design view, in the properties of that Field, set
Indexed (No duplicates).

If you simply want to know if there are multiple Records with the same value
in a given Field, use the Query Builder to create a Select Query, selecting
the given Field. Then in Design View in the Query Builder, on the menu View
| Totals. Select Group By for the Field, then drag the same Field down
again, and select Count.

If you are entering values in a Form that is open on the entire Table or a
Query that includes all records from the Table, in the Field's Before Update
event, where "Fld1" is the Text Field in which you are interested, and it is
entered in TextBox, "txtFld1":

Dim rs as DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Fld1] = """ & Me!txtFld1 & """"
If Not rs.NoMatch Then
MsgBox "The value " & Me!txtFld1 & " would cause a duplicate. Please
correct and try again."
Cancel = True
End If

Caveat: Untested air code.

Larry Linson
Microsoft Access MVP
 
Back
Top