Access loop through continuous form

Question

I am attempting to add a VBA to a command button to look at a text box and if not null, then look at a combo box. If that combo box is null, have a pop up message box. Since it is a continuous form and there are several records showing, it works fine on the first record, however it does not for the following records. I believe this is because I need to loop through each record. Below is the VBA code I have so far. I would greatly appreciate any help regarding the loop, as I am new to VBA.

If Not IsNull(Me.Text33.Value) Then
    If IsNull(Me.Combo24.Value) Then
        MsgBox "You must state if the rationale is aligned with procedures for each disputed subcategory."
    Else
     'do nothing
    End If
End If

DoCmd.Save
DoCmd.Close

Thank you in advance,

Susan

Could you please format your code by highlighting it and hitting Ctrl+K — WhatsThePoint
If Not IsNull(Me.Text33.Value) Then If IsNull(Me.Combo24.Value) Then MsgBox "You must state if the rationale is aligned with procedures for each disputed subcategory." Else 'do nothing End If End If DoCmd.Save DoCmd.Close — Susan
You want pop-up message boxes for all rows in your continuous form which could be hundreds or thousands? Does not seem user-friendly! Do it for the current record as user enters data. — Parfait
There should only be a maximum of 10 and the idea is they would get one pop up message and fix all the rows. — Susan

Kostas K. Kostas K. · Accepted Answer · 2017-07-20T14:26:57

Since this is a continuous form, you can Clone the Form's recordset and loop in each record.

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
    rs.MoveLast
    rs.MoveFirst

Dim idx As Integer
For idx = 1 To rs.RecordCount
    If Not IsNull(rs![TextBoxFieldName]) Then If IsNull(rs![ComboBoxFieldName]) Then MsgBox "..."
    rs.MoveNext
Next idx

Set rs = Nothing

With DoCmd
    .Save
    .Close
End With

Note in case this is intended for validation purposes, the DoCmd actions will always execute regardless of the message-box error/warning.

You can change that by adding an Exit Sub after showing the message box.

Edit:

Sub Example()

    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
        rs.MoveLast
        rs.MoveFirst

    Dim idx As Integer
    For idx = 1 To rs.RecordCount
        Select Case True
            Case Not IsNull(rs![ComboFieldName]) And IsNull(rs![TextBoxFieldName]):
                MsgBox "You must enter dispute comments for each disputed subcategory."
                GoTo Leave

            Case IsNull(rs![ComboFieldName]) And Not IsNull(rs![TextBoxFieldName]):
                MsgBox "You must state if the rationale is aligned with procedures for each disputed subcategory."
                GoTo Leave

            Case Else:
                'do nothing
        End Select
        rs.MoveNext
    Next idx

    Set rs = Nothing
    With DoCmd
        .Save
        .Close
    End With
    Exit Sub

Leave:
    Set rs = Nothing
End Sub

Access loop through continuous form

1 Answers