Solved Search and find records on a continuous subform (1 Viewer)

[LarryE]

@LarryE
Both the search box and the control containing the field I need to search are on the same subform. The search box is on the subform header, the control that needs to be searched is on the continuous subform.

I've tried both a combo box and text box for the search box. The search combo box is named cboRecordSearch, the search text box is called txtRecordSearch. The control I'm trying to find records in is a combo box named cboNameSelect. I mentioned this in my initial post when I said "I've added the combo box on the subform header with the label "Search by last name:"." But that was like 20 posts ago.

So this should work then. I am sorry for the miscommunication. I thought the name of control to be searched was "FamilyName".

Private Sub cboRecordSearch_AfterUpdate()
Dim Criteria As String
Criteria = Me.cboRecordSearch
DoCmd.GoToControl "cboNameSelect"
DoCmd.GoToRecord acActiveDataObject, , acFirst
DoCmd.FindRecord Criteria, acAnywhere, , acSearchAll, , acCurrent
End Sub

The code simply goes to the control on the subform (cboNameSelect) and searches it. It automatically goes to the first record and then searches. If the first record matches, then it stays there.

 

[mikenyby]

You seem to have a join in the subform that isn't necessary. Remove the join to PersonItem.

The join allows the combobox cboNameSelect to display identifying information of the person in question. The FamilyName, GivenName, Position, and CityofResidence fields are not in the PersonItem table, only in the Person table. If there's a way to include those fields in the combobox without joining the tables, I'll give it a go.

>Also, it seems illogical to name the subform EditAssociatedPerson when you seem to have a popup which is used to Edit associated persons.

The naming logic is based on the parent form. I have an AddItem form with a subform named AddAssociatedPerson. The form names will not be visible to the end users, the final product will have more descriptive captions.


>One more potential issue is that the EditItem is a combo and so probably does not contain a text value but an ID. In that case, you have to add the text field to the query and search on the text field rather than the ID.

I assume you mean the cboNameSelect is a combo? I listed the control's source code in my original post.

> That may require a join to the table that defines persons. -- this is actually the most likely issue.

Forgive me for the confusion here, but I don't quite understand. I have a join to the persons table and you asked me to get rid of it? I'm still a novice at Access, so I'm not entirely following the logic here.

And finally, if you have a table level lookup as is suggested by your field names, you should get rid of them for many reasons I won't go into but in this case, the lookup is causing confusion on what data is actually contained by PersonName in the child table. In reality, it is almost certainly PersonID but it is displaying the Name so you don't know that the field doesn't hold text at all but holds the ID and that is why your search is not working.

This is the row source of the control in question:

SELECT [FamilyName] & ", " & [GivenName] & " | " & [Position] & " | " & [CityOfResidence] AS FullName, Persons.PersonID FROM Persons ORDER BY [FamilyName] & ", " & [GivenName] & " | " & [Position] & " | " & [CityOfResidence];

It is necessary to display all these fields because the archives contains many people with similar or identical names, so [Position] and [CityOfResidence] are required as disambiguators.

Thank you for your help, my work day is nearly over but I'll try your suggested fixes tomorrow.

 

[LarryE]

Does this work when the field being searched is a combo with an ID as the bound control? It doesn't for me.

I addressed this in #16

It might work if you searched for a number in the whole field AND that field was visible. It might not if the text box is not visible. I was just playing around with the native Find dialog. I was just amazed that this simple thing got so complicated, that's all. The native Find/Replace dialog box can be viewed and used apparently only on non-popup forms. I didn't find it makes a difference if the form is single or continous but needs to be non-popup.

 

[mikenyby]

So this should work then. I am sorry for the miscommunication. I thought the name of control to be searched was "FamilyName".

Private Sub cboRecordSearch_AfterUpdate()
Dim Criteria As String
Criteria = Me.cboRecordSearch
DoCmd.GoToControl "cboNameSelect"
DoCmd.GoToRecord acActiveDataObject, , acFirst
DoCmd.FindRecord Criteria, acAnywhere, , acSearchAll, , acCurrent
End Sub

