Code to Search by Field in Subform

[larochy]

I'm trying to use a Popup search form I built that allows the user to search
by three different options, Customer, InvoiceID, and Deferred Revenue ID.
Deferred Revenue ID is in a subform. In the After_Update event of each combo
box on the popup search form is a macro that opens the Invoice form and
executes the search using the Where clause in the Open Form action. It
works fine for both the Customer and Invoice which are located on the main
form but I can't get it to work for the Deferred Revenue ID located on the
subform. Is it even possible to search by a field on the subform? The two
forms are linked by the InvoiceID but one invoice can have hundreds of
Deferred Revenue ID's so I'm trying to help them go one level deeper in the
search. Here is my Where clause in the Open Form action on my macro.

[forms]![Invoice]![DeferredRevenue].[Form]![DeferredRevenueID]=[Forms]![Search]![Combo23]

Should I be using VBA in the After_Update event to perform the filter? If
so, please provide an example of the code needed to do this.

Thanks in advance

 

[banem2]

I'm trying to use a Popup search form I built that allows the user to search
by three different options, Customer, InvoiceID, and Deferred Revenue ID. 
Deferred Revenue ID is in a subform.  In the After_Update event of eachcombo
box on the popup search form is a macro that opens the Invoice form and
executes the search using the Where clause in the Open Form action.   It
works fine for both the Customer and Invoice which are located on the main
form but I can't get it to work for the Deferred Revenue ID located on the
subform.  Is it even possible to search by a field on the subform?  The two
forms are linked by the InvoiceID but one invoice can have hundreds of
Deferred Revenue ID's so I'm trying to help them go one level deeper in the
search.  Here is my Where clause in the Open Form action on my macro.

[forms]![Invoice]![DeferredRevenue].[Form]![DeferredRevenueID]=[Forms]![Search]![Combo23]

Should I be using VBA in the After_Update event to perform the filter?  If
so, please provide an example of the code needed to do this.  

Thanks in advance

Hi,

I am not sure what this command should do:

[forms]![Invoice]![DeferredRevenue].[Form]![DeferredRevenueID]=[Forms]!
[Search]![Combo23]

Are you trying to set the filter or to find a record? How it is, field
DeferredRevenueID should got a value of Combo23.

Also the syntax does not look quite right(?). If you are not sure how
to refer a field on subform, use the following trick.

Start new query, don't add any table to it. Click "Build" button on
toolbar. Double click "Forms", double click "Loaded Forms" and go on
until you see the field you want in right listbox. Double click the
field name. Now copy (CTRL+C) reference from above text box and paste
it into code. Close the query, don't save.

You will got just a reference to field. To find a record you can use
something like this in subform:

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[DeferredRevenueID] = " & _
Str(Nz([Forms]![Search]![Combo23], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Regards,
Branislav Mihaljev
Microsoft Access MVP