Updating Several Subform Records

[Guest]

Hi, folks. While I can update fields through a subform one record at a
time, there are times when it would be convenient to be able to update all of
the subform records at the same time.

In the Northwinds database, there is a field called "Discount" in the Order
Details table, and in the Order Details Extended query and the Orders
Subform. The user can enter a discount percentage on each line item in the
order. I have added a similar "Discount" field in the Orders table, and to
the Orders Qry and the main "Orders" form. I'd like to be able to enter a
value in the Orders Discount field and have Access enter that discount to
every item in that order.

I've done a little poking around, and come up with the following code, based
on suggestions from many of the people in this forum. But there's something
that I'm not understanding, because it doesn't QUITE work yet.

------------------------------------
Private Sub Discount_AfterUpdate()

Dim rst As DAO.Recordset
Set rst = Me.Orders_Subform.Form.RecordsetClone
If rst.RecordCount > 0 Then
With rst
.MoveFirst
Do Until .EOF
Forms!Orders.[orders Subform].Form.Discount = Me.Discount
.MoveNext
Loop
End With
End If
rst.Close

End Sub
---------------------------------------------------

This code works, somewhat, but only updates the order Details.Discount field
for ONE record, not for all the records displayed in the subform. At first,
I thought it was only updating the first record from the subform, but I have
come to realize that if I select some other record by clicking on it and then
update the Order Discount, that the program updates that specific record and
no others.

The goal is to update all of the Order Details records at once, by copying
the Order Discount percentage to each Order Details Discount percentage.

I had thought that the Do Until .EOF .... Loop structure would cause
Access to loop through all of the detail records and update each one, but
apparently not.

Does anyone see where I've made the error? Thanks.

 

[Allen Browne]

You are updating the record in the form repeatedly, instead of the record in
the clone set:

------------------------------------
Private Sub Discount_AfterUpdate()
Dim rst As DAO.Recordset
If Me.Orders_Subform.Form Then
Me.Orders_Subform.Form.Dirty = False
End If
Set rst = Me.Orders_Subform.Form.RecordsetClone
If rst.RecordCount > 0 Then
With rst
.MoveFirst
Do Until .EOF
.Edit
!Discount = Me.Discount
.Update
.MoveNext
Loop
End With
End If
set rst = Nothing
End Sub
---------------------------------------------------

