Get Control Value for Current Record in Continuous Subform

Question

Background

Microsoft Access 2010 database.
Subform control is a continuous form control.
Each record in the continuous form has several controls (comboboxes, textboxes) and a delete button.
The underlying data source for the subform is a temp table.

Desired Behavior

When the delete button is clicked for a record on the subform, the record should be deleted from the display, temp table, and another permanent table.

Note

Temp table is created by query using several permanent tables. Normally the structure would be different, however this particular solution required normalization and re-creation of a new wide temp table (similar to a SQL view) to handle manipulating all the data in one form (subform included).

Problem

Whenever the delete button is clicked the subroutine's code fails to properly populate any of the controls for the current record in the subform.

Code

Private Sub btnDelete_Click()
On Error GoTo Err_Handler

  Dim Reply As Integer
  Reply = MsgBox("Are you sure you want to delete this record?", vbYesNo, "Species Delete")  

  'do the deletion for temp & underlying tables
  If Reply = 6 Then

    'delete the record in temp_table
    DoCmd.SetWarnings False
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
    DoCmd.SetWarnings True

    'do for @ Quadrat that has a SpeciesCover record
    Dim i As Integer
    Dim strControl As String

    For i = 1 To QUADRATS_PER_TRANSECT

        strControl = "tbxCoverID_Q" & i

        'only delete existing records (these should have a Cover ID value)
        If Me.Controls(strControl) > 0 Then

            Dim sp As New CoverSpecies

            With sp
                .CoverID = Me.Controls(strControl)

                .DeleteCoverRecord

            End With

        End If

        Next

  End If

Exit_Handler:
    Exit Sub
Err_Handler:
    Select Case Err.Number
      Case Else
        MsgBox "Error #" & Err.Number & ": " & Err.Description, vbCritical, _
            "Error encountered (#" & Err.Number & " - btnDelete_Click[Cover form])"
    End Select
    Resume Exit_Handler
End Sub

Symptoms

The code runs properly, except controls in the subform's current record are always NULL so the ID for the record needing deletion from the underlying table (not the temp) is never identified and that record is not deleted.

NWdev NWdev · Accepted Answer · 2017-07-24T18:29:10

Initially I considered the following possible issues:

subform control was not being referenced properly
current record was somehow always the "new" record which would have NULL values

Referencing was checked using the immediate panel in the IDE while stepping through the code. ?Me.Name provided the proper subform name and other controls for the subform (outside of continuous record detail controls) provided accurate values. ?Me.Controls(strControl) continued to produce NULL regardless of how it was referenced.

Identifying the current record in the continuous form was considered, but every search for related information on Google suggested that I should be able to reference the subform control directly since doing so would always give me the current record's controls.

Considering what the current record was made me think a bit about what I was wanting to do. That was:

delete the record from the display
delete the record from the temp table
delete the record from the permanent table

The first two were not problematic. The display immediately deleted the record from the display and also the temp table since the controls were bound to that record. Once that was done I needed the ID of the record to delete...

The "aha" moment came with that last consideration.

I could never get the ID of the record to delete because... it was deleted already!

As soon as the following code was shifted to the end - just be fore the last End If statement...

'delete the record in temp_table
DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.SetWarnings True

... the code ran properly and deleted from both the temp & permanent tables.

Lessons Learned

When troubleshooting code actions for continuous forms -- particularly delete actions, remember to consider the sequence of the steps you are working through. With deletes, if your controls yield NULL it's entirely possible you already removed the record you wanted information from.

When working with continuous forms, when you reference a control (e.g. Me.tbxMyControl) the value you receive is from the current record. You don't need to do anything else to retrieve it.

Hope this helps someone else avoid spinning wheels when the solution is simply to consider your process sequence.

Get Control Value for Current Record in Continuous Subform

1 Answers