In a form how to check the value in a related table before deleting a field

[equestrian]

I have a form frmEntryTabbed (allows the user to enter fields in tblEntry) and a subform frmEntry_subform (allows the user to enter fields in tblShowClassEntry). I have a button on frmEntryTabbed that deletes the current record in tblEntry.

 
Private Sub cboDeleteEntry_Click()
On Error GoTo Err_cboDeleteEntry_Click







 
 
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord
 
Exit_cboDeleteEntry_Click:
    Exit Sub
 
Err_cboDeleteEntry_Click:
    MsgBox Err.Description
    Resume Exit_cboDeleteEntry_Click
   
End Sub

Before deleting the record I would like to check the value in a related table. The basic layout of the tables is as follows:

tblEntry
EntryNum_pk
RiderNum_fk
HorseNum_fk

tblShowClassEntry
EntryNum_fk
PlacingNum_fk

tblPlacement
PlacementNum_pk
PlacementNumerical
Placement

For each entry there may be multiple classes entered. If any of the classes entered has a value in PlacinNum_fk not equal to 7, I want to display a message and not allow the record to be deleted.

Any help will be appreciated. Thank you

[June7]

Maybe do a DLookup()? Might want to use the BeforeDelete event to do this validation.

[equestrian]

Thank you for the help. I could not get the DLookup to work. It returns the first value and there could be multiple classes entered. The first may or may not be equal to 7. It did give me another idea. I put 2 text boxes with visible marked as no on the form. One has the minimum value of PlacingNum_fk and the other has the maximum value of PlacingNum_fk. if either of these are not equal to 7, I display a message and do not delete the record.

[June7]

Then the DLookup did not have specific enough search criteria.

Glad you found a solution.