Using Code to change a field in all records in a subform

[Amy E. Baggott]

I have an order form that has an order details subform. When the button to
cancel the order is clicked, I want the event to (among other things) cycle
through each item in the order details for that order and set a "cancelled"
flag to true. I know I have to build a loop and go through the recordset for
Order Details for that specific order, but I'm having a brain freeze and
can't figure out how to build the recordset to make sure I only mark the
records for that order. Is there an easy way to do it?

TNX

 

[Marshall Barton]

I have an order form that has an order details subform. When the button to
cancel the order is clicked, I want the event to (among other things) cycle
through each item in the order details for that order and set a "cancelled"
flag to true. I know I have to build a loop and go through the recordset for
Order Details for that specific order, but I'm having a brain freeze and
can't figure out how to build the recordset to make sure I only mark the
records for that order. Is there an easy way to do it?

The subform should be linked to the main form via the
LinkMaster/Child properties. If so, the subform's recordset
would only have records for the main form order and you can
mark all of them.

 

[FrozenJackal]

You could create an update query, then set a macro to trigger the query when
you click a button to cancel the order.

 

[Dirk Goldgar]

I have an order form that has an order details subform. When the button to
cancel the order is clicked, I want the event to (among other things)
cycle
through each item in the order details for that order and set a
"cancelled"
flag to true. I know I have to build a loop and go through the recordset
for
Order Details for that specific order, but I'm having a brain freeze and
can't figure out how to build the recordset to make sure I only mark the
records for that order. Is there an easy way to do it?

Yes, but ...

If the order is cancelled, and that is marked in the Order record, why do
you need to cancel all the individual detail items, too?

To answer your question anyway, presuming that the items in the OrderDetails
table are all linked to the Order record by an OrderID field, you can
execute an update query that updates all the related records right there in
the table, and then refresh the form. No looping required. For example:

CurrentDb.Execute _
"UPDATE OrderDetails SET Cancelled = True " & _
"WHERE OrderID = " & Me.OrderID, _
dbFailOnError

Me.sfOrderDetails.Form.Refresh

But I still don't understand the reason for doing this.

 

[Amy E. Baggott]

The reason is that I have a sales report that sums the quantities in the
order detail records by product, and it needs to ignore cancelled records;
but my boss also wants to be able to look at the order and see what they had,
so I can't just clear them out.

 

[Dirk Goldgar]

The reason is that I have a sales report that sums the quantities in the
order detail records by product, and it needs to ignore cancelled records;
but my boss also wants to be able to look at the order and see what they
had,
so I can't just clear them out.

But your sales report could be based on a query that joins the OrderDetails
table to the Orders table, and excludes records where Orders.Cancelled =
True.

 

[Amy E. Baggott]

Doing this allows for those exhibitors who only cancel part of an order,
rather than the entire order (we get a few every year).

 

[Dirk Goldgar]

Doing this allows for those exhibitors who only cancel part of an order,
rather than the entire order (we get a few every year).

Sure, so you also have a cancelled flag in the OrderDetails record, and
retrieve only records where Orders.Cancelled = False And
OrderDetails.Cancelled = False.

I'm not trying to be argumentative; just trying to suggest the most
efficient way to enforce the premise that if an order is cancelled, all its
detail items are cancelled (so far as sales reports are concerned). I don't
know if there is any useful information in the cancelled status of the
detail items, after an order has been cancelled, which would be lost if
cancelling the order involved also marking all its items as cancelled. But
I can imagine a sequence of events where some -- but not all -- of an
order's detail items have been cancelled, and then you inadvertently cancel
the order. If you then want to "un-cancel" and resurrect the order, you
might regret having had the cancellation mark all the detail items as
cancelled.

 

[Amy E. Baggott]

When I put that code in and replace the form and variable names as shown here"
CurrentDb.Execute _
"UPDATE Forms![frmOrders]![frmOrderDetails] SET [Inactive] = True "
& _
"WHERE OrderID = " & Me.OrderID, _
dbFailOnError
Forms![frmOrders]![frmOrderDetails].Form.Refresh

it compiles fine, but when I try to run the code, I get
Run-time error '3144'

Syntax error in UPDATE statement.

What am I doing wrong? The form frmOrderDetails is a subform of frmOrders,
and the code is actually run from a different form that acts as a dialog for
the user to add information about whether they get a refund and other
pertinent information. All three forms have the field OrderID in common.

 

[Dirk Goldgar]

When I put that code in and replace the form and variable names as shown
here"
CurrentDb.Execute _
"UPDATE Forms![frmOrders]![frmOrderDetails] SET [Inactive] = True
"
& _
"WHERE OrderID = " & Me.OrderID, _
dbFailOnError
Forms![frmOrders]![frmOrderDetails].Form.Refresh

it compiles fine, but when I try to run the code, I get
Run-time error '3144'

Syntax error in UPDATE statement.

What am I doing wrong? The form frmOrderDetails is a subform of
frmOrders,
and the code is actually run from a different form that acts as a dialog
for
the user to add information about whether they get a refund and other
pertinent information. All three forms have the field OrderID in common.

An update query doesn't update a *form*, it updates the table where the
records the form is displaying are stored. In my suggested code, I posited
the name of that table as OrderDetails:

CurrentDb.Execute _
"UPDATE OrderDetails SET Cancelled = True " & _
"WHERE OrderID = " & Me.OrderID, _
dbFailOnError

What is the name of the table that holds the order details records?
Substitute that in my suggested code, in place of "OrderDetails".

 

[Amy E. Baggott]

Ah! I misunderstood. That makes a difference. Thank you. It's working now.
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson

When I put that code in and replace the form and variable names as shown
here"
CurrentDb.Execute _
"UPDATE Forms![frmOrders]![frmOrderDetails] SET [Inactive] = True
"
& _
"WHERE OrderID = " & Me.OrderID, _
dbFailOnError
Forms![frmOrders]![frmOrderDetails].Form.Refresh

it compiles fine, but when I try to run the code, I get
Run-time error '3144'

Syntax error in UPDATE statement.

What am I doing wrong? The form frmOrderDetails is a subform of
frmOrders,
and the code is actually run from a different form that acts as a dialog
for
the user to add information about whether they get a refund and other
pertinent information. All three forms have the field OrderID in common.

An update query doesn't update a *form*, it updates the table where the
records the form is displaying are stored. In my suggested code, I posited
the name of that table as OrderDetails:

CurrentDb.Execute _
"UPDATE OrderDetails SET Cancelled = True " & _
"WHERE OrderID = " & Me.OrderID, _
dbFailOnError

What is the name of the table that holds the order details records?
Substitute that in my suggested code, in place of "OrderDetails".

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)