SyntaxSocialist
Registered User.
- Local time
- Today, 04:12
- Joined
- Apr 18, 2013
- Messages
- 109
I've got an Access 2007 db with a form (frmEdit), on which there is a subform (subMain), whose record source is "SELECT * FROM tblMain;".
frmEdit is intended to allow the use to filter the results and the fields (columns) displayed in subMain, and to allow 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 didn't like it much. 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):
My (abridged) code:
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/split-form-orientation-coding-20194.html
frmEdit is intended to allow the use to filter the results and the fields (columns) displayed in subMain, and to allow 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 didn't like it much. 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):
Code:
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)
End If
rst.FindFirst BuildFilter2
If rst.NoMatch Then
msgbox "Error! Contact your IT Representative", vbExclamation, "No Match Found"
Else
Me.Parent.Bookmark = rst.Bookmark
End If
rst.Close
Set rst = Nothing
My (abridged) code:
Code:
Private Sub InstantSearch()
'Update subMain record source
Me.subMain.Form.RecordSource = "SELECT * FROM tblMain " & BuildFilter
'Requery subMain
Me.subMain.Requery
End Sub
'________________________________________________________________
Public Function BuildFilter() As Variant
Dim varWhere As Variant: varWhere = Null
'...
'Build date parameters into varWhere
If Me.startDateBefore > "" Then
varWhere = varWhere & "[StartDate] <= #" & Me.startDateBefore & "# And "
End If
If Me.startDateAfter > "" Then
varWhere = varWhere & "[StartDate] >= #" & Me.startDateAfter & "# And "
End If
'...
'Tidy and finish varWhere
If IsNull(varWhere) Then
'Do nothing
Else
If Right(varWhere, 5) = " And " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
varWhere = "WHERE " & varWhere
End If
End If
BuildFilter = varWhere
End Function
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/split-form-orientation-coding-20194.html
Last edited: