Forms :: Can't Clear Filter Or Orderby Permanently On Form

May 14, 2013

I have a tabular form that is based on a select statement (Query) in the source property. It filters & sorts from this select statement. permanently

I have just added a
Me.Filter = "[Form1].[InDate] = #" & MyValue & "# "
Me.OrderBy = "Indate DESC"

This all works fine, filters & sorts on the existing results.However this new filter & sort seems to remain no matter what I do.I have used every variation one me.filter ="" with Me.FilterOn = True, False & every combination.

Me.Filter="" & Me.FilterOn = False does initially seem to clear the lsit to show all records but as soon as type anything that filters the Form on the original, existing underlying select statement the extra Filter Property & Sort properties are occupied with the last filter/sort.

I have manually deleted any remaining Filter Or Sort properties direct from design view, saved, compacted & saved again.Once I use the new me.filter it will always returned as soon an any filter on the form is applied.I definately am not setting it again anywhere. Is there something better than me.filter ="", seen clearallfilters mentioned but not sure if this would apply just to the current form but reluctant to use a blanket clear as I do have quite a lot of other things going on.

Not sure when but many months (If not years) ago my form source stopped being a query and started using the query (sql) direct from the source property.I have left it this way as not sure if any consequence of moving back but don't remember how or how it happened. What are the pros cons of using a separate query.

View Replies


ADVERTISEMENT

Modules & VBA :: Form Filter And OrderBy Pass To Report

Aug 16, 2014

I am able to filter a data on a continuous form using drop downs and then the following code attached to a cmdbutton to create a report of the filtered data.

Code:
Private Sub Command30_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "rptconveyorerrors", acViewReport, , strWhere
End Sub

On the same form where I filter the data i can sort it by clicking the headings aswell, however when i generate the report using the above VBA it doesn't take the sort with it and just generates it without the sort.

I am using the following VBA to sort my form

Code:
Me.OrderByOn = True
If Me.OrderBy = "[empname] DESC" Then
Me.OrderBy = "[empname] ASC"
Else
Me.OrderBy = "[empname] DESC"
End If
Me.Refresh

I thought it may be possible to use the following sort of VBA to pass the sort however i cant get it to work:

Code:
Private Sub Command30_Click()
Dim strOrder As String
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then strWhere = Me.Filter
If Me.OrderByOn Then strOrder=Me.OrderByOn

View 1 Replies View Related

Forms :: Clear Only One Filter

Mar 17, 2015

I've seen code similar to below that clears all filters, but is it possible that instead of clearing all filters, I can clear just a specific one?

Private Sub cbGroup_Click()
Me.cboUnitName = Null
Me.cboGroup = Null
Me.tbl_users_subform.Form.Filter = ""
Me.tbl_users_subform.Form.FilterOn = False
Me.tbl_users_subform.Form.RecordSource = "tbl_users"
Me.tbl_users_subform.Requery
End Sub

View 2 Replies View Related

HELP ! How To Clear A Filter ..................

Jun 14, 2006

hi,

How do I clear a filter ? When I re-open a form that is based on a stored parameter query, I get a message about "apply/filter that cannot be applied..."

So, as a result, I put in some code for that command button that closes the form, then reopens it, and I get my prompt from the query. Isnt there a way to clear the filter without closing the form? It looks funny to me and I know the users will have a cow. I sure appreciate any ideas.Im digging around to see what I can do quickly, but I cant find anything that works. thanks a lot!

View 3 Replies View Related

Forms :: How To Make Hidden Fields Permanently Appear When Another Field Is Changed

Jan 14, 2015

i have a form that shows payments (checks) that have been issued. sometimes those checks need to be voided and i want 2 fields (Updated By and Updated Date) to pop up when the payment distribution field is changed to a void status.

payment distribution: "I" for issued and "V" for void

i've gotten the On Change Event to work with VBA when the payment distribution changes from "I" to "V" and the 2 new fields pop up but if i exit the form and go back in to look at that record, the fields are gone. Is there a way to make the fields permanently if the payment is "V" on the form?

this is what i have so far for the On Change Event:

If [payment distribution] = "V" Then
me.cmbo_UpdatedBy = True
me.txt_UpdatedDate = True
Else
me.cmbo_UpdatedBy = False
me.txt_UpdatedDate = False
End If
End Sub

View 3 Replies View Related

Forms :: Clear Form Upon Loading - Only Allow Labels

Dec 12, 2013

I'm having trouble getting my form to be "blank" except for the labels upon loading. I've tried putting in some code "on load" but it doesn't clear everything. I haven't had this trouble on some of my other db's, the only difference here is it's a switchboard. I don't know if that matters.

View 1 Replies View Related

Forms :: Clear Fields On Form Open

Jan 2, 2014

I have a timesheet form that pulls from a query that pulls from a table I want to clear my daily charges and reset back to the default value of 0 when my form opens ...

Saturday, Sunday, Monday , Tuesday , Wednesday, Thursday, Friday

