Prev: Browse option and sving folder path
Next: Report based on form value
From: Jaybird5013 on 23 Apr 2010 14:15
I feel so stupid for doing things this way, but I have to be forced to think
things through or I won't do it correctly. Mr. Hibbs, If I understand your
code correctly, the changes made to the underlying table take place during
the flxgrd_LeaveCell event. If I want everything about your example to
remain the same other than the data in the two cells discussed above, I take
it that it I should concentrate on this section of the code.

If that's true, then I'm guessing that the problem is that I'm re-formatting
either vDate or WorkDate into a format that can't be used by the form. And,
of course, I've disabled the error catcher. Don't see how that's affecting
things, though. The data in my table is fine. The problem is my code. Here
it is again:

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)), "yyyy/m/d")
'fetch hours worked from grid
vOvertime = Format(Val(flxgrd.TextMatrix(vRow, vCol + 1)),
"yyyy/m/d") '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, "yyyy/m/d") & "#, " _
& "#" & Format(vWork, "yyyy/m/d ") & "#, " _
& "#" & Format(vOvertime, "yyyy/m/d ") & "#)"
'add a new record for this
employee/date
End If
Else
CurrentDb.Execute "UPDATE tblTimeSheets SET " & "StartTime =
" & Format(vOvertime, "yyyy/m/d") _
& ", " & "EndTime = " & Format(vOvertime, "yyyy/m/d") & " "
& "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 hangs up at the second execute command.
--
Jaybird
From: Peter Hibbs on 23 Apr 2010 16:58
Jaybird,

I can't be sure because I don't know exactly what data you are
entering into the grid but in the execute UPDATE statement (which I
assume is the one that is not working) you should be enclosing the
variables in # characters because they are Date/Time type variables.

Try replacing the UPDATE statement with this :-

CurrentDb.Execute "UPDATE tblTimeSheets SET StartTime = #" _
& Format(vOvertime, "yyyy/m/d") & "#, EndTime = #" _
& Format(vOvertime, "yyyy/m/d") _
& "# WHERE EmployeeID = " & vEmployeeID _
& " AND WorkDate = #" & Format(vDate, "yyyy/m/d") & "#"

If you are relying on the users to enter a time in a cell and then
copying that data back to the table I think this is a bit fraught
because it relies on the users entering the time in exactly the right
format, i.e. something like 09:00 or whatever, if they enter something
like 09am instead the code will just fail. Remember, a Flex Grid can
only store text strings, it cannot automatically correct the date
and/or time for you if it is entered incorrectly. If this is what you
need it might be safer to add some checks on the entered text before
you copy it to the grid.

Anyway, try the mods above, if that doesn't work you can send me a
copy of the database and I will have a look at it.

Peter Hibbs.


On Fri, 23 Apr 2010 11:15:01 -0700, Jaybird5013
<Jaybird5013(a)discussions.microsoft.com> wrote:

>I feel so stupid for doing things this way, but I have to be forced to think
>things through or I won't do it correctly. Mr. Hibbs, If I understand your
>code correctly, the changes made to the underlying table take place during
>the flxgrd_LeaveCell event. If I want everything about your example to
>remain the same other than the data in the two cells discussed above, I take
>it that it I should concentrate on this section of the code.
>
>If that's true, then I'm guessing that the problem is that I'm re-formatting
>either vDate or WorkDate into a format that can't be used by the form. And,
>of course, I've disabled the error catcher. Don't see how that's affecting
>things, though. The data in my table is fine. The problem is my code. Here
>it is again:
>
>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)), "yyyy/m/d")
> 'fetch hours worked from grid
> vOvertime = Format(Val(flxgrd.TextMatrix(vRow, vCol + 1)),
>"yyyy/m/d") '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, "yyyy/m/d") & "#, " _
> & "#" & Format(vWork, "yyyy/m/d ") & "#, " _
> & "#" & Format(vOvertime, "yyyy/m/d ") & "#)"
> 'add a new record for this
>employee/date
> End If
> Else
> CurrentDb.Execute "UPDATE tblTimeSheets SET " & "StartTime =
>" & Format(vOvertime, "yyyy/m/d") _
> & ", " & "EndTime = " & Format(vOvertime, "yyyy/m/d") & " "
>& "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 hangs up at the second execute command.
From: Jaybird5013 on 24 Apr 2010 08:51
Perhaps I should be calling a form which will fill the grid for me in the
proper format. (of course, I don't know the peculiarities of the flexgrid
yet)
--
Jaybird

From: Peter Hibbs on 24 Apr 2010 10:16
Jaybird,

If you mean that when you click on a cell it pops up another form
which shows the information relating to that cell (i.e. the Client
info from the row and the Carer info from the column) then, yes, I
think that would be preferable (and easier to code) in your situation.

The method you are currently using (with a hidden sub-form appearing
over the selected cell, etc) is quite difficult to do in code and has
some limitations.

If you need an example of using a separate pop-up form you can look at
the Northwind Orders Summary form (on the green menu) in the Flex Grid
Demo database which does something similar or you could download the
Crosstab Flex Grid demo from my Web site which shows the same thing in
more detail.

Basically, when you click on a cell you would use the click event to
open a form in acDialog mode and pass the ID of the Client (from the
Row) and the ID of the Carer (or the date, if that is easier) from the
Column to the pop-up form as an OpenArgs and then in the pop-up form
you can show all the information for that appointment by collecting it
from the relevant tables. The user would make any changes or add a new
appointment or whatever and click an OK button which would trigger
some code to write the data back to the tables. When the form closes
the Flex Grid control would be then redrawn to show the amended data.

It sounds complicated (I suppose it is really) but it gives you more
flexibility than your current method.

Actually, if you download the Appointments/Bookings demo form the Web
site, all this code is already done for you (although you would
obviously have to change your field and tables names, etc).

Peter Hibbs.

On Sat, 24 Apr 2010 05:51:01 -0700, Jaybird5013
<Jaybird5013(a)discussions.microsoft.com> wrote:

>Perhaps I should be calling a form which will fill the grid for me in the
>proper format. (of course, I don't know the peculiarities of the flexgrid
>yet)
First  |  Prev  | 
Pages: 1 2 3 4
Prev: Browse option and sving folder path
Next: Report based on form value