monkeychimps
06-25-2013, 01:27 AM
Hi All,
First time posting for code help - ever! Apologies in advance for any information omitted - happy to fill in anything I've missed.
Requirement: Records appended to an Access table containing Timesheet information, calculations then required based on certain fields to denote who was "on-call" and if they were called out.
Solution: Manager opens a form each week and scrolls through each timesheet to perform their check, on opening each timesheet form calculations are made for rates and written back to the table entry (creating a new entry each time for auditing purposes).
My problem:
I've written some code to loop through each control on the timesheet form looking for matching strings in the name of the controls so the code follows this logic:
1. for each control first test the last 7 characters of name, if they match "Standby" continue
2. Next test the control's first 3 characters, if they match "txt" continue
3. Now look for the value of the control, if it matches rate1 (which is shown in another Dlookup text box on the form) then use the control name to reference the calculated field (so txtMonStandby needs to write rate1 to txtMonMoney). Repeat this for all controls testing all rates.
The problem is my code doesn't seem to affect Monday's field unless Friday's is changed to a value other than Null and changing Friday back to null doesn't revert Monday's...
This is a little specific, here is my code anyway... maybe someone can offer a better way of doing this ? :)
Public Sub StandbyMoneyCalc()
On Error GoTo Err_StandbyMoney
Dim ctrl As Control
'loop through all controls on the form
For Each ctrl In Me.Controls
'for each control assign the variable string ctrlname the string value of the controls name
'Now test the value of the control name, if it ends with "Standby" then continue else skip to end if
If Right(CStr(ctrl.Name), 7) = "Standby" Then
'Next test to see if it begins with "txt", if it does continue but if not then skip to End If
If Left(CStr(ctrl.Name), 3) = "txt" And Right(CStr(ctrl.Name), 7) = "Standby" Then
'Now we've found a valid txtXXXStandby field create a string based on field in the form of
'"txt___Money" where ___ is the day stripped from the current control
'Now test for value of current field against name of each standby rate, when matched assign
'value to the newly defined control [Me.Controls(Left(CStr(ctrl.Name), 6) & "Money")]
If Me.Controls(CStr(ctrl.Name)) = Me.wkNght.Name Then
Me.Controls(Left(CStr(ctrl.Name), 6) & "Money") = Me.wkNght
ElseIf Me.Controls(CStr(ctrl.Name)) = Me.wkEnd.Name Then
Me.Controls(Left(CStr(ctrl.Name), 6) & "Money") = Me.wkEnd
ElseIf Me.Controls(CStr(ctrl.Name)) = Me.BankHol.Name Then
Me.Controls(Left(CStr(ctrl.Name), 6) & "Money") = Me.BankHol
ElseIf Me.Controls(CStr(ctrl.Name)) = Me.Xmas.Name Then
Me.Controls(Left(CStr(ctrl.Name), 6) & "Money") = Me.Xmas
ElseIf Me.Controls(CStr(ctrl.Name)) = Me.XmasST.Name Then
Me.Controls(Left(CStr(ctrl.Name), 6) & "Money") = Me.XmasST
ElseIf Me.Controls(CStr(ctrl.Name)) = Me.XmasEN.Name Then
Me.Controls(Left(CStr(ctrl.Name), 6) & "Money") = Me.XmasEN
ElseIf Me.Controls(CStr(ctrl.Name)) = "" Or IsNull(Me.Controls(Left(CStr(ctrl.Name), 6) & "Money")) Then
Me.txtMonMoney = ""
End If
End If
End If
Next ctrl
Exit_StandbyMoney:
Exit Sub
Err_StandbyMoney:
MsgBox Err.Description
Resume Exit_StandbyMoney
End Sub
First time posting for code help - ever! Apologies in advance for any information omitted - happy to fill in anything I've missed.
Requirement: Records appended to an Access table containing Timesheet information, calculations then required based on certain fields to denote who was "on-call" and if they were called out.
Solution: Manager opens a form each week and scrolls through each timesheet to perform their check, on opening each timesheet form calculations are made for rates and written back to the table entry (creating a new entry each time for auditing purposes).
My problem:
I've written some code to loop through each control on the timesheet form looking for matching strings in the name of the controls so the code follows this logic:
1. for each control first test the last 7 characters of name, if they match "Standby" continue
2. Next test the control's first 3 characters, if they match "txt" continue
3. Now look for the value of the control, if it matches rate1 (which is shown in another Dlookup text box on the form) then use the control name to reference the calculated field (so txtMonStandby needs to write rate1 to txtMonMoney). Repeat this for all controls testing all rates.
The problem is my code doesn't seem to affect Monday's field unless Friday's is changed to a value other than Null and changing Friday back to null doesn't revert Monday's...
This is a little specific, here is my code anyway... maybe someone can offer a better way of doing this ? :)
Public Sub StandbyMoneyCalc()
On Error GoTo Err_StandbyMoney
Dim ctrl As Control
'loop through all controls on the form
For Each ctrl In Me.Controls
'for each control assign the variable string ctrlname the string value of the controls name
'Now test the value of the control name, if it ends with "Standby" then continue else skip to end if
If Right(CStr(ctrl.Name), 7) = "Standby" Then
'Next test to see if it begins with "txt", if it does continue but if not then skip to End If
If Left(CStr(ctrl.Name), 3) = "txt" And Right(CStr(ctrl.Name), 7) = "Standby" Then
'Now we've found a valid txtXXXStandby field create a string based on field in the form of
'"txt___Money" where ___ is the day stripped from the current control
'Now test for value of current field against name of each standby rate, when matched assign
'value to the newly defined control [Me.Controls(Left(CStr(ctrl.Name), 6) & "Money")]
If Me.Controls(CStr(ctrl.Name)) = Me.wkNght.Name Then
Me.Controls(Left(CStr(ctrl.Name), 6) & "Money") = Me.wkNght
ElseIf Me.Controls(CStr(ctrl.Name)) = Me.wkEnd.Name Then
Me.Controls(Left(CStr(ctrl.Name), 6) & "Money") = Me.wkEnd
ElseIf Me.Controls(CStr(ctrl.Name)) = Me.BankHol.Name Then
Me.Controls(Left(CStr(ctrl.Name), 6) & "Money") = Me.BankHol
ElseIf Me.Controls(CStr(ctrl.Name)) = Me.Xmas.Name Then
Me.Controls(Left(CStr(ctrl.Name), 6) & "Money") = Me.Xmas
ElseIf Me.Controls(CStr(ctrl.Name)) = Me.XmasST.Name Then
Me.Controls(Left(CStr(ctrl.Name), 6) & "Money") = Me.XmasST
ElseIf Me.Controls(CStr(ctrl.Name)) = Me.XmasEN.Name Then
Me.Controls(Left(CStr(ctrl.Name), 6) & "Money") = Me.XmasEN
ElseIf Me.Controls(CStr(ctrl.Name)) = "" Or IsNull(Me.Controls(Left(CStr(ctrl.Name), 6) & "Money")) Then
Me.txtMonMoney = ""
End If
End If
End If
Next ctrl
Exit_StandbyMoney:
Exit Sub
Err_StandbyMoney:
MsgBox Err.Description
Resume Exit_StandbyMoney
End Sub