Solved

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

Posted on 2014-09-16
8
84 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
  • 5
  • 3
8 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:yasanthax
Comment Utility
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 34

Assisted Solution

by:PatHartman
PatHartman earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

772 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

10 Experts available now in Live!

Get 1:1 Help Now