Clearing filters on close or open

[Kevin Ensign]

Created a Switchboard for front end users. It has two buttons opening different forms and two combo boxes to filter with. Issue I am having is the filter stays in place (shows the last record viewed) so when I open the button it only shows me one of one record ( currently have 47 records) . I realize I can hit the filter tab at the bottom to clear this/remove and show all records but believe this will be confusing for users.



When I click the button to open the form I would like to be able to see all records or choose next record.

behind the form in Event Procedure, On Close I have tried a few things such as: without luck.

Me.Form.Filter = ""
Me.Form.FilterOn = False

and

DoCmd.RunCommand acCmdRemoveAllFilters

Looking for some guidance as I am not sure what I'm doing wrong?
Thanks in advance

[Micron]

Me.Filter = ""
Me.FilterOn = True

try that in the form open event instead of the close event. I can't recall prefacing filter or filteron with .Form, so I'm not sure if that could be the issue either.

[ssanfu]

behind the form in Event Procedure, On Close I have tried a few things such as: without luck.

Do you have any other code on the form close event?

[Kevin Ensign]

Do you have any other code on the form close event?

I know the below is messy, but last I was attempting to solve this was what i had.

Private Sub Form_Close()
If Me.Form.FilterOn = True Then
Me.Form.Filter = ""
Me.Form.FilterOn = False
'Me!btn.Caption = "Apply Filter"
Else:
'Me.Form.Filter = "[RequestID]="
'Me.Form.FilterOn = True
'Me!btn.Caption = "Remove Filter"
End If
End Sub

[Micron]

This
step 1:
is a line label because of the colon ":" so your your Else is a line label, not the Else part part of an IF block. If you had it that way, you were turning the filter on each time you close the form regardless of what its setting is, but you seem to have it commented out a present. As mentioned, I'm not sure I would apply a filter on form closing, but maybe it makes sense for what you're doing, whatever that is. I think you can simplify this by moving the code to the form open event, turning it off, then on or off when the user actions occur.

Again, Me refers to the form and Form refers to the form. I've never used Me.Form.FilterOn - just Me.FilterOn. Maybe that's not significant here.

[Kevin Ensign]

I removed the event procedure from on close and added:
Me.Form.Filter = ""
Me.Form.FilterOn = False
to the event procedure to on open. Now it is performing correctly BUT when I enter a claim# into the combo box it does not function. It just opens the form and does not locate the claim#in drop down.

[Micron]

I'm getting confused about what's going on here. I thought the filter was being applied to the form via the combo box and you were having difficulty removing it upon subsequent opening? Now it's not being applied at all? Perhaps you should elaborate on the steps involved and what is supposed to happen where.

[Kevin Ensign]

Database is being created to track warranty claims that are being returned for an evaluation and/or warranty recovery($). On the switchboard we have 4 buttons:
1)manage warranty =allows user to enter a start and end date to view 3 reports
2)manage recovery=opens a form called return & recovery. allows user to add a claim# and its data
3)enter debit= allows user to enter a claim# and amount debited
4)close= closes the database
Also we have two combo boxes to allow user to quickly find a specific claim# or RGA#(given by supplier to return part)

User should be able to open "mange recovery" button, enter a claim# generated from the warranty system and then import business information related to that part number. Then information can be added as the process goes along, tracking number, date started and closed, evaluation, expected recovery and actual, etc...

From the combo box user should be able to enter the claim# or RGA# to quickly find that entry and add more information or close it. At present time, this combo box is not working. When I open, it shows me a list of all claim #'s entered in so far and when we choose one the form opens but always to the first entry.

[Kevin Ensign]

In the form return & recovery we have the following in the property sheet-->event-->on open-->event procedure-->
Private Sub Form_Open(Cancel As Integer)
Me.Filter = ""
Me.FilterOn = True
End Sub

[ssanfu]

I also have never seen filter syntax set using

Me.Form.Filter = ""
Me.Form.FilterOn = False

But it works. Entering the lines, IntelliSense 'built' the command; I then tested the code and the form was filtered.


And speaking of your dB, any chance you would post your dB? It would be much easier to figure out....
Only need 5 - 10 records for testing. Change any sensitive data (change names... Mighty Mouse).
Do a "Compact & Repair", then Zip it.

[Kevin Ensign]

any specific instructions on how to zip it and attach? I did the right click a zip folder. Then a blank "canvas" showed up on desktop. Have never used this before so....

[Kevin Ensign]

trying to add zipped file

[Micron]

In your original post, you were looking for help on applying or removing filters, but you don't need to do that at all. You are opening the form from the switchboard with a WHERE condition, so no additional filtering is required for this. Also remove your filter property from the property sheet for the form - one got saved there. You might want to remove the navigation controls, record selectors and any dividing lines if you're always going to open this form to a single record. At the bottom, a user can click to remove the filter. No problem if you're ok with that, otherwise, you might want to open this form based on a select query instead. This could also work as long as your query is updatable, which I don't see why it wouldn't be. As a normal practice, you'd create the query and see if you can alter data in its datasheet display before making it the record source for a form. In fact, I don't usually build forms until I have working queries. I might have more time to peruse the rest later, but I gotta get back to Santa's work shop.

[Micron]

So some comments after perusing as promised (or was it a threat?)

manage recoveries - might want to provide button for new vs edit. Going into a filtered form, then having to click to add a record is not how I'd do it. As for the buttons on that form, pictures are nice and usually say 1000 words, but when it comes to db's, words are better. It takes too long for the control tip text to let you know you're about to delete the record.

no relationships defined, so can't say for 100% but looks like db is not normalized:
- supplier info in export table instead of supplier ID (no supplier table either)
- multiple description fields in same table
- no stockingtype table, but don't know what that data is supposed to look like
- no branch table; looks like branch info would be repeated

spaces and special characters in field and/or table names;
claiminfo 1 and 2 - not very descriptive as table names;
default control names like Text12 (or whatever the default name is) won't help when trying to follow code.
looks like totals are being stored; probably should not be
attachments being kept in db rather than links to them (not wrong, but not worth the trouble they cause)
can't tell if recoveries are being totaled or just entered. Storing calculations is generally not advised.
form r&r status combo - show status, not id number
if you enter debits, they don't show in report because there's no claim1/2 or standard cost records (may be that forms are not complete yet)
report debit by dates is too wide for the paper size
maybe it would be best to manage debits from the return/recover form, or some other way to know you have the correct claim number, otherwise looks too easy to make a debit against the wrong claim
switchboard - I'd remove the value from the combo not used, but I'd code for these in the AfterUpdate event, not click event
The code for these is obviously converted macro code, which is OK, but it tends to be a bit over done. All of this
'Dim stDocName As String
'Dim stLinkCriteria As String

'stDocName = "Frm_Return and Recovery"

'stLinkCriteria = "[Request ID]=" & Me![ClaimQuickSeach]
'DoCmd.OpenForm stDocName, , , stLinkCriteria

can be replaced with
DoCmd.OpenForm "Frm_Return and Recovery", , , "[Request ID]=" & Me![ClaimQuickSeach]
and if clearing out the other control:

Me.Combo12 = ""

The bold line opens the form as you wish, I think, and no filters need be applied
That's it for now.

[Kevin Ensign]

Micron & ssanfu,

Thank you for your guidance and suggestions! May both of you have a Merry Christmas and Happy New Years!. I Have a week off from daily work items so looking forward to working on this project and getting it up running early next year. Lots to learn and this site really makes that easier when the Boss isn't around to answer questions.

Again, your help is much appreciated! Will try and make improvements and get back with more questions asap ....Kevin