Split Form Behaviours with Subform

pbaldy said:
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.
Click to expand...

Oh, that'd be fantastic. Thanks so much :)

EDIT: removed lengthy code addition. Included in code tags in post below.
 
Last edited:
Is this the one? I'll let you clean up whatever formatting got lost in the shuffle. ;)

SyntaxSocialist post added to preceding post:
 
pbaldy said:
Is this the one? I'll let you clean up whatever formatting got lost in the shuffle. ;)
Click to expand...

That's the one! Thanks!

Sorry for the delay. I think I've managed to make BuildFilter work properly again using your debugging techniques. strWhere wasn't being constructed properly and was feeding nonsense to .FindFirst through BuildFilter.

I'm still encountering issues with the Form_Current() code for subMain. Here's what it's doing:

- When I open the form, I get runtime error 3077: Syntax error(missing operator) in expression. The Debug button takes me to "rst.FindFirst BuildFilter2"
- I reset the code and let frmEdit open, place a breakpoint at the beginning of the code in the subMain module put a date in my txtStartDateBefore field, and start stepping through the code.
- There are no issues when I get to "rst.FindFirst Buildfilter2"; the Immediate Window displays "[StartDate] <= #12/31/2011#" which is the date I picked with the date picker. I can successfully step through the rest of the code without any errors... Strange...
- But my bound controls still don't change the record displayed when I click on different records/fields in the subform, and clicking the First/Previous/Next/Last buttons (which I created to change the displayed record in the bound controls) doesn't change anything in the subform, either.

So the code seems to run successfully, but is not performing as I want/expect it to. And then there's the issue of the error on Load...

I really feel the need to thank you guys for all this help and guidance you've been providing. I know you don't have to do this, and I know that spoon feeding can be frustrating. So thank you both, so much, again.

Code: 
Option Compare Database
Option Explicit
'------------------------------------------------------------------------

'frmEdit has txt, cmb, and chk controls, as well as an option group with 2 radio buttons on it.
'The combo boxes are meant to allow the user to choose how their results are filtered, so
'users can select "begins with," "ends with," "contains," "is bewteen," etc. for many of the
'text box controls.
'
'I've abridged and edited the code, but it's still a rather lengthy. The sections for First Name,
'Last Name, and Sponsor are mostly identical to the Project ID section, so I cut them out 
'except for a reference to their location.

'BuildFilter function used with InstantSearch():
Public Function BuildFilter() As String

'Declare and define all variables
    'Filters
    Dim strWhere As String    'Main ("AND") filter
    
        'Sub ("OR") filters
        Dim strIDOr As String		'Project ID "[NOT] IN" and "Between/Outside" cmb subfilter
        Dim strRep As String           	'Representative chk subfilter
        Dim strLastOr As String       	'Last name "[NOT] IN" cmb subfilter
        Dim strFirstOr As String       	'First name "[NOT] IN" cmb subfilter
        Dim strSponsorOr As String  	'Sponsor "[NOT] IN" cmb subfilter
        Dim strStatus As String       	'Status chk subfilter
        Dim strType As String         	'Type chk subfilter

    'Condition Variables
    Dim strIDCond1 As String		'Start of Project ID condition
        Dim strIDCond2 As String         'End of Project ID condition
    Dim strLastCond1 As String       	'Start of last name condition
        Dim strLastCond2 As String      'End of last name condition
    Dim strFirstCond1 As String       	'Start of first name condition
        Dim strFirstCond2 As String      'End of first name condition
    Dim strSponCond1 As String      	'Start of sponsor condition
        Dim strSponCond2 As String     'End of sponsor condition

'Check cmbs for conditions and apply comparison operator ("LIKE") to Condition Variables

'Project ID
    If Len(Me.txtProjectID & vbNullString) > 0 Then
        'For Project ID "begins with"
        If Me.cmbProjectIDCond = "begins with" Then
            strIDCond1 = "[Project_ID] LIKE """
            strIDCond2 = "*"" AND "
        End If

