Check if data already entered based on three fields

[tscotti]

Hello All,

I'm using the code below to verify if a Product Number has already been entered into the form.

Private Sub ProductID_BeforeUpdate(Cancel As Integer)
Dim varX As Variant
varX = DLookup("[ProductID]", "tblProducts", "[ProductID] = '" & Forms!frmProducts.[ProductID] & "'")
If Not IsNull(varX) Then
MsgBox [ProductID].Value & " already exists as a product number!"
Me.Undo
Cancel = True
Me.Recordset.FindFirst "ProductID='" & varX & "'"
Else
'do nothing!
End If
End Sub​

I'm having trouble, however, adapting it to three primary key fields. For example, I need to verify if the same "Product#", "Description" and "Colour" has already been entered - not only the "Product#".

Thanks, in advance, for all your help.

 

[petehilljnr]

Create a function that checks all three fields and call it on the before update event of each of Product_ID, Description and Colour.

This should work, just not sure about how the Cancelling of the event will affect the recordset movements.

Regards,
Pete.

Function CHECK_PRODUCT() As Boolean 'return true if combination exists else false

Dim sCriteria as String

sCriteria = "[ProductID] = '" & Me.[ProductID] & "'" & _
               " AND [Description] = '" & Me.[Description] & "'" & _
               " AND [Colour] = '" & Me.[Colour] & "'"

If DCount("[ProductID]","tblProducts",sCriteria) <> 0 Then
     'This product exists
     Msgbox "This product combination already exists"
     Me.Undo
     Me.Recordset.FindFirst sCriteria
     CHECK_PRODUCT = True
Else
     CHECK_PRODUCT = False
End If

End Function

Private Sub ProductID_BeforeUpdate(Cancel As Integer)

If CHECK_PRODUCT() Then
     Cancel = True
End If

End Sub

Private Sub Description_BeforeUpdate(Cancel As Integer)

If CHECK_PRODUCT() Then
     Cancel = True
End If

End Sub

Private Sub Colour_BeforeUpdate(Cancel As Integer)

If CHECK_PRODUCT() Then
     Cancel = True
End If

End Sub

 

[tscotti]

Thank you PeteHillJnr for your reply - appreciate it.

I've tried your code but I get the following message:

Run-time error "2465' MS Access can't find the field "|" referred to in your expression.
​

Which is weird because there is no "|" anywhere near this code!

When I click on the "Debug" button, it highlights this section:

sCriteria = "[ProductID] = '" & Me.[ProductID] & "'" & _
" AND [Description] = '" & Me.[Description] & "'" & _
" AND [Colour] = '" & Me.[Colour] & "'"​

Again thanks in advance for your or anyone's help.

 

[tscotti]

Anyone out there that can help on this?

I'm stuck!

 

[petehilljnr]

Do all three of those fields exist on your form?

 

[Bat17]

Private Sub Description_BeforeUpdate(Cancel As Integer)

If CHECK_PRODUCT() Then
     Cancel = True
End If

End Sub

Private Sub Colour_BeforeUpdate(Cancel As Integer)

If CHECK_PRODUCT() Then
     Cancel = True
End If

End Sub

I think that the problem is here.
When you are calling the code from Description the colour will still be null. At the start of the function you need to check that thall three fields have been filld in and if not exit the function with out checking.

HTH

Peter