SyntaxSocialist
Registered User.
- Local time
- Today, 12:32
- Joined
- Apr 18, 2013
- Messages
- 109
A few months ago I posted a question about how to get a form and subform to behave like a split form (because split forms suck). I'm very close, but am stumbling at the finish line.
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 allows users to search subMain and edit records, so it's got:
- a Search section: with several unbound controls
- a subform (subMain): for displaying the search results
- a Display section: where the current record is displayed using bound controls.
When I click a record in subMain, I want the selected record to be displayed in the main form's bound controls (like a split form does).
Conversely, when I navigate to the next or previous record in the main form, I'd like subMain to reflect that as well. That is a secondary priority, though.
The great boblarson brought me within arm's reach of a solution via this post:
http://www.accessforums.net/forms/sp...ing-20194.html
(last post in thread).
For the string ("[IDFieldNameHere]=" & Me!IDFieldHere) I've used a copy of a Public Function called "BuildFilter()" from a Module containing all my public subs & functions. Buildfilter builds the WHERE clause for my Search section.
The subform and mainform now reflect each other's selections (yay!), but subMain only displays 1 record at a time! (boo!) Obviously, I want it to display all of the search results (or the entire data table, if no search has been executed). frmEdit's navigation controls say there are many records (which is correct), but subMain's say there is only 1... Can anyone help with this?
Bob's code with my edits:
My original (embarassingly uninformed) thread is here:
http://www.access-programmers.co.uk/forums/showthread.php?t=245654
EDIT: Actually, even if I comment out the above code, the subform still only displays one record at a time. This is very perplexing because I have another form that uses an identical (but separate) subform, and it is not exhibiting this behaviour...
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 allows users to search subMain and edit records, so it's got:
- a Search section: with several unbound controls
- a subform (subMain): for displaying the search results
- a Display section: where the current record is displayed using bound controls.
When I click a record in subMain, I want the selected record to be displayed in the main form's bound controls (like a split form does).
Conversely, when I navigate to the next or previous record in the main form, I'd like subMain to reflect that as well. That is a secondary priority, though.
The great boblarson brought me within arm's reach of a solution via this post:
http://www.accessforums.net/forms/sp...ing-20194.html
(last post in thread).
For the string ("[IDFieldNameHere]=" & Me!IDFieldHere) I've used a copy of a Public Function called "BuildFilter()" from a Module containing all my public subs & functions. Buildfilter builds the WHERE clause for my Search section.
The subform and mainform now reflect each other's selections (yay!), but subMain only displays 1 record at a time! (boo!) Obviously, I want it to display all of the search results (or the entire data table, if no search has been executed). frmEdit's navigation controls say there are many records (which is correct), but subMain's say there is only 1... Can anyone help with this?
Bob's code with my edits:
Code:
Private Sub Form_Current()
Dim rst As DAO.Recordset
Set rst = Me.Parent.RecordsetClone
Dim BuildFilter2 As String
BuildFilter2 = BuildFilter
If Len(BuildFilter2 & vbNullString) > 0 Then
'Remove "WHERE (" from the beginning
BuildFilter2 = Right(BuildFilter2, Len(BuildFilter2) - 7)
'Remove ")" from the end
BuildFilter2 = Left(BuildFilter2, Len(BuildFilter2) - 1)
rst.FindFirst BuildFilter2
Else
rst.MoveFirst
End If
If rst.NoMatch Then
MsgBox "No match found.", vbExclamation, "No Match Found"
Else
Me.Parent.Bookmark = rst.Bookmark
End If
rst.Close
Set rst = Nothing
End Sub
My original (embarassingly uninformed) thread is here:
http://www.access-programmers.co.uk/forums/showthread.php?t=245654
EDIT: Actually, even if I comment out the above code, the subform still only displays one record at a time. This is very perplexing because I have another form that uses an identical (but separate) subform, and it is not exhibiting this behaviour...
Last edited: