Goto next control

[Gasman]

I have a form and subform that logs employee hours.
The mainform has a combo to select the employee in the header and the subform in the detail.
The subform record source is a query.
As I amend the start and end times the Hours in the record automatically update. These hours are calculated and not stored. The query code is below

However the total control I have in the footer of the main form does not update to the correct value until I select another record, due to my setting it in the Current event of the subform.
I created a control in the footer of the subform with source of

=NZ(DSum("[qryHour]","[qryHours]"),0)

and qryHours is

SELECT Sum(IIf([DateType]=(15 Or 16),0,Round(calctime([starttime],[endtime],[lunch])/60,2))) AS qryHour, tblEmployeeDay.DateType
FROM tblEmployeeDay
WHERE (((tblEmployeeDay.EmployeeID)=[Forms]![frmEmployeeHours]![cboEmployee]) AND ((tblEmployeeDay.Processed) Is Null))
GROUP BY tblEmployeeDay.DateType
HAVING (((tblEmployeeDay.DateType)<>15 And (tblEmployeeDay.DateType)<>16));

and then tried to reference that control as the source for my mainform total control as

=Forms![frmEmployeeDay]![txtSumHours]

but get the #Name? error, so amended the current event of the subform to use

Me.Parent.txtWeekHours = Me.txtSumHours

I have since amended the code for each control to

  Dim strBookMark As String
  strBookMark = Me.Bookmark
  Application.Echo False
  Me.Recalc
  Me.Bookmark = strBookMark
  Application.Echo True

which works fine in that it comes back to the amended record, except it goes to the first control in the datasheet, and I'd like it to go to the next as it would do after a Tab and not having this code.?
Now I only have a few controls on this form, so could hardcode the setfocus for the next control, but was wondering is there a neat way to just go to the next control on a form?

Subform source Query code

SELECT tblEmployeeDay.*, nz(WeekdayName(Weekday([tblEmployeeDay].[DayDate],2),True),"") AS DayName, IIf([DateType]=15 Or [DateType]=16,0,calctime([starttime],[endtime],[lunch])/60) AS Hours
FROM tblDates INNER JOIN tblEmployeeDay ON tblDates.DayDate = tblEmployeeDay.DayDate
ORDER BY tblDates.DayDate;

TIA

 

[arnelgp]

try removing the bookmark code portion. recalc only recalculate the control

 

[Gasman]

try removing the bookmark code portion. recalc only recalculate the control

Hi Arnelgp,
Thanks for the reply.

The reason I have that in there is due to the Recalc putting me to the first record in the form? Also when I tried to validate the times and use Cancel = True the Me.Recalc errored, hence just the warning for the moment.

I admit I am putting this together as I go. I do plan on using that tip of yours of Me.AllowEdits for the form instead of disabling each control when I get this working as well.

Here is all the code for the form (subform) which is in datasheet view.

Option Compare Database
Option Explicit

Private Sub DateType_AfterUpdate()
Dim strBookMark As String
strBookMark = Me.Bookmark
Application.Echo False
Me.Recalc
Me.Bookmark = strBookMark
Application.Echo True

End Sub

Private Sub DayDate_AfterUpdate()
' Get DayID for now. This was replaced by date for ease of use, but still in table just in case.
' Now need to update when date is selected.
Me.DayID = DLookup("DayID", "tbldates", "DayDate = " & Format(Me.DayDate, strcJetDate))
End Sub

Private Sub EndTime_AfterUpdate()
Dim strBookMark As String
strBookMark = Me.Bookmark
Application.Echo False
Me.Recalc
Me.Bookmark = strBookMark
Application.Echo True

End Sub

Private Sub EndTime_Click()
With Me.EndTime
    If .Locked = False Then
        .SelStart = 0
        .SelLength = Len(.Text)
    End If
End With

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.EndTime <= Me.StartTime Then
    MsgBox "End Time should be greater than Start Time", , "Data Validation"
    'Me.StartTime.SetFocus
    'Cancel = True
End If
End Sub

Private Sub Form_Current()
' Disable controls if the processed data is set
'Me.txtFocus.SetFocus
'Call DisEnableCtrl(Me, IsNull(Me.Processed))
Dim ctrl As Control
For Each ctrl In Me.Controls
    If InStr(1, ctrl.Tag, "E", vbTextCompare) > 0 Then ctrl.Locked = Not IsNull(Me.Processed)
