Searching Subform but returning all data from all forms

[mindbender]

I have a main form with account data on it, a subform with contact data on it, a subform with activity data on it and a subform with opportunity data on it.



Right now you can filter the main form and it will return the main form record and all of the related subform data but I am looking to create a way to search within the contacts subform and return all of the other data.

Basically - have a way for the user to search on customer name and have the results return all the data for the contact including all account, contact, activity and opportunity info.

Thanks - I attached a screen shot of the form for reference


Presentation1.pdf

[June7]

The subforms are dependent on main form. They can only show records that are related to the main form record.

Could have an unbound combobox on the main form that lists all contacts and their associated account name/ID. Select contact and then use the account for the search/filter of main form.

[mindbender]

Can you point me to a step by step on this somewhere. I have seen this solution proposed in other forums/questions but am not quite getting how it works

[June7]

Exactly what do you not understand?

Options:

http://datapigtechnologies.com/flash...tomfilter.html

or my preference

http://www.allenbrowne.com/ser-62.html

In your case, the code would reference the combobox column that has the account name/ID value.

[mindbender]

I have the account field searchable using the Code listed for creating a Search form and it works fine. The contact fields do not work because they are in a subform. I created a combo box that returns a list of account ids and if I choose one it brings back the right record with all the correct details.

I guess what I would like to do is to get account, contact last name and phone number to all work as searchable fields like he has outlined in his searchable form code but where I have subforms I cannot seem to get both to work together Below is the code I have that is working for the contact. It returns a list of all account ids and successfully returns the records except I would like it to be a list of names or even better work the same way the allen browne code works for searching I included below as well

Private Sub ContactName_AfterUpdate()
Dim strSQL As String
If IsNull(Me.ContactName) Then
Me.RecordSource = "tblMain"
Else
strSQL = "SELECT tblMain.* FROM tblMain " & _
"INNER JOIN tblOrigContacts ON " & _
"tblMain.Account = tblOrigContacts.Account " & _
"WHERE tblOrigContacts.Account =  '" & Me.ContactName & "';"
Me.RecordSource = strSQL
End If

[CODE]
Option Compare Database
Option Explicit
Private Sub CmdFilter_Click()
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.AccountName) Then
strWhere = strWhere & "([Account Name] Like ""*" & Me.AccountName & "*"") AND "
End If

' If Not IsNull(Me.PhoneNumber) Then
' strWhere = strWhere & "([Primary Contact - Phone] Like ""*" & Me.AccountName & "*"" AND tblMain.Account = tblOrigContacts.Account ) 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 entered", vbInformation, "Null Search."
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
End Sub
Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all records again.
Dim ctl As Control
'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next
'Remove the form's filter.
Me.FilterOn = False
End Sub
Private Sub Form_BeforeInsert(Cancel As Integer)
'To avoid problems if the filter returns no records, we did not set its AllowAdditions to No.
'We prevent new records by cancelling the form's BeforeInsert event instead.
'The problems are explained at http://allenbrowne.com/bug-06.html
Cancel = True
MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
End Sub
Private Sub Form_Open(Cancel As Integer)
'Remove the single quote from these lines if you want to initially show no records.
'Me.Filter = "(False)"
'Me.FilterOn = True
End Sub

[CODE]

[June7]

The end CODE is missing the /.

Did you try what I suggested? Have a multi-column combobox with contact names and their associated account. User selects contact name from list but code references the account column for search/filter.

I suppose same could be done with phone numbers.

[mindbender]

I am close but stuck using the search form outlined by Allen Browne.