The RecordsetClone has its own currrent record (that's its point), and you
need to use the Edit and Update methods to effect the change.

The save is optional (esp. as you seem to be in the mainform when executing
this.)

BTW, you don't close the cloneset, as you did not open it.
Just set your variable to Nothing.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi, folks. While I can update fields through a subform one record at a
time, there are times when it would be convenient to be able to update all
of
the subform records at the same time.

In the Northwinds database, there is a field called "Discount" in the
Order
Details table, and in the Order Details Extended query and the Orders
Subform. The user can enter a discount percentage on each line item in the
order. I have added a similar "Discount" field in the Orders table, and
to
the Orders Qry and the main "Orders" form. I'd like to be able to enter a
value in the Orders Discount field and have Access enter that discount to
every item in that order.

I've done a little poking around, and come up with the following code,
based
on suggestions from many of the people in this forum. But there's
something
that I'm not understanding, because it doesn't QUITE work yet.

------------------------------------
Private Sub Discount_AfterUpdate()

Dim rst As DAO.Recordset
Set rst = Me.Orders_Subform.Form.RecordsetClone
If rst.RecordCount > 0 Then
With rst
.MoveFirst
Do Until .EOF
Forms!Orders.[orders Subform].Form.Discount = Me.Discount
.MoveNext
Loop
End With
End If
rst.Close

End Sub
---------------------------------------------------

This code works, somewhat, but only updates the order Details.Discount
field
for ONE record, not for all the records displayed in the subform. At
first,
I thought it was only updating the first record from the subform, but I
have
come to realize that if I select some other record by clicking on it and
then
update the Order Discount, that the program updates that specific record
and
no others.

The goal is to update all of the Order Details records at once, by copying
the Order Discount percentage to each Order Details Discount percentage.

I had thought that the Do Until .EOF .... Loop structure would cause
Access to loop through all of the detail records and update each one, but
apparently not.

Does anyone see where I've made the error? Thanks.

 

[Guest]

Allen, I guarantee that Micro$oft doesn't pay you enough to do all that you
do for the members of this group, and I appreciate it.

There appears to be one "Ooops!" in this code, but I'm not sure if it really
matters. I copied this code into my database and ran it, and came up with a
"Type Mismatch" error in the line "If Me.Orders_Subform.Form Then". Looking
at this, it looks like this needs to be an inequality of some sort, but I'm
not sure what it ought to be.

However, when I commented out the If ... End If section (those 3 lines) the
program ran and seems to do everything that I need it to do. I'm guessing
that the If... End If block is to trap some error that might occur, but I'm
not sure what.

Thanks for all your help. Are you updating your book any time soon? I'll
buy a copy...

You are updating the record in the form repeatedly, instead of the record in
the clone set:

------------------------------------
Private Sub Discount_AfterUpdate()
Dim rst As DAO.Recordset
If Me.Orders_Subform.Form Then
Me.Orders_Subform.Form.Dirty = False
End If
Set rst = Me.Orders_Subform.Form.RecordsetClone
If rst.RecordCount > 0 Then
With rst
.MoveFirst
Do Until .EOF
.Edit
!Discount = Me.Discount
.Update
.MoveNext
Loop
End With
End If
set rst = Nothing
End Sub
---------------------------------------------------

The RecordsetClone has its own currrent record (that's its point), and you
need to use the Edit and Update methods to effect the change.

The save is optional (esp. as you seem to be in the mainform when executing
this.)

BTW, you don't close the cloneset, as you did not open it.
Just set your variable to Nothing.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi, folks. While I can update fields through a subform one record at a
time, there are times when it would be convenient to be able to update all
of
the subform records at the same time.

In the Northwinds database, there is a field called "Discount" in the
Order
Details table, and in the Order Details Extended query and the Orders
Subform. The user can enter a discount percentage on each line item in the
order. I have added a similar "Discount" field in the Orders table, and
to
the Orders Qry and the main "Orders" form. I'd like to be able to enter a
value in the Orders Discount field and have Access enter that discount to
every item in that order.

I've done a little poking around, and come up with the following code,
based
on suggestions from many of the people in this forum. But there's
something
that I'm not understanding, because it doesn't QUITE work yet.

------------------------------------
Private Sub Discount_AfterUpdate()

Dim rst As DAO.Recordset
Set rst = Me.Orders_Subform.Form.RecordsetClone
If rst.RecordCount > 0 Then
With rst
.MoveFirst
Do Until .EOF
Forms!Orders.[orders Subform].Form.Discount = Me.Discount
.MoveNext
Loop
End With
End If
rst.Close

End Sub
---------------------------------------------------

This code works, somewhat, but only updates the order Details.Discount
field
for ONE record, not for all the records displayed in the subform. At
first,
I thought it was only updating the first record from the subform, but I
have
come to realize that if I select some other record by clicking on it and
then
update the Order Discount, that the program updates that specific record
and
no others.

The goal is to update all of the Order Details records at once, by copying
the Order Discount percentage to each Order Details Discount percentage.

I had thought that the Do Until .EOF .... Loop structure would cause
Access to loop through all of the detail records and update each one, but
apparently not.

Does anyone see where I've made the error? Thanks.

 

[Allen Browne]

You're right: the code should test the Dirty property:
If Me.Orders_Subform.Form.Dirty Then

I take it you are aware that MS doens't pay MVPs.

No plans to publish at this point.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen, I guarantee that Micro$oft doesn't pay you enough to do all that
you
do for the members of this group, and I appreciate it.

There appears to be one "Ooops!" in this code, but I'm not sure if it
really
matters. I copied this code into my database and ran it, and came up with
a
"Type Mismatch" error in the line "If Me.Orders_Subform.Form Then".
Looking
at this, it looks like this needs to be an inequality of some sort, but
I'm
not sure what it ought to be.

However, when I commented out the If ... End If section (those 3 lines)
the
program ran and seems to do everything that I need it to do. I'm guessing
that the If... End If block is to trap some error that might occur, but
I'm
not sure what.

Thanks for all your help. Are you updating your book any time soon? I'll
buy a copy...

You are updating the record in the form repeatedly, instead of the record
in
the clone set:

------------------------------------
Private Sub Discount_AfterUpdate()
Dim rst As DAO.Recordset
If Me.Orders_Subform.Form Then
Me.Orders_Subform.Form.Dirty = False
End If
Set rst = Me.Orders_Subform.Form.RecordsetClone
If rst.RecordCount > 0 Then
With rst
.MoveFirst
Do Until .EOF
.Edit
!Discount = Me.Discount
.Update
.MoveNext
Loop
End With
End If
set rst = Nothing
End Sub
---------------------------------------------------

The RecordsetClone has its own currrent record (that's its point), and
you
need to use the Edit and Update methods to effect the change.

The save is optional (esp. as you seem to be in the mainform when
executing
this.)

BTW, you don't close the cloneset, as you did not open it.
Just set your variable to Nothing.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi, folks. While I can update fields through a subform one record at
a
time, there are times when it would be convenient to be able to update
all
of
the subform records at the same time.

In the Northwinds database, there is a field called "Discount" in the
Order
Details table, and in the Order Details Extended query and the Orders
Subform. The user can enter a discount percentage on each line item in
the
order. I have added a similar "Discount" field in the Orders table,
and
to
the Orders Qry and the main "Orders" form. I'd like to be able to enter
a
value in the Orders Discount field and have Access enter that discount
to
every item in that order.

I've done a little poking around, and come up with the following code,
based
on suggestions from many of the people in this forum. But there's
something
that I'm not understanding, because it doesn't QUITE work yet.

------------------------------------
Private Sub Discount_AfterUpdate()

Dim rst As DAO.Recordset
Set rst = Me.Orders_Subform.Form.RecordsetClone
If rst.RecordCount > 0 Then
With rst
.MoveFirst
Do Until .EOF
Forms!Orders.[orders Subform].Form.Discount = Me.Discount
.MoveNext
Loop
End With
End If
rst.Close

End Sub
---------------------------------------------------

This code works, somewhat, but only updates the order Details.Discount
field
for ONE record, not for all the records displayed in the subform. At
first,
I thought it was only updating the first record from the subform, but I
have
come to realize that if I select some other record by clicking on it
and
then
update the Order Discount, that the program updates that specific
record
and
no others.

The goal is to update all of the Order Details records at once, by
copying
the Order Discount percentage to each Order Details Discount
percentage.

I had thought that the Do Until .EOF .... Loop structure would cause
Access to loop through all of the detail records and update each one,
but
apparently not.

Does anyone see where I've made the error? Thanks.

 

[Guest]

Thanks; that answers that nagging little question.

I know that M$ doesn't pay MVPs, but in your case, Bill can afford to throw
a few pennies your way - and he ought to. Thanks so much!

If you ever get to northern California, I owe you a beer. Or three.

You're right: the code should test the Dirty property:
If Me.Orders_Subform.Form.Dirty Then

I take it you are aware that MS doens't pay MVPs.

No plans to publish at this point.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen, I guarantee that Micro$oft doesn't pay you enough to do all that
you
do for the members of this group, and I appreciate it.

There appears to be one "Ooops!" in this code, but I'm not sure if it
really
matters. I copied this code into my database and ran it, and came up with
a
"Type Mismatch" error in the line "If Me.Orders_Subform.Form Then".
Looking
at this, it looks like this needs to be an inequality of some sort, but
I'm
not sure what it ought to be.

However, when I commented out the If ... End If section (those 3 lines)
the
program ran and seems to do everything that I need it to do. I'm guessing
that the If... End If block is to trap some error that might occur, but
I'm
not sure what.

Thanks for all your help. Are you updating your book any time soon? I'll
buy a copy...

You are updating the record in the form repeatedly, instead of the record
in
the clone set:

------------------------------------
Private Sub Discount_AfterUpdate()
Dim rst As DAO.Recordset
If Me.Orders_Subform.Form Then
Me.Orders_Subform.Form.Dirty = False
End If
Set rst = Me.Orders_Subform.Form.RecordsetClone
If rst.RecordCount > 0 Then
With rst
.MoveFirst
Do Until .EOF
.Edit
!Discount = Me.Discount
.Update
.MoveNext
Loop
End With
End If
set rst = Nothing
End Sub
---------------------------------------------------

The RecordsetClone has its own currrent record (that's its point), and
you
need to use the Edit and Update methods to effect the change.

The save is optional (esp. as you seem to be in the mainform when
executing
this.)

BTW, you don't close the cloneset, as you did not open it.
Just set your variable to Nothing.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi, folks. While I can update fields through a subform one record at
a
time, there are times when it would be convenient to be able to update
all
of
the subform records at the same time.

In the Northwinds database, there is a field called "Discount" in the
Order
Details table, and in the Order Details Extended query and the Orders
Subform. The user can enter a discount percentage on each line item in
the
order. I have added a similar "Discount" field in the Orders table,
and
to
the Orders Qry and the main "Orders" form. I'd like to be able to enter
a
value in the Orders Discount field and have Access enter that discount
to
every item in that order.

I've done a little poking around, and come up with the following code,
based
on suggestions from many of the people in this forum. But there's
something
that I'm not understanding, because it doesn't QUITE work yet.

------------------------------------
Private Sub Discount_AfterUpdate()

Dim rst As DAO.Recordset
Set rst = Me.Orders_Subform.Form.RecordsetClone
If rst.RecordCount > 0 Then
With rst
.MoveFirst
Do Until .EOF
Forms!Orders.[orders Subform].Form.Discount = Me.Discount
.MoveNext
Loop
End With
End If
rst.Close

End Sub
---------------------------------------------------

This code works, somewhat, but only updates the order Details.Discount
field
for ONE record, not for all the records displayed in the subform. At
first,
I thought it was only updating the first record from the subform, but I
have
come to realize that if I select some other record by clicking on it
and
then
update the Order Discount, that the program updates that specific
record
and
no others.

The goal is to update all of the Order Details records at once, by
copying
the Order Discount percentage to each Order Details Discount
percentage.

I had thought that the Do Until .EOF .... Loop structure would cause
Access to loop through all of the detail records and update each one,
but
apparently not.

Does anyone see where I've made the error? Thanks.