Use Query Results onLoad of Form to conduct an Action

[padlocked17]

Alright. I've tried searching, but to be honest, I'm not even sure what to search for.

I want to create some VBA that onLoad of the opening form of the database, it will take info from a query that looks for data entered for a student. It will be looking to see if there is data in two date fields. If it finds data in both fields, then I want to make a change to the EnrollmentID row of another table to changed the graduated column from No to Yes.

To expand on that, I'll want to check each ClassID row and if all of the associated students of that class have the column Graduated checked as yes, it will place the date in the date column for that particular class.

This is essentially my way of auto-archiving data as the combo box selections on my forms don't allow for class data to be shown if there is a Closed Date entered for the class and you won't be able to enter more test results for a student if the Graduated Column is checked in the Enrollment table (associates memberID, ClassID, CourseID into a single table)

Any insight on how to do this would be greatly apprecaited.

 

[Rich]

Use an Update query

 

[padlocked17]

Would I use a normal query to find the criteria and create an If Else statement and then use an Update query to change values?

 

[padlocked17]

Or just use the criteria in the Update Query?

 

[Rich]

Or just use the criteria in the Update Query?

Yes and check that the date field is null to prevent the update running repeatedly

 

[padlocked17]

I've got one of the queries figured out. Thanks for the pointer.

My other issue is lying within the attached images of a query.

I'm trying to take a look at all students in a particular class. If all students have Graduated, I want to enter the current date in the Class ClosedDate field.

The problem as you can see in the image is that I can't figure out how to have the query analyze each class automatically.

 

[padlocked17]

Really what I'm trying to do is simply design a query that will look at each unique class name and the associated records with each.