I am getting run time error 3075 syntax error in query expression (Select Q1.* FROM Where EXISTS (Select TblMain.* FROM tblMain WHERE tblOrigContacts.Account = tblMain.Account AND [Primary Contact - Last name] Like "*bret*") as Q1

I am trying to seach for contacts (which is on a subform) using this sql

If Not IsNull(Me.LastName) Then
    strWhere = strWhere & "(SELECT Q1.Account FROM WHERE EXISTS (SELECT tblMain.* from tblMain where tblOrigContacts.Account = tblMain.Account AND [Primary Contact - Last name] Like ""*" & Me.LastName & "*"")as Q1) AND "
    End If

[June7]

I don't see that nested subquery is needed or useful here.

My suggestion is to filter the main form to find the desired account and then the associated contacts will display in subform. Is the subform in Datasheet or Continuous view? If you want to apply filter directly to subform, that gets a little tricky.

Where is Me.LastName? Is that an UNBOUND combobox used to select contact/account criteria?

[mindbender]

The subform is in datasheet view. I attached a pic of the form. The section at the top in blue is in the header of the main form. The issue I am having is the contacts are in the subform. I agree with what you are saying about getting the accounts and returning the contacts and I have that one working (Account Name on the left of the search form) I am trying to make the same search functionality work for Contact last name as well but the data residing in a subform is causing me to go batty

The Me.lastname is the unbound filed on the search form where the user would enter their search criteria

[June7]

Again, apply filter criteria to the main form and the associated contacts will tag along.

The LastName combobox would have columns for LastName and account ID. User selects name but code uses the account ID to filter the main form.

If you want to also set focus in the subform to corresponding contact record, that is more complicated.

[mindbender]

Do you have some steps on how to do this or where to start. I have been trying everything and I can only get account id to show in the drop down...no last name

[June7]

Multi-column combobox - review http://datapigtechnologies.com/flash...combobox3.html

RowSource something like: SELECT AccountID, LastName & ", " & FirstName AS ContactName FROM Contacts ORDER BY LastName, FirstName;
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0";2.0"

[mindbender]

I have done all that - thank you very much by the way

but the vba is throwing an error. It runs on the Event AfterUpdate of the unbound combo box. The highlighted row is the last row Me.RecordSource = strSQL but I figure it has to do something with the sql itself. The error is Runtime 3135 Syntax Error in Join Operation.

Any thoughts?

Private Sub ContactName_AfterUpdate()
Dim strSQL As String
If IsNull(Me.ContactName) Then
Me.RecordSource = "tblMain"
Else
strSQL = "SELECT tblMain.*, b.[Primary COntact - Last name] FROM tblMain, tblOrigContacts b " & _
"INNER JOIN tblOrigContacts ON tblMain.Account = tblOrigContacts.Account " & _
"WHERE (((tblOrigContacts.[Account])= '" & Me.ContactName & "'));"
Me.RecordSource = strSQL
End If

[June7]

Why include tblOrigContacts in the form RecordSource? Why set the form RecordSource property? Why not just set Filter and FilterOn properties as demonstrated in Allen Brown tutorial?

If Account is a number type field, remove the apostrophe delimiters from the parameter.

[mindbender]

I think I skipped a step. I have it working now. I used the Row Source setting you provided as well

THANK YOU SO MUCH FOR YOUR HELP AND PATIENCE. I APPRECIATE IT

this is the code I used

Private Sub ContactName_AfterUpdate()
    ' Purpose: Change the form's RecordSource to only products from this supplier.
    Dim sSQL As String
    Dim bWasFilterOn As Boolean
    ' Save the FilterOn state. (It's lost during RecordSource change.)
    bWasFilterOn = Me.FilterOn
    ' Change the RecordSource.
    If IsNull(Me.ContactName) Then
        If Me.RecordSource <> "tblMain" Then
            Me.RecordSource = "tblMain"
        End If
    Else
        sSQL = "SELECT tblMain.* FROM tblMain " & _
                "INNER JOIN tblOrigContacts ON tblMain.Account = tblOrigContacts.Account " & _
                "WHERE (((tblOrigContacts.[Account])= '" & Me.ContactName & "'));"
        Me.RecordSource = sSQL
    End If
    ' Apply the filter again, if it was on.
    If bWasFilterOn And Not Me.FilterOn Then
        Me.FilterOn = True
    End If
Exit_ContactName_AfterUpdate:
    Exit Sub
End Sub