Full Version: limiting population of a control (field) on a form based on the
UtterAccess Forums > Microsoft® Access > Access Forms
GPSue
I'm creating a database to track minor behavior infractions for a middle school (chewing gum, rude behavior, etc.) and I've set up several tables which contains fields for student name, teacher name, infraction, points, etc. Every time a student chews gum, he receives an infraction point. When a student reaches 5 points, he/she is referred to the assistant principal. Putting any personal opinions aside... sad.gif
o I have an Infractions table which holds the infractions. I also have an incident detail form which allows the user to add the name of the student, the teacher recording it, and the infraction name, the date and the point (one per infraction) into the table when an infraction is recorded.
Now want to set up a form with a control which is filtered based on a query. I want to prevent the addition of more infractions for those with more than 4 points, so my ideas are to allow a teacher to click a combo box on the form to choose a student's name for the incident, but only have students in the list who have less than 5 infractions. If the student has 5 or more points, I would like the teachers to be prevented from making an incident entry.
I've tried creating my form based on the query and incident table, but receive an error message. It seems like I'm creating something like a circular reference because it can't sum the points and produce a list of names meeting the criteria at the same time it is looking up all the entries in the incidents table for the sum. Can I control the sequence of the events with some additional queries, macros or command buttons? Something with onopen? Refresh?
I'm a beginner here but will do my best to be a fast learner!
Thanks for your help! A quick response is greatly appreciated. I'm trying to get this done in a few days.
cpetermann
GPSue,
Could you make a copy of your db, compact & repair it, then zip & attach it,please?
It will be easier to offer suggestions if we can see the whole picture!
Looking forward to helping,
cynthia
GPSue
I have it "split" so that multiple users can use it. What would be the process to compact and repair it?
thanks!
cpetermann
GPSue,
Select Tools from Tool Bar, Database Utilies
Compact & Repair.
Just make a copy of your BE, --remove any sensitive data--like addresses, phone, email, etc.
but leave enough data in each table so that we can see how it works.
HTH,
Cynthia
GPSue
Attempted to attach a file, but received an error message that it was not one of the accepted formats: .jpg, .png, etc.
That do you suggest?
cpetermann
Try this
UA How to attach a file
cynthia
GPSue
Thanks. The database is at work which is only accessible via VPN. Having trouble logging in now, will try again as soon as I can.
DougY
Welcome to Utter Access Forums!
You can point the combo box row source to a query that will return only student infraction < 5:
air code -- change to meet your database structure and object names
CODE
SELECT    StudentID,
    StudentName
FROM    tblStudents
INNER JOIN
    (
    SELECT  StudentID
    FROM    tblInfractions
    GROUP BY
        StudentID
    HAVING    COUNT(InfractionID) < 5
    ) AS LT5
ON    tblStudents.StudentID = LT5.StudentID;

If the combo box property of Limit To List = Yes -- the students with infractions count of 5 or greater will not be available for a new record.
Makes sense?
Larry Larsen
Hi
Took a small liberty during some spare time.. and put together a small example of how it could be done..
I would pay some attention to the table structure and how and where I store/capture the infractions for each student along side other bit's of details..
The listboxes will be unusable when the value of infractions is =>5 for any one student..
I added some colour to indicate when the listboxes are available for use..(cheeky I know..)
Hope you can "gleam" an idea or two from the example..
(db attached)
thumbup.gif
GPSue
THANK YOU to both Doug and Larry! Larry, my table structure is set up in the same way as yours but my infraction form is not as beautiful as yours! Doug, the select statement is the one I have as well to exclude those with >5 points.
hese ideas work beautifully! However, there is one roadblock which I failed to mention in my original post. The students that should be listed when adding an infraction should show those without an infraction, as well as those that have <5. In other words, in the add infractions form, the student name control should list all those in tblstudents and without those in the tblinfractions whose total points >5. I hope that makes sense.
So I'm thinking that this might be a two step process -- first to compare the count of stuID in the tblInfractions with the count of stuID in the tblstudents and then go from there? Am I on the right track with that?
Thanks again for your help.
Sue
DougY
Hi Sue,
>> The students that should be listed when adding an infraction should show those without an infraction, as well as those that have <5.
NP... just change the SQL (see below)... the students with no infraction will have Count = 0:
CODE
SELECT    StudentID,
    StudentName
FROM    tblStudents
INNER JOIN
    (
    SELECT  StudentID    
    FROM    tblStudents
    LEFT JOIN tblInfractions
    ON    tblStudents.StudentID = tblInfractions.StudentID
    GROUP BY
        StudentID
    HAVING    COUNT(InfractionID) < 5
    ) AS LT5
ON    tblStudents.StudentID = LT5.StudentID;
GPSue
Larry,
if you add a student to your tblstudentinformation, will the student name be part of the list, and the total infractions display as a zero on the form/subform? And also a question on doubl-clicking the incident options to add one...can you give me more information abut how to accomplish this? An error message appears when I give it a try and I assume it is because you have it write protected...
thanks,
Sue
Larry Larsen
Hi Sue
Yes.. adding a new student will allow you to add infractions directly after the main record has been saved, as you need this parent record.( a solution could be to have a save button..)

What I may failed to point out is you have to select a staff member with each infraction added, so the sequence would be:

Select student.. either from the combo or navigation controls at the bottom left of the form..

Select Staff member..
Select infraction..

(to add more than one repeat the above..)

Error trapping can be put in place to catch those "nulls"..(this is when you select an infraction and not a staff member..)

HTH's

thumbup.gif
Edited by: Larry Larsen on Wed Sep 3 4:51:18 EDT 2008.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.