I've got an Access 2007 db with a form (frmEdit), on which there is a subform (subMain), whose record source is "SELECT *
frmEdit is intended to allow the use to filter the results and the fields (columns) displayed in subMain, and to allow them
editing of the entries displayed. This part I've accomplished with a series of unbound controls. I've also added several
bound controls to display the active record. It looks quite a lot like a split form.
But I also want subMain and the bound controls to mirror one another (like in a split form). So when the "FirstName" field of
Jane Smith's record is selected in subMain, I want the bound controls to display Jane's record and for the focus to be set to
the txtFirstName text box. Conversely, if I select txtLastName in the bound controls, I want the "LastName" field in Jane's
record to be highlighted in subMain.
I've tried just using a split form, but I've decided I don't like split forms. I'm sure many of you agree.
The great boblarson has, I think, brought me within arm's reach of achieving the subform functionality I seek, with this
forum post (last post in thread). I can't quite get there, though (I'm still very much a VBA novice). Below is what I've done
and the issues I've been encountering.
I put bob's code (which I still don't quite fully understand) into Private Sub Form_Current() in my Form_subMain Module, and
for the string ("[IDFieldNameHere]=" & Me!IDFieldHere) I've used Public Function BuildFilter() from my Form_frmEdit Module,
but I've removed the "WHERE ".
Bob's code (with my edits):
Dim rst As DAO.Recordset
Set rst = Me.Parent.RecordsetClone
Dim BuildFilter2 As String
Set BuildFilter2 = CStr(Form_frmEdit.BuildFilter)
If Left(BuildFilter2, 6) = "WHERE " Then
BuildFilter2 = Right(BuildFilter2, Len(BuildFilter2) - 6)
If rst.NoMatch Then
msgbox "Error! Contact your IT Representative", vbExclamation, "No Match Found"
Me.Parent.Bookmark = rst.Bookmark
Set rst = Nothing
My (abridged) code:
Private Sub InstantSearch()
'Update subMain record source
Me.subMain.Form.RecordSource = "SELECT * FROM tblMain " & BuildFilter
Public Function BuildFilter() As Variant
Dim varWhere As Variant: varWhere = Null
'Build date parameters into varWhere
If Me.startDateBefore > "" Then
varWhere = varWhere & "[StartDate] = #" & Me.startDateAfter & "# And "
'Tidy and finish varWhere
If IsNull(varWhere) Then
If Right(varWhere, 5) = " And " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
varWhere = "WHERE " & varWhere
BuildFilter = varWhere
I'm getting Compile error: Object required when I try to open frmEdit. "BuildFilter2 =" is highlighted (under "Dim
BuildFilter2 As String"). I wonder if the problem is that Microsoft DAO 3.6 Object Library isn't added as a reference. When I
try to add it, though, I get "Name conflicts with existing module, project, or object library." The other references I have
checked off are "Visual Basic For Applications," "Microsoft Access 12.0 Object Library," "OLE Automation," and "Microsoft
Office 12.0 Access database engine Object Library."
Bob's thread post: http://www.accessforums.net/forms/sp...ing-20194.html