Access Updating the Subform with Item selected in Listbox

Question

I have the following code which does the following: 1. delete the data from a view table 2. Appends data from the main from to the main table 3. Selects ID & Product nr of the appended record and populates in the view table 4. Clears the controls on the main form 5. Refreshes the form

Note: ID Listbox Rowsource is a Select ID query from the view table. Subform populates when I click on the ID from the listbox. However I would like it to prompt the subform to populate once the ID box is populated.

Problem: After appending the record, the ID listbox populates, it selects the item, however the subform (which is linked through Master - Child) does not requery with record data for the ID nr which is selected in the Listbox.

Private Sub cmdInsert_Click()
Dim ctl As Control
DoCmd.SetWarnings (WarningsOff)
CurrentDb.Execute "delete from EC_ID_Table"
DoCmd.OpenQuery ("EC_Insert_New_Record")
DoCmd.OpenQuery ("EC_ID_Table qry")
For Each ctl In Form_frmEC.Controls
Select Case ctl.ControlType
Case acTextBox
    If ctl.Name <> "ID" Then
        ctl.Value = ""
    End If
    Case acOptionGroup, acComboBox, acListBox
    ctl.Value = Null
Case acCheckBox
    ctl.Value = False
End Select
Next
DoCmd.SetWarnings (Warningson)
MsgBox "Insert Complete!"
Me.Form.Refresh
Me.ID.Selected(0) = True
End Sub

Thank you!

Johnny Bones Johnny Bones · Accepted Answer · 2015-10-27T16:01:19

Try refreshing the subform. I think it goes like this:

Forms!Mainform!Subform1.Form.Refresh

(changing the name of your Mainform and Subform1 where applicable).

Otherwise, you could try rebuilding the RecordSource:

Forms!Mainform!Subform1.Form.RecordSource = "SELECT * FROM MyTable"

Access Updating the Subform with Item selected in Listbox

1 Answers