Split Form Behaviours with Subform (1 Viewer)

SyntaxSocialist · Apr 18, 2013

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):

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

pbaldy · Apr 24, 2013

You don't "Set" a string variable. Try without that word.

BuildFilter2 = CStr(Form_frmEdit.BuildFilter)

SyntaxSocialist · Apr 24, 2013

pbaldy said:
You don't "Set" a string variable. Try without that word.

BuildFilter2 = CStr(Form_frmEdit.BuildFilter)

Thanks. Now I'm getting a new error, though:

Run-time error '94':
Invalid use of Null

Clicking Debug highlights the line you advised me to change.

pbaldy · Apr 24, 2013

On that line? Then the BuildFilter function is returning a Null, which a string variable can't accept. You either need to change what the function returns or declare that variable as Variant, which can take a Null.

SyntaxSocialist · Apr 24, 2013

pbaldy said:
On that line? Then the BuildFilter function is returning a Null, which a string variable can't accept. You either need to change what the function returns or declare that variable as Variant, which can take a Null.

Ok, so I initialized varWhere as "" instead of as Null. That makes sense, right?
That led to another error, though:

Run-time error '3077':
Syntax error (missing operator) in expression

Clicking Debug highlights

Code:

rst.FindFirst BuildFilter2

pbaldy · Apr 24, 2013

You need to know what the string contains. One way is the Immediate window:

http://www.baldyweb.com/ImmediateWindow.htm

or set a breakpoint:

http://www.baldyweb.com/Debugging.htm

SyntaxSocialist · Apr 24, 2013

pbaldy said:
You need to know what the string contains. One way is the Immediate window:

http://www.baldyweb.com/ImmediateWindow.htm

or set a breakpoint:

http://www.baldyweb.com/Debugging.htm

Ok, I'll play around with that (though hovering over BuildFilter2 informed me it was ""). I'm new to VBA, and thus also to debugging, so please forgive my next question:

The run button makes me create a macro? I don't understand. I just want to step through the code in my existing module, no?

EDIT: A similar problem has been described here:

http://www.access-programmers.co.uk/forums/showthread.php?t=214801

SyntaxSocialist · Apr 24, 2013

Ok yeah so I can't get the Immediate window to display the value of BuildFilter2, even when I'm actually able to step into each line of code and "properly" debug. But hovering over it tells me BuildFilter2 = "". So that's a start.

I assume that "" is an invalid criteria for .FindFirst. Aside from that I'm still at a loss.

pbaldy · Apr 24, 2013

I would set a breakpoint at the beginning of the process and step through it. That should help you find where the problem is.

boblarson · Apr 24, 2013

SyntaxSocialist - I just got your PM from a couple of days ago.

Using your code from the first post, not the one you said was mine with your changes, but the second one. I included notes with #### before and after where I wrote notes.

Code:

Private Sub InstantSearch()
'Update subMain record source
    Me.subMain.Form.RecordSource = "SELECT * FROM tblMain " & BuildFilter
    
'Requery subMain
'#### No need for this - when the record source is assigned it automatically requeries ####
 
End Sub
'________________________________________________________________
Public Function BuildFilter() As String
' ########variants are null when first instantiated so you don't need to do that.######
' ##### You are going to be building a string, so use string types.  A string with no value will be an empty string "" and not null unlike #####
' #####the variant type if there is no value returned.#####
Dim strWhere As String

    'Build date parameters into varWhere
    '##### You need different code to check for a value, your current does not take Null into account.####
    '##### so use this instead ####
    If Len(Me.startDateBefore & vbNullString) > 0 Then
        strWhere = strWhere & "[StartDate] <= #" & Me.startDateBefore & "# And "
    End If
 
    If Len(Me.startDateAfter & vbNullString) > 0 Then
        strWhere = strWhere & "[StartDate] >= #" & Me.startDateAfter & "# And "
    End If
 
    'Tidy and finish varWhere
    '##### no need to use an if that has nothing after it.  Check for what you want.#####
    If strWhere <> vbNullString Then
        If Right(strWhere, 5) = " And " Then
            strWhere = Left(strWhere, Len(strWhere) - 5)
            strWhere = "WHERE " & strWhere
        End If
    End If
 