The code simply goes to the control on the subform (cboNameSelect) and searches it. It automatically goes to the first record and then searches. If the first record matches, then it stays there.

Thank you for your perseverence, @LarryE! However, I have already tried this code. It simply changes the focus to the first record on the subform. Like this:

Clearly the name "Mollard" is not being highlighted there.

I also tried your suggestion of changing the combobox name to "FamilyName" so it matches the field being searched. No dice.

 

[mikenyby]

It might work if you searched for a number in the whole field AND that field was visible. It might not if the text box is not visible. I was just playing around with the native Find dialog. I was just amazed that this simple thing got so complicated, that's all. The native Find/Replace dialog box can be viewed and used apparently only on non-popup forms. I didn't find it makes a difference if the form is single or continous but needs to be non-popup.

And of course I've create the entire database around pop-up forms. It seems to be the easiest way for the user to navigate between the various forms needed to find information. Of course, one can just hit ctrl+F and search, and I've done that myself with some of my entries, but the find dialog box needs to be canceled and the focus reset to the subform (I think) before the user can do it again.

 

[Gasman]

Really should use @MajP FAYT classes.

About 3 lines of code in all.

Otherwise, sanitize (if needed?) and upload your DB.

Not sure why you do not use a Filter?
I had a form with 3 textboxes that I could filter on, Crew, Ship and Date.

Private Sub cmdFilter_Click()
Dim strFilter As String

If Me.cboCrew <> "" Then
    strFilter = "Crew = " & Me.cboCrew.Column(0)
End If
If Me.cboShip <> "" Then
    strFilter = strFilter & " AND Ship = " & Me.cboShip.Column(0)
End If
If Me.cboDate <> "" Then
    strFilter = strFilter & " AND [Date] = " & Me.cboDate.Column(0)
End If

If Left(strFilter, 4) = " AND" Then
    strFilter = Mid(strFilter, 5)
End If
Me.sfrmLinks.Form.Filter = strFilter
Me.sfrmLinks.Form.FilterOn = True
End Sub

 

[LarryE]

Thank you for your perseverence, @LarryE! However, I have already tried this code. It simply changes the focus to the first record on the subform. Like this:
View attachment 111092
Clearly the name "Mollard" is not being highlighted there.

I also tried your suggestion of changing the combobox name to "FamilyName" so it matches the field being searched. No dice.

OK I found what the problem is. The field you are searching is not a bound field in the forms Record Source. You will never be able to search any field on this form if it is not a bound field on the form. You can't search a lookup field or SQL-based Control Source like you have. The search will not recognize:

SELECT [FamilyName] & ", " & [GivenName] & " | " & [Position] & " | " & [CityOfResidence] AS FullName, Persons.PersonID FROM Persons ORDER BY [FamilyName] & ", " & [GivenName] & " | " & [Position] & " | " & [CityOfResidence];

I missed that in post #22. But if you click the Edit Person button and it brings up a form that is editable, then you will be able to search it. That is where the search criteria belongs, on the editable form that is bound to a legitimate Record Source.

If the Record Source for your sub-form does not include the field FamilyName, then:

1. Open your sub-form in design mode
2. Include the FamilyName field in your Record Source query for the sub-form
3. Create a textbox on your sub-form and set its Control Source AND its Name to FamilyName
4. Move the FamilyName textbox so it is behind the cboNameSelect textbox but do not set its visiblity property to No
5. Right-click the FamilyName textbox and set its Position Send To Back. So when the sub-form opens, the FamilyName field will be hidden behind the cboNameSelect combobox.

Now you have the FamilyName field bound and available to search on your sub-form.
Now you can:

Private Sub cboRecordSearch_AfterUpdate()
Dim Criteria As String
Criteria = Me.cboRecordSearch
DoCmd.GoToControl "FamilyName"
DoCmd.GoToRecord acActiveDataObject, , acFirst
DoCmd.FindRecord Criteria, acAnywhere, , acSearchAll, , acCurrent
Me.cboNameSelect.SetFocus
End Sub

