Check for existing Records on Delete

[Lance]

Hello,

I have two tables, tblOrg, and tblPrograms. I have a form frmOrg, which
has all of the Organization data. I also have a Delete button, so you
can delete an organization from the database. I would like to create a
check to see if the OrgID from tblOrg exists in tblPrograms first,
however, so that I can give my users an error message saying they can't
delete an Organization that has existing data elsewhere. What is the
best way to go about this?

Thanks,

Lance

 

[SusanV]

Assuming the field containing the OrgID in tblPrograms is called OrgID and
there is a control on the form called OrgID which holds this Organizations
ID (Me.OrgID):

If IsNull(DLookup("OrgID", "tblPrograms", "[OrgID] = 'me.OrgID'")) Then
docmd.runsql "delete whatever from wherever"
Else
MsgBox "You can't delete an Organization while data exists!"
End If

 

[Chris]

You could also create some RI between the tables but without the cascade
delete option. Access will then prevent you from deleting the parent row if a
child exists
--
HTH

Chris

Assuming the field containing the OrgID in tblPrograms is called OrgID and
there is a control on the form called OrgID which holds this Organizations
ID (Me.OrgID):

If IsNull(DLookup("OrgID", "tblPrograms", "[OrgID] = 'me.OrgID'")) Then
docmd.runsql "delete whatever from wherever"
Else
MsgBox "You can't delete an Organization while data exists!"
End If

--
hth,
SusanV

Hello,

I have two tables, tblOrg, and tblPrograms. I have a form frmOrg, which
has all of the Organization data. I also have a Delete button, so you
can delete an organization from the database. I would like to create a
check to see if the OrgID from tblOrg exists in tblPrograms first,
however, so that I can give my users an error message saying they can't
delete an Organization that has existing data elsewhere. What is the
best way to go about this?

Thanks,

Lance