Solved Search and find records on a continuous subform

[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.

 

[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.