Quota control in a form

[roberttran52002]

Hi all,

May I seek your advice on how to create a summary in a form to show the total number of quota achieved & stop data entry if the quota is fulfilled:

Ie. Out of 1000 students in my data base, I just need to interview 150 male student & 110 female student (filed status="done" means already interviewed)

1/ I wish to have a button that helps show the summary of how many Male & Female students have been interviewed

2/ If 150 male & 110 female students are interviewed ==> form is blocked for data entry with the warning "Quota Achieved"

Thank you very much in advance!

TH

 

[Kafrin]

1) You need a query. I'm guessing that StudentGender is a field against the Student and is set to something like "M" or "F", and that Status is also a field against the student, so you'd end up with something like:

SELECT Sum(Iif(StudentGender="M",1,0)) AS Male, Sum(Iif(StudentGender="F",1,0) AS Female
FROM Students
WHERE Students.Status="Done";

2) On your form you will need code that runs in the OnOpen and AfterInsert events that looks to see if the quota is reached and then if it has blocks the form. If the query above is called Quotas, this will be something like:

Private Sub Form_Open(Cancel As Integer)
    CheckQuota
End Sub
 
Private Sub Form_AfterInsert()
    CheckQuota
End Sub
 
Private Sub CheckQuota()
    Dim rs as Recordset
 
    Set rs = CurrentDb.OpenRecodrset("Quotas")
    rs.MoveFirst
 
    Me.AllowAdditions = (rs!Male < 150 Or rs!Female < 110)
 
    rs.Close
 
End Sub

This means that you will only be able to add new records (using the form) if the number of Male students in Quotas is <150 and/or the number of Female students is <110.

 

[roberttran52002]

Thank you very much for your codes!

It workrs very well for the first point of summarizing the achieved quota, however the second code cannot run (even after I change [FONT=&quot]OpenRecodrset[/FONT] into [FONT=&quot]OpenRecordset). It yellow-shaded the [[/FONT] [FONT=&quot]Set rs = CurrentDb.OpenRecordset("Quotas")][/FONT]
[FONT=&quot]
Could you please help look into the issue. Thanks a million!
[/FONT]

 

[Kafrin]

Oops, sorry about the typo in the code!

I can't immediately see a problem with that line of code now. Just to check, the query from part (1) that you said works, what have you called that? Because the line should be:
Set rs = CurrentDb.OpenRecordset("[the name of the query]")

If that's not it, then please tell me what the error message actually says, as that'll help me narrow down the problem.

 

[roberttran52002]

Dear K,

It's very kind of you! I couldn't think that the second code can also link to the 1st Query. So great!

After updating the query name accordingly, the second code does not show any error message. However, It seems not to stop data entry given the quota has reached. I have 17 male and 21 female in my data base & I tried to put: "Me.AllowAdditions = (rs!male < 5 Or rs!female < 11)" but it I still can data entry on all fields.

Much appreciation for your help!

TH

 

[roberttran52002]

May I know the solution for stopping data entry once the quota is reached? ie. male=150 & Female=110.

Thanks a lot!

 

[Kafrin]

Hmm, concerned that this line doesn't seem to work for you:
Me.AllowAdditions = (rs!Male < 150 Or rs!Female < 110)

It should mean that if either the quota of male students is less than 150 or the quote of female students is less than 110 then Me.AllowAdditions=True, otherwise it's False. An alternative to try would be:

If rs!Male >= 150 And rs!Female >= 110 Then
Me.AllowAdditions = False
Else
Me.AllowAdditions = True
End if

...but this should mean exactly the same thing. It may be an issue to do with the other settings on the form. Eg do you have the DataEntry property set to Yes? If so then you may also need to set this with the same criteria. If you don't want users to have access to earlier records then you may want to look at closing the form once the quota has been reached, rather than just locking it.

 

[roberttran52002]

Once again, thanks Kafrin a million for your time! Your codes work very well now. So fantastic!

 

[Kafrin]

Excellent, glad to hear it