Help with Dcount mulitple criteria VBA / Access

[ozchadl]

I have a horse database with the following fields:
Horse_id
Name
Sire
Dam
Born
Died
Gender

Sample Data
-----------------
Horse_id - 10
Name - Black
Sire - 20
Dam - 30
Born - 2000
Died - 0
Gender - S


What I am trying to do is on the form (frmHorses) when you click the "rubbish bin" to delete that record, it checks the Horse_id with all the records of Sire and Dam.

This is so that the Horse can not be deleted if it is the Sire or Dam of an existing horse.

However the code is not letting me delete any horses,

For the sample data above this should delete this record, but it does not.

Thanks for you help

This is the code for "rubbish bin" (delete record)

If DCount("Sire", "horse", "horse_id =" & Me.txtHorse_id. Value) >= 0 Then
If DCount("Dam", "horse", "horse_id =" & Me.txtHorse_id. Value) >= 1 Then
End If

MsgBox "A horse can not be deleted if it is the Sire or Dam of an existing horse"

Else
DoCmd.DoMenuIte m acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuIte m acFormBar, acEditMenu, 6, , acMenuVer70


End If

[TheSmileyCoder]

If DCount("Sire", "horse", "horse_id =" & Me.txtHorse_id.Value) >= 0 Then
If DCount("Dam", "horse", "horse_id =" & Me.txtHorse_id.Value) >= 1 Then

I am a bit confused. The >=0 will always be true, and from what your telling me thats now what you want.

Im going to guess that Sire is the ID of the "father" horse (sorry, my field of expertise is certainly not horses). So when you do a dcount, with criteria

"horse_id =" & Me.txtHorse_id.Value)

your actually checking whether the current horse has a father (i hope it does....)

What your looking to see is if there are any horses that have the current horse as father. This should accomplish just that:

If Dcount("*","horse","Sire=" & me.txtHorse_ID & " OR Dam=" & me.txtHorseID)>0 then
  Msgbox "NO dont delete me! think of the kiddy horses!"
  exit sub
else
  'Delete record
end If

I hope this helps you.

PS:

• Its a good idea to prefix your table like so: tbl_Horse. It matters little in design view of access, but when writing codes like this, it helps to keep it clear, what your doing.
• Personally i prefer to use:
  

  Docmd.Runcommand acCmdDeleteRecord

  as I find it alot easier to read the code later on, when revisiting the code.

[ozchadl]

It now works.

The correct code is:
If DCount("*", "horse", "Sire=" & Me.txtHorse_id. Value & " OR Dam=" & Me.txtHorse_id. Value) > 0 Then
MsgBox "A horse can not be deleted if it is the Sire or Dam of an existing horse"
Exit Sub
Else
DoCmd.DoMenuIte m acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuIte m acFormBar, acEditMenu, 6, , acMenuVer70
End If