Prev: Browse option and sving folder path
Next: Report based on form value
From: Steve on 21 Apr 2010 10:43
With my calendar form/report you can select any caregiver and then view all
the client appointments for the selected caregiver. To see this send me your
email address and I will send you a screen shot.

Steve
santus(a)penn.com


"Jaybird5013" <Jaybird5013(a)discussions.microsoft.com> wrote in message
news:F4EAA2C4-C636-475C-B114-E3C255281A4A(a)microsoft.com...
> I've kind of danced around this topic for a week or so. I hope you will
> forgive me for not knowing the sort of questions to ask initially. I'm
> trying to create a scheduling matrix which will do something like this:
> 04/19/10 04/20/10
> 04/21/10
> CLIENTS
> Monday------------------Tuesday---------------Wednesday---
> Cargiver/ Time Cargiver/ Time
> Cargiver/ Time A. Levign B. Shaw /900-1230 C.
> Califlower/10-2 J. Byrd/11-4
> D. Bowie A. Murphee/8-12 G. Tigliabu/4-8 Y.
> Mama/1-5
> F. Crissake O. Boyaboy/7-5 G. Durnit/10-12 D.
> Gonnit/730-5
>
> Lacking the imagination and expertise to come up with something on my own,
> I
> thought I would confer with the wise and wonderful wizards of the
> Discussion
> Group! (Let me know if I'm laying it on too thick.) I saw Peter Hibbs'
> use
> of Flex Grid and how it does almost Exactly what I want. But I don't know
> how to use it. Then I played around with a crosstab query until I got
> something similar too. I'm beginning to think that I need to totally
> rethink
> my concept. As I type this, I suspect that I need is three or four
> tables.
> One for clients, one for Caregivers, one for Appointments, and maybe one
> that
> creates one field for every day of the year. None of these methods uses
> an
> updatable query as the source for the form as near as I can tell.
> However, I
> know that I can use events to call up forms that will update the table and
> form upon entry. I would like to figure out what the merits and pitfalls
> are
> for these methods. As you can see, my requirements are fairly specific.
> Perhaps my concept is myopic. Perhaps someone can help me to see my way
> through the forest, or at least, a path I can follow.


From: Jaybird5013 on 21 Apr 2010 12:31
Sounds good, Steve, but I've got no money and I would like to learn how to do
this myself! Thanks, anyway...
--
Jaybird


"Steve" wrote:

> With my calendar form/report you can select any caregiver and then view all
> the client appointments for the selected caregiver. To see this send me your
> email address and I will send you a screen shot.
>
> Steve
> santus(a)penn.com
>
>
> "Jaybird5013" <Jaybird5013(a)discussions.microsoft.com> wrote in message
> news:F4EAA2C4-C636-475C-B114-E3C255281A4A(a)microsoft.com...
> > I've kind of danced around this topic for a week or so. I hope you will
> > forgive me for not knowing the sort of questions to ask initially. I'm
> > trying to create a scheduling matrix which will do something like this:
> > 04/19/10 04/20/10
> > 04/21/10
> > CLIENTS
> > Monday------------------Tuesday---------------Wednesday---
> > Cargiver/ Time Cargiver/ Time
> > Cargiver/ Time A. Levign B. Shaw /900-1230 C.
> > Califlower/10-2 J. Byrd/11-4
> > D. Bowie A. Murphee/8-12 G. Tigliabu/4-8 Y.
> > Mama/1-5
> > F. Crissake O. Boyaboy/7-5 G. Durnit/10-12 D.
> > Gonnit/730-5
> >
> > Lacking the imagination and expertise to come up with something on my own,
> > I
> > thought I would confer with the wise and wonderful wizards of the
> > Discussion
> > Group! (Let me know if I'm laying it on too thick.) I saw Peter Hibbs'
> > use
> > of Flex Grid and how it does almost Exactly what I want. But I don't know
> > how to use it. Then I played around with a crosstab query until I got
> > something similar too. I'm beginning to think that I need to totally
> > rethink
> > my concept. As I type this, I suspect that I need is three or four
> > tables.
> > One for clients, one for Caregivers, one for Appointments, and maybe one
> > that
> > creates one field for every day of the year. None of these methods uses
> > an
> > updatable query as the source for the form as near as I can tell.
> > However, I
> > know that I can use events to call up forms that will update the table and
> > form upon entry. I would like to figure out what the merits and pitfalls
> > are
> > for these methods. As you can see, my requirements are fairly specific.
> > Perhaps my concept is myopic. Perhaps someone can help me to see my way
> > through the forest, or at least, a path I can follow.
>
>
> .
>
From: John... Visio MVP on 21 Apr 2010 21:58
"Steve" <notmyemail(a)address.com> wrote in message
news:%23BiH3DW4KHA.4932(a)TK2MSFTNGP06.phx.gbl...
> With my calendar form/report you can select any caregiver and then view
> all the client appointments for the selected caregiver. To see this send
> me your email address and I will send you a screen shot.
>
> Steve






Stevie is our own personal pet troll who is the only one who does not
understand the concept of FREE peer to peer support!
He offers questionable results at unreasonable prices.

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

Please do not feed the trolls.

John... Visio MVP



From: Jaybird5013 on 22 Apr 2010 12:49
Mr. Hibbs,

I've managed to isolate you timesheet example from the rest of the download
to avoid confusing myself... Hey, it happens! I've also managed to
substitute Clients for Employees without incident. Functionality is pretty
much the same. I've tried to substitute StartTime for HoursWorked and
EndTime for HoursOvertime, but the error checking has me stumped. I've
changed the data types on the tblTimeSheets to date and changed the formats
to be added to the tables, but something about the error checking is catching
me. Well, I'm not feeling real good about all the cutting and pasting I'm
doing, but it should help me to learn the basics... Here's the code:

Private Sub flxgrd_LeaveCell()

'Cell loses focus, add new record or update table with amended data

Dim vRow As Long, vCol As Long, vEmployeeID As Long
Dim vWork As Date, vOvertime As Date
Dim vDate As Date

vRow = flxgrd.Row
'fetch Row
vCol = flxgrd.Col
'and Col of leaving cell
If vRow >= conTop And vRow <= flxgrd.Rows - 2 And vCol >= conLeft And
vCol <= conRight Then 'if user exits an editable cell then
vEmployeeID = Val(flxgrd.TextMatrix(vRow, 17))
'fetch EmployeeID for this cell
vDate = txtWeekEnding - (6 - ((vCol - 1) \ 2))
'calc date from Col number and W/E date
If vCol Mod 2 = 0 Then vCol = vCol - 1
'force Col number to 1st col (not Overtime col)
vWork = Format(Val(flxgrd.TextMatrix(vRow, vCol)), "H:nn")
'fetch hours worked from grid
vOvertime = Format(Val(flxgrd.TextMatrix(vRow, vCol + 1)), "H:nn")
'fetch overtime worked from grid
' If vWork > 24 Or vOvertime > 24 Then
'check if not over 24 hours
' Beep
' MsgBox "ERROR. Number of hours worked in a day cannot be
greater than 24 hours, please enter number again.", vbCritical + vbOKOnly,
"Invalid Hours"
Else
If Nz(DLookup("ID", "tblTimeSheets", "EmployeeID = " &
vEmployeeID & " AND WorkDate = #" & Format(vDate, "yyyy/m/d") & "#")) = 0 Then
If flxgrd.TextMatrix(vRow, vCol) <> "" Or
flxgrd.TextMatrix(vRow, vCol + 1) <> "" Then 'if current day is NOT blank
then
CurrentDb.Execute "INSERT INTO tblTimeSheets
(EmployeeID, WorkDate, StartTime, EndTime) VALUES (" _
& vEmployeeID & ", " _
& "#" & Format(vDate, "H:nn ") & "#, " _
& vWork & ", " _
& vOvertime & ")"
'add a new record for this employee/date
End If
Else
CurrentDb.Execute "UPDATE tblTimeSheets SET " _
& "HoursWorked = " & vWork & ", " _
& "HoursOvertime = " & vOvertime & " " _
& "WHERE EmployeeID = " & vEmployeeID & " AND WorkDate = #"
& Format(vDate, "yyyy/m/d") & "#" 'update tblTimeSheets table with amended
data
End If
End If
'End If
FillGrid
'refresh grid

End Sub

It catches right here and won't insert the new format into the cell:

CurrentDb.Execute "INSERT INTO tblTimeSheets
(EmployeeID, WorkDate, StartTime, EndTime) VALUES (" _
& vEmployeeID & ", " _
& "#" & Format(vDate, "H:nn ") & "#, " _
& vWork & ", " _
& vOvertime & ")"
'add a new record for this employee/date
End If


--
Jaybird

From: Peter Hibbs on 22 Apr 2010 14:41
Jaybird,

Well, there is probably more wrong than this but - you changed the two
fields called HoursWorked and HoursOvertime from Number fields in the
table to Date/Time type fields and renamed them to StartTime and
EndTime- correct?

In the INSERT statement you must also change the format that you use
to enter times into those fields. So the line :-

CurrentDb.Execute "INSERT INTO tblTimeSheets
(EmployeeID, WorkDate, StartTime, EndTime) VALUES (" _
& vEmployeeID & ", " _
& "#" & Format(vDate, "H:nn ") & "#, " _
& vWork & ", " _
& vOvertime & ")"

should be something like (watch for word wrapping) :-

CurrentDb.Execute "INSERT INTO tblTimeSheets
(EmployeeID, WorkDate, StartTime, EndTime) VALUES (" _
& vEmployeeID & ", " _
& "#" & Format(vDate, "H:nn ") & "#, " _
& "#" & Format(vWork, "H:nn ") & "#, " _
& "#" & Format(vOvertime, "H:nn ") & "#)"

The same applies to the UPDATE statement after the Else command.

Having said that, I think it is a bad idea if you are storing the
times separately from the date part, the StartDate and EndDate fields
in the table should store the date AND time together. It will make it
easier to check for overlapping appointments and other things at a
later stage.

Whether that will fix your code I can't say for sure but this is the
first thing to correct.

Peter Hibbs.


On Thu, 22 Apr 2010 09:49:01 -0700, Jaybird5013
<Jaybird5013(a)discussions.microsoft.com> wrote:

>Mr. Hibbs,
>
>I've managed to isolate you timesheet example from the rest of the download
>to avoid confusing myself... Hey, it happens! I've also managed to
>substitute Clients for Employees without incident. Functionality is pretty
>much the same. I've tried to substitute StartTime for HoursWorked and
>EndTime for HoursOvertime, but the error checking has me stumped. I've
>changed the data types on the tblTimeSheets to date and changed the formats
>to be added to the tables, but something about the error checking is catching
>me. Well, I'm not feeling real good about all the cutting and pasting I'm
>doing, but it should help me to learn the basics... Here's the code:
>
>Private Sub flxgrd_LeaveCell()
>
>'Cell loses focus, add new record or update table with amended data
>
>Dim vRow As Long, vCol As Long, vEmployeeID As Long
>Dim vWork As Date, vOvertime As Date
>Dim vDate As Date
>
> vRow = flxgrd.Row
> 'fetch Row
> vCol = flxgrd.Col
> 'and Col of leaving cell
> If vRow >= conTop And vRow <= flxgrd.Rows - 2 And vCol >= conLeft And
>vCol <= conRight Then 'if user exits an editable cell then
> vEmployeeID = Val(flxgrd.TextMatrix(vRow, 17))
> 'fetch EmployeeID for this cell
> vDate = txtWeekEnding - (6 - ((vCol - 1) \ 2))
> 'calc date from Col number and W/E date
> If vCol Mod 2 = 0 Then vCol = vCol - 1
> 'force Col number to 1st col (not Overtime col)
> vWork = Format(Val(flxgrd.TextMatrix(vRow, vCol)), "H:nn")
> 'fetch hours worked from grid
> vOvertime = Format(Val(flxgrd.TextMatrix(vRow, vCol + 1)), "H:nn")
> 'fetch overtime worked from grid
> ' If vWork > 24 Or vOvertime > 24 Then
> 'check if not over 24 hours
> ' Beep
> ' MsgBox "ERROR. Number of hours worked in a day cannot be
>greater than 24 hours, please enter number again.", vbCritical + vbOKOnly,
>"Invalid Hours"
> Else
> If Nz(DLookup("ID", "tblTimeSheets", "EmployeeID = " &
>vEmployeeID & " AND WorkDate = #" & Format(vDate, "yyyy/m/d") & "#")) = 0 Then
> If flxgrd.TextMatrix(vRow, vCol) <> "" Or
>flxgrd.TextMatrix(vRow, vCol + 1) <> "" Then 'if current day is NOT blank
>then
> CurrentDb.Execute "INSERT INTO tblTimeSheets
>(EmployeeID, WorkDate, StartTime, EndTime) VALUES (" _
> & vEmployeeID & ", " _
> & "#" & Format(vDate, "H:nn ") & "#, " _
> & vWork & ", " _
> & vOvertime & ")"
> 'add a new record for this employee/date
> End If
> Else
> CurrentDb.Execute "UPDATE tblTimeSheets SET " _
> & "HoursWorked = " & vWork & ", " _
> & "HoursOvertime = " & vOvertime & " " _
> & "WHERE EmployeeID = " & vEmployeeID & " AND WorkDate = #"
>& Format(vDate, "yyyy/m/d") & "#" 'update tblTimeSheets table with amended
>data
> End If
> End If
> 'End If
> FillGrid
> 'refresh grid
>
>End Sub
>
>It catches right here and won't insert the new format into the cell:
>
> CurrentDb.Execute "INSERT INTO tblTimeSheets
>(EmployeeID, WorkDate, StartTime, EndTime) VALUES (" _
> & vEmployeeID & ", " _
> & "#" & Format(vDate, "H:nn ") & "#, " _
> & vWork & ", " _
> & vOvertime & ")"
> 'add a new record for this employee/date
> End If
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Browse option and sving folder path
Next: Report based on form value