Form goes to 1st record when filter applied to subform 
Author Message
 Form goes to 1st record when filter applied to subform

I have a db for a mortgage lender.  She wanted to be able to check different
boxes for here client, such as Single Family Home or Second Mortgage, and
then have a list of Lenders that handle those types of loans pop up.  I put
the lenders in a continuous subForm of the Client's form and wrote the code
like this:

'**********************
If Me.chkRefinance = True Then
strRefinance = "tblLenders.Refinance = " & True & " and "
Else
strRefinance = ""
End If

If Me.chkSingleFamilyAttached = True Then
strSingleFamilyAttached = "tblLenders.SingleFamilyAttached = " & True & "
and "
Else
strSingleFamilyAttached = ""
End If

strFilter = strRefinance & strSingleFamilyAttached

strFilter = Trim(strFilter)

If Len(strFilter) > 0 Then
strFilter = Left(strFilter, Len(strFilter) - 4)
End If

Forms!frmSearch!fsubSearch.Form.Filter = strFilter
Forms!frmSearch!fsubSearch.Form.FilterOn = Len(strFilter)

Me.Requery
'**********************

The subform is not linked to the parent because there aren't any
corresponding fields.  There are about 30 check boxes.  It works okay,
except if I uncheck the last box so strFilter is empty.  Then what happens
is the client form pops to the first record.  How can I prevent this from
happening?

Thank you in advance.

Cindy



Mon, 05 May 2003 03:00:00 GMT  
 Form goes to 1st record when filter applied to subform
Cindy,
I believe the
Me.Requery
is causing your main to go to the first record.
I didn't think you had to requery when applying a filter,
I thought it did it automatically,
but if you do, just requery the sub. Not Me (the main).

--
Hope This Helps,
Jeff Davies

Quote:

> I have a db for a mortgage lender.  She wanted to be able to check different
> boxes for here client, such as Single Family Home or Second Mortgage, and
> then have a list of Lenders that handle those types of loans pop up.  I put
> the lenders in a continuous subForm of the Client's form and wrote the code
> like this:

> '**********************
> If Me.chkRefinance = True Then
> strRefinance = "tblLenders.Refinance = " & True & " and "
> Else
> strRefinance = ""
> End If

> If Me.chkSingleFamilyAttached = True Then
> strSingleFamilyAttached = "tblLenders.SingleFamilyAttached = " & True & "
> and "
> Else
> strSingleFamilyAttached = ""
> End If

> strFilter = strRefinance & strSingleFamilyAttached

> strFilter = Trim(strFilter)

> If Len(strFilter) > 0 Then
> strFilter = Left(strFilter, Len(strFilter) - 4)
> End If

> Forms!frmSearch!fsubSearch.Form.Filter = strFilter
> Forms!frmSearch!fsubSearch.Form.FilterOn = Len(strFilter)

> Me.Requery
> '**********************

> The subform is not linked to the parent because there aren't any
> corresponding fields.  There are about 30 check boxes.  It works okay,
> except if I uncheck the last box so strFilter is empty.  Then what happens
> is the client form pops to the first record.  How can I prevent this from
> happening?

> Thank you in advance.

> Cindy



Tue, 06 May 2003 07:11:27 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Applying a filter on a form filters subforms, too

2. applying filter on a subform

3. Subform code for apply filter??

4. How do I apply a filter to a SUBFORM (In VBA CODE)

5. Applying filter to a subform

6. Filter by form - apply filter button required - Access 97

7. Copy Form records and Subform records....

8. Help removing a filter and not going to first record

9. Form Question - Stepping Through Subform Records on Master Form

10. Filter By Form Using Subform Fields

11. Help: Form/Subform filter

12. Requery a Combo Box on a Subform When Filtering the Main Form in Access 7.0

 

 
Powered by phpBB® Forum Software