UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Another Time Tracker, Access 2013    
 
   
tykra
post May 4 2017, 07:20 PM
Post#1



Posts: 34
Joined: 14-January 17



I am attempting to build a time sheet app utilizing an embedded calendar (makes for a nicer gui) that users can utilize to select dates. Once they click a date they select a start time, end time, enter a billing code if applicable, record notes and click a submit button. This records the info in a table with all that basic data.

tEmpID (employee ID)
tDate (date of entry)
tStartHour (start hour)
tStartMinute (start minute)
tEndHour (end hour)
tEndMinute (end minute)
tbillCode (billing code)
tNotes (notes associated with entry)

The calendar I started my design with was created by Arvin Meyer, it is not based on a table so as an access newbie I am having a fit trying to tie dates to those entries. I would like to change the background color of days that have been recorded to visually indicate to users that they have been completed.

Is there a way I am not considering to do this using the ready made calendar or is there another table based calendar out there that would make that easy to do?

Thanks for any ideas you might have!
Go to the top of the page
 
ScottGem
post May 4 2017, 07:30 PM
Post#2


UtterAccess VIP / UA Clown
Posts: 32,047
Joined: 21-January 04
From: LI, NY


In the code Archives search under my screenname for Calendars. I have one that is table based so may be easier to do what you want

Can you explain why you chose to store the time value as separate hours and minutes instead of time values?

--------------------
Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
 
doctor9
post May 5 2017, 09:47 AM
Post#3


UtterAccess Editor
Posts: 17,098
Joined: 29-March 05
From: Wisconsin


Scott makes a good point - while you might want your form to allow the user to enter the hours, minutes and date in separate textboxes, it's much more efficient to store all of those values in a single date/time field along these lines:

tEmpID (employee ID)
dteStartDateTime (date/time of entry)
dteEndDateTime (end date/time)
tbillCode (billing code)
tNotes (notes associated with entry)

This makes calculating the total work time much easier, and even allows for things like starting at 10pm Thursday and ending 6am Friday. In addition to Scott's recommendation, I'd also throw in a recommendation for the Timepieces demo in our Code Archive, which includes a pop-up date/time picker form.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
tykra
post May 5 2017, 03:43 PM
Post#4



Posts: 34
Joined: 14-January 17



I will look for your calendar - thanks!

I have hours and minutes because the company bills in 15 minute increments. It made it a bit more fun to calculate totals, but using query expressions makes it pretty simple to do. No one will ever need to track time across dates; for the most part its M-F 8-5 type work; with occasional weekend time that falls within the 24 hour period.

EDIT - checked your calendar form, thanks again for the recommendation it would need quite a bit of reworking as it doesnt appear to show dates by work week. Thinking I might need to create the calendar entirely from a table so that I can link a status field to every single date.


QUOTE (scottgem)
In the code Archives search under my screenname for Calendars. I have one that is table based so may be easier to do what you want

Can you explain why you chose to store the time value as separate hours and minutes instead of time values?

This post has been edited by tykra: May 5 2017, 04:17 PM
Go to the top of the page
 
Peter Hibbs
post May 5 2017, 05:21 PM
Post#5


UtterAccess VIP
Posts: 1,396
Joined: 17-June 10
From: Dorset. UK.


Hi tykra,

Maybe this DEMO PROGRAM will do what you want!

Peter Hibbs.
Go to the top of the page
 
tykra
post May 7 2017, 08:12 PM
Post#6



Posts: 34
Joined: 14-January 17



For those not familiar with this particular calendar here is VBA for Form_Open

CODE
Private Sub Form_Open(Cancel As Integer)
On Error Resume Next
    Dim x%, ctl$
    For x = 1 To 42

    If Month(Me("D" & x)) <> Month(Me![Date]) Then Me("D" & x).ForeColor = RGB(182, 182, 182)
    
    Next x
    
    Set Today = Me("D" & Trim(str$(DateDiff("d", Me!WD, Date))))
    Today.FontBold = True
    Me!Back.SetFocus
    
End Sub

Basically it creates a D# (D1-D42) and then there are 42 text boxes arranged in a grid 6 x 7 - with names (D1-D42) to represent the all possible calendar dates for any given month.

What I want to do is during the load of the form, is check each of those dates against a table of entries to see if the status for any given date is either saved, submitted, or processed.

I attempted something like this inside the loop to no avail, not even sure if its possible or if I am totally on the wrong track .... I got it to work using a dlookup, but that only grabs the first entry it finds I need to check all possibly days on viewed calendar - can this been done?

CODE
    varS = "SELECT [tStatus] FROM tblTimesheets WHERE [tEmp] = 23 AND Me('D' & x) = [tDate]"
    If varS = "Saved" Then
        Me("D" & x).BackColor = RGB(190, 230, 80)
    ElseIf varS = "Submitted" Then
        Me("D" & x).BackColor = RGB(250, 200, 0)
    ElseIf varS = "Processed" Then
        Me("D" & x).BackColor = RGB(220, 220, 220)
    Else
        Me("D" & x).BackColor = RGB(255, 255, 255)
    End If


