Runtime Error 13 on preventing a duplicate value with 2 fields?

[d9pierce1]

Hi all,
I am trying to prevent a duplicate value on a subform if a dup value is entered and if it has the same ID field as the previous subform ID.
Code is:

Private Sub Form_BeforeUpdate(Cancel As Integer)


If DLookup("[SystemPrimaryRole]", "tblSystemPrimaryRole", [SystemPrimaryRole] > 0 And [SystemPrimaryIndustryID] = Me.TxtSystemPrimaryIndustryID) Then
    MsgBox "This Primary Role Value has all ready been entered"
    Me.Undo
    Exit Sub
    End If
End Sub

Data base is attached, opens to form in question. If you type in Lodging in second subform, it should not allow it but getting error 13?


Can I get some assistance witht this please?
Thannks
Dave

SystemIndustryData.zip

[June7]

DLookup is missing quote marks and concatenation in the WHERE CONDITION.

DLookup("[SystemPrimaryRole]", "tblSystemPrimaryRole", "[SystemPrimaryRole] > 0 And [SystemPrimaryIndustryID] = " & Me.TxtSystemPrimaryIndustryID)


BTW, "all ready" should be "already".

[d9pierce1]

Hi June7
This is getting a syntax error

[d9pierce1]

Hi June7
I am getting error on this? Runtime 3464
If DLookup("[SystemPrimaryRole]", "tblSystemPrimaryRole", "[SystemPrimaryRole] > 0 And [SystemPrimaryIndustryID] = " & Me.TxtSystemPrimaryIndustryID) Then
Data Type Mismatch

[d9pierce1]

HI June7
Thank you, I got it to work!
Again, thank you very much
If DLookup("[SystemPrimaryRole]", "tblSystemPrimaryRole", "[SystemPrimaryRole] And [SystemPrimaryIndustryID] = " & Me.TxtSystemPrimaryIndustryID) > 0 Then
Dave

[June7]

DLookup will return Null if there is no match. Most would use: If Not IsNull(DLookup(...)) Then

DCount is the only domain aggregate that will return 0 if there is no match.

[Welshgasman]

Hi June7
This is getting a syntax error

Compile your code before trying to use it.