Subform Calculations

  • Thread starter Connell Giacomini
  • Start date


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
- 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
MsgBox strMsg
End If
End Sub

Add error handling.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question