Need Help to validate More than 3 Field in Access 2003 MDB

[pervezjan]

Hi
Thanks in advance for help


I have a form and I tried to validate the fields. Through Primary Key in Table It is possible but cursor go to the Command button then its validating. I want if I entered 3 fields it must give a message of duplication. The detail is as under
Table Name Examination
FormName Exam
Field Name IDNO, Semistername, Year (Note the IDNO is numeric Field and rest are text)

Need your help to resolve.

[ranman256]

If you key/index the fields in the table ,access will automatically tell you about the duplcates.

[pervezjan]

Dear ranman256 Thank you, Please read my question is I already did the Index It is working but its work with command button. I mean that If I put the data in the start in these 3 fields I need a message that "Record is duplicate". In indexing system I have to fill all the fields then message appeared so that I should stop to put further information in the form that the record is duplicate

[June7]

Then you have the indexing set wrong.

If you want index in table to prevent duplicate combinations of the three fields, then need a compound index set on those three fields.

Otherwise, use code in form BeforeUpdate event.

[pervezjan]

Thanks June7
That is way I am asking how to put codes. This compound index validation is ok. But I have to fill all the field and when I reach on command button to save the record then message of validation is coming. I need that if I fill these 3 field a message should appear immediately.

[June7]

Would need code in AfterUpdate event of each of the controls bound to those fields. Build a procedure behind the form that can be called by each control's event.

Example with text, date, number fields.

Sub Validate()
With Me
If Not IsNull(.control1name) And Not IsNull(.control2name) And Not IsNull(.control3name) Then
If DCount("*", "tablename", "field1='" & .control1name & "' And field2=#" & .control2name & "# And field3=" & .control3name) > 0 Then
MsgBox "Record already exists with this data. Enter different values."
End If
End If
End With
End Sub

[pervezjan]

Thanks a lot
June7
Excellent giving me idea of Dcount function
Great Job Now I will be able to solve my problem soon.