Form Update on Before Update event...I know it might sound weird

[petrosky]

Hi,

I have been trying to find a way around this little problem for some time now and am almost there...

I have a bound text box [TheirClaimNumber] and if a user enters in a duplicate entry I have this code so far which works only to a certain degree.

The form Duplicates recordsource is a duplicate query based on the table in question.

My problem is if the user is entering the very first duplicate my Dlookup triggers the MSGBOX but when I click Yes the form comes up blank.

Help please!

Here is what I have so far...please be kind.


Private Sub TheirClaimNumber_BeforeUpdate(Cancel As Integer)

If (Not IsNull(DLookup("[TheirClaimNumber]", "tblClaimants", _
"[TheirClaimNumber] ='" & Me!TheirClaimNumber & "'&quot)) Then
If MsgBox("Warning claim number has already been entered in the database" _
& vbNewLine & "Click Yes to View the Existing Claim" & vbNewLine & _
"Click No to Add this as a New Claim", vbYesNo, "Verify Duplicate Claim&quot = vbYes Then

DoCmd.OpenForm "Duplicates", acNormal, , , acFormReadOnly

End If

End If

End Sub

Peter Remember- It's nice to be important,
but it's important to be nice

 

[lblastick]

I think you just might need to like the form duplicates to the data you want to review. Here's a revision of your code to try. Hope it helps



Private Sub TheirClaimNumber_BeforeUpdate(Cancel As Integer)
Dim stLinkCriteria As String
stLinkCriteria = "[TheirClaimNumber]=" & Me![TheirClaimNumber]
If (Not IsNull(DLookup("[TheirClaimNumber]", "tblClaimants", _
"[TheirClaimNumber] ='" & Me!TheirClaimNumber & "'&quot)) Then
If MsgBox("Warning claim number has already been entered in the database" _
& vbNewLine & "Click Yes to View the Existing Claim" & vbNewLine & _
"Click No to Add this as a New Claim", vbYesNo, "Verify Duplicate Claim&quot = vbYes Then

DoCmd.OpenForm "Duplicates", acNormal, , , acFormReadOnly, stLinkCriteria

End If

 

[petrosky]

Hi,

Many thanks for trying to help.

I think the problem is that the query for the Form duplicates runs correctly but because the data in the original form hasn't been saved yet...the recordset comes back as no duplicate entry....

Here is the criteria for my duplicate query (searchs for a duplicate based on Me!TheirClaimNumber)

In (SELECT [TheirClaimNumber] FROM [tblClaimants] As Tmp GROUP BY [TheirClaimNumber] HAVING Count(*)>1 and [TheirClaimNumber] = Forms!instrial![TheirClaimNumber])


I hope someone can help me or at suggest perhaps a better approach.

petrosky
Remember- It's nice to be important,
but it's important to be nice