Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2009
    Posts
    5

    Exclamation Unanswered: How can I open a form that inherits another form filter (forms recordsources differ)

    Hello everyone,

    I'm a very unexperienced Access user and possibly worse at programming dbs/sql/vb code. I'm almost done with my database, still there's one problem that prevents me to complete it.

    Here's it so far:

    Two forms are given in this database.
    form1 recordsource is a select-query based on table1 (the query acts as a filter)
    form2 recordsource is table1

    form1 is a divided-form that shows me a part of table1 records.
    form2 shows table1 records one by one in detail.

    As I said form1 shows me only part of table1 records. While using this form, I might want to introduce more filters (using Access UI) and eventually have form2 to show me in detail only those records I filtered from form1.

    So most likely I'll create a button with a command that orders to open on click "form2" that inherits mask1 filtered data.

    I came along with this code for the button:

    Code:
    If Me.Filter = "" Then
            DoCmd.OpenForm "form2"
        Else
            DoCmd.OpenForm "form2", acNormal, , Me.Filter
    And here the problem comes as it doesn't want to work.

    I have found out that the different form recordsources could be the problem. Infact if I change form1 to the same recordsource as form2 it works (as form1 filters are applied to form2 correctly).

    Unfortunately I can't do this, because of the structure of the database I have made.

    How could I possibily solve this?
    Last edited by eviscerator; 11-04-09 at 13:42.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try this:
    Code:
    DoCmd.OpenForm "form2"
    ' No need to test
    Forms!Form2.Filter = Me.Filter
    Have a nice day!

  3. #3
    Join Date
    Nov 2009
    Posts
    5
    Nope, it just opens the mask, but nothing goes on. I have put the code you provided me into the private sub of the button.

    btw what's the meaning of 'no need to test comment ?

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    More details are needed. What exactly is the filter in the first form that you want to apply to the second form?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    After setting the filter of Form2 you have to activate it:
    Code:
    Forms!Form2.FilterOn = True
    The comment simply means that you are not obliged to test wether a filter is defined in Form1. If it's not you'll simply pass an empty string to Form2.
    Have a nice day!

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I've had too many problems using the filter. It doesn't upsize nicely with MSAccess 2007 and if there is a ' or " or other odd characters within the field value, it can cause problems using the Filter.

    Why not just open form2 using the WHERE criteria versus the filter when opening it: ie.

    docmd.openform "Form2",,"[RecordIDField] = " & Forms!Form1!RecordIDField & ""

    or if that doesn't work, put an unbound field on Form2, then after you open it, populate the unbound field with the value and then requery the recordset.

    ex:
    docmd.openform "Form2"
    Forms!Form2!MyUnboundField = me!SomeFieldValue
    Forms!Form2.requery

    making sure the recordsource of Form2 has criteria in it where the appropriate field matches the unbound field on Form2 (ie. Like Forms!Form2!MyUnboundField & *).
    Last edited by pkstormy; 11-04-09 at 23:24.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Nov 2009
    Posts
    5
    Quote Originally Posted by StarTrekker View Post
    More details are needed. What exactly is the filter in the first form that you want to apply to the second form?
    I can't really define the filter I want to export in form2, as it might change everytime. I'm using the Access 2007 UI to filter "on the fly" data I want to see. For example some day I'll filter out a few Categories of my stuff. Some other day I might want to check all those items below 50 value, items sold the past month and so on.

    Quote Originally Posted by pkstormy View Post
    I've had too many problems using the filter. It doesn't upsize nicely with MSAccess 2007 and if there is a ' or " or other odd characters within the field value, it can cause problems using the Filter.

    Why not just open form2 using the WHERE criteria versus the filter when opening it: ie.

    docmd.openform "Form2",,"[RecordIDField] = " & Forms!Form1!RecordIDField & ""

    or if that doesn't work, put an unbound field on Form2, then after you open it, populate the unbound field with the value and then requery the recordset.

    ex:
    docmd.openform "Form2"
    Forms!Form2!MyUnboundField = me!SomeFieldValue
    Forms!Form2.requery

    making sure the recordsource of Form2 has criteria in it where the appropriate field matches the unbound field on Form2 (ie. Like Forms!Form2!MyUnboundField & *).
    I have tried the first suggestion you gave me, still there is no sign of the filter. The WHERE cmd doesn't work.

    As for the second suggestion I couldn't come along with it, I'm trying to understand it and make it work, but here comes my lack of experience/knowdledge.

    Perhaps I might send you the database file, so that you could take a look yourself.

    Cheers

  8. #8
    Join Date
    Nov 2009
    Posts
    5
    Ok so far, thanks to Sinndho we came across the problem and solved it. Posting this for future reference.

    The problem was all due to the fact Access saves these filters using their "full path". So that's why I was getting errors every time I tried to apply a filter. Basically filters were saved as [query1].namefield (query1 being form1 recordsource) and couldn't be applied since form2 had a different recordsource and couldnt find any [query1].namefield.

    So we (better say Sinndho) simply cut out the [query]. part and have those filters saved just as "relative paths". Everything works fine because every time I load a filter into form2 Access is able to find "namefield".

    Below is the code. And forgive me for this brutal way to explain things

    Very big thanks to Sinndho.

    Code:
    DoCmd.OpenForm "form2"
    Forms!Form2.Filter = Replace(Me.Filter, "[query1].", "")
    Forms!Form2.FilterOn = True
    Cheers

  9. #9
    Join Date
    Nov 2009
    Posts
    5
    Well looks like I'm not done yet.

    Filters apply just fine to form2. But a new problem has risen.

    Form1 was a form with (select query) query1 as recordsource. Query1 is basically used as a filter on table1, but it's not a filter. I thought that with all this code form2 would have inherit every item that met this query1 values AND those filters I was creating with Access UI.

    Right now I'm just exporting to form2 only those filters I created with the UI but not query1's filter.

    I've been looking around and still haven't found anything, and my poor knowdledge for sure doesn't help.

    What I'm looking for now is a vb code that basically adds to the code below here query1 filter too.

    Code:
    DoCmd.Openform "form2"
    forms!form2.Filter = Replace(Me.Filter, "[qryscarico].", "")
    forms!form2.FilterOn = True

    It would be something like "pass to form2 all items that met criteria from query1 and access ui filters".

Posting Permissions

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