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

 

[BruceM via AccessMonster.com]

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

 

[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

--



.

 

[BruceM via AccessMonster.com]

My suggestion was to use the code in the form's *Error* event. Not the
Before Update event, but the Error event. First you need to determine what
constitutes a unique record that cannot be duplicated. You mentioned a
subform, so I assume the subform table (OsQ2?) is related to the main table.
If so, there is a linking field. If the idea is that for each main form
record the employee can create one subform record, the combination of the
linking field and the OsEmpCode (which I assume is the employee ID number)
would be added to a single unique index as described in my previous posting.
Maybe OsDate would need to be included in the index.

Are OsEmpCode, OsEmpName, and OsDate fields in OsQ2? If so, I will assume
OsEmpName is a text field. The formatted date value would also be treated as
text, so you would probably have something like this if you prefer using the
hard way (DCount):

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strWhere as String
strWhere = "OsEmpName = " " " &
[Forms]![OsFormA]![Combo11] & " " " AND OsDate = #" " " &
Format([Forms]![OsFormA]![Text13], "dd-mm-yyyy") & "#" " "
Debug.Print strWhere

If DCount("[OsEmpCode]", "[OsQ2]",strWhere) > 0 Then
MsgBox "Record will duplicate"
Me.Undo
Cancel = True
End If

End Sub

Note that you need to cancel the update.

After running the code, press Ctrl + G. That will open the immediate code
window, where you will see strWhere. That's what Debug.Print does. Post
strWhere if it is not working as expected.

I am doing a lot of guessing here, and do not have time to set up a test,
expecially since I don't know if my guesses are valid.

Again, my suggestion after setting up the unique index in the table:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 3022 Then
Response = acDataErrContinue
MsgBox "Duplicate information"
End If
End Sub

If you are still having problems, post your table structure (not every field,
but enough to show the fields you are using in your DCount expression.
Describe any table relationships.

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

[quoted text clipped - 33 lines]