setting a value in one record equal to the value in another record

I hope we're making some progress, Clifford. I appreciate you hanging in
there with me.

I pasted in your code and made the changes, but I am getting the message
box. Here's what I inserted (with my changes):

Private Sub Form_AfterUpdate()
Dim lngRecordsUpdated As Long

CurrentProject.Connection.Execute _
"update tblPayrollRecords " & _
"set SuspenseBegin = " & SuspenseEnd & " " & _
"where EmployeeID = " & EmployeeID & " and " & _
"[MonthYear] = #" & DateAdd("m", 1, MonthYear) & "#", _
lngRecordsUpdated, adCmdText
If lngRecordsUpdated = 0 Then
MsgBox "No next record found to update."
End If

End Sub

“SuspenseBegin� and “SuspenseEnd� are my actual fieldnames for what I had
been calling the OpeningBalance and EndingBalance. Also, I don’t have a
field named Date.

I assumed I needed to remove the underscore characters at the end of your
lines, which I did.

I KNOW there is a next record to update, so I don't know why I'm getting the
message box.

Jerry


"Clifford Bass" wrote:

Hi Jerry,

That does simplify it. I know we ended up essentially where you
started. The questions and answers were valuable in that they helped me
understand the situation so that I answer the question correctly.

Create an After Update event for your form. In it add this:

Dim lngRecordsUpdated As Long

CurrentProject.Connection.Execute _
"update YourTableName " & _
"set OpeningBalance = " & txtEndingBalance & " " & _
"where EmployeeID = " & txtEmployeeID & " and " & _
"[Date] = #" & DateAdd("m", 1, txtDate) & "#", _
lngRecordsUpdated, adCmdText
If lngRecordsUpdated = 0 Then
MsgBox "No next record found to update."
End If

Where YourTableName is, well, your table's name and txtEndingBalance,
txtEmployeeID and txtDate are the names of the controls on your form. You
may need to do a Me.Refresh or Me.Requery after you do the update to the
table in order for it to show in the form.

A note of caution. Usage of the word Date as a column or other object
name is not a good idea as it is a reserved word. As such sometimes you can
get unexpected results. Better to use a more descriptive name such as
Start_Date or whatever is appropriate. Do a search in Access's online help
for "reserved words" for a list of the reserved words.

Hope this helps,

Clifford Bass

"JWCrosby" wrote:

Actually, it may be simpler than what you described. Picture a form with 12
rows showing, one for each month of the year, with several columns of various
calculations.

When I punch in some numbers it updates the "EndingBalance" using the
formula of [GrossPotential] minus [GrossAvailable].

If, say, I'm working in the January line, I want the updated EndingBalance
for January to become the "BeginningBalance" for February (BeginningBalance
is another column). It's just to save me from having to manually enter the
beginning balance each month since it's always the same as the ending balance
of the previous month. (This form is only used once a month.)

No changes need to be made in any previous months, just the one "next" month.

Need any more information? I've been struggling with this for some time
now. Thanks for any help you may have to offer.

Jerry

"Clifford Bass" wrote:

Hi Jerry,

Sorry for the delay--for some reason I did not get notified of your
response.

I presume then that if you make a change in any one month, then you
will want to update the opening and ending balances of any months between the
changed month and the months up through the first month in the future. Is
that accurate? What are the calculations you do to get the ending balance
for any one month? Those will need to be incorporated into the process.

Clifford Bass

"JWCrosby" wrote:

Actually, Clifford, a record for each month is already created. It's such a
small database I could do that without worry of bloat. So, all 12 months are
visible at one time on the form. I want it so that if I make a change in
January's ending balance it will update the beginning balance in the February
record.

The December record of the previous year exists, too, so January's beginning
balance would have already been set.

Each record has a date field, which is set to the first day of the month
(e.g., 1/1/2009, 2/1/2009, 3/1/2009, etc.) and anEmployeeID key field.

That help any?

Jerry

Hi Jerry,

The underscores are VB/VBA's continuation character that indicates the
statement continues on the following line.

It is rather odd, but the code you have worked fine for me. I wonder
if it is a date format issue. Try changing the DateAdd() part to this:

