Checking for duplicate value on subform

[maha]

Hi,

I have a subform where a user can enter reference numbers for various parts that correspond to pages on a catalog. If a user repeats a reference number on the subform (if it's already been entered on the subform), then I want an error message to pop up indicating that this number has already been used.

So, when a new reference number is entered, I'm wondering if I should iterate through the referencenumber control and check each value for duplicates. Or, if I should do some kind of lookup in the table for these values. I'd rather iterate through the values if this is possible, this way I don't have to worry about requerying the table. Does anyone know if/how this is possible?

 

[scking]

You could set the field value in the table for 'No Duplicates' which would probably be the best way. But...
here is another. After you get a TRUE from IsDupe in the AfterUpdate event then then you would need to notify the user and probably perform a me.undo which would remove the record that was being inserted before it was saved.


Public Function IsDupe(strRefNo As String) As Boolean

' One way to determine if a duplicate for a record exists.

Dim strSQL As String
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
strSQL = "SELECT * FROM tblWhatever WHERE RefNo=" & """" & strRefNo & """" & ";"
Set rst = Db.OpenRecordset(strSQL)

' If the recordset contains a record then
' There is a duplicate
' Otherwise there is no duplicate

If rst.RecordCount <> 0 Then
IsDupe = True
Else
IsDupe = False
End If

Set db = Nothing
Set rst = Nothing

Exit Function Growth follows a healthy professional curiosity