'...

        'For Project_ID "ends with"
        If Me.cmbProjectIDCond = "ends with" Then
            strIDCond1 = "[Project_ID] LIKE ""*"
            strIDCond2 = """ AND "
        End If

'...

        'For Project_ID "contains"
        If Me.cmbProjectIDCond = "contains" Then
            strIDCond1 = "[Project_ID] LIKE ""*"
            strIDCond2 = "*"" AND "
        End If

'...

        'For Project_ID "equals"
        If Me.cmbProjectIDCond = "equals" Then
            strIDCond1 = "[Project_ID] = """
            strIDCond2 = """ AND "
        End If

'...

        'For Project_ID "is between"
        If Me.cmbProjectIDCond = "is between" Then
            'Trim and standardize the user input
            strIDOr = strIDOr & Me.txtProjectID
            strIDOr = Trim(Replace(strIDOr, ", ", """ AND [Project_ID] <= """))
            strIDOr = Trim(Replace(strIDOr, " ,", """ AND [Project_ID] <= """))
            strIDOr = Trim(Replace(strIDOr, " , ", """ AND [Project_ID] <= """))
            strIDOr = Trim(Replace(strIDOr, ",", """ AND [Project_ID] <= """))

            'Strip trailing """ AND [Project_ID] <= """ if present (trailing comma entered)
            If Right(strIDOr, 23) = """ AND [Project_ID] <= """ Then
                strIDOr = Left(strIDOr, Len(strIDOr) - 23)
            End If

            'Strip leading """ AND [Project_ID] <= """ if present (leading comma entered)
            If Left(strIDOr, 23) = """ AND [Project_ID] <= """ Then
                strIDOr = Right(strIDOr, Len(strIDOr) - 23)
            End If

            'Append """ AND "
            strIDOr = strIDOr & """ AND "

            'Finalize construction of the strIDOr Condition Variable
            strIDOr = "[Project_ID] >= """ & strIDOr
        End If

'...
        
        'Check if cmbProjectIDCond uses "[NOT] IN" subfilter (strIDOr)
        If (Me.cmbProjectIDCond = "is between") Then
                'Integrate "[NOT] IN" subfilter (strIDOr) with main ("AND") filter
                '(strWhere)
                strWhere = strWhere & strIDOr
        Else
            'Integrate with main ("AND") filter (strWhere)
            strWhere = strWhere & strIDCond1 & Me.txtProjectID & strIDCond2
        End If
    End If

'Last Name (basically the same as Project ID)

'...

'First Name (basically the same as Project ID)

'...

'Sponsor (basically the same as Project ID)

'...

'That's it for the cmbs and associated txts.
'Below are the chks, date txts, and the option group.
    
'Construct Filters: Check txts; check optGrps (rads); check if cmbs use "[NOT] IN" subfilters,
'and if not, integrate with main ("AND") filter (strWhere); check chks and build sub ("OR")
'filter; integrate sub ("OR") filter with main ("AND") filter (strWhere)

'Representatives
    'Check chk for Charlene
    If Me.chkCharlene = -1 Then
        'Build sub ("OR") filter (strRep)
        strRep = strRep & "[Representative] = ""Charlene"" OR "
    End If

    'Check chk for Gail
    If Me.chkGail = -1 Then
        'Build sub ("OR") filter (strRep)
        strRep = strRep & "[Representative] = ""Gail"" OR "
    End If

    'Check chk for Horia
    If Me.chkHoria = -1 Then
        'Build sub ("OR") filter (strRep)
        strRep = strRep & "[Representative] = ""Horia"" OR "
    End If

'...

    'Integrate sub ("OR") filter (strRep) with main ("AND") filter (strWhere)
    'Test for subfilter
    If Len(strRep & vbNullString) > 0 Then
        'Strip last " OR " in the sub ("OR") filter (strRep)
        If Right(strRep, 4) = " OR " Then
            strRep = Left(strRep, Len(strRep) - 4)
        End If

        'Concatenate with strWhere and add parentheses around the sub ("OR") filter (strRep)
        strWhere = strWhere & "(" & strRep & ") AND "
    End If

'Dates
    'Check txt for txtStartDateBefore
    If Len(Me.txtStartDateBefore & vbNullString) > 0 Then
        strWhere = strWhere & "[StartDate] <= #" & Me.txtStartDateBefore & "# And "
    End If

    'Check txt for txtStartDateAfter
    If Len(Me.txtStartDateAfter & vbNullString) > 0 Then
        strWhere = strWhere & "[StartDate] >= #" & Me.txtStartDateAfter & "# And "
    End If

    'Check txt for txtEndDateBefore
    If Len(Me.txtEndDateBefore & vbNullString) > 0 Then
        strWhere = strWhere & "[EndDate] >= #" & Me.txtEndDateBefore & "# And "
    End If

    'Check txt for txtEndDateAfter
    If Len(Me.txtEndDateAfter & vbNullString) > 0 Then
        strWhere = strWhere & "[EndDate] >= #" & Me.txtEndDateAfter & "# And "
    End If
    
