checking a query when marking a checkbox

[vt800c]

I have a form that contains employee data. When a person leaves, I don't want to delete them, but I want to mark it as archived (using a checkbox linked to a boolean field). When I click on the checkbox, I want to run a query on a related table, and if any rows are returned, not allow the box to be checked. I want to present a message box to the user telling them why that person can't be archived, to give them the opportunity to rectify the outstanding discrepancies.

So how do I fire the query and get a return (entier a number, or a null set or ANYthing that I can use as a descriminator)?



All of you have helped me get up to speed with this and I've used alot of your code and methods. My project is growing nicely and with your assistance I'm creating a very robust and usable application.

Thank you

[NoellaG]

Hi,

you can put some code in the beforeUpdate event of the checkbox control resembling:

If me.chkMyCheckBox = True then
lngRecords = DSum("Name_PK_Field","Tablename_related_table","FK _Field = " & me.ControlNamePK_field")
If lngrecords > 0 then
Msgbox .................................
cancel = true
me.undo
end if
end if

Success
NG

[vt800c]

BINGO! that was EXACTLY what I neede. Thanks!