'#### because you used a string and, if nothing is returned but an empty string, it will just make your #####
'#### Select statement not have any limit unlike your use of variant and setting it to Null.####
    BuildFilter = strWhere
    
End Function

SyntaxSocialist · Apr 25, 2013

Bob, thanks so much for getting back to me. I really really appreciate it. I've taken your advice and changed the variants in buildfilter to strings. My code (modified from your original) now looks like:

Code:

Private Sub Form_Current()

    Dim rst As DAO.Recordset
    Set rst = Me.Parent.RecordsetClone

    Dim BuildFilter2 As String
    BuildFilter2 = 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 "No match found.", vbExclamation, "No Match Found"
    Else
        Me.Parent.Bookmark = rst.Bookmark
    End If

    rst.Close
    Set rst = Nothing

End Sub

Still getting that 3077 (missing operator) error at the "rst.FindFirst BuildFilter2" line. I'll keep plugging away at it, though.

pbaldy: I still can't get the Immediate window to display anything. Don't know what I'm doing wrong. Will keep experimenting. But when I step through the code with a break at the beginning, the line that stops me from moving any further forward is "rst.FindFirst BuildFilter2"

That's as far as I've gotten at this point... so not far

pbaldy · Apr 25, 2013

Right before the line that errors put

Debug.Print BuildFilter2

which should output it to the Immediate window.

boblarson · Apr 25, 2013

And to go with what Paul said, you should change this line:

BuildFilter2 = Form_frmEdit.BuildFilter

To this

BuildFilter2 = Forms!frmEdit.BuildFilter

don't use Form_ as that can do things that are hidden and cause problems.

SyntaxSocialist · Apr 25, 2013

Based on the behaviours I'm getting from this code and the debugging process, it's pretty clear to me that the problem is in the Buildfilter function itself. What confuses me is that an almost identical version of this code exists on another form, and it functions perfectly over there. So I guess I'll be troubleshooting this bad boy for a little while, but I'll post back once I've made a dent in my ignorance. I'd post the whole Buildfilter code for your review, but it's rather lengthy...

pbaldy · Apr 25, 2013

Another tool is setting a breakpoint and stepping through the code as it runs:

http://www.baldyweb.com/Debugging.htm

That way you can see what's happening as the code runs.

boblarson · Apr 25, 2013

SyntaxSocialist said:
I'd post the whole Buildfilter code for your review, but it's rather lengthy...

Go ahead, just use code tags.

And, as Paul wrote, set a breakpoint at the beginning and F8 through so you can check values at each step.

SyntaxSocialist · May 2, 2013

Wait... I swear I replied to this, posting my code and whatnot. Was it removed? I spent a lot of time redacting everything, etc...

pbaldy · May 2, 2013

There was a problem at the site's host and they had to restore from backup. Your post may have been in the lost time frame.

SyntaxSocialist · May 2, 2013

I was not aware. Thanks for the info. Working on putting it back together. Should have saved it in a separate file

pbaldy · May 2, 2013

By the way, I can copy that post here from the email notification, but it will be in a bit. I'm on an iPad right now and copy/pasting is harder.

Split Form Behaviours with Subform (1 Viewer)

Registered User.

Wino Moderator

Registered User.

Wino Moderator

Registered User.

Wino Moderator

Registered User.

Registered User.

Wino Moderator

Smeghead

Registered User.

Wino Moderator

Smeghead

Registered User.

Wino Moderator

Smeghead

Registered User.

Wino Moderator

Registered User.

Wino Moderator

Similar threads

Users who are viewing this thread