View 1 Replies View Related

Using OrderBy Gives Error At Reopening Form.

Nov 15, 2006

I have a form where the recordsource is a stored procedure.

This forms shows a list. When the user clicks on a column title, the column is sorted by using OrderBy in VBA:

f.i.:

Private Sub lblCode_Click()

Me.OrderBy = "strCode"

End Sub

As a result, in the properties of the form, for the property Order By, the fieldname is filled in (f.i. strCode).

This works fine, but when the form is closed, access saves the property Order By.

Next time the form is opened, it gives an error: The recordsource <storedprocedurename> specified on this form does not exist.

When I remove the fieldname in the Order By property of the form, the form opens without an error.

I have tried to clear the Order By property by using Me.OrderBy = "" in the Close event of the form, but this does not work.

Any solution ?

View 2 Replies View Related

Modules & VBA :: OrderBy When Opening A Form

Nov 29, 2013

I'm trying to have a form sorted in a particular order when it's opened.

When using the "order by" option in the properties sheet, it is sorted the way I want the first time I open the form.

However, if I change the order afterwards while using the form (for instance, by right clicking and selecting another sort order), well the next time the form is opened, it is sorted with this new sort order, and not the default one.

So I tried to add the following code :

Code:
Private Sub Form_Open()
Me.OrderBy "employee_name ASC"
Me.OrderByOn = True
End Sub

Which failed epically.

View 6 Replies View Related

Forms :: How To Clear Up A Specific Error On Search Form

Jan 7, 2015

I have a search form that's slightly modified search form modeled after the one available here on this site. It's a main form with a list box. I use the listbox to narrow down the results I want from the search function.

The search details are shown in a sub form that contains sub-sub forms

As seen in the code below, I am using the listbox to set a filter on the subform . Works famously until I search for something that does not exist in the database. I then receive runtime error 3075.

I believe it's related to the way I'm showing the details in the subform (by enabling a filter). If there is no record in the database that match the search criteria, I don't want any error messages or pop-ups and preferably would like the listbox to be blank.

Attached a screenshot of my form.
red = main form
green = subform
blue = sub-subforms
tan = obscuring the search results

Code:
Private Sub SearchFor_Change()
Dim vSearchString As String
vSearchString = SearchFor.Tex
SrchText.Value = vSearchString
Me.SearchResults.Requery

[Code] .....

View 2 Replies View Related

Forms :: Looping Through A Form To Clear Each Field For All Rows

Sep 4, 2014

How can I loop loop through a tabular form to clear each value in a field of all rows ? I tried the below code, but it did not work.

Loopcnt = DCount("*", "Budget")
For Loopcnt = 1 To Loopcnt
[Forms]![Budget Form].[Newbudgt] = Null
DoCmd.GoToRecord , , acNext
Next Loopcnt

View 8 Replies View Related

Forms :: Clear Fields In Form And From Table With A Button

Mar 10, 2015

I want to be able to clear all the contents in my fields (which are bound to my table) with a click of a button

View 1 Replies View Related

Transfering RecordSource/OrderBy Of Form To Report

Oct 17, 2006

I have a subform within a form that displays filtered data based on dynamic selection criteria which is assigned to the RecordSource/OrderBy of the subform. On this form, I have a "Print Results" button -- OnClick, I would like the RecordSource/OrderBy of the subform to be copied to the Report, then run the report. What I have that doesn't work is:

Reports!rpt_frmTaskMain.RecordSource = Forms!frmTaskMain.frmTaskSub1.RecordSource
Reports!rpt_frmTaskMain.OrderBy = Forms!frmTaskMain.frm.TaskSub1.OrderBy

On execution I get error: "Object doesn't support this property or method"

I am thinking this can be done, and likely have the references wrong. Help?

View 1 Replies View Related

Forms :: Possible To Clear Filters Set On Subform Using Button On Main Form?

Jan 12, 2014

Is it possible to clear filters set on a subform using a button on the main form?

View 1 Replies View Related

Forms :: Filter Button On Form As Filter

Aug 1, 2013

I have placed a filter button on a form as a filter and written the following on-click event procedure:

DoCmd.SetWarnings False
DoCmd.RunCommand acCmdApplyFilterSort
Me.Filter = "ACCOUNT_DO_NOT_EMAIL = 'HS'"
Me.FilterOn = True

When I click the filter button I get a blank message box titled 'Microsoft Access" and an OK button, when closed the filter works perfectly.I have checked this procedure in other forms and it works without showing the blank message box.The only difference with this form is that its control source is a union query.

View 10 Replies View Related

Permanently Deleting Entries In Access!

Dec 6, 2005

When i enter entries into Access, and then they are deleted, for some reason the size of the Access Database doesn't reduce in size. I've only just noticed this as i accidently entered over 65000 entries in and then had to delete them now the database is twice it's usual size.

Can someone please tell me how to reduce the size of the database and explain why when you select delete record it doesn't actually delete the record.

Regards,

Donni.

