Access连续表单问题：文本框值锁定、单价控制及总成本计算

Hey there, let’s work through these two Access form issues—they’re super common with continuous forms, so I’ve got straightforward fixes for you:

If you want a textbox (like your total cost field) to stay fixed and uneditable, you’ve got a couple of easy options:

• Quick Property Fix
  Open your form in Design View, select the target textbox, and head to the Data tab in the Property Sheet:

  • Set Locked to Yes: This lets users click the textbox but not edit its value.
  • If you want to make it fully unresponsive (gray it out), also set Enabled to No.

• Dynamic Locking with VBA
  If you only want to lock the textbox for existing records (letting users edit it when adding new entries), add this code to your form’s OnCurrent event:

  Private Sub Form_Current()
      ' Lock the textbox unless we're on a new, unsaved record
      Me.TotalCost.Locked = Not Me.NewRecord
  End Sub
  

The issue with all rows updating at once happens because continuous forms reuse a single control instance for every row—so the Change event triggers a global update before Access has locked onto the specific record. Here’s how to fix it and add your custom pricing rules:

Step 1: Swap Change Event for AfterUpdate #

Delete your existing StockIDCombo_Change code and replace it with an AfterUpdate event. This event fires only after the user selects a value and moves away from the combo box, ensuring Access targets only the current record:

Private Sub StockIDCombo_AfterUpdate()
    Dim calculatedPrice As Currency
    
    ' Apply your custom pricing rules
    If Me.StockIDCombo.Value = 2 Then
        calculatedPrice = 0
    Else
        calculatedPrice = 1000
        ' If you still need to pull from the combo's column (e.g., for dynamic prices), use:
        ' calculatedPrice = Me.StockIDCombo.Column(3)
    End If
    
    ' Update ONLY the current record's price field
    Me.PricePerUnit.Value = calculatedPrice
    
    ' Optional: Force the record to save immediately to avoid sync glitches
    Me.Dirty = False
End Sub

Step 2: Make Sure PricePerUnit is Bound to a Table Field #

This is critical! Your form must be bound to a table (e.g., OrderDetails) that has a PricePerUnit field. If PricePerUnit is an unbound control, all rows will share the same value—binding it ensures each record stores its own price independently.

Step 3: Auto-Calculate Total Cost #

For the "Price × Quantity = Total Cost" calculation, no VBA is needed. Just set your total cost textbox’s Control Source to this expression:

=[PricePerUnit]*[Quantity]

It’ll update automatically whenever either value changes.

Bonus: Set Default Price for New Records #

If you want new records to start with a default price (1000, unless StockID is 2), add this to your form’s BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.PricePerUnit.Value = 1000
End Sub

内容的提问来源于stack exchange，提问作者Dev Patel