What do you experts think? Is there another easier way to accomplish what I am wanting to do?

Thanks for the brain-cells. wink.gif
Go to the top of the page
 
cheekybuddha
post May 8 2017, 06:10 AM
Post#7


UtterAccess VIP
Posts: 8,982
Joined: 6-December 03
From: Telegraph Hill


Probably a bit slow to call 42 times per load, but you can use DLookup() to get the value of each day:
CODE
  Dim varS As Variant, lColour As Long

  varS = DLookup("tStatus", "tblTimesheets", "tEmp = 23 AND tDate = " & Format(Me("D" & x), "\#yyyy\-mm\-dd\#"))
  Select Case varS
  Case "Saved"
    lColour = RGB(190, 230, 80)
  Case "Submitted"
    lColour = RGB(250, 200, 0)
  Case "Processed"
     lColour = RGB(220, 220, 220)
  Case Else
    lColour = RGB(255, 255, 255)
  End Select
  Me("D" & x).BackColor = lColour


Otherwise, you might just open a recordset for the required date range, and loop through - this would be better with a helper table of dates so you can sync looping with the looping of x.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
ScottGem
post May 8 2017, 06:48 AM
Post#8


UtterAccess VIP / UA Clown
Posts: 32,047
Joined: 21-January 04
From: LI, NY


Are you talking about my Calendar? it reads from a table of events and displays the events for that date.

--------------------
Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
 
tykra
post May 8 2017, 06:52 PM
Post#9



Posts: 34
Joined: 14-January 17



Sorry no, I am still trying to get Arvin Meyers embedded calendar to work for me.

I've only been doing access for about 6 months so don't know all the tricks. If anyone is confident that they could figure it out PM me, with the right credentials I'd be willing to pay for the work. smile.gif
Go to the top of the page
 
tykra
post May 9 2017, 11:50 AM
Post#10



Posts: 34
Joined: 14-January 17



I tried the dLookup code above and was unable to get it to render the days a different color. I thought it might have been the date format since my dates are mm/dd/yyyy but changing that also didn't result in a render of colors.

I also tried to open a record set and run the dlookup with no success.

I am attaching a bit of the db I am working with; mostly just the embedded calendar and a few of the data entry fields on the form to get a feel of what I am doing.

Again, what I am trying to do is check each "x" in the initial "for" build to see if the date related to the "x" has a status recorded and if so set that "x" backcolor. (probably a horrible explanation; but maybe the db will help see.)
This post has been edited by tykra: May 9 2017, 11:51 AM
Attached File(s)
Attached File  TS_Calendar.zip ( 135.84K )Number of downloads: 3
 
Go to the top of the page
 
cheekybuddha
post May 9 2017, 12:28 PM
Post#11


UtterAccess VIP
Posts: 8,982
Joined: 6-December 03
From: Telegraph Hill


tEmp is defined as text in your tblTimesheets.

Try my original code with the following line substituted:
CODE
varS = "SELECT [tStatus] FROM tblTimesheets WHERE [tEmp] = '23' AND Me('D' & x) = [tDate]"


Note the single quotes around 23 - text values must be delimited by quote marks in the WHERE condition.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post May 9 2017, 12:44 PM
Post#12


UtterAccess VIP
Posts: 8,982
Joined: 6-December 03
From: Telegraph Hill


Also,

Add the following couple of lines to the top of your form's module:
CODE
Option Compare Database
Option Explicit


Also, you probably don't need the On Error Resume Next line in the procedure, and the code might be netter placed in the form's Load event rather than the Open event.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
tykra
post May 9 2017, 12:57 PM
Post#13



Posts: 34
Joined: 14-January 17



Very odd, I tried your updated code and it didnt render, I then put in the original code (with 23 as '23') and it renders ....

CODE
varS = DLookup("tStatus", "tblTimesheets", "tEmp = '23' AND tDate = " & Format(Me("D" & x), "\#yyyy\-mm\-dd\#"))


Going to test this further to see if that does the trick.

Thanks for taking the time to offer some guidance!
This post has been edited by tykra: May 9 2017, 12:58 PM
Go to the top of the page
 
cheekybuddha
post May 9 2017, 03:36 PM
Post#14


UtterAccess VIP
Posts: 8,982
Joined: 6-December 03
From: Telegraph Hill


Ah yes! I mis-copied the line from your version instead of mine! blush.gif

The reason for specifying the format of the date is because the DLookup() effectively is using an SQL statement.

