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

[mikenyby]

Hello! I'm working on an archives database and I'm trying to make the user interface a little easier. I have a form that looks like this:

Some of the items in the database have over a hundred associated persons, and I want to make it a little easier for the archivists to find associated persons on the form to either edit their information or add page numbers. I've added the combo box on the subform header with the label "Search by last name:". What I would like it to do is go to the first record that matches the string in the combo box, just as if you searched the form using ctrl+f. After searching these forums for guidance, I've added this code to the combo box after update event:

Private Sub cboRecordSearch_AfterUpdate()
Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[cboNameSelect] = " & Str(Nz(Me![cboRecordSearch], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Unfortunately it does sweet nothing. Anyone have any ideas?

If it's any help, the row source for cboRecordSearch is
SELECT Persons.FamilyName FROM Persons;

And the row source for cboNameSelect is
SELECT [FamilyName] & ", " & [GivenName] & " | " & [Position] & " | " & [CityOfResidence] AS FullName, Persons.PersonID FROM Persons ORDER BY [FamilyName] & ", " & [GivenName] & " | " & [Position] & " | " & [CityOfResidence];

 

[Gasman]

Search here for FAYT and user @MajP

 

[pbaldy]

And FYI, this:

rs.FindFirst "[cboNameSelect] = " & Str(Nz(Me![cboRecordSearch], 0))

Would need to be a field name in the underlying record source of the form, not the name of a control. You likely also need delimiters, at the simplest:

rs.FindFirst "[cboNameSelect] = '" & Str(Nz(Me![cboRecordSearch], 0)) & "'"

 

[LarryE]

Hello! I'm working on an archives database and I'm trying to make the user interface a little easier. I have a form that looks like this:
View attachment 111090
Some of the items in the database have over a hundred associated persons, and I want to make it a little easier for the archivists to find associated persons on the form to either edit their information or add page numbers. I've added the combo box on the subform header with the label "Search by last name:". What I would like it to do is go to the first record that matches the string in the combo box, just as if you searched the form using ctrl+f. After searching these forums for guidance, I've added this code to the combo box after update event:

Private Sub cboRecordSearch_AfterUpdate()
Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[cboNameSelect] = " & Str(Nz(Me![cboRecordSearch], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Unfortunately it does sweet nothing. Anyone have any ideas?

If it's any help, the row source for cboRecordSearch is
SELECT Persons.FamilyName FROM Persons;

And the row source for cboNameSelect is
SELECT [FamilyName] & ", " & [GivenName] & " | " & [Position] & " | " & [CityOfResidence] AS FullName, Persons.PersonID FROM Persons ORDER BY [FamilyName] & ", " & [GivenName] & " | " & [Position] & " | " & [CityOfResidence];

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

You only need to enter part of any name. The FindRecord search will find the first instance of it. You can also add a Find Next command button with the following code to find the next instance of your search criteria. Name your commandbutton FindNext and put this code in its On Click Event

Private Sub FindNext_Click()
Dim Criteria As String
Criteria = Me.cboRecordSearch
DoCmd.GoToControl "FamilyName"
DoCmd.FindNext
Exit Sub
End Sub

 

[mikenyby]

And FYI, this:

rs.FindFirst "[cboNameSelect] = " & Str(Nz(Me![cboRecordSearch], 0))

Would need to be a field name in the underlying record source of the form, not the name of a control. You likely also need delimiters, at the simplest:

rs.FindFirst "[cboNameSelect] = '" & Str(Nz(Me![cboRecordSearch], 0)) & "'"

Thanks @pbaldy. I'm working on @MajP 's module in a backup file and just tried your suggestion in the original. Here's the code as I have it now:

Private Sub cboRecordSearch_AfterUpdate()
Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[FamilyName] = '" & Str(Nz(Me![cboRecordSearch], 0)) & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I'm getting a "Run-time error '13': Type mismatch" error message. Any clue?

 

[Gasman]

Why would you want zero for any null string?

 

[mikenyby]

