Which data entry approach to take?

Key issues identified:

When you requery the SubcategoryCB in the CategoryCB_AfterUpdate event, it resets the SubcategoryCB value for all records.

Continuous forms share control properties like RowSource among all instances, leading to unexpected behavior.

Event-driven updates may unintentionally alter data in existing records due to shared state.


Instead of directly modifying the RowSource for SubcategoryCB, use a TempVar or a temporary unbound combo box for filtering.

Private Sub CategoryCB_AfterUpdate()
    ' Use a TempVar to store the selected category
    TempVars("SelectedCategoryID") = Me.CategoryCB.Value

    ' Filter SubcategoryCB only for the current record
    Me.SubcategoryCB.RowSource = "SELECT SubcategoryID, SubcategoryName " & _
                                 "FROM ExpenseSubcategoryT " & _
                                 "WHERE CategoryID = " & TempVars("SelectedCategoryID") & " " & _
                                 "ORDER BY SubcategoryName"
    Me.SubcategoryCB.Requery

    ' Clear SubcategoryCB value only for the current record
    Me.SubcategoryCB.Value = Null

    ' Clear relevant fields
    ClearRelevantFields
End Sub

Add logic to differentiate between new and existing records to prevent overwriting existing data.

Private Sub Form_Current()
    If Me.NewRecord Then
        ' New record: Disable fields until category/subcategory is selected
        Me.MilesTravelledTB.Enabled = False
        Me.MonthsUsedTB.Enabled = False
        Me.AmountTB.Enabled = False
        Me.InvoiceNoTB.Enabled = False
        Me.DescriptionTB.Enabled = False
    Else
        ' Existing record: Enable fields for editing
        Me.MilesTravelledTB.Enabled = True
        Me.MonthsUsedTB.Enabled = True
        Me.AmountTB.Enabled = True
        Me.InvoiceNoTB.Enabled = True
        Me.DescriptionTB.Enabled = True
    End If
End Sub


Modify the ClearRelevantFields procedure to clear fields only for new records or when explicitly required.

Private Sub ClearRelevantFields()
    If Me.NewRecord Then
        Me.MilesTravelledTB.Value = Null
        Me.MonthsUsedTB.Value = Null
        Me.AmountTB.Value = Null
        Me.InvoiceNoTB.Value = Null
        Me.DescriptionTB.Value = Null
    End If
End Sub



Add logic in SubcategoryCB_AfterUpdate to avoid updating CategoryCB if it’s already set

Private Sub SubcategoryCB_AfterUpdate()
    If Not IsNull(Me.SubcategoryCB.Value) And Not Me.NewRecord Then
        ' Access the CategoryID from SubcategoryCB
        Dim CategoryID As Integer
        CategoryID = Me.SubcategoryCB.Column(2)
        
        ' Update the CategoryCB only if it differs
        If Me.CategoryCB.Value <> CategoryID Then
            Me.CategoryCB.Value = CategoryID
        End If
    End If

    ' Enable fields for data entry
    Me.InvoiceNoTB.Enabled = True
    Me.DescriptionTB.Enabled = True
    UpdateColumnVisibility
End Sub

======================================

Create a few dummy records and test adding/editing to ensure the functionality works as expected.
Error Handling:

Add error-handling blocks to gracefully handle unexpected errors, especially in database queries or updates.

On Error GoTo ErrorHandler
' Your code here
Exit Sub
ErrorHandler:
    MsgBox "Error: " & Err.Description, vbExclamation
End Sub

Alternative, use Single Form View
If your use case allows, consider using a single form view for better control over individual records. Continuous forms can be challenging for dynamic controls.