When you pass a condition it must form the WHERE clause of a valid SQL string, and in Access SQL dates are delimited using octothorpes (#), and, as you have seen, strings are delimited by quote marks.

Also, when passing a date in an SQL statement as a criteria it must be in an unambiguous format. Either mm/dd/yyyy or yyyy-mm-dd. It is also possible to use something like 9 May 2017 as well, but that limits you to English versions of Access only.

Since I didn't know your location and regional date format I suggested yyyy-mm-dd. This is because in the UK where I am we natively use dd/mm/yyyy and if I were to pass the date 09/05/2017 to an SQL statement it would be interpreted as 5th September instead of 9th May. Using yyyy-mm-dd works in all cases.

The format expression I suggested for the Format() function takes care of the octothorpes as well. The backslash character indicates that the following character should be represented literally, and not substituted.

You should also note that although your tables display dates in mm/dd/yyyy format, Access does not store the date like this. DateTime is stored as a floating point number with the integer portion representing the number of days elapsed since 31/12/1899 and the fractional portion representing the fraction of 24 hours to indicate the time of day.

As an extra note, did you mean the EmpID to be text datatype? It would be more usual and marginally simpler for you if it were an [Long] Integer datatype. Obviously, if it could ever contain alpha characters then it would need to be text. Passing numbers to SQL requires no delimiters.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
tykra
post May 9 2017, 06:40 PM
Post#15



Posts: 34
Joined: 14-January 17



The tEmp is actually an autonumber / long integer field (actually called EmpID) in my enduser table; I just set up my example very sloppily with 1/8 of all my tables/code etc. The EmpID will actually be filled by passing the id into a tempvar which is pulled into the timesheet forms via query.

I really appreciate the guidance you helped me overcome a big hurdle there.

My next one will be making sure users can not enter a time range that has already been entered. I am hopeful that will be an easier hurdle.

Thanks again!
Go to the top of the page
 
cheekybuddha
post May 9 2017, 06:50 PM
Post#16


UtterAccess VIP
Posts: 8,982
Joined: 6-December 03
From: Telegraph Hill


yw.gif

Good luck with your project!

--------------------


Regards,

David Marten
Go to the top of the page
 
tykra
post May 19 2017, 12:54 PM
Post#17



Posts: 34
Joined: 14-January 17



Ran into another time related issue with my project.

I need to be able to prevent duplicate ranges from being entered - Indexes allow me to prevent an exact time from being entered, but I need to prevent overlaps/ranges. I am planning on doing this via validation in the click event of the submit button.

Upon clicking submit I need to compare the newly entered fields against existing records to make sure time does not fall within the exiting recorded times. Users shouldnt be able to enter a new start time or a new end time that would fall within an already recorded time range.

In the primary form I have controls for tStartHour & tStartMinute then tEndHour & tEndMinute - there are 4 comboboxs that have 24 hours in the hours and (00,15,30,45) in the minutes. When the user selects those values they are formatted in hidden unbound textboxes into times with Format(([tStartHour])) & ":" &[tStartMinute])) AND Format(([tEndHour])) & ":" &[tEndMinute]))

A subform is displayed that shows previous entries made on a particular date - those are filtered by empID so that each users sees their own times.

Where to I need to be headed with this?

Do I set variables equal to queries for start and end times then use those variables in the above between code?

Sorta lost right now on which way to go.

What makes this seem more difficult to me is that their possibly might be multiple times entered on a day by a user. They might enter 8:00-10:00 | 10:00-12:30 | 1:30-4:00 | 4:00-5:00 .... the validation for each subsequent entry would have to consider all times and be less than the earliest entered time and later than the latest entered time. Is that confusing?
This post has been edited by tykra: May 19 2017, 01:16 PM
Go to the top of the page
 
tykra
post May 20 2017, 01:44 PM
Post#18



Posts: 34
Joined: 14-January 17



I have referenced something incorrectly and am overlooking a simple mistake that you MVPs will see at first glance.

As I described above, after entering the times and clicking the submit button to add time this is part of the validation....

The first part gets the users ID - simple enough.

The second part counts items where conditions match parameters and if any return it returns error.

CODE
Dim varID as Integer

varID = DLookup("[ID]", "[EndUsers]", "[UserDBLogin='" & [TempVars]![UserName] & "'")

If Dcount ("*", "Q_subformTimes", "[tStartTime] < '" & Me.txtEndTime &"'  AND [tEndTime] > '" & Me.txtStartTime  &"'  AND [empID] = ' " & varID &" ' AND [tDate] = '"& Me.txtDate &"'") > 0 Then
MsgBox "Error overlapping time entered"

End If


When I try to enter a new time I get error 3464 Data type mismatch in criteria expression.


What is my stupid mistake? Do I need to wrap the Me.txtDate in # ?
This post has been edited by tykra: May 20 2017, 02:36 PM
Go to the top of the page
 
tykra
post May 20 2017, 02:41 PM
Post#19



Posts: 34
Joined: 14-January 17



YES you dummy you need to wrap that in #

CODE
Format(Me.txtDate, "\#yyyy\-mm\-dd\#"))



Works like a charm. geeesh I need to get away from the code!
Go to the top of the page
 
tykra
post May 20 2017, 05:15 PM
Post#20



Posts: 34
Joined: 14-January 17



Talking to myself again ...

I reckon what I am doing is not going to work, it appears that Dlookup is only seeing the first row that matches my empID and tDate values and not ALL rows that match those values. hmph!

Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    25th May 2017 - 10:46 PM