if duplicate goto record

  • Thread starter Thread starter deb
  • Start date Start date
D

deb

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!!!
 
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.
 
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.
 
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.
 
Back
Top