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

[JWCrosby]

I'll see if I can explain this clearly...

I have a table that, among other things, has the following fields:
EmpllyeeID
OpeningBalance
EndingBalance (a calculated field)
Date

I only have one record per month; never two records for the same EmployeeID
in the same month.

I want the opening balance for one month to equal the ending balance in the
previous month (for the same EmployeeID).

So, when I work on some calculations in a particular month (via a form) and
it calculates the ending balance, I want it to also set the beginning balance
for the next month equal to that.

anyone follow that? I can put the code in an after update event if I knew
how to write it.

Thanks in advance.

Jerry

 

[Clifford Bass]

Hi Jerry,

If you are working in say February, with March not yet here, do you
want to create a new record and then have to update it each time you enter
something into February? Or do you only want to put in the beginning balance
when March is started? Also, in February, do you make changes to January's
information, or earlier? So, if you make a correction in January 2007, you
then have to correct all of the succeeding months of data up through January
2009?

Clifford Bass

 

[JWCrosby]

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

 

[Clifford Bass]

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]

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

 

[JWCrosby]

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

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

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

 

[JWCrosby]

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

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

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

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

:

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

:

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

 

[JWCrosby]

i might also add, Clifford, that one of the fields on the form has Me.Recalc
in the after update event. I was thinking I could put some sort of a "set
the value of the next month's beginning balance equal to the ending balance
of this month" code there, but I haven't figured out how to write it.

Jerry

 

[JWCrosby]

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

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

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

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

:

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

 

[Clifford Bass]

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

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

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]

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

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

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

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

:

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

 

[Clifford Bass]

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]

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

 

[JWCrosby]

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

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

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

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

:

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

 

[JWCrosby]

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

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

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

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

:

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

 

[JWCrosby]

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

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

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

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

:

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

 

[JWCrosby]

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

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