if duplicate goto record
in [General]
|
Prev: Format
Next: vehbi22@hotmail.com
From: deb on 11 May 2010 15:00 access 2003 Main form is called f018ContrPerfEmissGua has the fields UnitNo(number) and ckGEN(yes/No ckbox) subform is called f018ContrPerfEmissGuaDetails (linked by ContrPerfEmissGuaID) User will select the unitNo, and check ckGEN(if it is a GEN) from the main form When the user tries to enter data into the subform and it already has a record with the same unitNo and ckGEN values then undo the current record, display msg and move to original record. I tried, but cannot figure it out. see below On the subform - before update I tried... I think the stLinkCriteria and dcount are wrong. Private Sub Form_BeforeUpdate(Cancel As Integer) Dim UID As String Dim GID As String Dim stLinkCriteria As String Dim rsc As DAO.Recordset Set rsc = Me.RecordsetClone UID = Me.parent.UnitNo.Value GID = Me.parent.ckGEN.Value stLinkCriteria = "f018ContrPerfEmissGua.UnitNo=" & UID & " and f018ContrPerfEmissGua.ckGEN= " & GID 'Check t81ContrPerfEmissGuaDetails table for duplicate UnitID(number) and ckGEN(yes/no) If DCount("me.parent.UnitNo", " & me.parent.ckGEN" "t81ContrPerfEmissGuaDetails", _ stLinkCriteria) > 0 Then 'Undo duplicate entry Me.Undo 'Message box warning of duplication MsgBox "Warning Unit or Unit Gen " _ & UID & " has already been entered." _ & vbCr & vbCr & "You will now been taken to the record.", _ vbInformation, "Duplicate Information" 'Go to record of original record rsc.FindFirst stLinkCriteria Me.Bookmark = rsc.Bookmark End If Set rsc = Nothing End Sub Thanks in advance!!! -- deb
From: Jeff Boyce on 11 May 2010 15:03 Is there a chance you could simply add an index (unique, no duplicates) to the table that holds those records, ensuring that no duplicates (on those fields) could be added? Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "deb" <deb(a)discussions.microsoft.com> wrote in message news:68AE0F73-88E5-48AE-90DA-4BDC1C320650(a)microsoft.com... > access 2003 > Main form is called f018ContrPerfEmissGua has the fields UnitNo(number) > and > ckGEN(yes/No ckbox) > subform is called f018ContrPerfEmissGuaDetails (linked by > ContrPerfEmissGuaID) > > User will select the unitNo, and check ckGEN(if it is a GEN) from the main > form > When the user tries to enter data into the subform and it already has a > record with the same unitNo and ckGEN values then undo the current record, > display msg and move to original record. > > I tried, but cannot figure it out. see below > > On the subform - before update I tried... > I think the stLinkCriteria and dcount are wrong. > > Private Sub Form_BeforeUpdate(Cancel As Integer) > Dim UID As String > Dim GID As String > Dim stLinkCriteria As String > Dim rsc As DAO.Recordset > > Set rsc = Me.RecordsetClone > > UID = Me.parent.UnitNo.Value > GID = Me.parent.ckGEN.Value > > stLinkCriteria = "f018ContrPerfEmissGua.UnitNo=" & UID & " and > f018ContrPerfEmissGua.ckGEN= " & GID > > 'Check t81ContrPerfEmissGuaDetails table for duplicate UnitID(number) > and ckGEN(yes/no) > If DCount("me.parent.UnitNo", " & me.parent.ckGEN" > "t81ContrPerfEmissGuaDetails", _ > stLinkCriteria) > 0 Then > 'Undo duplicate entry > Me.Undo > 'Message box warning of duplication > MsgBox "Warning Unit or Unit Gen " _ > & UID & " has already been entered." _ > & vbCr & vbCr & "You will now been taken to the record.", _ > vbInformation, "Duplicate Information" > 'Go to record of original record > rsc.FindFirst stLinkCriteria > Me.Bookmark = rsc.Bookmark > End If > > Set rsc = Nothing > > End Sub > > Thanks in advance!!! > -- > deb
From: deb on 11 May 2010 15:45 No, Need to have the user taken to the original record so they can input the data in the correct place. Thank you for your idea. -- deb "Jeff Boyce" wrote: > Is there a chance you could simply add an index (unique, no duplicates) to > the table that holds those records, ensuring that no duplicates (on those > fields) could be added? > > Regards > > Jeff Boyce > Microsoft Access MVP > > -- > Disclaimer: This author may have received products and services mentioned > in this post. Mention and/or description of a product or service herein > does not constitute endorsement thereof. > > Any code or pseudocode included in this post is offered "as is", with no > guarantee as to suitability. > > You can thank the FTC of the USA for making this disclaimer > possible/necessary. > > "deb" <deb(a)discussions.microsoft.com> wrote in message > news:68AE0F73-88E5-48AE-90DA-4BDC1C320650(a)microsoft.com... > > access 2003 > > Main form is called f018ContrPerfEmissGua has the fields UnitNo(number) > > and > > ckGEN(yes/No ckbox) > > subform is called f018ContrPerfEmissGuaDetails (linked by > > ContrPerfEmissGuaID) > > > > User will select the unitNo, and check ckGEN(if it is a GEN) from the main > > form > > When the user tries to enter data into the subform and it already has a > > record with the same unitNo and ckGEN values then undo the current record, > > display msg and move to original record. > > > > I tried, but cannot figure it out. see below > > > > On the subform - before update I tried... > > I think the stLinkCriteria and dcount are wrong. > > > > Private Sub Form_BeforeUpdate(Cancel As Integer) > > Dim UID As String > > Dim GID As String > > Dim stLinkCriteria As String > > Dim rsc As DAO.Recordset > > > > Set rsc = Me.RecordsetClone > > > > UID = Me.parent.UnitNo.Value > > GID = Me.parent.ckGEN.Value > > > > stLinkCriteria = "f018ContrPerfEmissGua.UnitNo=" & UID & " and > > f018ContrPerfEmissGua.ckGEN= " & GID > > > > 'Check t81ContrPerfEmissGuaDetails table for duplicate UnitID(number) > > and ckGEN(yes/no) > > If DCount("me.parent.UnitNo", " & me.parent.ckGEN" > > "t81ContrPerfEmissGuaDetails", _ > > stLinkCriteria) > 0 Then > > 'Undo duplicate entry > > Me.Undo > > 'Message box warning of duplication > > MsgBox "Warning Unit or Unit Gen " _ > > & UID & " has already been entered." _ > > & vbCr & vbCr & "You will now been taken to the record.", _ > > vbInformation, "Duplicate Information" > > 'Go to record of original record > > rsc.FindFirst stLinkCriteria > > Me.Bookmark = rsc.Bookmark > > End If > > > > Set rsc = Nothing > > > > End Sub > > > > Thanks in advance!!! > > -- > > deb > > > . >
From: Jeff Boyce on 11 May 2010 18:30 This may be bass-ackwards to what you are trying to do, but another approach is to provide the user a way to look up existing records, and to add a new one if an existing one isn't found. Good luck on your project. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "deb" <deb(a)discussions.microsoft.com> wrote in message news:ED20910E-483E-436A-A103-A34572E62220(a)microsoft.com... > > > No, Need to have the user taken to the original record so they can input > the data in the correct place. > > Thank you for your idea. > -- > deb > > > "Jeff Boyce" wrote: > >> Is there a chance you could simply add an index (unique, no duplicates) >> to >> the table that holds those records, ensuring that no duplicates (on those >> fields) could be added? >> >> Regards >> >> Jeff Boyce >> Microsoft Access MVP >> >> -- >> Disclaimer: This author may have received products and services mentioned >> in this post. Mention and/or description of a product or service herein >> does not constitute endorsement thereof. >> >> Any code or pseudocode included in this post is offered "as is", with no >> guarantee as to suitability. >> >> You can thank the FTC of the USA for making this disclaimer >> possible/necessary. >> >> "deb" <deb(a)discussions.microsoft.com> wrote in message >> news:68AE0F73-88E5-48AE-90DA-4BDC1C320650(a)microsoft.com... >> > access 2003 >> > Main form is called f018ContrPerfEmissGua has the fields UnitNo(number) >> > and >> > ckGEN(yes/No ckbox) >> > subform is called f018ContrPerfEmissGuaDetails (linked by >> > ContrPerfEmissGuaID) >> > >> > User will select the unitNo, and check ckGEN(if it is a GEN) from the >> > main >> > form >> > When the user tries to enter data into the subform and it already has a >> > record with the same unitNo and ckGEN values then undo the current >> > record, >> > display msg and move to original record. >> > >> > I tried, but cannot figure it out. see below >> > >> > On the subform - before update I tried... >> > I think the stLinkCriteria and dcount are wrong. >> > >> > Private Sub Form_BeforeUpdate(Cancel As Integer) >> > Dim UID As String >> > Dim GID As String >> > Dim stLinkCriteria As String >> > Dim rsc As DAO.Recordset >> > >> > Set rsc = Me.RecordsetClone >> > >> > UID = Me.parent.UnitNo.Value >> > GID = Me.parent.ckGEN.Value >> > >> > stLinkCriteria = "f018ContrPerfEmissGua.UnitNo=" & UID & " and >> > f018ContrPerfEmissGua.ckGEN= " & GID >> > >> > 'Check t81ContrPerfEmissGuaDetails table for duplicate >> > UnitID(number) >> > and ckGEN(yes/no) >> > If DCount("me.parent.UnitNo", " & me.parent.ckGEN" >> > "t81ContrPerfEmissGuaDetails", _ >> > stLinkCriteria) > 0 Then >> > 'Undo duplicate entry >> > Me.Undo >> > 'Message box warning of duplication >> > MsgBox "Warning Unit or Unit Gen " _ >> > & UID & " has already been entered." _ >> > & vbCr & vbCr & "You will now been taken to the record.", _ >> > vbInformation, "Duplicate Information" >> > 'Go to record of original record >> > rsc.FindFirst stLinkCriteria >> > Me.Bookmark = rsc.Bookmark >> > End If >> > >> > Set rsc = Nothing >> > >> > End Sub >> > >> > Thanks in advance!!! >> > -- >> > deb >> >> >> . >>
|
Pages: 1 Prev: Format Next: vehbi22@hotmail.com |