Next
'Me.DateType.SetFocus
'Me.Parent.txtWeekHours = Nz(DSum("[qryHour]", "[qryHours]"), 0)
Me.Parent.txtWeekHours = Me.txtSumHours
'Me.Refresh

End Sub

Private Sub Lunch_AfterUpdate()
Dim strBookMark As String
strBookMark = Me.Bookmark
Application.Echo False
Me.Recalc
Me.Bookmark = strBookMark
Application.Echo True

End Sub

Private Sub StartTime_AfterUpdate()
Dim strBookMark As String
strBookMark = Me.Bookmark
'Application.Echo False
Me.Recalc
'Me.Bookmark = strBookMark
'Application.Echo True

End Sub

Private Sub StartTime_Click()
With Me.StartTime
    If .Locked = False Then
        .SelStart = 0
        .SelLength = Len(.Text)
    End If
End With

End Sub

Code for the mainform is

Option Compare Database
Option Explicit

Private Sub cboEmployee_Change()
Me.frmEmployeeDay.SetFocus
'Me.frmEmployeeDay.Form!DateType.SetFocus
'Me.txtWeekHours = Nz(DLookup("[qryHour]", "[qryHours1]", "EmployeeID = " & Me.cboEmployee), 0)
'DoCmd.RunCommand acCmdRecordsGoToLast
End Sub

Private Sub Form_Load()
Me.cboEmployee = Me.cboEmployee.ItemData(0)
Call cboEmployee_Change
End Sub


Private Sub frmEmployeeDay_Enter()
'DoCmd.GoToRecord , , acLast
DoCmd.RunCommand acCmdRecordsGoToLast
End Sub

 

[arnelgp]

recalc will not put you on first record, the requery will. recalc only update the calculation of controls not getting new records.

 

[Gasman]

Hi arnelgp,
I've copied my forms,tables and code to this DB,a nd have commented out the return to the bookmark.
Would you mind taking a look and telling me where I have gone wrong please?, as without resetting the bookmark, I end up on the first record.

Code is in the afterupdate events of starttime, endtime, lunch and datetype.
Form is frmEmployeeHours

Please amend one of the controls as the forms are now to see the end result.

TIA

 

[arnelgp]

mr. gasman, i think you should build new form. it is behaving unlikely. i try setting different color to the working hours but wont accept my changes so i had to remive and replace it. the fix i made, i am sure is temporary. as i stress, you can recalc the form without affecting the position of the cursor.

see the changes i made to the form and ti its code.

because this has taken my time i wasnt able to play war robots.

 

[Gasman]

Thank you arnelgp,

Fortunately as you can see there is not a lot to the form, so I will recreate tomorrow.

Although I asked for assistance I did not mean for you to miss your war robots.

Thank you for taking the time to look.

mr. gasman, i think you should build new form. it is behaving unlikely. i try setting different color to the working hours but wont accept my changes so i had to remive and replace it. the fix i made, i am sure is temporary. as i stress, you can recalc the form without affecting the position of the cursor.

see the changes i made to the form and ti its code.

because this has taken my time i wasnt able to play war robots.

 

[Gasman]

arnelgp,

Have now had a chance to look at you amendments and have implemented them in my DB

Thank you very much for the help.

 

[dianneleigh]

Thank you! I've always done time as numbers and this is a bit confusing. I figured out what is happening though. If the total time is over 24 hours it is resetting it to zero instead of totaling the time.

 

[Gasman]

You need to subtract one from the other but the data must have date & time.
Then just do the math with seconds or minutes depending on how accurate you wish to ne.

My situation is for times to always be in the same day. We do not work nightshift.

HTH

 

[dianneleigh]

You need to subtract one from the other but the data must have date & time.
Then just do the math with seconds or minutes depending on how accurate you wish to ne.

My situation is for times to always be in the same day. We do not work nightshift.

HTH

So instead of having a date field and a time field I need the combo of both?

 

[Gasman]

Possibly or add the time to the date.?
Not something I have had to do, but the dates will be the integers and the rest will be the hours,minutes and seconds.
I suppose it is how you wish to present the data.
Personally I would probably go with a date control and time control, then add them together, then do the math.?

Perhaps some experts will chip in with how they do it?

 

[dianneleigh]

Thank you!