Hi Daryl,
I'm sorry...I am still having trouble. I think I understand the code,
but I am still confused as to where to put it. I have a form and a subform.
The form has the employee information and a textbox for the Dept Service Time
(where all weeks service are calculated). The subform has each department
name where the employee has worked along with the weeks service in that
department. The Dept Service Time textbox currently has the following
expression which you said that I could leave, if I understood correctly:
=DSum("[WeeksService]","Service Record Query","[EmployeeID] = " &
[EmployeeID] & " And [DepartmentName] = Current_Department_Name")
When I open the window to view my code I have this code:
Private Sub Form_Current()
Dim s As String
Dim rs As DAO.Recordset
s = "SELECT JobTitleName,DepartmentName,LOP" & _
" FROM [Service Record Query] WHERE EmployeeID = " _
& Nz(Me.EmployeeID, 0) & " AND DateEnd Is Null"
If Not Me.NewRecord Then
Set rs = CurrentDb.OpenRecordset(s, dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then
Me.Current_Job_Title_Name = rs!JobTitleName
Me.Current_Department_Name = rs!DepartmentName
Me.Current_LOP = rs!LOP
rs.Close
Set rs = Nothing
Else
Me.Current_Job_Title_Name = Null
Me.Current_Department_Name = Null
End If
End If
End Sub
I put the code you sent above that, but it did not help. I'm sure that was
not where it needed to be. What did I do wrong? Thanks!
--
Judy
Daryl S said:
Judy -
You can leave the code you have in the control source, but you will
over-write it with the if/then/else code from the prior forum. Where to put
this if/then/else depends on how your form is used. You want to run the code
once we know if the department name is Reserves or not. So if you can scroll
through records on this form, you would use the OnCurrent event of the form,
which will run when the form opens and whenever a new record is displayed.
--
Daryl S
:
Hi Daryl,
Thanks for responding. This looks like what I need, but I am unsure as
to where I need to put this code. I have a form titled "Employees" with a
subform titled "Service Record Subform". In the form, I have a text box
where I wanted to sum the weeks service. It is titled "Dept Service Time".
I had the following as the Control Source in that text box:
=DSum("[WeeksService]","Service Record Query","[EmployeeID] = " &
[EmployeeID] & " And [DepartmentName] = Current_Department_Name")
It was working fine until I realized that I needed the code to sum all
of the departments if the Current Department Name was equal to "Reserves".
Do I remove the expression above from the Control Source and add your
code as an Event Procedure? This is my first database and I know just enough
to get me in big trouble.
By the way, the DeptLOP and Current Dept LOP (mentioned in first post)
was added in my attempt to get the desired results. It looks as though I can
remove that by using your code. Any help is greatly appreciated. Thanks!!!!!
--
Judy
:
Judy -
I assume you are populating an unbound control on a form with the week's
service. I am calling this control WeekServiceFieldName, so substitute that
with yours. You did not mention what the DeptLOP was for, so I left that
criteria in place for both cases. Adjust as needed. The if/then statement
indicates that if the department name is Reserves, then don't restrict the
critera to that department name (it will sum all departments). Otherwise
just sum the hours for the given department:
IF Current_Department_Name = "Reserves" Then
Me.WeekServiceFieldName = DSum("WeeksService","Service Record
Query","[EmployeeID]= " & [EmployeeID] & " And [DeptLOP] >= " &
Current_Dept_LOP)
ELSE
Me.WeekServiceFieldName = DSum("WeeksService","Service Record
Query","[EmployeeID]= " & [EmployeeID] & " And [DepartmentName] = '" &
Current_Department_Name & "' And [DeptLOP] >= " & Current_Dept_LOP)
END IF
--
Daryl S
:
I am working with a database that provides me with the weeks service that
each employee works in any given department. The database is working
correctly now, but I have discovered that I need to make a small change to
have the code pick up additional weeks service if the Current Department Name
is “Reservesâ€. For example:
Current Department Name Weeks Service
DeptLOP
Reserves 20
100
Millwright 10
200
Reserves 3
100
Currently the database is giving me a total of 23, which is the number of
weeks that the employee worked in the Reserves Department. I need code that
will also include the weeks service in the Millwright Department to give me a
total of 33 weeks service. I added the DeptLOP along with the following code:
=DSum("WeeksService","Service Record Query","[EmployeeID]= " & [EmployeeID]
& " And [DepartmentName] = Current_Department_Name" & " And [DeptLOP] >=
Current_Dept_LOP" & "")
This did not do the trick. Can anyone please show me where I am going
wrong? Thanks in advance.
