Subforms

[Simon]

I have a form that shows products in an order that need to be
dispatch. it also has a subform that show optional extras for each
product that also need to be despacted with the product

If the main product is not to be despatch i wilol enter
QuantityShipped 0
When i enter 0 is there a wasy that the QuantiyShipped for all the
extras to aultomaticaly change to 0 as welll


Thanks

Simon

 

[Ken Sheridan]

Simon:

You can execute an SQL statement in code which updates the table on which
the subform is based and then requeries the subform. Lets assume the parent
form is based on a table such as OrderDetails with columns OrderID and
ProductID, and the subform is based on a table such as ExtrasShipped with
columns OrderID, ProductID and ExtraID, the former two referencing the
OrderDetails table, the last referencing an Extras table, all these columns
being a number data type. The code, which could go in the BeforeUpdate event
procedure of the QuantityShipped control on the parent form would go like
this:

Const conMESSAGE = "This action will also " & _
"set the quantity shipped of all optional " & _
"extras for this item to 0. Continue?"

Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strSQL = "UPDATE ExtrasShipped" & _
" SET QuantityShipped = 0" & _
" WHERE OrderID = " & Me.OrderID & _
" AND ProductID = " & Me.ProductID

cmd.CommandText = strSQL

If Me.QuantityShipped = 0 Then
If MsgBox(conMESSAGE, vbQuestion + vbYesNo, "Confirm") = vbYes then
cmd.Execute
Me.sfrExtras.requery
Else
Cancel = True
QuantityShipped.Undo
End If
End If

where sfrExtras is the name of the subform control in the parent form, i.e.
the name of the control which houses the subform. Back up the table and test
this well until you are satisfied that its updating the correct rows, and
only those rows.

Ken Sheridan
Stafford, England