How to avoid duplicate entry in the subform

[Irshad Alam]

I have a form and subform, while posting the data I want a message to prompt
if data is going to duplicate and undo the entry


My Table name for the main form : OsTabA
My table name for the subform : OsTabB

My Query having all the fields from both the above table named : OsQ1


Main Form Name : OsFormA

subForm Name : OsFormB
Field in the subform are as below :
OsEmpName
OsDate


I tried the to use the below code in the Subform Before update event, But it
produces error - Runtime error 3077:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "OsEmpName = '" & [Forms]![OsFormA]![Combo11] & "' And " &
"OsDate = #" & [Forms]![OsFormA]![Text13]
If Not rs.NoMatch Then
MsgBox "The Record will Duplicate, check It !!!"
Cancel = True
End If

End Sub



Please advice the correct the VB code to handle this situation.

Thanks and best regards

Irshad

 

[Irshad Alam]

I tried another method also failed of dcount But failed, below the details :

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("[OsEmpCode]", "[OsQ2]", "[OsEmpName] = " &
[Forms]![OsFormA]![Combo11] And "[OsDate] = " & [Forms]![OsFormA]![Text13]) >
0 Then
MsgBox "Record will duplicate"
Me.Undo
End If
End Sub


Please advice.

Regards

Irshad

 

[Irshad Alam]

Dear Sir,

I could not understand your below reply .

I am still trying the below code and getting the "Runtime error 3075


Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("[OsEmpCode]", "[OsQ2]", "OsEmpName =" &
[Forms]![OsFormA]![Combo11] & " AND " & "OsDate = " &
Format([Forms]![OsFormA]![Text13], "\#dd-mm-yyyy\#")) > 0 Then
MsgBox "Record will duplicate"
Me.Undo
End If
End Sub


In the above, it checks in a query, if in a same date, the same name is
found , then it should make the value more than 0 and it produce msgbox. I
hope this method will help me to avoid duplicate entry in the subform.

Please correct my above code syntax.

Regards

Irshad

You could place a unique index on the combination of fields that would
constitute duplication if they were all the same. For a subform this may be
the linking field and the OsEmpCode field, but I can't be sure from your
description. To set the index, go to View >> Indexes. Give the index a name
(best to avoid spaces and special characters other than underscores), and
select a field name. Go to the next line down. Leave the name blank, and
select another field. When both fields are selected, set Unique to Yes.

Now in the form's Error event you could have:

If DataErr = 3022 Then
Response = acDataErrContinue
MsgBox "Duplicate information"
End If

I tried another method also failed of dcount But failed, below the details :

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("[OsEmpCode]", "[OsQ2]", "[OsEmpName] = " &
[Forms]![OsFormA]![Combo11] And "[OsDate] = " & [Forms]![OsFormA]![Text13]) >
0 Then
MsgBox "Record will duplicate"
Me.Undo
End If
End Sub

Please advice.

Regards

Irshad

I have a form and subform, while posting the data I want a message to prompt
if data is going to duplicate and undo the entry

[quoted text clipped - 31 lines]

Irshad

--
Message posted via AccessMonster.com


.