If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
DSum Code
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 |
#3
|
|||
|
|||
DSum Code
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. |
#4
|
|||
|
|||
DSum Code
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 |
#5
|
|||
|
|||
DSum Code
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 |
#6
|
|||
|
|||
DSum Code
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 |
#7
|
|||
|
|||
DSum Code
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 |
#8
|
|||
|
|||
DSum Code
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 |
#9
|
|||
|
|||
DSum Code
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 |
#10
|
|||
|
|||
DSum Code
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 |
Thread Tools | |
Display Modes | |
|
|