Results 1 to 13 of 13
  1. #1
    torpid is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    10

    Student Sign in/out system

    Hi again
    I'm designing a database for students to sign in and out of school. It is based on a card scanner, which works for signing in, but I'm unsure about how to get them to sign out. I would like to know: How can I make it so that when the student swipes their card, a check is made to see if they are signed in. If they are not, it signs them in, but if they are, it enters the current time to the 'timeout' field.
    Thanks for any help

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Depends on your table structure, but sounds like you can test for a record where the student = the swiped student and the time out field is null. I'd use a recordset, which will let you update the time out field if a record is found or add a new record to sign them in.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    torpid is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    10
    I've done some research on recordsets, but I can't figure out how to compare the input against the record set. FYI, my table structure is very simple, with one 'sign_in' table that contains stud_id, timeIn, timeOut, date. I want to search this table for previous matching stud_id's and check for a null timeOut field. Any more information?

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This type of thing, which also checks the date:

    Code:
      strSQL = "SELECT * FROM DriverStatus " _
             & "WHERE Driver = " & lngDriver & " AND DOR_date = #" & datDORDate & "# " _
             & "AND DateTimeEnd Is Null"
    
      Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    torpid is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    10
    Ok, so I've implemented that style of code, which looks good, and it works for the first entry, where the student hasn't logged in before. My IF statement checks if there is a value in the recordset, and if there isn't then it signs them in. On the second card swipe, however, it comes up with the error:
    Run-time error: 3464 Data type mismatch in expression

    and highlights the 'set rs = dp.OpenRecordset(sqlStr, dbOpenDynaset, dbSeeChange) for where the error occurred.

    Not sure what to do from here

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The problem is probably in the SQL, so I'd need to see how that's built, but it may help to see the whole procedure. Also, what are the data types of any fields in the WHERE clause (which is likely the source of the problem)?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    torpid is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    10
    I think it's a bit early in our relationship to be showing you my SQL :O.
    Anyway, this is what I have:

    Dim sqlStr As String
    Dim rs As Recordset
    sqlStr = "SELECT student_id FROM sign_in " _
    & "WHERE student_id = " & synResult & " AND AttendanceDate = DATE() " _
    & "AND timeOut Is NULL;"
    Set rs = db.OpenRecordset(sqlStr, dbOpenDynaset, dbSeeChange)

    where synResult is the input from the card. Hopefully this gives you some insight, I'll be online if you want to IM for any more queries.
    Thanks

  8. #8
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, you didn't give me the data types, but try changing this line:

    & "WHERE student_id = " & synResult & " AND AttendanceDate = #" & DATE() & "# " _
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    torpid is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    10
    Thanks pbaldy. Unfortunately that wasn't the fix, same error. Sorry I didn't give you the data types, here they are now:
    AttendanceDate: Date/Time - Formatted to short date, default = date()
    timeIn: Date/Time - Formatted to Long Time
    timeOut: Date/Time - Formatted to Long Time
    student_id: text

    Thanks for your ongoing assistance on troubleshooting this, much appreciated, and also thankful for anyone else that may give assistance.

  10. #10
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If student ID is text it needs delimiters:

    & "WHERE student_id = '" & synResult & "' AND AttendanceDate = #" & DATE() & "# " _
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    torpid is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    10
    Thankyou, that was exactly the solution.

  12. #12
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad we got it sorted out.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    jamiers is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    9
    Torpid,

    I know it has been over a year.... I would really love to see the database that you created.... is there any possible way??

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

Similar Threads

  1. Replies: 2
    Last Post: 04-12-2011, 10:14 AM
  2. Pound sign in text field
    By eww in forum Programming
    Replies: 3
    Last Post: 09-29-2010, 10:30 AM
  3. Student warning system
    By spqr in forum Database Design
    Replies: 1
    Last Post: 03-17-2010, 10:45 AM
  4. Daily Sign In form
    By JHansford in forum Access
    Replies: 2
    Last Post: 12-09-2009, 08:41 AM
  5. The ampersand sign (&) and Access reports
    By tigers in forum Reports
    Replies: 2
    Last Post: 09-28-2009, 08:23 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