Error Checking Duplicates on SubForm

[Ruth]

I want to simply check for duplicates to a primary key on a subform
before I get the ugly Microsoft Access error. I'm using the Before
Update event:

If DLookup(("[AdultID]), "tblAdultDinner", "[AdultID] = " &
Me.cboAdultNameID) Then
DoCmd.Beep
MsgBox "You've already entered that person for this meal."
Me.cboAdultNameID.Undo
Cancel = True

My problem is that the primary key is a combination of 2 fields on
this table: AdultID and MealDate. I want to check that the same
person is not paying for dinner twice on the same night. The code
above just checks to see if the Adult's ID is already in the table,
regardless of the Meal Date.

The meal date textbox is on the main form, and the adultID combo box
is on the subform.

I haven't been able to figure out how to make this code work to look
up both AdultID and MealDate. Is this possible without making the
code much more complicated?

Thanks, Ruth

 

[Allen Browne]

The 3rd argument for DLookup() has to look like the WHERE clause of a query:

Dim strWhere As String
If Not (IsNull(Me.cboAdultNameID) Or IsNull(Me.Parent!MealDate)) Then
strWhere = "([AdultID] = " & Me.cboAdultNameID & ") AND ([MealDate] = "
_
& Format(Me.Parent!MealDate, "\#mm\/dd\/yyyy\#" & ")
If Not IsNull(DLookup("[AdultID]), "tblAdultDinner", strWhere)) Then
MsgBox ...
End If
End With

This approach has a problem: if you edit an existing record where the values
have not changed, the DLookup() will find the existing record in the table
and complain that it is a duplicate, with in fact it is itself.

 

[ruthl]

Thank you. That worked well. Ruth