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

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
yasanthaxAuthor Commented:
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?

yasanthaxAuthor Commented:

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.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
yasanthaxAuthor Commented:
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.

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")
yasanthaxAuthor Commented:
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.
yasanthaxAuthor Commented:

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.