@LarryE it seems your suggestion is on the right track, but one snafu: my control is not called "FamilyName", it's called "cboNameSelect". FamilyName is one of the fields that cboNameSelect pulls from. If I put the code in as you wrote it, I get "Run-time error '2109': There is no field named 'FamilyName' in the current record."

Alternatively, if I change the offending line of your code to this:
DoCmd.GoToControl "cboNameSelect"

The focus moves to the first record of the subform. I don't know how to reference the desired field that cboNameSelect includes. Here's the row source for cboNameSelect:
SELECT [FamilyName] & ", " & [GivenName] & " | " & [Position] & " | " & [CityOfResidence] AS FullName, Persons.PersonID FROM Persons ORDER BY [FamilyName] & ", " & [GivenName] & " | " & [Position] & " | " & [CityOfResidence];

 

[mikenyby]

Why would you want zero for any null string?

Honestly I don't know. That was a code template suggested by another user who had a similar issue they were trying to work out.

 

[LarryE]

@LarryE it seems your suggestion is on the right track, but one snafu: my control is not called "FamilyName", it's called "cboNameSelect". FamilyName is one of the fields that cboNameSelect pulls from. If I put the code in as you wrote it, I get "Run-time error '2109': There is no field named 'FamilyName' in the current record."

Alternatively, if I change the offending line of your code to this:
DoCmd.GoToControl "cboNameSelect"

The focus moves to the first record of the subform. I don't know how to reference the desired field that cboNameSelect includes. Here's the row source for cboNameSelect:
SELECT [FamilyName] & ", " & [GivenName] & " | " & [Position] & " | " & [CityOfResidence] AS FullName, Persons.PersonID FROM Persons ORDER BY [FamilyName] & ", " & [GivenName] & " | " & [Position] & " | " & [CityOfResidence];

You should GoTo the name of the field that you wish to search. So if the Name of the field on the form is FamilyName, then it will go to that field. Open the form in design mode and make sure the controls Name is FamiltyName. The Control Source is also FamilyName. The Name of the text box control also needs to be FamilyName.

 

[Gasman]

Honestly I don't know. That was a code template suggested by another user who had a similar issue they were trying to work out.

I would expect 0 if the control was numeric and "" if it was a string.

 

[Gasman]

You should GoTo the name of the field that you wish to search. So if the Name of the field on the form is FamilyName, then it will go to that field. Open the form in design mode and make sure the controls Name is FamiltyName. The Control Source is also FamilyName. The Name of the text box control also needs to be FamilyName.

Don't most experienced developers recommend naming the control differently?, EG: txtFamilyName

 

[Pat Hartman]

A simpler solution is to use criteria to filter the subform. Change the recordsource for the subform to:

Select..
From ...
Where SomeFK = Forms!mainform!SomeFK and (LastName Like "*" & Forms!mainform!subform.Form!txtSearch & "*" OR Forms!mainform!subform.Form!txtSearch Is Null)

Then you requery using two new buttons - search and clear
The click event of the search =
Me.Requery
and the click event of the clear =
Me.txtSearch = null
Me.Requery

 

[LarryE]

Don't most experienced developers recommend naming the control differently?, EG: txtFamilyName

I don't know. Naming the control the same as its control source has never caused a problem for me in 24 years of using ACCESS.

 

[mikenyby]

@

A simpler solution is to use criteria to filter the subform. Change the recordsource for the subform to:

Select..
From ...
Where SomeFK = Forms!mainform!SomeFK and (LastName Like "*" & Forms!mainform!subform.Form!txtSearch & "*" OR Forms!mainform!subform.Form!txtSearch Is Null)

Then you requery using two new buttons - search and clear
The click event of the search =
Me.Requery
and the click event of the clear =
Me.txtSearch = null
Me.Requery

Thanks for the suggestion, but this doesn't seem to work. The form loads with no records visible in the subform. Searching for a name in the txtRecordSearch text box only sets the focus to an empty record on the subform. After plugging in the WHERE clause and saving the record, the SQL comes out looking like this:

