Solved Search and find records on a continuous subform

[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

 

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

 

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

 

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