:cool:

View 1 Replies View Related

Tables :: Permanently Locking Date Field

Nov 23, 2013

I have a table that has two date entry fields.

DateCreated [ Default Value =Date() ]

DateUpdated [ free data entry ]

I need to lock the DateCreated field as a permanent record which cannot be altered.

View 3 Replies View Related

How To Make Total Rows In Query Permanently Visible

Aug 29, 2011

How do I make the 'total rows' in query permanently visible? Right now, every single time when I click on to my query, I will have to go to HOME---> RECORDS ---> TOTALS then the Total row will appear. And when I export that query, the TOTAL ROW is missing. how to make the total rows visible always?

View 4 Replies View Related

Forms :: Applying Sort And Filter In Form Results In Design Changes To Form

Dec 27, 2014

When I'm applying a sort and filter in a form, Access is updating the Filter and Order By properties of the form, so that it is possible to re-use in conjunction with Filter on Load and Order By on Load properties. However, a consequence of this is that when the form is closed, it prompts the user whether they want to save the design of the form. I want to circumvent this as I don't want to re-use the sort and filter and I don't want to be prompted to save the design of the form.

Although I can circumvent this by closing the form using a method that doesn't prompt for saving, the additional complication here is that the form in question is in the Navigation subform of a Navigation Control. Hence when I click on a another Navigation button, it (not me) closes my current form and hence prompts me whether I want to save the design of the form (if I have been sorting and/or filtering). I can't see how to circumvent this and the prompting is resulting in unacceptable usability.

View 2 Replies View Related

Orderby

Apr 26, 2006

Hallo everyone,

I have a table with one of its column value as "B" or "D" or "E".

Now i am in need to display the rows using the select statement with the following conditions.

1. First display the rows with value "D"
2. then display the rows with value "E"
3. then display the rows with value "B"

Is it possible to specify the above condition in a orderby clause.

thanks,

aravind.s

View 1 Replies View Related

Forms :: Filter On A Form

Mar 2, 2015

I can understand with placing a filter on a form.I have a table that needs updating. I created a form based on the table, but would like to only see the records that need updating, instead of the entire table.

I have no SQL knowlegde. Is there some simple answer? I would like to filter on one field for the word "NO" ie seeing only the records which contain NO in that field.

View 10 Replies View Related

Forms :: Filter List Box On Form By Using A Control On Form

Jan 14, 2015

On a form I have a:

control called "FilterListBox"
list box called "lstCustomer"
option Box called "optCustomerType"

When the user selects an option in the option box, "FilterListBox" is updated to either "1", "2" or "1 or 2"..One of the fields in the query for "lstCustomer" is "CustomerType" and its criteria is set as follows:

[Forms]![frmPrintHowCustomersPaidInvoice]![FilterListBox]

if "FilterListBox" = 1 the query for "lstCustomer" returns the correct records
if "FilterListBox" = 2 the query for "lstCustomer" returns the correct records

But if "FilterListBox" = 1 or 2, no records are returned.

View 9 Replies View Related

Forms :: Filter A Form Based On A Field In Another Form

Jul 13, 2014

I have a form (Suppliers) with a button that opens another form (Product List).

At the moment the form (Product List) is set as a pop-up form and when opened lists all products.

I want to filter the pop-up form to only display products for the current supplier displayed in the Supplier Form.

I have tried the following below without success - I have varied this but the filter does not apply ...

Private Sub Form_Load()
DoCmd.OpenForm "Product List", acNormal, , "SupplierID = " & SupplierID
End Sub

View 3 Replies View Related

Forms :: Filter Second Form Based On Text Box In First Form

Apr 30, 2014

I have a button that opens a second form and filters that form based on a text box on the first form(which remains open). the problem is that it does not filter the second form unless the second form is already loaded for some reason.

For example if i click on the button it opens the form Unfiltered then i go back to the previous form without closing the first form, click the button again and it is then filtered .

I attached the screen shot of the macro for the button and there are no other events on the second form.

View 5 Replies View Related

Forms :: Clear Yes / No Check Box

Mar 13, 2013

I am using my form to add job descriptions and if the user checks the check box for current, it adds the description to the table as the current job description. I need to clear the check box for the next entry but the box retains the choice so when the user selects another description, the form saves it as current even if the box shows no check in the box. I tried the code below, but that only changes the state of the box, but not the reality of the state of the box.

Code:
Me.CheckBox.Value = No

View 9 Replies View Related

OrderBy With Wildcards

Oct 13, 2006

I am trying to get the OrderBy to recognize wildcards (or to that effect). A scenario would be I would have more than one name in a field, EX: Name2, 4, 6. How do I use wildcards in the OrderBy and they are recognized.

ORDER BY (Table.Field)="Name1", (Table.Field)="Name2", (Table.Field)="Name3", (Table.Field)="Name4", (Table.Field)="Name5", (Table.Field)="Name6", (Table.Field)="Name7", (Table.Field)="Name8";

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved