Checking for a filtered duplicate value on a form before closing

[duthiedon]

Solved: Checking for a filtered duplicate value on a form before closing

Below is some code that I'm struggling with and I get an error message "Type Mismatch". Have tried several different ways but had other issues, so I'm posting to see if anyone can help. Perhaps I'm missing something or just need new glasses.

Description: The database contains multiple projects and the subset of each project is a release. The "PublishedNumber" can't be a duplicate within a release, but it can be a duplicate within the table where the data is stored. There can however be multiple occurrences of 0.

Here's my code, thanks in advance for your help

Dim stLinkCriteriaRelease As String
Dim stLinkPublishedNumber As String
Dim DuplicateNumber As Variant

stLinkCriteriaRelease = "[AssociatedRelease]=" & [AssociatedRelease]
stLinkPublishedNumber = "[PublishedNumber]=" & [PublishedNumber]

DuplicateNumber = DLookup("[ID-InternalNum]", "tqry_ManageQuestionsAnswersProc", stLinkCriteriaRelease And stLinkPublishedNumber)

If Me.PublishedNumber = 0 Then

Exit Sub

ElseIf Not IsNull(DuplicateNumber) Then

MsgBox ("WARNING - The published number you have chosen is a duplicate. Either select another published number or set the number to 0.")

Cancel = True

Exit Sub

End If

 

[pbaldy]

More like

stLinkCriteriaRelease & " And " & stLinkPublishedNumber

 

[duthiedon]

Thanks pbaldy, I think I need new glasses I'm using the code to check before closing the form, and I ran into another problem where the DuplicateNumber was not null because the record itself shows up in the Dlookup, so I included another filter to filter out the acceptable record. Here's my final code:

Dim stLinkCriteriaRelease As String
Dim stLinkPublishedNumber As String
Dim stLinkNotInterNum As String
Dim DuplicateNumber As Variant

stLinkCriteriaRelease = "[AssociatedRelease]=" & [AssociatedRelease]
stLinkPublishedNumber = "[PublishedNumber]=" & [PublishedNumber]
stLinkNotInterNum = "[ID-InternalNum]<>" & ID_InternalNum

DuplicateNumber = DLookup("[ID-InternalNum]", "tqry_ManageQuestionsAnswersProc", stLinkCriteriaRelease & " And " & stLinkPublishedNumber & " And " & stLinkNotInterNum)

If Me.PublishedNumber = 0 Then

Exit Sub

ElseIf Not IsNull(DuplicateNumber) Then

MsgBox ("WARNING - The published number you have chosen is a duplicate. Either select another published number or set the number to 0.")

Cancel = True

Exit Sub

End If

 

[pbaldy]

Happy to help!