How to avoid duplicate entry in the subform
in [Forms]
|
Prev: Form flashing
Next: check subform for records
From: Irshad Alam on 6 Apr 2010 06:35 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
From: Irshad Alam on 6 Apr 2010 08:03 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" wrote: > > 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 >
From: BruceM via AccessMonster.com on 6 Apr 2010 10:15 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 Irshad Alam wrote: >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 http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201004/1
From: Irshad Alam on 7 Apr 2010 02:20 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 "BruceM via AccessMonster.com" wrote: > 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 > > Irshad Alam wrote: > >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 > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201004/1 > > . >
From: BruceM via AccessMonster.com on 7 Apr 2010 07:44 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. Irshad Alam wrote: >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] >> >> >> >> Irshad -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201004/1
|
Pages: 1 Prev: Form flashing Next: check subform for records |