How to alert the user of duplicate entries within a form?

[kat17]

I have a sub-form that represents a matrix.

I would like to alert the user if a value has been already entered. (These values are not primary keys)

I gather some type of code would need to be used in the AFTER UPDATE event but am not sure what to use. I only want to search the current form for duplicates, not the table.

Any tips?

 

[ZmrAbdulla]

You can use "DLookUp Function" with criteria to check the duplicate.
See the help file for more info.


________________________________________________________
Zameer Abdulla
Help to find Missing people
You may be the boss' pet; but you are still an animal

 

[TheAceMan1]

How are ya kat17 . . . .

Here's an example using the [blue]BeforeUpdate[/blue] event of the form:

[blue]   Dim Criteria As String
   
   [green]'Use this if FieldName DataType is Text[/green]
   Criteria = "[FieldName] = '" & Me!FieldName & "'"
   
   [green]'Use this if FieldName DataType is Numeric[/green]
   Criteria = "[FieldName] = " & Me!FieldName

   If Not IsNull(DLookup("[FieldName]", "TableName", Criteria)) Then
      [green]'Your Duplicate Error Message[/green]
      Cancel = True
   End If[/blue]

See Ya! . . . . . .

 

[Ed2020]

How many values will you be checking? Remember that Domain Aggregate functions, such as DLoolup, can be quite slow on larger recordsets....

Ed Metcalfe.

Please do not feed the trolls.....

 

[PHV]

These values are not primary keys
Have a look at composite (ie multifields) unique index.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kat17]

Thanks for all your posts.. sorry for the delay!

Anyway, I went to use "DLookup" but am not sure if this is what I need. Or maybe it's because I am new to the "user-side" of things... (gimme SQL thru a DOS prompt anyday!!)

What I THINK I need something that will not lookup values in the table - I want it to lookup values from fields on the form. Is this possible?

I don't really know how to battle this problem so any feedback would be appreciated.

Below is a brief of what I am TRYING to acheive:

USER-SIDE OF THINGS:
The database is for a school photographer.
When the photographer takes "group shots" he get the students to hold up a number & he takes a photo.
Back at the office, the photographer enters the row names (eg. 1st row, 2nd row, 3rd row...) and student numbers (the ones they are holding up) into the database. (we call this the MATRIX)

I need to create a form to assist with user-friendly data entry.
So, I created a form (containing school, class & teacher details) and a subform (containing the matrix)
The form works well. I just need help to create some sort of error checking for duplicate numbers. Usually, around 30 values to check.

THE DATABASE:
I will just explain the MATRIX part because it will get very boring and you might miss the important bit!
Basically, there are two tables that make the matrix.

1) tblMatrix
idMatrix
idClass
groupNo

2) tblRows
idRow
idMatrix
rowName
a
b
c
d
e
f
g
h
i
j

-----

I'm not sure what other info you need but let me know!

Thanx all!