Format(DateAdd("m", 1, MonthYear), "yyyy/mm/dd")

That should work regardless of your local data format settings.

Clifford Bass

"JWCrosby" wrote:

I hope we're making progress, Clifford. I pasted in your code and made the
changes, but I'm getting the message box. Here's what I have in the code:

Private Sub Form_AfterUpdate()
Dim lngRecordsUpdated As Long

CurrentProject.Connection.Execute _
"update tblPayrollRecords " & _
"set SuspenseBegin = " & SuspenseEnd & " " & _
"where EmployeeID = " & EmployeeID & " and " & _
"[MonthYear] = #" & DateAdd("m", 1, MonthYear) & "#", _
lngRecordsUpdated, adCmdText
If lngRecordsUpdated = 0 Then
MsgBox "No next record found to update."
End If

End Sub

I thought I was supposed remove the underscore characters at the ends of the
lines, but when I tried it a few lines went red and I got errors.

I KNOW there is a next record to update, so I'm not sure why I'm getting the
message box.

Jerry

Clifford, as I continue working on this I'll feed you additional thoughts
until we crack this case.

Rather than "set" the value in the Recalc routine, couldn't I just put a
DLookUp control in the BeginningBalance field on each row that would, in
plain English say:

Look Up the field named “EndingBalance�
In the table named “tblPayrollRecords�
Where
The EmployeeID field in that table equals the EmployeeID field in the
current record
AND
The MonthYear field in that table equals one month earlier than the
MonthYear field in the current record.

The MonthYear field is a date: ex: 1/1/2009

Jerry


"Clifford Bass" wrote:

Hi Jerry,

Sorry for the delay--for some reason I did not get notified of your
response.

I presume then that if you make a change in any one month, then you
will want to update the opening and ending balances of any months between the
changed month and the months up through the first month in the future. Is
that accurate? What are the calculations you do to get the ending balance
for any one month? Those will need to be incorporated into the process.

Clifford Bass

"JWCrosby" wrote:

Actually, Clifford, a record for each month is already created. It's such a
small database I could do that without worry of bloat. So, all 12 months are
visible at one time on the form. I want it so that if I make a change in
January's ending balance it will update the beginning balance in the February
record.

The December record of the previous year exists, too, so January's beginning
balance would have already been set.

Each record has a date field, which is set to the first day of the month
(e.g., 1/1/2009, 2/1/2009, 3/1/2009, etc.) and anEmployeeID key field.

That help any?

Jerry

I hope we're making some progress, Clifford. I appreciate you hanging in
there with me.

I pasted in your code and made the changes, but I am getting the message
box. Here's what I inserted (with my changes):

Private Sub Form_AfterUpdate()
Dim lngRecordsUpdated As Long

CurrentProject.Connection.Execute _
"update tblPayrollRecords " & _
"set SuspenseBegin = " & SuspenseEnd & " " & _
"where EmployeeID = " & EmployeeID & " and " & _
"[MonthYear] = #" & DateAdd("m", 1, MonthYear) & "#", _
lngRecordsUpdated, adCmdText
If lngRecordsUpdated = 0 Then
MsgBox "No next record found to update."
End If

End Sub

“SuspenseBegin� and “SuspenseEnd� are my actual fieldnames for what I had
been calling the OpeningBalance and EndingBalance. Also, I don’t have a
field named Date.

I assumed I needed to remove the underscore characters at the end of your
lines, which I did.

I KNOW there is a next record to update, so I don't know why I'm getting the
message box.

Jerry


"Clifford Bass" wrote:

Hi Jerry,

That does simplify it. I know we ended up essentially where you
started. The questions and answers were valuable in that they helped me
understand the situation so that I answer the question correctly.

Create an After Update event for your form. In it add this:

Dim lngRecordsUpdated As Long

CurrentProject.Connection.Execute _
"update YourTableName " & _
"set OpeningBalance = " & txtEndingBalance & " " & _
"where EmployeeID = " & txtEmployeeID & " and " & _
"[Date] = #" & DateAdd("m", 1, txtDate) & "#", _
lngRecordsUpdated, adCmdText
If lngRecordsUpdated = 0 Then
MsgBox "No next record found to update."
End If

