Results 1 to 7 of 7
  1. #1
    charliejoe is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2013
    Posts
    4

    Cool Student Check-in / Check-Out Activity Log

    My First Post - help please


    The project involves a main form with large Check-In and Check-Out command buttons. When a student clicks the Check-In button a parameter query asks the student to scan his/her ID card. The Student database is searched and the student's information is displayed along with the check-in date and time. A combo box allows the student to select a course to study. I need to learn how to store this information in a student activity log and return to the main form again. After the study period the student would click the large Check-Out button, scan their ID card and a select query would search the student activity log for the students ID and today's date for a match then update the student activity log with the current date and time before returning to the main form again.

    I use three tables, three forms and two select queries.
    1. tblStudents (a list of student information)
    autonum
    ID number (six digits)
    Name
    Grade.

    2. tblStudentActivity (a log for check-in and check-out dates/times)
    autonum
    StudentID
    Course
    DateTimeIn
    DateTimeOut
    Comments

    3. tblCourseTitles (used with unbound combo box on frmCheckIn)
    autonum
    Course

    4. frmMain containing
    a label as a title heading
    a large command button for Check-In (opens the Check-in form)
    a large command button for Check-Out (opens the Check-Out form)
    a medium side command button for Closing the program.

    5. frmCheckIn
    Prompts the student to scan their ID card
    Displays the students information and check-in date and time
    Allows student to select a course title from a combo box
    I probably need to add a "save record button" also but am not sure how to store the displayed info in the Student Activity table'

    6. frmCheckOut
    Prompts the student to scan their ID card
    Checks the Student Activity table and displays today's check-in info plus the check-out date and time. I haven't gotten this far yet because I could not figure out how to store the Check-in information.

    Any help would be appreciated. I only know bits and pieces of access but will signup for the next ed2go class.

  2. #2
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Short answer and comments -

    1) The "Onclick" event of a button or the "AfterUpdate" event of a combo box are appropriate places to place your desired code.

    2) I assume you know how your ID card scanner works, and interfaces with Access, because I don't. You need those details to program the part of your application that gets the student's identity.

    3) Will the student be doing the course on the same computer, or is this a standalone station at the entry/exit of the room? What happens if the student forgets to log out?

    4) You haven't accounted for a student walking away from the computer. Are you going to set up some kind of timeout, or what?

    Basically, you need to determine your business rules more completely before you can finish your application design. Maybe you save the checkout record when the student selects a course, and apply a default 15-minute time period and set a flag showing that they "we guess" will have logged out fifteen minutes after they came in. When the student really checks out, we update the log record to show that, yes, they in fact actually logged out, and the real time was X.

    The logout screen will select the most recent record for that student which is not marked as having been logged out, verify that it is the same day or within 4 hours, and ask the student to verify the course worked on. It's your choice whether it will update the course name if they answer different than they did when they logged in, or whether you will ask them how much time they spent on each and create a new log record, etc.

  3. #3
    charliejoe is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2013
    Posts
    4
    Thanks for the quick response. I did leave out the part where a study monitor will actually operate the program and scan the card for a student. Currently they hand-write the info or type it into MS Excel.

    It is the code I am not sure about. After trying all types of queries and union queries and forms based on queries and tables, I think that a "save" button may be the answer. I have not figured out how to capture the query values stored in the form's text boxes. I know bits and pieces of Visual Basic but am not sure of the syntax, especially with Access. I thought that if the Student ID results for the query were blank, another form could display to allow the user (study monitor) to enter the student's information. Once I get some information stored in the Student Activity table I will try my hand at creating reports which are not available with the current process. Thanks again.

  4. #4
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    I have not figured out how to capture the query values stored in the form's text boxes.
    Saving is automatic if the fields are bound to an underlying record. You have to let Access do the things that it's good at, and not try to reinvent the wheel.

    Create a form with a "Checkin" and "Checkout" buttons.

    Checkin Button will open a form (frmCheckIN) in DataEntry mode, bound to tblStudentActivity. Scanning the card (however that works) will populate the student ID control, and display the resulting student name information in a designated area of the form. It will also default the time-in field to now(), default the time-out field to (now() + 1/24) default the courseid field to Unknown, and set the checkedout checkbox to NO. Since it's a bound form, selecting the course from the courseid will automatically update the underlying record. Updating the checkin time, checkout time, or checkedout checkbox will also update the underlying record. (This form can also be used for general maintenance.)

    CheckOut Button will open a similar form, frmCheckOut, but there will be a dropdownbox on the header. Scanning the card (however that works) will populate the studentID box in the header, which sets a filter on the file. The filter should get the latest record for that student where checkedout is NO. (If there is none, then it should pop a message asking whether one should be created.) Pressing a "Checkout" button on that form will set the checkout time to Now() and change checkedout to Yes.

    Come to think of it, all functions could use the same form layout, just with the form and controls set differently for each function. For Checkin, the form is set to dataentry mode, the filter is set to studentID number on the header and the studentID in the detail is set with default value of the student ID on the header, checkout checkbox is set to default NO, CheckinTime is updatable and checkouttime is not. The Delete button is labeled "Cancel".

    For Maintenance, dataentry mode is NO, the filter is set to studentID, and all the fields are open for maintenance. A Delete button is enabled, the Checkout Button is disabled.

    For Checkout, dataentry mode is NO, the filter is set to studentID, and all fields are open for maintenance. A Delete button and a Checkout button are enabled. Checkout sets the checkouttime and sets checkedout to Yes.

  5. #5
    charliejoe is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2013
    Posts
    4
    Thanks again for the fast reply. I will try to implement the suggestions and get back with you.
    Charlie

  6. #6
    charliejoe is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2013
    Posts
    4
    Checkin Button will open a form (frmCheckIN) in DataEntry mode, bound to tblStudentActivity. Scanning the card (however that works) will populate the student ID control, and display the resulting student name information in a designated area of the form.

    I still have the same problem. The student info is stored in tblStudents. The tblStudentActivity starts out empty. I created a parameter query using tblStudents that does find and display the student info on the form. Binding the form to tblStudentActivity does not update any of the info displayed by the parameter query. Not even the Student ID. The card scanner just inputs the ID number as text into the query prompt text box- no problem there - then the query checks tblStudents and finds / displays the student info. The student info is in the tblStudent fields - not the tblStudentActivity fields. With the form bound to tblStudentActivity, the selected course updates and the Now() function should enter the date and time in the appropriate field. Your suggestion to only store the ID in tblStudentActivity makes sense but I may have to use an Access VBA module on the form to capture the Student ID - something like tblStudentID = tblStudentActivityID without displaying it (not the correct syntax). With the form bound to tblStudentActivity, the tblStudentActivity.ID field should update also.
    I think it might work if I can figure out the correct syntax to store the ID number. I like the idea of using the on-click event in the Save button.

    To summarize, the Student ID, Student Name and Student Grade boxes on the Form will be bound to tblStudents so the info displays. The Course and Date-In Date-Out fields on the form will be bound to tblStudentActivity so they update. A VBA module in the save button On-Click event will assign the displayed Student ID to the tblStudentActivity ID field. Something like that. I'll try to figure out the VBA syntax and get back.

  7. #7
    TheTony is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2016
    Posts
    9
    Hi
    I am doing a similar project using RFID, I use the phiget software and they give you a basis to build the rest of the database on.
    If you need any more info let me know.
    Tony

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Check For Duplicate Check Before Posting.
    By burrina in forum Queries
    Replies: 1
    Last Post: 01-22-2013, 01:39 PM
  2. Check if record exists, Check Number
    By burrina in forum Forms
    Replies: 9
    Last Post: 01-06-2013, 03:49 PM
  3. Check Box
    By Trojnfn in forum Access
    Replies: 2
    Last Post: 08-03-2012, 01:19 PM
  4. Replies: 1
    Last Post: 05-10-2012, 11:56 AM
  5. To check or Un-Check all Boxes in a form
    By devcon in forum Forms
    Replies: 7
    Last Post: 05-01-2010, 12:03 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums