Problems updating a time on a form

[rwmorton]

I am doing a database for an after school daycare program. They want
to know at all times where a student is at. There are three table,
student, a list of student names and ID numbers, activity, a list of
activities and locations and short code for htme, and lastly inout, a
record of the students as they are checked in and out. For time I used
a time mask and type.
The forms are simple enough for the student add and activity add. I
tinkered around with the sign in form to get it to work. When a user
enters a student number it adds that to the table and automatically
enters the time in the time in. I also add to the timeout a null (more
on that later). When they get all done (choosing activity and
verifying name is right, the record is added.

The time out form was a bit trickier. I orignally set up a query that
would ask for the student ID and then look for that record in the inout
table if it had a NULL entry in timeout. When using time it seems you
can not search for a non-zero value. Since it could theoretically
return one entry it seemed the best idea. Unfortunately this would not
allow for the data to be changed. I then switch to a query that would
find all the NULL entried in timeout and used a dropdown menu so that
the user would be able to find the right student. It seems to work OK,
but no matter what I try the =NOW() function will not change the
timeout. I have tried getfocus, loose focus, onenter, etc. Is there a
way to update this value to the current time without making the user
enter it?

Any help would be appreciated, thanks..

R Morton

 

[John Vinson]

The time out form was a bit trickier. I orignally set up a query that
would ask for the student ID and then look for that record in the inout
table if it had a NULL entry in timeout. When using time it seems you
can not search for a non-zero value. Since it could theoretically
return one entry it seemed the best idea. Unfortunately this would not
allow for the data to be changed. I then switch to a query that would
find all the NULL entried in timeout and used a dropdown menu so that
the user would be able to find the right student. It seems to work OK,
but no matter what I try the =NOW() function will not change the
timeout. I have tried getfocus, loose focus, onenter, etc. Is there a
way to update this value to the current time without making the user
enter it?

I don't understand why a query with two criteria - the studentID and
IS NULL as a criterion on time out - shouldn't be updateable. Could
you try this query again, and if it still doesn't let you update, post
the SQL?

If you want to use the form with the combo box for the student ID, try
code like this in the combo's AfterUpdate event:

Private Sub cboStudentID_AfterUpdate()
Me!txtTimeOut = Now
End Sub

You can't just put =NOW() in the Event line; you need to click the ...
icon and invoke the Code Builder.

John W. Vinson[MVP]

 

[Dr Bob]

I don't understand why a query with two criteria - the studentID and
IS NULL as a criterion on time out - shouldn't be updateable. Could
you try this query again, and if it still doesn't let you update, post
the SQL? <<

I have tried it several times. Per your request here is the SQL code
that is produced by the query that I created.

SELECT inout.studen_number, inout.Activity_code, inout.time_in,
inout.time_out, Students.LastName, Students.FirstName
FROM Students INNER JOIN inout ON Students.studen_number =
inout.studen_number
GROUP BY inout.studen_number, inout.Activity_code, inout.time_in,
inout.time_out, Students.LastName, Students.FirstName
HAVING (((inout.studen_number)=[Enter Student Number]) AND
((inout.time_out) Is Null));

I will look at your idea. I finally starting tinkering with the Code
builder last night and got it to update with the NOW() function when it
gotFocus. The results were OK, but it still needs work. If I can not
get it to return one record I am going to have to close the form as
soon as they update because the chances of error are too high if they
try to go back through the recods (the newer query pulls up all the
NULL entries and when you go back through the records it comes across
the one that you just checked out and errors that it is no longer
there).

I do appreciate your help.

R Morton

 

[Rick Brandt]

IS NULL as a criterion on time out - shouldn't be updateable. Could
you try this query again, and if it still doesn't let you update, post
the SQL? <<

I have tried it several times. Per your request here is the SQL code
that is produced by the query that I created.

SELECT inout.studen_number, inout.Activity_code, inout.time_in,
inout.time_out, Students.LastName, Students.FirstName
FROM Students INNER JOIN inout ON Students.studen_number =
inout.studen_number
GROUP BY inout.studen_number, inout.Activity_code, inout.time_in,
inout.time_out, Students.LastName, Students.FirstName
HAVING (((inout.studen_number)=[Enter Student Number]) AND
((inout.time_out) Is Null));

Any query with GROUP BY in it will always be read only.

 

[Dr Bob]

Thank you Mr. Brant. I am not sure how that got turned on but that did
the trick. I am sure that I will have other questions on this project.
It has been for an internship for School. At 52 I am finally getting a
batchlors degree and the internship is in my way.

Again, thanks...

R Morton