ACCESS FEED: RE: [MS_AccessPros] When load form store filter and click reset button to reset filter

Rabu, 24 Agustus 2011

RE: [MS_AccessPros] When load form store filter and click reset button to reset filter

Connie-

I don't understand the purpose of strFilterListings_AllChange. If you're trying
to "collect" multiple filters, you should do:

strFilterListings_AllChange = ("(" + strFilterListings_AllChange + ") AND ") &
"(" & Me.Filter & ")"

What that does is wrap parens around the existing contents of
strFilterListings_AllChange and then add an AND operator, then add the current
filter surrounded by parens. If there's nothing in strFilterListings_AllChange,
then you'll get the current filter surrounded with parens.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
Sent: Wednesday, August 24, 2011 6:51 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] When load form store filter and click reset button to
reset filter

Good morning! I am using Access 2007. I have a form Listings that is currently
opened in two different ways. One button on another form opens the Listings
form with a where condition. The code behind that button is:

DoCmd.OpenForm "Listings_AllChange", , , "CurrentListing = -1"
Forms!Listings_AllChange.FormTitle.Caption = "Current Listings"
Forms!Listings_AllChange.Caption = "CurrentListings"

The toggle filter button on the ribbon is highlighted and pressing it makes the
source go from all records, to current listings, and back.

I want to store the filter that is used when this form is opened so that no
matter what filter or non filter is applied, it can be reapplied. The On Load
property where I'm trying to get the filter has this code which I'm pretty sure
is not correct:

Dim strFilterListings_AllChange As String
DoCmd.GoToRecord , , acNewRec
strFilterListings_AllChange = strFilterListings_AllChange & " & Me.Filter &
"

The Listings form has a header with search boxes and a button to reset the
search boxes to "". I would like that reset button to apply the filter or
nonfilter that was applied upon opening. Or should I say the where condition?
Here's the code I have:

'Reset filter and source of records
Me.Filter = ""
cboSearch = ""
If Me.FilterOn = True Then Me.FilterOn = False
Me.Filter = strFilterListings_AllChange
Me.FilterOn = True

As you can see below when I search for a "non current" listing name it looks at
the recordsource without the filter. How can I change that code to add the
filter? .

The code behind the search box is:
Private Sub cboSearch_AfterUpdate()
'~~~~~~~~~~~~~~~~~~~~~~Error Handler came from Crystal
'set up Error Handler
On Error GoTo Proc_Err
'~~~~~~~~~~~~~~~~~~~~~~

Dim strSql As String

If IsNull(Me.cboSearch) Then
'If the combo is Null, use the whole table as the RecordSource.
Me.RecordSource = "qListings_All"
Else
strSql = "SELECT DISTINCTROW qListings_All.* FROM qListings_All " & _
"INNER JOIN ListingContacts ON " & _
"qListings_All.ListID = ListingContacts.ListID " & _
"WHERE ListingContacts.ContactID = " & Me.cboSearch & ";"

Me.RecordSource = strSql
End If

thank you!
Connie

------------------------------------

Yahoo! Groups Links