Looking for simple Database design to simply check in checkout timestamps with comments, and istory recorded

Posted on 2014-09-16
Last Modified: 2015-10-16
I am looking for a simple database design simply used as a diary or work log.   Two buttons - Check in and Checkout.  
When checking out the user has to fill in one line of narrative.  This is recorded in a table and then goes to the next row and the process can be repeated.  Its almost acts as a diary and work log and all history is saved.

The table should contain the start and end times (dd/mm/yyy hh:mm:ss), username  and the narrative.  Calculated fields will also  display the duration in minutes and seconds.

I would be grateful if some recommended code can be provided or example can be found on internet.
Question by:yasanthax
  • 5
  • 3
LVL 34

Expert Comment

ID: 40325694
Check in should be done automatically when the database opens.  You can do this every time the database opens or only the first time it opens on a given day for that person.

Check out is a little harder to control.  I always hide my login form rather than closing it.  That means that the login form is the last form to close when the database closes which gives me a place to hang code I want to run before the database closes.  So, if you do this, your end of app routine can pop up the check out form when the database closes the database.  Of course, if you only want them to check out at the end of the day then there really isn't any way to automate this unless you are willing to prompt them every time they close the app.

The duration should not be stored in the table since you can calculate it whenever you need in a query.

Author Comment

ID: 40328088
Hi Thanks for the feedback and I agree your points on (1)checkout impact when database closed, (2) Duration can be calculated by a query

Essentially I want the database to work so that:

(a) A form consists of One button status Checkin or Checkout (caption updated according to current status)
(b) The rest of the form is a table view of history sorted by Most recent record to last, I am not sure if I need to create a subform or a list box.  I think the latter is good.- Please help?
(c)  When checking in a new record is created in the table with the start date and a prompt will appear to enter description and user can press OK.  Form refreshed as the start date can be seen in list box - Insert into table curret date and time
(d)  When checkingout the existing records description can appear again and user can amend.  If press OK, this record will be closed and recordcompleted.-  - Insert into table curret date and time and complete record
(e)If user presses check in again the same process will repeat.
(f) If user decide to close database after checkin, a prompt will appear and ask "Do you want to close task"  If Y record is checkedout and completed.  If N database will close and user can checkout ones database in reopened.

Does this functionality seem appropriate?


Author Comment

ID: 40328170

just come up with a problem and may need help on 3 pieces of script for the below.

-how do I retain the same recordset /last  being active , between the checkin and checkout
- If checkingin  how do ensure it uses a ne record set in the backend table
-  If logged out I want it to point at the last recordset if it has not been checkut yet.

LVL 34

Accepted Solution

PatHartman earned 500 total points
ID: 40328198
Check in/out could be anything.  Are you using this app as a time clock?  So, someone would check in when they start something and then check out when they finish/leave for the day.  The check in process should look for a record with an empty check out date (any date, not just today) and display some error - you can't check in if you are already here.  Check out should look for a record with today's date.  Are you checking in/out multiple times during the day or just once?  The check out process looks for a record with today's date as the checkin date and an empty check out date.  If none is found you give an error message - can't check out if you haven't checked in.
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)


Author Comment

ID: 40328260
Hi this is to log time during the day to different tasks, almost like a stop watch many times during the day. gives the users flexibility to open and close database when they know they are about to begin or end a task.

Have you some script tp perform the check desribed.  I.E.  If a start and end date is populated in a record can create a new recort inserting start time.  Else update end date.

LVL 34

Assisted Solution

PatHartman earned 500 total points
ID: 40328314
No.  I don't have anything similar.  Why don't you try to write it and we'll help.  Start by creating a query that looks for a record with an empty logout slot.

Select .. From .. Where OutDT Is Null;

You can run the query from a DCount() and if the record count is >0 then the query found a record.  You'll have to decide what you want to do about it.  You might want to display it in a form.  If there is no error, then run an append query to insert a login record.  Keep in mind that you need to have some way to know with whom you are interacting.  So the user either needs to log in each time he opens the app or you have to use an Environ(..) variable to identify him such as Environ("UserName")

Author Comment

ID: 40328574
Hi thanks , however I am having some other issues in the code and unable to do this at moment as I cant even appear to create the recordset.   Error  " The connection cannot be used to perform this operation.  It is either closed or invalid in this context"  I will put this on a new theat.  This open question  will have to wait or be cancelled.

Author Comment

ID: 40343686

I have got the following to work.  In addition a form open command checks if any records are incomplete if so updated ifthe button should openup with checkin and checkout. Therefore the database can be closed and reopened before a checkout as the table dteremines the record status.
Only probem is I cant get the List box linked to the table on the form to refresh instantaniously unless pressing F5.  Any overall recommendations?

' -GetCurrent Records and check.  Loop through each record to find incomplete record-----
Set con = DBConnection
Set rst = Getrs(con)  ' -Creates recortset

With rst
    Do While Not rst.EOF ' And Me.Command1.Caption = "Checkout"
        If IsNull(rst.Fields("DCheckout")) Then
            RecNum = rst.Fields("Id")  '--RecNum only populated where incomplete record
        End If
End With
' -Update records.  If RecNum populated updpadte else insert new record ------
Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = DBConnection
        .CommandType = adCmdText
            If RecNum <> 0 Then
                .CommandText = "UPDATE TblTracker SET TblTracker.Dcheckout = Now(), TblTracker.Tsigner = ''" & _
                "WHERE (((TblTracker.ID)=" & RecNum & "));"
                .CommandText = "INSERT INTO TblTracker(Dcheckin) VALUES('" & Now() & "')"
            End If
    End With

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now