Where YourTableName is, well, your table's name and txtEndingBalance,
txtEmployeeID and txtDate are the names of the controls on your form. You
may need to do a Me.Refresh or Me.Requery after you do the update to the
table in order for it to show in the form.

A note of caution. Usage of the word Date as a column or other object
name is not a good idea as it is a reserved word. As such sometimes you can
get unexpected results. Better to use a more descriptive name such as
Start_Date or whatever is appropriate. Do a search in Access's online help
for "reserved words" for a list of the reserved words.

Hope this helps,

Clifford Bass

"JWCrosby" wrote:

Actually, it may be simpler than what you described. Picture a form with 12
rows showing, one for each month of the year, with several columns of various
calculations.

When I punch in some numbers it updates the "EndingBalance" using the
formula of [GrossPotential] minus [GrossAvailable].

If, say, I'm working in the January line, I want the updated EndingBalance
for January to become the "BeginningBalance" for February (BeginningBalance
is another column). It's just to save me from having to manually enter the
beginning balance each month since it's always the same as the ending balance
of the previous month. (This form is only used once a month.)

No changes need to be made in any previous months, just the one "next" month.

Need any more information? I've been struggling with this for some time
now. Thanks for any help you may have to offer.

Jerry

"Clifford Bass" wrote:

Hi Jerry,

Sorry for the delay--for some reason I did not get notified of your
response.

I presume then that if you make a change in any one month, then you
will want to update the opening and ending balances of any months between the
changed month and the months up through the first month in the future. Is
that accurate? What are the calculations you do to get the ending balance
for any one month? Those will need to be incorporated into the process.

Clifford Bass

"JWCrosby" wrote:

Actually, Clifford, a record for each month is already created. It's such a
small database I could do that without worry of bloat. So, all 12 months are
visible at one time on the form. I want it so that if I make a change in
January's ending balance it will update the beginning balance in the February
record.

The December record of the previous year exists, too, so January's beginning
balance would have already been set.

Each record has a date field, which is set to the first day of the month
(e.g., 1/1/2009, 2/1/2009, 3/1/2009, etc.) and anEmployeeID key field.

That help any?

Jerry

I hope we're making progress, Clifford. I pasted in your code and made the
changes, but I'm getting the message box. Here's what I have in the code:

Private Sub Form_AfterUpdate()
Dim lngRecordsUpdated As Long

CurrentProject.Connection.Execute _
"update tblPayrollRecords " & _
"set SuspenseBegin = " & SuspenseEnd & " " & _
"where EmployeeID = " & EmployeeID & " and " & _
"[MonthYear] = #" & DateAdd("m", 1, MonthYear) & "#", _
lngRecordsUpdated, adCmdText
If lngRecordsUpdated = 0 Then
MsgBox "No next record found to update."
End If

End Sub

I thought I was supposed remove the underscore characters at the ends of the
lines, but when I tried it a few lines went red and I got errors.

I KNOW there is a next record to update, so I'm not sure why I'm getting the
message box.

Jerry

"Clifford Bass" wrote:

Hi Jerry,

That does simplify it. I know we ended up essentially where you
started. The questions and answers were valuable in that they helped me
understand the situation so that I answer the question correctly.

Create an After Update event for your form. In it add this:

Dim lngRecordsUpdated As Long

CurrentProject.Connection.Execute _
"update YourTableName " & _
"set OpeningBalance = " & txtEndingBalance & " " & _
"where EmployeeID = " & txtEmployeeID & " and " & _
"[Date] = #" & DateAdd("m", 1, txtDate) & "#", _
lngRecordsUpdated, adCmdText
If lngRecordsUpdated = 0 Then
MsgBox "No next record found to update."
End If

Where YourTableName is, well, your table's name and txtEndingBalance,
txtEmployeeID and txtDate are the names of the controls on your form. You
may need to do a Me.Refresh or Me.Requery after you do the update to the
table in order for it to show in the form.