SELECT PersonItem.PersonIDFK, PersonItem.ItemIDFK, Persons.FamilyName, Persons.GivenName, Persons.CityOfResidence, [FamilyName] & ", " & [GivenName] & " | " & [Position] & " | " & [CityOfResidence] AS FullName, PersonItem.PageNumbers, Persons.Position
FROM Persons INNER JOIN PersonItem ON Persons.PersonID = PersonItem.PersonIDFK
WHERE (((PersonItem.PersonIDFK)=[Forms]![EditItem]![PersonIDFK]) AND ((Persons.FamilyName) Like "*" & [Forms]![EditItem]![SubFrmEditAssociatedPerson].[Form]![txtRecordSearch] & "*")) OR (((PersonItem.PersonIDFK)=[Forms]![EditItem]![PersonIDFK]) AND (([Forms]![EditItem]![SubFrmEditAssociatedPerson].[Form]![txtRecordSearch]) Is Null));

 

[mikenyby]

You should GoTo the name of the field that you wish to search. So if the Name of the field on the form is FamilyName, then it will go to that field. Open the form in design mode and make sure the controls Name is FamiltyName. The Control Source is also FamilyName. The Name of the text box control also needs to be FamilyName.

I made a backup and changed all instances of "cboNameSelect" and the control name to FamilyName. After refresh still only goes to the first record in the subform.

 

[Pat Hartman]

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

WHERE (Persons.PersonIDFK=[Forms]![EditItem]![PersonIDFK] AND (Persons.FamilyName Like "*" & [Forms]![EditItem]![SubFrmEditAssociatedPerson].[Form]![txtRecordSearch] & "*")) OR (Person.PersonIDFK=[Forms]![EditItem]![PersonIDFK] AND [Forms]![EditItem]![SubFrmEditAssociatedPerson].[Form]![txtRecordSearch] Is Null));

Use the QBE to build the query and use the intellisense to make sure you get the references to the form controls correct.
Also, it seems illogical to name the subform EditAssociatedPerson when you seem to have a popup which is used to Edit associated persons.

I hate the way that the QBE messes up Where logic like this and then adds so many parentheses that the result is unreadable.

Access loads the subform before it loads the mainform so that looks like it is interfering with the criteria in the query. If fixing the PersonIDFK reference in the query doesn't solve the problem, I think you can fix it by requerying the subform in the form's load event to sync it once the main form is loaded. It should be fine after that. The final thing to try is to just remove the criteria that includes the PersonIDFK and just go with the other part that uses family name.

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. That may require a join to the table that defines persons. -- this is actually the most likely issue.

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.

So, there are three problems to address
1. the table level lookup that is causing confusion (not necessary to fix to solve the problem)
2. getting the actual text value into the query (necessary)
3. handling the problem of the subform loading prior to the main form. (necessary if 2 doesn't fix the problem)

 

[LarryE]

Another suggestion is to just open the Find Dialog box using the following command:
Application.CommandBars.ExecuteMso ("FindDialog")
This will open a general Find Dialog box using your command buttons On Click Event ONLY if the form is NOT a pop up form. The command fails on pop up forms.

 

[LarryE]

I made a backup and changed all instances of "cboNameSelect" and the control name to FamilyName. After refresh still only goes to the first record in the subform.

Wait a minute! Are you using a search box (cboNameSelect) to search a field on the forms subform?
Are the search box and the search field on the same form or NOT?

Searching a field on a form should be easy. You should not need to open recordsets. The recordset is already open on the form BUT if you are searching a subform, but the search criteria combobox is on the parent form, that is a different VBA coding issue. You need to set the focus to the subform first, then set the focus to the field you want to search and then do the search.

Is that the case?

If the search box (cboNameSelect) is on the same form you are searching and you search for the name "Lord", the search code I provided should find "Lord, Frances E."

 

[mikenyby]

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

 

[Pat Hartman]

Another suggestion is to just open the Find Dialog box using the following command:
Application.CommandBars.ExecuteMso ("FindDialog")

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