Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Restrict values of subform via VB (2003)

    Hello,

    well it seems like this is one of my last questions here, database looks like it can be used by others now.

    Ok my problem:
    I'm trying to limit records on a form which I want to open, where a certain checkmark on a subform is set. In SQL it looks like this:

    SELECT tblSchools.*, joinSchoolsParticipation.Participation
    FROM joinSchoolsParticipation INNER JOIN tblSchools ON joinSchoolsParticipation.ID = tblSchools.ID
    WHERE (((joinSchoolsParticipation.Participation)=Yes));

    Now all I need is how the stLinkCriteria from DoCmd.OpenForm "frmyaddayadda", , , stLinkCriteria looks like. I tried messing around with the SQL statement and push it into that stLinkCriteria variable, but... well that's why I'm here.

    Happy day all dwellers
    Dennis

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

    Re: Restrict values of subform via VB (2003)

    Let's try to get this clear. From your description, the situation seems like this to me:

    - You have a form A from which you want to open form B.
    - Form B has a subform C.
    - You want to restrict the records displayed in subform C.

    You can use DoCmd.OpenForm to restrict the records displayed in form B, but you can NOT use it to restrict the records displayed in subform C. Which records are displayed in subform C is controlled by the record source of subform C and by the link between form B and subform C.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restrict values of subform via VB (2003)

    You got it straight, but one thing:

    Why does it work then with the above stated SQL code as record-source? I mean I don't really want to limit the records displayed in subform C, I only want to show all participating schools (the checkmark) - that table is a lookup table for the regular schooltable, that's why the INNER JOIN connects join...ID with tblSchools.ID

    And you're saying that I cannot create a VB statement what that SQL code does?

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

    Re: Restrict values of subform via VB (2003)

    If you set the Record Source of the main form to this SQL statement:

    SELECT tblSchools.*, joinSchoolsParticipation.Participation FROM joinSchoolsParticipation INNER JOIN tblSchools ON joinSchoolsParticipation.ID = tblSchools.ID

    or, preferably, to the name of a stored query with this SQL, the form will display all schools that have a corresponding record in joinSchoolsParticipation, whether Participation is True or False. You can then open the form from another form with a Where condition that selects only those schools for which Participation is True:

    DoCmd.OpenForm FormName:="frmSomething", WhereCondition:="Participation = True"

  5. #5
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restrict values of subform via VB (2003)

    That was it Hans, thanks again to you!

    One followup: Can I just set a standard source for a form in the onLoad event or something? Because my problem now is that the old selection stays... meaning that I have a button "Show all records" and one for "Show only Participating". Now when clicked on "only participating" the "all records" button returns only the participating because it is still in the source - or do I have to make code for every button now?

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

    Re: Restrict values of subform via VB (2003)

    Sorry, I don't understand. Where is this "old selection" that stays?

  7. #7
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restrict values of subform via VB (2003)

    sorry [img]/forums/images/smilies/wink.gif[/img]

    Ok. I click on show only Participating schools, the new form opens, 24 for records returned. Form close.
    I click on show all schools (just opening the form again without arguments), 24 records returned (the same as before)
    of course I want all records returned when clicking on the all records button!

    So my question is, do I have to set code for every all-records button I have for every form, or can I just set standard code for every form? (with code I mean the source of course)
    I hope I made it clearer to you.

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

    Re: Restrict values of subform via VB (2003)

    What happens if you open the form directly from the database window?

  9. #9
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restrict values of subform via VB (2003)

    Ok I know where this problem comes from:

    the joinSchools... table is just a lookup table as I said, meaning that only certain schools are in there and NOT all (just the 24 participating of course). Now when I set "SELECT tblSchools.*, joinSchoolsParticipation.Participation FROM joinSchoolsParticipation INNER JOIN tblSchools ON joinSchoolsParticipation.ID = tblSchools.ID" as record source, only those 24 records get returned.

    I just need some way to get all records returned and not just the 24. (of course upon request via "Participation=True" only those records)

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

    Re: Restrict values of subform via VB (2003)

    What happens if you change INNER JOIN to RIGHT JOIN? If the records in the form can still be edited, it will probably do what you want.

  11. #11
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restrict values of subform via VB (2003)

    Lot's to learn about access,thanks Hand you're a genius

Posting Permissions

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