A note of caution. Usage of the word Date as a column or other object
name is not a good idea as it is a reserved word. As such sometimes you can
get unexpected results. Better to use a more descriptive name such as
Start_Date or whatever is appropriate. Do a search in Access's online help
for "reserved words" for a list of the reserved words.

Hope this helps,

Clifford Bass

"JWCrosby" wrote:

Actually, it may be simpler than what you described. Picture a form with 12
rows showing, one for each month of the year, with several columns of various
calculations.

When I punch in some numbers it updates the "EndingBalance" using the
formula of [GrossPotential] minus [GrossAvailable].

If, say, I'm working in the January line, I want the updated EndingBalance
for January to become the "BeginningBalance" for February (BeginningBalance
is another column). It's just to save me from having to manually enter the
beginning balance each month since it's always the same as the ending balance
of the previous month. (This form is only used once a month.)

No changes need to be made in any previous months, just the one "next" month.

Need any more information? I've been struggling with this for some time
now. Thanks for any help you may have to offer.

Jerry

"Clifford Bass" wrote:

Hi Jerry,

Sorry for the delay--for some reason I did not get notified of your
response.

I presume then that if you make a change in any one month, then you
will want to update the opening and ending balances of any months between the
changed month and the months up through the first month in the future. Is
that accurate? What are the calculations you do to get the ending balance
for any one month? Those will need to be incorporated into the process.

Clifford Bass

"JWCrosby" wrote:

Actually, Clifford, a record for each month is already created. It's such a
small database I could do that without worry of bloat. So, all 12 months are
visible at one time on the form. I want it so that if I make a change in
January's ending balance it will update the beginning balance in the February
record.

The December record of the previous year exists, too, so January's beginning
balance would have already been set.

Each record has a date field, which is set to the first day of the month
(e.g., 1/1/2009, 2/1/2009, 3/1/2009, etc.) and anEmployeeID key field.

That help any?

Jerry

I hope we're making some progress, Clifford. I appreciate you hanging in
there with me.

I pasted in your code and made the changes, but I am getting the message
box. Here's what I inserted (with my changes):

Private Sub Form_AfterUpdate()
Dim lngRecordsUpdated As Long

CurrentProject.Connection.Execute _
"update tblPayrollRecords " & _
"set SuspenseBegin = " & SuspenseEnd & " " & _
"where EmployeeID = " & EmployeeID & " and " & _
"[MonthYear] = #" & DateAdd("m", 1, MonthYear) & "#", _
lngRecordsUpdated, adCmdText
If lngRecordsUpdated = 0 Then
MsgBox "No next record found to update."
End If

End Sub

I assumed I needed to remove the underscore characters at the end of your
lines, but when I did a few lines went to red and it didn't work Was I
supposed to remove them?

I KNOW there is a next record to update, so I don't know why I'm getting the
message box.

Jerry


"Clifford Bass" wrote:

Hi Jerry,

That does simplify it. I know we ended up essentially where you
started. The questions and answers were valuable in that they helped me
understand the situation so that I answer the question correctly.

Create an After Update event for your form. In it add this:

Dim lngRecordsUpdated As Long

CurrentProject.Connection.Execute _
"update YourTableName " & _
"set OpeningBalance = " & txtEndingBalance & " " & _
"where EmployeeID = " & txtEmployeeID & " and " & _
"[Date] = #" & DateAdd("m", 1, txtDate) & "#", _
lngRecordsUpdated, adCmdText
If lngRecordsUpdated = 0 Then
MsgBox "No next record found to update."
End If

Where YourTableName is, well, your table's name and txtEndingBalance,
txtEmployeeID and txtDate are the names of the controls on your form. You
may need to do a Me.Refresh or Me.Requery after you do the update to the
table in order for it to show in the form.

A note of caution. Usage of the word Date as a column or other object
name is not a good idea as it is a reserved word. As such sometimes you can
get unexpected results. Better to use a more descriptive name such as
Start_Date or whatever is appropriate. Do a search in Access's online help
for "reserved words" for a list of the reserved words.

Hope this helps,

Clifford Bass

"JWCrosby" wrote:

