Equipment and personnel check in and out database using a barcade reader

[rdrcl]

My desired output of this is to have a database to allow me to quickly check in and out equipment and personnel using a barcode scanner. I am using access 2013 and started with the asset tracking template downloaded from Microsoft. Where I am hung up right now is adding the personnel check in and out (each member of the team will have a barcode attached to their id) I want to be able to just scan their badge when they show up and again when they leave. At the end I will run a report that shows who was on scene (it is for a search and rescue team) during a certain day or time range.
I have added two new tables one check in and one check out the only fields are firedeptID and CheckIn or CheckOut a date/time field CheckIn and CheckOut Auto populatewith Now(). The first problem is that I think I need to turn off (if possible)the auto creation of a new record every time, when I scan in an ID right now that record gets the time stamp of the last time the new record was created IE.Last week when the last person scanned in and the new record was created. I assume I am going the same type of issue when I start working on checking out the equipment.
I am sorry if this has already been answered but I can’t seem to find the solution. Also it has been a REALLY long time (Access 2003 and VB5) since I used accessmuch. Any suggestions or obvious mistakes I am making?

[Dal Jeanis]

This isn't an access question, it's a conceptual question.

You need to create a new record each time a scan occurs, not update a record. You should NEVER update your check-in check-out records, or you will never know what originally happened.

Add an autokey field to the record. That way each record can be referred to by its unique key.

I, personally, would add a "WHERE" field to the check-in check-out tables. Otherwise, the data is ambiguous. If there were three fires going at the same time, or one fire with three "fronts", and Fireman 451 checked in at moment X, where was he? (This is the one piece of data that might occasionally need to be updated later, to correct mistakes made due to the transitory nature of "locations".)

I'd add a location table to handle that. Your checkin/checkout form could have a control that allows you to quick-set the new location ID, give it a short name and fill in the complete details into the location table later.

(Some of my suggestions above are based on the assumption that, in real world conditions, there will be more than one possible person or location doing the checkin/checkout, the different scan databases will need to be reconciled later. Having each clone of the database assigning its own location handles that neatly, even allowing the real world case that a firefighter may check out from a different location than he/she checked in.)

[rdrcl]

You are correct I need to create a new record each time I scan a badge. I already have a primary key but failed to mention it in my orginal post. I do not need multible locations. So how do I force the database/new record to have the date time stamp of when I actually scan the badge instead of when new record was created. I think my problem has to do with the fact that everytime you add a record the database autmatically creates a new blank record and waits for it to be populated but could be wrong. The new blank record is getting the time stamp of when it is created instead of when I populate it with a id number. I have the default value set to Now() should that be set onaccess or onfocus instead?

[TG_W]

When the bar code is scanned, have the code that creates the new record set your time stamp field to Now().

[rdrcl]

perfect, how? All I have set up right now is the field I scan the barcode I want it to create a new record. Sorry I really do qualify for your signature SELECT * FROM Users WHERE Clue > 0 No results

[pbaldy]

I'd consider a single table with CheckIn and CheckOut fields rather than 2 tables. Otherwise it would be more difficult to associate the related records, and theoretically every check in must have a check out. A Null check out field tells you who is currently checked in. It's not hard to check the data when somebody scans their badge and automatically check them in or out based on their current status (a new record for check in, updating a record to check out). Obviously sometimes people forget to check in or out, but you verify with the user or whatever.

[Dal Jeanis]

I don't understand how the timestamp when the database creates a NEW record can be different from the time the user scans the bar code. There is no logical way it could be created at any other time...

Ah, I see. just a sec. Your entry screen is sitting open, and the default time is established by when the prior record is entered.... Hmmm.

Okay, simple. You need to add code to the AfterUpdate event of the badgeID field - the field that is scanned in - that sets the value of the timestamp field to now() at that moment.

[rdrcl]

That makes sense. I was afraid it was going to be something simple like that. I will make that change tonight and test it out. THANK you. Should I mark this as solved now or wait till testing?

[Dal Jeanis]

Mark it solved. If you post again on the thread after that, I'll check in and see what's up.