Changing data in a field using a form

P

ptlm65

OK I know this may sound simple but I am a novice so please be kind.

I have a table with four fields representing a student, communit
service hours assigned, community service hours served and communit
service hours remaining.

the fields are:
Name
Hours Given
Hours Served
Hours Remaining

What I want to do is create a form with a combo box containing th
student's name then a text box that I can enter hours served everytim
a particular student completes hours, and I want a command button tha
when I click it, the hours I enter into the text box to automaticall
be subtracted from the hours given entering the new total into th
hours remaining for that student.

I was thinking of adding another table to enter the date a student di
the hours and how many they did and link it to the student's nam
(maybe fields are "Date Completed" and "hours completed" and the tabl
linked to the "Name" field of the first table). I don't know if I ca
enter another text box to enter the date they did the particular hour
and how many and add that to the table so that when I enter the hour
served on the certain date it adds that info to the "date and hour
served on that date" table, as well as what I want listed above
updating the total hours remaining.

I am sure it sounds like way to much to ask but if anyone could help m
draw up a code for a form that would accomplish this that would be
life saver. Thanks for any help in advanc
 
K

Klatuu

You really do need two tables for this. Here is how they should be structured:
tblStudent
STUDENT_ID - AutoNumber, Primary Key
STUDENT_NAME - Text
HOURS_GIVEN - Long Integer

tblHours
HOUR_ID - AutoNumber, Primary Key
STUDENT_ID - Long Integer Links to tblStudent
DATE_COMPLETED - Date
HOURS_COMPLETED - Long Integer

Don't store the Hours remaining. It violates one of the basic rules of
database normalization - "never store a calculated value"

You will need a Form with a SubForm. The form should have a combo box to
look up the student, a text box for the student's name, a text box for the
hours given, and a text box for the hours remaining. The SubForm should have
DATE_COMPLETED and HOURS_COMPLETED and should be a datasheet view.

You will calculate the hours remaining for the form using the following
function:
DSum("[HOURS_COMPLETED]", "tblHours", "[STUDENT_ID] = " & Me!cboStudent)
This calculation should go in the Current event of your form. A similar
verison should go in the After Update event of the Subform:
DSum("[HOURS_COMPLETED]", "tblHours", "[STUDENT_ID] = " &
Me.Parent!cboStudent).
 
P

ptlm65

I created the tables as listed below and created the form. However, whe
I entered the function into the current event of the form it gave me a
error that said that it cannot find the macro
You really do need two tables for this. Here is how they should b
structured:
tblStudent
STUDENT_ID - AutoNumber, Primary Key
STUDENT_NAME - Text
HOURS_GIVEN - Long Integer

tblHours
HOUR_ID - AutoNumber, Primary Key
STUDENT_ID - Long Integer Links to tblStudent
DATE_COMPLETED - Date
HOURS_COMPLETED - Long Integer

Don't store the Hours remaining. It violates one of the basic rule
of
database normalization - "never store a calculated value"

You will need a Form with a SubForm. The form should have a combo bo
to
look up the student, a text box for the student's name, a text box fo
the
hours given, and a text box for the hours remaining. The SubFor
should have
DATE_COMPLETED and HOURS_COMPLETED and should be a datasheet view.

You will calculate the hours remaining for the form using th
following
function:
DSum("[HOURS_COMPLETED]", "tblHours", "[STUDENT_ID] = "
Me!cboStudent)
This calculation should go in the Current event of your form.
similar
verison should go in the After Update event of the Subform:
DSum("[HOURS_COMPLETED]", "tblHours", "[STUDENT_ID] = " &
Me.Parent!cboStudent).
Click to expand...
 
Top