Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    May 2003
    Location
    Kansas City, Kansas, USA
    Posts
    76
    Thanks
    1
    Thanked 0 Times in 0 Posts

    open form and show filtered records (2002)

    Hello, I have a database that holds client info and for each client they have several plans that we work on. I have my form set up to show the client and their info. for the plans we have to prepare Census work each year, so when you open the client form and click on the Census tab, I would like to have a datasheet that shows the census year and the plan name (since we could have two plans that had census work done in the same year). I have accomplished this how ever the next thing I would like to do is to double click on the plan name and open a form for the census for that specific plan and the plan year. This is where I am having problems. I have the following command on the double click for the PlanID: DoCmd.OpenForm "CensusLetterInfo Subform", acNormal, , "[PlanID]=[Forms]![subCensusList]![PlanID]", , acDialog THis will open the form I want (CensusLetterInfo Subform) but I can't seem to filter the form for the PlanID or the year that I selected.... Any help!!!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: open form and show filtered records (2002)

    Is subCensusList the main form, or is it a subform? If the latter, what is the name of the main form?

  3. #3
    Star Lounger
    Join Date
    May 2003
    Location
    Kansas City, Kansas, USA
    Posts
    76
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: open form and show filtered records (2002)

    yes subCensusList is a subform the Main form is "Edit Client"

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: open form and show filtered records (2002)

    Try this as WhereCondition argument for DoCmd.OpenForm:
    <code>
    "[PlanID]=[Forms]![Edit Client]![subCensusList]![PlanID]"
    </code>
    Note: subCensusList must be the name of the subform as a control on the main form. This is not necessarily the same as the name of the subform in the database window. Open the main form in design view and click once on the subform to see its name as a control in the Properties window.

  5. #5
    Star Lounger
    Join Date
    May 2003
    Location
    Kansas City, Kansas, USA
    Posts
    76
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: open form and show filtered records (2002)

    ok this releived the error message that i was getting, however it is not filtering the records. It opens the form and displays all the records. I need it to be filtered by not only the plan name by the year in which we select.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: open form and show filtered records (2002)

    Do you have a separate field for the year? Or should it be derived from a date field? And what is the name of the relevant field?
    Where does the selected year come from?

  7. #7
    Star Lounger
    Join Date
    May 2003
    Location
    Kansas City, Kansas, USA
    Posts
    76
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: open form and show filtered records (2002)

    Sorry, Let me give you some better info. The Censustbl has the data that I am trying to access. Yes CensusYr is a field where the admin person preparing the census enters the year. There is also a field called PlanID which references the census to Plantbl. I also have a field called ClientID that references the Clienttbl. Basically every year we will be entering new census data into the db.

    So in my main form (Edit Client) most everythng is driven by the clientID except for this(census), and one other tab. The subform (subCensusList) is linked by the ClientID so that each entry in the Censustbl will show up on the Edit CLient Form.

    Thank you for you help with this. I appriciate all of your time.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: open form and show filtered records (2002)

    Does this do what you want (again, it is the WhereCondition argument for DoCmd.OpenForm):
    <code>
    "[PlanID]=[Forms]![Edit Client]![subCensusList]![PlanID] And [CensusYr]=[Forms]![Edit Client]![subCensusList]![CensusYr]"</code>

  9. #9
    Star Lounger
    Join Date
    May 2003
    Location
    Kansas City, Kansas, USA
    Posts
    76
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: open form and show filtered records (2002)

    Perfect Thanks!!!

Posting Permissions

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