Error message to display on entry of duplicate value with two fields as index

[jpkeller55]

Hello,
I was using the following code on a field (ItemCode) that was indexed to prevent duplication of records. The intent is that the user will get an error message that a duplicate exists before they enter all the data for the record and get the built in error message that Access 2003 provides when an index violation has occurred.

Private Sub ItemCode_BeforeUpdate(Cancel As Integer)
 Dim Answer As Variant
 Answer = DLookup("[ItemCode]", "tblQuestions", "[ItemCode] = '" & Me.ItemCode & "'")
 If Not IsNull(Answer) Then
 MsgBox "Item Code already exists" & vbCrLf & "Please enter unique Item Code.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
 
 Cancel = True
 Me.ItemCode.Undo
 
 Else:
 End If
End Sub

Now, the index for this is based on two fields (ItemCode and Question Group). I would like to display the same message before update but don't know how to include the second field in the syntax.

Anybody know a solution?

Thanks,

[jpkeller55]

I thought I might have it figured out by using the following code but get a type mismatch error. Am I heading down the right path?

Private Sub ItemCode_BeforeUpdate(Cancel As Integer) 
Dim Answer As Variant
Dim strWhere As String
strWhere = ("[ItemCode] = '" & Me.ItemCode & "'") And ("[Question Group] = '" & Me.Question_Group & "'")
 Answer = DLookup("[ItemCode]", "tblQuestions", strWhere)
 If Not IsNull(Answer) Then
 MsgBox "Item Code already exists" & vbCrLf & "Please enter unique Item Code.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
 
 Cancel = True
 Me.ItemCode.Undo
 
 Else:
 End If
End Sub

[jpkeller55]

Figured it out...syntax error. The darn single quote double quote...when to use messed me up again. Just had to mess around with it a bit.

Private Sub ItemCode_BeforeUpdate(Cancel As Integer) 
Dim Answer As Variant
Dim strWhere As String
strWhere = "[ItemCode] = '" & Me.ItemCode & "' And [Question Group] = '" & Me.Question_Group & "'"
Answer = DLookup("[ItemCode]", "tblQuestions", strWhere)
If Not IsNull(Answer) Then
MsgBox "Item Code already exists" & vbCrLf & "Please enter unique Item Code.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
 
Cancel = True
Me.ItemCode.Undo
 
Else:
End If
End Sub

[hawkdriver]

Good stuff, thanks Jim.

[veronica]

Figured it out...syntax error. The darn single quote double quote...when to use messed me up again. Just had to mess around with it a bit.

Private Sub ItemCode_BeforeUpdate(Cancel As Integer) 
Dim Answer As Variant
Dim strWhere As String
strWhere = "[ItemCode] = '" & Me.ItemCode & "' And [Question Group] = '" & Me.Question_Group & "'"
Answer = DLookup("[ItemCode]", "tblQuestions", strWhere)
If Not IsNull(Answer) Then
MsgBox "Item Code already exists" & vbCrLf & "Please enter unique Item Code.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
 
Cancel = True
Me.ItemCode.Undo
 
Else:
End If
End Sub

Hi,

A co-worker was building a database for our office, but went into labor and left it unfinished. I am trying to do essentially what is described here using a composite index of two number fields [IndicatorMonth] and [IndicatorYear] and tried to copy the code above into a before update event for the field [IndicatorMonth] (the second of the two fields that are entered, but am getting runtime errors. The code I tried is below.

Private Sub IndicatorMonth_BeforeUpdate(Cancel As Integer)
Dim Answer As Variant
Dim strWhere As String
strWhere = "[IndicatorMonth] = '" & Me.IndicatorMonth & "' And [IndicatorYear] = '" & Me.IndicatorYear & "'"
Answer = DLookup("[IndicatorMonth]", "CDCD", strWhere)
If Not IsNull(Answer) Then
MsgBox "A record for this month already exists" & vbCrLf & "Try again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"

Cancel = True
Me.IndicatorMonth.Undo

Else:
End If
End Sub


Help??