Subform Calculations

[Connell Giacomini]

I am using a tab-control continuous subform to enter multiple part numbers
and quantity for a manufacturing process.

Each quantity entered must be increased per a "Add-On %" field, which is a
combo box in the subform listing multiple %'s.

A SetValue macro calculates the entered Quantity * (1 + the Add-On) to
produce a "Total Quantity" for each entry.

The problem is that the calculation is only updating the first entry in
the subform, and ignores the rest of the list.

Your help will be appreciated.

 

[Allen Browne]

Execute an Update query statement that increases all relevant records
directly in the subform's table. Then Requery the subform so it knows about
the update.

This example assumes:
- The button is on the main form.
- The field named ID on the main form is the foreign key for the subform's
table.
- The subform is named Sub1.
- The [Add-On %] text box shows a percentage (e.g. 10%), not just 10.

Private Sub cmdIncrease_Click()
Dim strSql As String
Dim strMsg as String

If Not IsNull(Me.ID) Then
strMsg = strMsg & "Enter/select a record." & vbcrlf
End If

If Not IsNumeric(Me.[Add-On %]) Then
strMsg = strMsg & "Enter a percentage." & vbcrlf
End If

If strMsg = vbNullString Then
strSql = "UPDATE [Table2] SET [Quantity] = [Quantity] * (1 + " &
Me.[Add-On %] & ") WHERE ([ID] = " & Me.[ID] & ";"
dbEngine.Execute strSql, dbFailOnError
Me.[Sub1].Form.Requery
Else
MsgBox strMsg
End If
End Sub

Add error handling.