Solved

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

Posted on 2014-09-16
8
124 Views
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.
0
Comment
Question by:yasanthax
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 37

Expert Comment

by:PatHartman
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.
0
 

Author Comment

by:yasanthax
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?

Thanks
0
 

Author Comment

by:yasanthax
ID: 40328170
Hi

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.


Thanks
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 37

Accepted Solution

by:
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.
0
 

Author Comment

by:yasanthax
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.

Thanks
0
 
LVL 37

Assisted Solution

by:PatHartman
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")
0
 

Author Comment

by:yasanthax
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.
0
 

Author Comment

by:yasanthax
ID: 40343686
Hi

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
 
        rst.MoveNext
    Loop
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 & "));"
            Else
                .CommandText = "INSERT INTO TblTracker(Dcheckin) VALUES('" & Now() & "')"
            End If
        .Execute
    End With
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

728 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