Students Sign-In Sheet with Time in / Time out

[jairgarza]

Hi there,

I am definitively not an advanced Access user, but I always find my way around it, VBA, creating tables, queries, etc.



That being said, I am trying to build a "sign-in Form" for a School Office. They receive students on a daily basis for several reasons (reasons we do not need in the DB). What I would like to do is to build a form that allows a dedicated user to scan ID's, then what happens next is the trick:

* Get student name from Students DB (Table) and show it on screen
- St_ID (PK)
- St_Name
* Add a new record to Daily_Signin (Table)
- St_ID
- St_Name
- Date_in (MM/DD/YY)
- Time_in (HH:mm:ss)
- Period_in (based on the time, i.e. 7:20 - 10:44 = 1st period)
here is where it gets trickier for me, on same table (or maybe a new one?)
- Time_out (HH:mm:ss) no need for date as it should always be the same day
this Time_out should be recorded when the student leaves the office, now how to achieve this? well the first thought is of course to scan the ID again, hopefully on the same form and using VBA compare if the student has ONLY 1 Time_in with no Time_out and then add the time_out, yet a student can be in the office several times a day (not often, but possible), which in case I need for the code to be able to check for if student has a pair (or more) of time_in & time_out already, then to mark a new time_in

I would then need to run Reports by period_in for today (not a big deal once I have the data )

Any ideas how/where to tackling this mess?

Thank you very much for all/any of your help.

[ranman256]

you dont need a Date field AND a time field. 1 date field hold both and you want them togther....

DateIn (as date and time) general date
DateOut (as date and time)

this lets you do the elapsed time.
as to the design, you must decide on a design:

userID, datein,dateout
on the same record, then you must find it again
or
userID, TimeStamp ,
but this doesnt determine in or out, and you must sort the records in order to determine in/out.
or
userID, TimeStamp , IO
where you can click IN or OUT on the form then scan, but that needs user interface AND they may choose the wrong IO.

which do you want to use?

[jairgarza]

Hi ranman256,

Thanks for the prompt reply.. I would definitively like to use the first option you mentioned:

userID, datein,dateout
on the same record, then you must find it again


Where to start?

[ranman256]

theres no good solution.
a person may fail to login / or out and that disrupts the entire setup.
but to get started:

sub txtBadge_afterupdate()
dim vRet, vBadg

docmd.Setwarnings false

'[scan badge]
vBadg = txtBadge
vRet = Dlookup("[LogID]","tTable","[BadgeID]='" & vBadg & "' and [dateOut] is null")     'see if they already have an open record


if IsNull(vRet) then  'not clocked in
     sSql = "Insert into tTable (BadgeID,[DateIn]) values ('" & vBadg & ",#" & now() & "#)
     docmd.runSql sSql
else
   'we got the record , so close it:


     sSql = "Update tTable Set [DateOut]=#" & now() & "# where [LogID]='" & vRet & "'"
     docmd.runSql sSql
endif


docmd.Setwarnings true
end sub

[jairgarza]

wow! that is quite a nice code... I am sorry for the delay, been addressing technology issues in the building... I will definitively try it and let you know... many thanks!