Note the GoToControl name is now FamilyName which is the proper field to be searched. Then the focus is set to the combobox.

 

[mikenyby]

Really should use @MajP FAYT classes.

About 3 lines of code in all.

Otherwise, sanitize (if needed?) and upload your DB.

Not sure why you do not use a Filter?
I had a form with 3 textboxes that I could filter on, Crew, Ship and Date.

Private Sub cmdFilter_Click()
Dim strFilter As String

If Me.cboCrew <> "" Then
    strFilter = "Crew = " & Me.cboCrew.Column(0)
End If
If Me.cboShip <> "" Then
    strFilter = strFilter & " AND Ship = " & Me.cboShip.Column(0)
End If
If Me.cboDate <> "" Then
    strFilter = strFilter & " AND [Date] = " & Me.cboDate.Column(0)
End If

If Left(strFilter, 4) = " AND" Then
    strFilter = Mid(strFilter, 5)
End If
Me.sfrmLinks.Form.Filter = strFilter
Me.sfrmLinks.Form.FilterOn = True
End Sub

First of all, thank you for your continued help on this. I feel like it's some stupid little simple thing I'm missing, as is generally the case.

I would love to use @MajP's FAYT class, but something is awry when I plugged it into a backup database. I've followed the instructions in the module as best as I can but as soon as I type something into the search box I get the msgbox "Will not Filter. Verify Field Name is Correct."

I've copy/pasted the module into the database and added this code to the subform:

Public faytNames As New FindAsYouTypeCombo
Private Sub Form_Open(Cancel As Integer)
   faytNames.InitalizeFilterCombo Me.cboRecordSearch, "cboNameSelect", anywhereinstring, True, False
 End Sub

Here's a dummy database with the module imported:

 

[mikenyby]

@LarryE you did it! Works like a charm! Please let me buy you a pizza.

"You will never be able to search any field on this form if it is not a bound field on the form. You can't search a lookup field or SQL-based Control Source like you have."

I did not realize this but now that you mention it, it makes a lot of sense. This has been the longest and most complicated thread I've had on this forum, so I really appreciate your patience, as well as that of @Gasman, @pbaldy and @Pat Hartman

This experience has further cemented my belief that this is the best database forum on the internet. I hope that someday I will be able to provide as much help as y'all have provided me.

 

[LarryE]

@LarryE you did it! Works like a charm! Please let me buy you a pizza.

"You will never be able to search any field on this form if it is not a bound field on the form. You can't search a lookup field or SQL-based Control Source like you have."

I did not realize this but now that you mention it, it makes a lot of sense. This has been the longest and most complicated thread I've had on this forum, so I really appreciate your patience, as well as that of @Gasman, @pbaldy and @Pat Hartman

This experience has further cemented my belief that this is the best database forum on the internet. I hope that someday I will be able to provide as much help as y'all have provided me.

My pleasure, but I would like to know how that combo box cboNameSelect works or if it does work at all. Can you make a selection with it? I don't know how it can work. Maybe it should be a textbox instead. Just wondering. I have never seen a combobox with a SELECT statement as its Control Source before. If a combobox is not bound to an actual field in the forms Record Source, what does it do? Maybe it is just for display purposes.

 

[Pat Hartman]

I also tried your suggestion of changing the combobox name to "FamilyName" so it matches the field being searched. No dice.

Changing the name does not change the contents. I explained the problem. The combo is bound to an ID. The visible text is coming from the RowSource of the combo. To search on text, you need to include the text field in your RecordSource query and bind it to a hidden field on the subform.

 

[MajP]

I would love to use @MajP's FAYT class, but something is awry when I plugged it into a backup database.

Yeah, unfortunately I did not design this to handle queries with parameters, so you cannot reference another forms controls in the rowsource. That is why it did not work. Someday I will have to look into modifying to handle parameter queries.

 

[mikenyby]

