search a subform 2

[Fredgarner]

From my form I am trying to use a combo box to find a specific name in my subform (called 'shareholders subform') within the 'account designation' field. Can anyone identify why the code below does not work?

Private Sub Combo174_AfterUpdate()
DoCmd.GoToControl "Shareholders subform"
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Account Designation] = " & Str(Nz(Me![Combo174], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Remou]

As I indicated in my previous post, you must use the recordset belonging to the subform. So this:
Set rs = Me.Recordset.Clone
Should read:
Set rs = Me.[Shareholders subform].form.Recordset.Clone
Also, there is not much point search, when the combo is empty: rs.FindFirst "[Account Designation] = " & Str(Nz(Me![Combo174], 0))
If Dim rs as DAO.Recordset is not working for you, you may need a reference to the Microsoft DAO 3.x Object Library.
Generally if a post does not solve a problem, it is mentioned in the same thread. Often other people will step in, too.

 

[Fredgarner]

Many thanks for your posts, Remou. Unfortunately I am still not able to do what I wanted. I think this may be because each of my main forms is linked to a different part of the subform by a shareholder number (so that only the accuotn designation relevant to the currently diplated contact are dsiplays at the bottom of the screen) and unless the account designation I am searching for is actually located in the subform for the current record, the combo box is not coming up with anything in its search. I think there might not be a way of solving this one!

 

[Remou]

Ah! Yes there is. Dlookup the table, not the subform. Find the contact name from the table, go to the contact name on the main form, go to the account on the subform.

 

[markgrizzle]

an innicent bystander thanks you for the help.

 

[Remou]

Most welcome.