Split Form Behaviour using a form and subform (1 Viewer)

SyntaxSocialist · Jun 7, 2013

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:

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...

pbaldy · Jun 8, 2013

Potentially stupid question, but what is the Default View property of the subform? Can you post the db here?

SyntaxSocialist · Jun 10, 2013

pbaldy said:
Potentially stupid question, but what is the Default View property of the subform? Can you post the db here?

Never stupid. Sometimes (read: often) it's the small things that getcha

Default view of the subform is Datasheet.

Posting the db would require a pretty extensive amount of redaction... I'd rather not do that. But if it comes to it, I suppose...

EDIT: I'm trying to refine everything a bit more, as I'm encountering a lot of errors. As such, posting my db (redacted or not) would not be very helpful at this juncture. Working on it.

SyntaxSocialist · Jun 20, 2013

OK! So the issue turned out to be this (for me)

My table had a primary key. In any other situation, this would be absolutely essential, but the creator of this db didn't create any relationships, so I was able to remove the PK and turn it into an AutoNumber field with no duplicates allowed. I have no idea why the PK was causing such problems, and this is hardly a good solution for anyone with related tables, but it works for my purposes.

Then I created a Public Function (in a module that I have reserved for public procedures):

Code:

Public Function GoToRecord(RecordID As Long)

    Dim strCriteria As String

    If Len(RecordID & vbNullString) > 0 Then
        strCriteria = "RecordID = " & RecordID
        Forms.frmEdit.Recordset.FindFirst strCriteria
    Else
        Forms.frmEdit.Form.Recordset.MoveFirst
    End If

End Function

Then, in the subform (subMain) module, I put:

Code:

Private Sub Form_Current()

    GoToRecord (Me.RecordID.Value)

End Sub

To round things off, I disabled additions to the subform (to avoid a null RecordID) and added

Code:

    Me.Recordset.MoveNext
    Me.subMain.Form.Recordset.MoveNext

to the btnNext_Click Sub procedure, mutatis mutandis for the "Previous," "First," and "Last" buttons.

Split Form Behaviour using a form and subform (1 Viewer)

SyntaxSocialist

Registered User.

pbaldy

Wino Moderator

SyntaxSocialist

Registered User.

SyntaxSocialist

Registered User.

Similar threads

Users who are viewing this thread