Change Campus to Inactive When There Are No Programs Listed.

[DoveArrow]

I have a form for campuses, with a subform that lists all of the
advisors for that particular campus. What I want to have happen is, if
after deleting an advisor from the subform, I want the computer to
automatically place a check mark in a box marked "Inactive" on the
main form, but only if there are no other active advisors for that
location. I've tried a number of different things, but nothing is
working the way I want. Suggestions?

 

[KARL DEWEY]

Try this after backing up your database ---
UPDATE [campuses] LEFT JOIN [advisors] ON [campuses].campusID =
[advisors].campusID SET [campuses].Inactive= -1
WHERE ((([advisors].campusID) Is Null));

 

[DoveArrow]

Try this after backing up your database ---
UPDATE [campuses] LEFT JOIN [advisors] ON [campuses].campusID =
[advisors].campusID SET [campuses].Inactive= -1
WHERE ((([advisors].campusID) Is Null));

--
KARL DEWEY
Build a little - Test a little

I have a form for campuses, with a subform that lists all of the
advisors for that particular campus. What I want to have happen is, if
after deleting an advisor from the subform, I want the computer to
automatically place a check mark in a box marked "Inactive" on the
main form, but only if there are no other active advisors for that
location. I've tried a number of different things, but nothing is
working the way I want. Suggestions?- Hide quoted text -

- Show quoted text -

I tried putting together an Update Query very similar to this, called
qupdInactivetblLocations, and wrote the following code to have this
querey, and another query, called qupdInactivetblAdvisors, run
whenever the delete button on my subform was clicked:

Private Sub Delete_Location_Advisor_Click()
On Error GoTo Err_Delete_Location_Advisor_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.SetWarnings False
DoCmd.OpenQuery "qupdInactivetblLocations"
DoCmd.OpenQuery "qupdInactivetblAdvisors"
DoCmd.RepaintObject acForm, "frmLocationAdvisors"
DoCmd.SetWarnings True

Exit_Delete_Location_Advisor_Click:
Exit Sub

Err_Delete_Location_Advisor_Click:
MsgBox Err.Description
Resume Exit_Delete_Location_Advisor_Click

End Sub

However, it only seems to work when the form isn't Dirty. At least,
that's what I'm guessing is happening. The line of code works when I
initially open the form but it doesn't work if I, say, delete a
program add a new one, and then try to delete that new program as well
(not that I can really think of a reason as to why you would want to
do this, other than data entry error, but still).

Now one thing I have noticed is that it when I click on the delete
button, it actually updates the table in the background, but doesn't
do anything to the form. Also, the table that was been updated reverts
back to showing the campus as active whenever I close the form. So it
seems to be working (sort've), but just not the way I want it to.