Actually, it may be simpler than what you described. Picture a form with 12
rows showing, one for each month of the year, with several columns of various
calculations.

When I punch in some numbers it updates the "EndingBalance" using the
formula of [GrossPotential] minus [GrossAvailable].

If, say, I'm working in the January line, I want the updated EndingBalance
for January to become the "BeginningBalance" for February (BeginningBalance
is another column). It's just to save me from having to manually enter the
beginning balance each month since it's always the same as the ending balance
of the previous month. (This form is only used once a month.)

No changes need to be made in any previous months, just the one "next" month.

Need any more information? I've been struggling with this for some time
now. Thanks for any help you may have to offer.

Jerry

"Clifford Bass" wrote:

Hi Jerry,

Sorry for the delay--for some reason I did not get notified of your
response.

I presume then that if you make a change in any one month, then you
will want to update the opening and ending balances of any months between the
changed month and the months up through the first month in the future. Is
that accurate? What are the calculations you do to get the ending balance
for any one month? Those will need to be incorporated into the process.

Clifford Bass

"JWCrosby" wrote:

Actually, Clifford, a record for each month is already created. It's such a
small database I could do that without worry of bloat. So, all 12 months are
visible at one time on the form. I want it so that if I make a change in
January's ending balance it will update the beginning balance in the February
record.

The December record of the previous year exists, too, so January's beginning
balance would have already been set.

Each record has a date field, which is set to the first day of the month
(e.g., 1/1/2009, 2/1/2009, 3/1/2009, etc.) and anEmployeeID key field.

That help any?

Jerry

It's working, Clifford! I discovered that I had not included the MonthYear
field in the query behind the form. Once I did that it popped into action.

Thanks for all your help. And sorry for this late posting...the discussion
system wasn't taking my posts much of yesterday.

Jerry

"JWCrosby" wrote:

Clifford, as I continue working on this I'll feed you additional thoughts
until we crack this case.

Rather than "set" the value in the Recalc routine, couldn't I just put a
DLookUp control in the BeginningBalance field on each row that would, in
plain English say:

Look Up the field named “EndingBalance�
In the table named “tblPayrollRecords�
Where
The EmployeeID field in that table equals the EmployeeID field in the
current record
AND
The MonthYear field in that table equals one month earlier than the
MonthYear field in the current record.

The MonthYear field is a date: ex: 1/1/2009

Jerry


"Clifford Bass" wrote:

Hi Jerry,

Sorry for the delay--for some reason I did not get notified of your
response.

I presume then that if you make a change in any one month, then you
will want to update the opening and ending balances of any months between the
changed month and the months up through the first month in the future. Is
that accurate? What are the calculations you do to get the ending balance
for any one month? Those will need to be incorporated into the process.

Clifford Bass

"JWCrosby" wrote:

Actually, Clifford, a record for each month is already created. It's such a
small database I could do that without worry of bloat. So, all 12 months are
visible at one time on the form. I want it so that if I make a change in
January's ending balance it will update the beginning balance in the February
record.

The December record of the previous year exists, too, so January's beginning
balance would have already been set.

Each record has a date field, which is set to the first day of the month
(e.g., 1/1/2009, 2/1/2009, 3/1/2009, etc.) and anEmployeeID key field.

That help any?

Jerry

Just create a function that will return the ending balance for previous month
for the same employee and use the control's default value property
=TheFunction()

JWCrosby wrote:
It's working, Clifford! I discovered that I had not included the MonthYear
field in the query behind the form. Once I did that it popped into action.

Thanks for all your help. And sorry for this late posting...the discussion
system wasn't taking my posts much of yesterday.

Jerry

Clifford, as I continue working on this I'll feed you additional thoughts
until we crack this case.
[quoted text clipped - 44 lines]

Jerry

Hi Jerry,

Yeah, a missing field will do it. Glad to hear you figured it out and
it is working. You are welcome.

Clifford Bass

"JWCrosby" wrote:

After further sleuthing, I got it to work. I didn't have the MonthYear field
selected in the underlying query for the form. Once I added that and made it
an invisible control in the detail section, it worked!

Thanks for all your great help, Clifford. Have a great weekend!

Jerry