Prev: Using VBA to select Items in multiselect Listbox
Next: Combo Box Drop-down list is based on specific data
From: Steve on 21 Dec 2009 13:14
I have a data sheet sub form called "sfrmSubForm" linked by [idsDataID] to
frmMaster and resideing in the subform control called "fsubSubForm".
frmMaster displays individual records from tblData. sfrmSubForm displays all
the records from the same source. I have created a "click" event such that
when a record is selected from sfrmSubForm, that record is displayed in the
master. This works fine:
Private Sub Form_Click()
' Find the record on the master form that matches the subform.
Dim rs As Object
'Find the selected record
Set rs = Forms!frmMaster.RecordsetClone
rs.FindFirst "[idsDataID] = " & Me![txtDataID]
If Not rs.EOF Then Forms!frmMaster.Bookmark = rs.Bookmark
Unfortunately, when this code runs "Forms!frmMaster.Bookmark = rs.Bookmark",
it deselects the subform. I would like the selected record on the subform to
remain highlighted with that record being displayed on the master form. I
have tried to use the "On Current" event on frmMaster to in turn synchonize
the subform by setting the filter property as follows, but this does not
work. Any advice would be greatly appreciated.
Private Sub Form_Current()
'synchronize the subform with the current record displayed
Forms!frmMaster!fsubSubForm.Form.Filter = "idsDataID = " & Nz(Me.txtDataID, 0)
Where "txtDataID" is a text field on frmMaster that contains the appropriate
From: Mike Painter on 21 Dec 2009 14:00
> Unfortunately, when this code runs "Forms!frmMaster.Bookmark =
> rs.Bookmark", it deselects the subform. I would like the selected
> record on the subform to remain highlighted with that record being
> displayed on the master form.
The easy solution is to switch to Access 2007 which will do this for you
In your case I'd suggest turning things around and make the subform the
master and the full form the child.
This does work and you need write no code.