Access 2013：如何通过窗体计算字段更新Records表？

Alright, let's break this down to get your form working exactly how you want it. Your goal is to auto-fill StartChainage (using the previous record's EndChainage, or 0 for the first record), let users only input EndChainage, and ensure all fields update correctly in the Records table. Here's the step-by-step solution:

1. Set Up the Form's Basic Binding #

First, make sure your Record1 form is bound directly to the Records table:

• Open the form in Design View.
• Set the form's Record Source property to Records (pick this from the dropdown in the Property Sheet).
• Configure each field's text box:
  • ID: Bind to the ID field, set it to Locked and Enabled = No (since it should be an auto-number field, users shouldn't edit it).
  • StartChainage: Bind to the StartChainage field, also set to Locked and Enabled = No (we'll auto-fill this, no user input needed).
  • EndChainage: Bind to the EndChainage field, leave Enabled = Yes so users can input values here.
  • DistanceTraveled: Choose one of these options:
    • Option A (Table-side calculation, recommended): Go to your Records table design, change DistanceTraveled to a Calculated field with the formula [EndChainage]-[StartChainage]. This ensures the value auto-updates no matter how you edit the table.
    • Option B (Form-side calculation): Set this text box's Control Source to =[EndChainage]-[StartChainage] (it'll be a calculated control, but won't save to the table unless you use VBA).

2. Use VBA to Auto-Populate StartChainage #

A standalone DLookUp expression can't handle both new records and existing records reliably, so we'll use VBA events to fill StartChainage automatically.

Open the VBA editor (press Alt + F11 while in the form), then add these code blocks to the form's module:

a. Auto-Fill StartChainage When Adding a New Record #

This triggers when a user starts creating a new record, setting StartChainage to the last record's EndChainage (or 0 if the table is empty):

Private Sub Form_BeforeInsert(Cancel As Integer)
    Dim lastEndValue As Variant
    ' Get the most recent EndChainage from the Records table
    lastEndValue = DLookup("[EndChainage]", "Records", "[ID] = (SELECT Max(ID) FROM Records)")
    ' If no records exist yet, set StartChainage to 0; otherwise use the last EndChainage
    Me.StartChainage = Nz(lastEndValue, 0)
End Sub

b. Update StartChainage When Viewing/Editing Existing Records #

This ensures that if you edit a previous record's EndChainage, the next record's StartChainage updates when you navigate to it:

Private Sub Form_Current()
    If Not IsNull(Me.ID) Then
        If Me.ID = 1 Then
            ' First record always has StartChainage = 0
            Me.StartChainage = 0
        Else
            Dim previousEndValue As Variant
            ' For consecutive IDs, use this line:
            previousEndValue = DLookup("[EndChainage]", "Records", "[ID] = " & Me.ID - 1)
            ' If IDs aren't continuous (e.g., records deleted), replace with this:
            ' previousEndValue = DLookup("[EndChainage]", "Records", "[ID] = (SELECT Max(ID) FROM Records WHERE ID < " & Me.ID & ")")
            Me.StartChainage = Nz(previousEndValue, 0)
        End If
        ' Update DistanceTraveled if you're not using a table-side calculated field
        If Not IsNull(Me.EndChainage) Then
            Me.DistanceTraveled = Me.EndChainage - Me.StartChainage
        End If
    End If
End Sub

c. Update DistanceTraveled When EndChainage Changes #

If you're not using a table-side calculated field, add this to update DistanceTraveled immediately after the user inputs an EndChainage:

Private Sub EndChainage_AfterUpdate()
    If Not IsNull(Me.StartChainage) And Not IsNull(Me.EndChainage) Then
        Me.DistanceTraveled = Me.EndChainage - Me.StartChainage
    End If
End Sub

3. Test the Workflow #

Save your form and test it out to confirm everything works:

• Add a new record: StartChainage should auto-fill to 0 (if it's the first record) or the last record's EndChainage.
• Input an EndChainage: DistanceTraveled should update automatically.
• Navigate to existing records: StartChainage should reflect the previous record's EndChainage, and DistanceTraveled should stay accurate.

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