How to clear subforms when master form combo box is blank?

Question

I have a form made with a combo box. The user selects or types in the application number and then three subforms populate with data associated with that application number with an after update macro associated with the parent form combo box. It works great, so no issues there.

However, when the parent form's combo box is empty/blank/null, the subforms show a random record's data. Is there a way, without using VBA, to have the subforms display empty/blank upon load and then only populate after updating the combo box? As an alternative, I tried creating a blank record that thinking that a blank combo box would trigger the subforms to display the blank record, but that's not working.

Help please and thank you!

Answer 1

Is the main form bound? If not, you have the choice of linking the combobox to each subform via the Master/Child Link Fields, so you won't need to use a macro or VBA to update the subforms when you select an item from the combobox.

Answer 2

Figured it out. In DesignView for the main form, I selected the main form's box in the upper lefthand corner. Then, in the Property Sheet, I added a macro in the On Load field using the GoToRecord action to navigate the main form to a New Record. I had been trying to do this through the combo box, only to realize from your comments that it is the main form that controls the subforms, the combo box just affects the main form...eureka!

Answer 3

You might want to consider adding an item to the combo box's list to go to allow the user to elect to insert a new record in the parent form and subforms. The following is an example of the RowSource for a combo box to go to a contact record. The <New Contact> row is added at the top of the list by means of a UNION operation:

SELECT
    ContactID,
    FirstName & " " & LastName,
    1 AS SortColumn,
    LastName,
    Firstname
FROM
    Contacts
UNION
SELECT 
    0,
    "<New Contact>",
    0,    
    "",
    ""
FROM
    Contacts
ORDER BY
    SortColumn,
    LastName,
    FirstName;

The combo box's ColumnCount property is set to 2, and its ColumnWidths property to 0cm to hide the first column. The code in the combo box's AfterUpdate procedure to go to the selected record is:

Private Sub cboGotoContact_AfterUpdate()

    Const MESSAGETEXT = "No matching record"
    Dim ctrl As Control
    
    Set ctrl = Me.ActiveControl
    
    If Not IsNull(ctrl) Then
        If ctrl = 0 Then
            ' go to new record and move focus to FirstName control
            DoCmd.GoToRecord acForm, Me.Name, acNewRec
            Me.FirstName.SetFocus
        Else
            With Me.RecordsetClone
                .FindFirst "ContactID = " & ctrl
                If Not .NoMatch Then
                    ' go to record by synchronizing bookmarks
                    Me.Bookmark = .Bookmark
                Else
                    MsgBox MESSAGETEXT, vbInformation, "Warning"
                End If
            End With
        End If
    End If
    
End Sub