My pleasure, but I would like to know how that combo box cboNameSelect works or if it does work at all. Can you make a selection with it? I don't know how it can work. Maybe it should be a textbox instead. Just wondering. I have never seen a combobox with a SELECT statement as its Control Source before. If a combobox is not bound to an actual field in the forms Record Source, what does it do? Maybe it is just for display purposes.

Sure, cboNameSelect is overlaid with a text box 'txtName'. The text box overlaid on the combobox eases the data entry for the user and ensures the name is always displayed. The txtName_Enter event sets focus on the cboNameSelect field. If the user typed a name that is not in the database, a msgbox that gives them the option of adding a new person (DoCmd.OpenForm "AddNewPerson") to the database appears. When the AddNewPerson form closes, cboNameSelect requeries and autofills with the person just added. If you're curious, here's the code for the subform in its entirety. This design was actually @MajP 's suggestion from an earlier thread and the functionality is very clean. If there's a way to make it more elegant, I'm open to suggestions.

Option Explicit
Dim WithEvents frmNewPers As Access.Form
Dim WithEvents frmEditPers As Access.Form


Private Sub cboNameSelect_KeyDown(KeyCode As Integer, Shift As Integer)
Me.cboNameSelect.Dropdown
End Sub

Private Sub cboNameSelect_NotInList(NewData As String, Response As Integer)
If MsgBox("The name you entered is not in the current database. Add new person?", vbYesNo, "Name not in database") _
= vbYes Then DoCmd.OpenForm "EnterNewPerson", , , , acFormAdd, acWindowNormal
Response = acDataErrContinue
Set frmNewPers = Forms("EnterNewPerson")
frmNewPers.OnClose = "[Event Procedure]"
If Me.Dirty Then Me.Undo
End Sub

Private Sub cmdEditPerson_Click()
    DoCmd.OpenForm "EditPerson", acNormal, , "PersonID = Forms!EditItem!SubFrmEditAssociatedPerson.Form!PersonIDFK", acFormEdit, acWindowNormal
    Set frmEditPers = Forms("EditPerson")
    frmEditPers.OnClose = "[Event Procedure]"
End Sub

Private Sub cboRecordSearch_AfterUpdate()
Dim Criteria As String
Criteria = Me.cboRecordSearch
DoCmd.GoToControl "FamilyName"
DoCmd.GoToRecord acActiveDataObject, , acFirst
DoCmd.FindRecord Criteria, acAnywhere, , acSearchAll, , acCurrent
Me.cboNameSelect.SetFocus
Me.cboRecordSearch = ""
End Sub

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
Case Is = 3022
    Err.Clear
    Response = acDataErrContinue
    If MsgBox("Person already selected", vbOKOnly) = vbOK Then Me.Undo
   End Select
End Sub
Private Sub cmdAddNewPerson_Click()
Me.txtName.Undo
  Me.cboNameSelect.Undo
  DoCmd.OpenForm "EnterNewPerson", , , , acFormAdd
  Set frmNewPers = Forms("EnterNewPerson")
  frmNewPers.OnClose = "[Event Procedure]"
  End Sub


Private Sub CmdSortSubfrmAZ_Click()
Me.OrderBy = "FamilyName, GivenName"
Me.OrderByOn = True
End Sub

Private Sub txtName_Enter()
Me.cboNameSelect.SetFocus
End Sub

  Private Sub frmNewPers_Close()
Me.cboNameSelect.Requery
Me.cboNameSelect = frmNewPers.PersonID
Me.PageNumbers.SetFocus
End Sub
Private Sub frmEditPers_Close()
Me.cboNameSelect.Requery
Me.PageNumbers.SetFocus
End Sub

 

[Gasman]

In fact @MajP's classes might not have helped here.
I used them anyway to quickly find the combo entry anyway. I also sorted the combo.

Then this code found the first matching record easily enough?

Private Sub cboRecordSearch_AfterUpdate()
Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "FullName Like '" & Me.cboRecordSearch & "*'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

However that just locates that record in the subfom as shown by the record indicator.