DSum Code

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.

--
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


"judyb" wrote:

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.

--
Judy

Perhaps the following is what you want

=DSum("WeeksService","Service Record Query","[EmployeeID]= " & [EmployeeID]
& " And ([DepartmentName]=Current_Department_Name OR
[DeptLOP]=Current_Dept_LOP)")

Although instead of
[DeptLOP] = Current_Dept_LOP
I would think you might want
[DeptLOP] = Current_Dept_LOP

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

judyb wrote:
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.

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


"Daryl S" wrote:

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


"judyb" wrote:

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.

--
Judy

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


"judyb" wrote:

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


"Daryl S" wrote:

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


"judyb" wrote:

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.

--
Judy

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" wrote:

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


"judyb" wrote:

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


"Daryl S" wrote:

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


"judyb" wrote:

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.

--
Judy

Judy -

The code needs to be in the main form (where the text box is), not the
subform.

--
Daryl S


"judyb" wrote:

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" wrote:

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


"judyb" wrote:

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


"Daryl S" wrote:

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


"judyb" wrote:

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.

--
Judy

Hi Steve,

I am still not having any luck. I changed the "Dept_Service_Time" textbox
to an unbound textbox and removed the code I had in the textbox. Then I
added your code (on the main form):

Private Sub Dept_Service_Time_OnCurrent()
If Current_Department_Name = "Reserves" Then
Me.Dept_Service_Time = DSum("WeeksService", "Service Record Query",
"[EmployeeID]= " & [EmployeeID])

Else

Me.Dept_Service_Time = DSum("[WeeksService]", "Service Record Query",
"[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] =
Current_Department_Name")

End If

End Sub

I am not getting any error messages, but now I am not even getting an amount
in the Dept_Service_Time textbox at all. I noticed that when I am in Design
view and I open the Properties window, I do not see the option "OnCurrent".
I just went straight to the code and typed it in. Could that be an issue?

Thanks again for you help!
--
Judy


"Daryl S" wrote:

Judy -

The code needs to be in the main form (where the text box is), not the
subform.

--
Daryl S


"judyb" wrote:

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" wrote:

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


"judyb" wrote:

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


"Daryl S" wrote:

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


"judyb" wrote:

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.

--
Judy

Judy -

On Current is an event for the form (your main form), so the code should put
in that event.

--
Daryl S


"judyb" wrote:

Hi Steve,

I am still not having any luck. I changed the "Dept_Service_Time" textbox
to an unbound textbox and removed the code I had in the textbox. Then I
added your code (on the main form):

Private Sub Dept_Service_Time_OnCurrent()
If Current_Department_Name = "Reserves" Then
Me.Dept_Service_Time = DSum("WeeksService", "Service Record Query",
"[EmployeeID]= " & [EmployeeID])

Else

Me.Dept_Service_Time = DSum("[WeeksService]", "Service Record Query",
"[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] =
Current_Department_Name")

End If

End Sub

I am not getting any error messages, but now I am not even getting an amount
in the Dept_Service_Time textbox at all. I noticed that when I am in Design
view and I open the Properties window, I do not see the option "OnCurrent".
I just went straight to the code and typed it in. Could that be an issue?

Thanks again for you help!
--
Judy


"Daryl S" wrote:

Judy -

The code needs to be in the main form (where the text box is), not the
subform.

--
Daryl S


"judyb" wrote:

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" wrote:

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


"judyb" wrote:

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


"Daryl S" wrote:

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


"judyb" wrote:

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.

--
Judy

Thanks Steve,

I finally got it. Really appreciate all you help and patience!

--
Judy


"Daryl S" wrote:

Judy -

On Current is an event for the form (your main form), so the code should put
in that event.

--
Daryl S


"judyb" wrote:

Hi Steve,

I am still not having any luck. I changed the "Dept_Service_Time" textbox
to an unbound textbox and removed the code I had in the textbox. Then I
added your code (on the main form):

Private Sub Dept_Service_Time_OnCurrent()
If Current_Department_Name = "Reserves" Then
Me.Dept_Service_Time = DSum("WeeksService", "Service Record Query",
"[EmployeeID]= " & [EmployeeID])

Else

Me.Dept_Service_Time = DSum("[WeeksService]", "Service Record Query",
"[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] =
Current_Department_Name")

End If

End Sub

I am not getting any error messages, but now I am not even getting an amount
in the Dept_Service_Time textbox at all. I noticed that when I am in Design
view and I open the Properties window, I do not see the option "OnCurrent".
I just went straight to the code and typed it in. Could that be an issue?

Thanks again for you help!
--
Judy


"Daryl S" wrote:

Judy -

The code needs to be in the main form (where the text box is), not the
subform.

--
Daryl S


"judyb" wrote:

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" wrote:

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


"judyb" wrote:

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


"Daryl S" wrote:

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


"judyb" wrote:

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.

--
Judy