Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

Posted on 2014-09-16
Medium Priority
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 40

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.

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 40

Accepted Solution

PatHartman earned 2000 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.

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 40

Assisted Solution

PatHartman earned 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

581 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