'Status
    'Check chk for Prop App
    If Me.chkPropApp = -1 Then
        'Build sub ("OR") filter (strStatus)
        strStatus = strStatus & "[Status] = ""Prop App"" OR "
    End If

    'Check chk for Under Neg
    If Me.chkUnderNeg = -1 Then
        'Build sub ("OR") filter (strStatus)
        strStatus = strStatus & "[Status] = ""Under Neg"" OR "
    End If

    'Check chk for Sigs in Progress
    If Me.chkSigs = -1 Then
        'Build sub ("OR") filter (strStatus)
        strStatus = strStatus & "[Status] = ""Sigs in Progress"" OR "
    End If

'...

    'Integrate sub ("OR") filter (strStatus) with main ("AND") filter (strWhere)
    'Test for subfilter
    If Len(strStatus & vbNullString) > 0 Then
        'Strip last " OR " in the subfilter
        If Right(strStatus, 4) = " OR " Then
            strStatus = Left(strStatus, Len(strStatus) - 4)
        End If

        'Concatenate with strWhere and add parentheses around the sub ("OR") filter (strStatus)
        strWhere = strWhere & "(" & strStatus & ") AND "
    End If

'Industry
    'Check optGrp (rads) for Industry and Non-industry
    If Me.optGrpInd = 1 Then
        'Integrate with main ("AND") filter (strWhere)
        strWhere = strWhere & "[Industry] = -1 AND "
    End If

    If Me.optGrpInd = 2 Then
        'Integrate with main ("AND") filter (strWhere)
        strWhere = strWhere & "[Industry] = 0 AND "
    End If

'Type
    'Check chk for 001
    If Me.chk001 = -1 Then
        strType = strType & "[Type] = ""001"" OR "
    End If

    'Check chk for 002
    If Me.chk002 = -1 Then
        strType = strType & "[Type] = ""002"" OR "
    End If

    'Check chk for 003
    If Me.chk003 = -1 Then
        strType = strType & "[Type] = ""003"" OR "
    End If

'...

    'Integrate sub ("OR") filter (strType) with main ("AND") filter (strWhere)
    'Test for subfilter
    If Len(strType & vbNullString) > 0 Then
        'Strip last " OR " in the subfilter
        If Right(strType, 4) = " OR " Then
            strType = Left(strType, Len(strType) - 4)
        End If

        'Concatenate with strWhere and add parentheses around the sub ("OR") filter (strType)
        strWhere = strWhere & "(" & strType & ") AND "
    End If

'Check for/tidy/finish constructing Main Filter
    'Check for Main Filter
    If Len(strWhere & vbNullString) > 0 Then
        If Right(strWhere, 5) = " And " Then
            strWhere = Left(strWhere, Len(strWhere) - 5)
            'Finish constructing Main Filter
            strWhere = "WHERE (" & strWhere & ")"
        End If
    End If

'Assign Main Filter to BuildFilter() function
    BuildFilter = strWhere
    
End Function
 
Last edited:
WOAH!

So I took a bit of a hiatus on this, finding a workable alternative involving a textbox whose input would allow the user to navigate directly to whatever record they wanted.

But I've made some progress! Turns out my big problem was that the original datatable (which, in my defense, I didn't create), didn't have a primary key. So I created one, and lo and behold my form started (almost) working!

What's happening now is that, while the main form and subform do reflect each other's behaviours, my subform is only displaying one record at a time. Obviously, I would like it to display all the records that meet the user's search criteria and highlight rather than isolate the current record.

Haven't done much troubleshooting, yet, but I thought I'd post the update :)

PS: I'm going to repost some of the more pertinent info in a new thread, since this one's getting rather dated. Find it here: http://www.access-programmers.co.uk/forums/showthread.php?p=1265126#post1265126
 
Last edited:
You must log in or register to reply here.

Similar threads

D
Solved How to quickly duplicate Tables & associated subforms within a main form
2 3 4
Replies
71
Views
2,423
dibblermail
D
A
Solved Filtering (using VBA) a subform based on Option Buttons on main form
Replies
19
Views
829
CJ_London
C
M
Open Unbound form and filter subform
Replies
7
Views
465
Gasman
Gasman
N
VBA MS Access Error Handling
Replies
5
Views
808
Pat Hartman
P
nimeshaanethmini
Need Help with VBA Code for Access Form Filtering
Replies
5
Views
1,047
Uncle Gizmo
Uncle Gizmo

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom