Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2009

    Unanswered: Sorting forms based on criteria

    I have a form that is used to update a few pieces of data from my master table. To simplify things down a bit, when I open this form, I only want my data to show for records where "Remaining $" are greater than $0. So far, I haven't discovered any way to successfully do this.

    1. When I look at the properties for the form, I have a line of code in the Filter box (under the Data tab) that reads ((Not MasterInfo.[Remaining $]=0)). I can't remember where this comes from, but apparently it doesn't work.
    2. I also tried creating a macro that applies a filter where I built an expression that essentially states the same thing as point 1 above. I put the macro on the form properties under Open. However, when I opened the form, it showed 1 data row with zero values all the way across. Perhaps the code was wrong, but I should have about 800 rows of data.
    3. I guess the long way to do this would be to create a "make-table query" that pulls all the data greater than zero from my master table. I could then use a form to update this temporary table, and then run an update query to take data from my temp to master table. This seems like a bit of overkill to me (if it would even work).

    I imported the filter buttons in to my design view, but they aren't applicable there. Any idea how to filter this report other than the standard option (i.e. right click in the field to filter and use Exclude filter)?

    See below for a sample view of the database (click it).

    Click image for larger version. 

Name:	Database posting.jpg 
Views:	12 
Size:	24.5 KB 
ID:	9338

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    Both solutions should work.

    1. Set the Filter property of the form to:
    [Remaining $] <> 0
    and set the AllowFilter property to True. In the OnOpen Event code of the form, add this:

    Me.FilterOn = True.
    2. You can also create a query (say MyQuery) based on the master table and specify a condition, like this:

    SELECT * FROM master WHERE master.[Remaining $] <> 0
    then base your form on that query instead of basing it on the table (ie change the RecordSource property of the form to MyQuery).

    You do not need anything else and the modifications you make in your form will be automatically sent back to the table (no make-table query and no update query).

    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts