Put the result of a query in a variable

  • Thread starter Thread starter Alvaro
  • Start date Start date
A

Alvaro

I have the STUDENTS table and the CLASSROOMS table. I need
to know, after I add one student in the form, how many
students are on the class and compare to the the maximum
number of students that the CLASSROOM admits.

So, I have to COUNT my students and then compare with the
field CAPACITY of CLASSROOMS. If the limit is too close,
or higher than the classrooms I wish to show a msg to the
operator warning for this situation, but I will not limit
him.

I though I can use the BeforeUpdate event but I stopped
programming Access in 1997 and I really don't remember how
I have to do this things todyay!!!

Any ideas? Thank you very much.
Alvaro
 
-----Original Message-----
I have the STUDENTS table and the CLASSROOMS table. I need
to know, after I add one student in the form, how many
students are on the class and compare to the the maximum
number of students that the CLASSROOM admits.

So, I have to COUNT my students and then compare with the
field CAPACITY of CLASSROOMS. If the limit is too close,
or higher than the classrooms I wish to show a msg to the
operator warning for this situation, but I will not limit
him.

I though I can use the BeforeUpdate event but I stopped
programming Access in 1997 and I really don't remember how
I have to do this things todyay!!!

Any ideas? Thank you very much.
Alvaro
.
Click to expand...
Hi Alvaro,
you might like to consider using the form's recordset
instead of a query. For example use the form_oncurrent()
event to put the count into a textbox....

Text20 = Me.Recordset.RecordCount

Thing to keep in mind is that this is a count of saved
records.

Luck
Jonathan
 
I have the STUDENTS table and the CLASSROOMS table. I need
to know, after I add one student in the form, how many
students are on the class and compare to the the maximum
number of students that the CLASSROOM admits.

So, I have to COUNT my students and then compare with the
field CAPACITY of CLASSROOMS. If the limit is too close,
or higher than the classrooms I wish to show a msg to the
operator warning for this situation, but I will not limit
him.

I though I can use the BeforeUpdate event but I stopped
programming Access in 1997 and I really don't remember how
I have to do this things todyay!!!

Any ideas? Thank you very much.
Alvaro
Click to expand...

Alvaro,
You can use the From's RecordsetClone.RecordCount to keep track of how
many students are shown on the form.

Let's say you want notification when the count is within 3 of
capacity.
Set the Code to 1 less than the 3, (as the new record hasn't been
added yet) so code the Form's BeforeUpdate event:

If Me![ClassCapacityField] - 2 >= Me.RecordsetClone.RecordCount then
Else
MsgBox "There are " & Me.RecordsetClone.RecordCount & " Students
already registered for this class" & vbNewLine & "which has a capacity
of " & Me![ClassCapacityfield]
End If

The record will still be saved, but notice will be given.

If [ClassCapacityField] is not a field in the Form, use DLookUp to
find it in the table:
If DLookUp("[ClassCapacityField]","TableName","[ClassID] = " &
Me![ClassIDControl]) - 2 >= etc.
 
Back
Top