Filter by subform's field values

[Gina Maylone]

Hello again everyone, I've been doing mental gymnastics for a couple of days now and as always, look to you good people to help me out.

I have Contacts related to Bids (one to many on ContactID), which are related (one to many on BidID) to Job Dates, Bid Details, and Images. Bids, Job Dates, Bid Details are located in a tabbed subform under contacts. I have 3 key filter fields in Contacts statusfilter, bidderfilter and biddatefilter. I am attempting to filter records located in the subform (and also pull up the related contacts information). Bid.Status, Bid.Bidder, JobDates.BidDate.

I have tried all of the ideas in various forums, setting the recordsource, filter by form, etc. I am currently testing this:

Private Sub Command197_Click()
On Error Resume Next
    Dim sSql As String
    Dim sCriteria As String
        sCriteria = "WHERE 1=1 "

        'This code is for a specific search where you will need to enter the exact string
        'The source for this code can either be from a table or query
        If Me![statusfilter] <> "" Then
                sCriteria = sCriteria & " and status = """ & statusfilter & """"
        End If
        'This code is for a Like search where can enter part of a string
        'The source for this code can either be from a table or query
        If Me![bidderfilter] <> "" Then
            sCriteria = sCriteria & " AND bidder like """ & bidderfilter & "*"""
        End If
        If Me![biddatefilter] <> "" Then
                sCriteria = sCriteria & " AND biddate Like """ & biddatefilter & "*"""
        End If
        
        If Nz(DCount("*", "bid", Right(sCriteria, Len(sCriteria) - 14)), 0) > 0 Then
        sSql = "SELECT DISTINCTROW BID.*, Contacts.Order, Contacts.Zone, Contacts.FirstName, Contacts.LastName, Contacts.FullName, Contacts.[PreviousCustomer?], Contacts.ServiceText, Contacts.Address, Contacts.[Street Name], Contacts.City, Contacts.State, Contacts.Zip, Contacts.Phone, Contacts.Notes, Contacts.fulladdress, Contacts.emailaddress, Contacts.DoJobBy, Jobdates.CalledDate, Jobdates.BidDate, Jobdates.AcceptedDate " & sCriteria
        Debug.Print sSql
        
        Forms![contacts]![bidsheet].Form.recordsource = sSql
        Forms![contacts]![bidsheet].Form.Requery
        Else
        MsgBox "The search failed find any records" & vbCr & vbCr & _
        "that matches your search criteria?", vbOKOnly + vbQuestion, "Search Record"
        End If
 
End Sub

I'm getting Runtime error 3163, field is too small to accept....

Throughout my trials with various methods, my subforms have completely disappeared, the wrong data is displayed, no data is displayed. At one point I had 2 criteria working (Status and Bidder) but not the Bid Date. But I digress, is what I'm trying to do actually possible?



Thank you in advance!
Gina

[June7]

It is possible to filter subform records. I do it.

Parameters for date/time type fields require # delimiter, not apostrophe or quote marks. LIKE operator and wildcard only works for text type field, not number or date/time.

Review http://allenbrowne.com/ser-62code.html

[Gina Maylone]

Thanks June! I learned something today I have tried Allen's filter and am now trying it again:

'Purpose:   This module illustrates how to create a search form, _
            where the user can enter as many or few criteria as they wish, _
            and results are shown one per line.
'Note:      Only records matching ALL of the criteria are returned.
'Author:    Allen Browne (allen@allenbrowne.com), June 2006.
'Option Compare Database
'Option Explicit
    'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                    we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
    '***********************************************************************
    'Look at each search box, and build up the criteria string from the non-blank ones.
    '***********************************************************************
    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.statusfilter) Then
        strWhere = strWhere & "( [Forms]![contacts]![bidsheet].[Form]![status] = """ & Me.statusfilter & """) AND "
    End If
    'Another text field example. Use Like to find anywhere in the field.
     If Not IsNull(Me.bidderfilter) Then
        strWhere = strWhere & "( [Forms]![contacts]![bidsheet].[Form]![bidder] = """ & Me.bidderfilter & """) AND "
    End If

   
    If Not IsNull(Me.biddatefilter) Then
        strWhere = strWhere & "( [Forms]![contacts]![bidsheet].[Form]![jobdates].[Form]![biddate] = " & Format(Me.biddatefilter, conJetDate) & ") AND "
    End If
    
    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    
    
    End If
    Debug.Print strWhere
    
End Sub

However, I am propted for "Bidder" and "Bid Date" when those two search fields are filled in. Also, I have gotten all of the records (not just the filtered ones) so I added references to the subforms as you can see. Also, my subform has completely disappeared! The errors are so random, I don't know how to explain it any better. I've removed "Like" because I don't want that, my criteria comes from combo boxes so it is exact. Any other thoughts? Oh, debug.print gives me: ([bidder] = "John Doe") AND ([biddate] = #10/21/2015#).


Thank you again, have a beautiful day!
Gina

[June7]

Where is this code? Behind main form or subform? The input controls are on main form or subform?

If you want to provide db for analysis, follow instructions at bottom of my post.

[Gina Maylone]

The code and controls are located in the mail form (Contacts), but I think I've got some corruption going on, I get chinese characters when referring to the biddatefilter control in a query:

[Gina Maylone]

So I deleted the biddatefilter control and created a new one. And ran the query on the control like before, and am now getting this:

I apologize if this is an inappropriate post, I've started a new database and imported all my objects and still have the corruption. UGH!

[Gina Maylone]

I managed to eliminate the chinese characters by formatting the date fields. I still do not get accurate filtered results. Database is located here: https://www.dropbox.com/s/b88epxzrc6...tepro.zip?dl=0

Thank you again for your time!!
Gina

[June7]

Sorry, cannot download from Dropbox. If you can attach to post then I can get it.

[Gina Maylone]

I can't get it under 2 MB. Why can't you get it from dropbox? Would you be able to get it from OneDrive or icloud? Thanks!

[June7]

I am blocked here from all those share sites and my home internet is too weak/slow for large files.

[orange]

I'm looking at your database. Please give specific directions to the problem area-What exactly should I be looking at?

[Gina Maylone]

Hi Orange, the Contacts form, Status, Bidder and Bid date filters at the top. And the search button. I am attempting to filter the records in the subform.

Thank you!

[orange]

Ok,
How about giving specific instructions such as
On Contact Form in the filters
--select XXX in MMMM
--select YYY etc

then what did you expect, and what are you getting.
This represents something that isn't working as expected.

I chose Job Complete in Status and got rid of the bottom half of the screen.

A comment -- I think your form is too busy ---trying to do too much. But you know your users better than I do.

[Gina Maylone]

Sorry Orange, I thought I had explained it previously. In a nutshell, I have Contacts related to Bids (one to many on ContactID), which are related (one to many on BidID) to Job Dates, Bid Details, and Images. Bids, Job Dates, Bid Details are located in a tabbed subform under contacts. I have 3 key filter fields in Contacts statusfilter, bidderfilter and biddatefilter. I am attempting to filter records located in the subform (and also pull up the related contacts information). Bid.Status, Bid.Bidder, JobDates.BidDate. Right now, every choice gets rid of the bottom of the screen. And you're right, it has gotten too busy. It started out nice and simple - I was trying to give the users a complete overview without having to switch pages (screens).

Thank you for your time!

[orange]

Why does Bidders have compound PK?

I'm not following what you're doing, and have a few other things on the go at the moment.

I did try a compact and repair and zip , but I'm still just over 2.1GB--so can not upload for others.