Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query by form (2000)

    I have a database that I am in the final stages of creating for adminstering a church's membership. In the testing phases, I am seeing an alarming proliferation of reports for mailing labels which I want to eliminate by having all labels generated through a query by form procedure. That's the easy part.

    My question is this: there are two types of criteria for determining membership in groups 1) direct assignment (committee members, shut-ins and other groups where membership is voluntary) and 2) membership based on age (Sunday school classes, seniors groups) which is determined based on an age calculating field in a query.

    Is there a straightforward way to deal with these seemingly completely different types of criteria on one form so that all labels are generated through one dialogue? I have thought of doing it in VBA with a series of select case statements, but I would like to set up something that is self-updating (i.e. when a new committee or age-related grouping is created, someone else besides me has a hope of adding them into the query merely by creating the definition for the group.) The select case idea seems to preclude this idea.

    Or is the operator-friendly solution to have two reports: volunteer groups and age-related membership?

    Peter N

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

    Re: Query by form (2000)

    You can pass a filter (where-condition) to a report when you open it, so it should be possible to use only one report. You would have to construct the appropriate where-condition in the On Click event procedure of a command button. The form of the where-condition would be different for the two kinds of groups.

    The instructions to open a report look like this:

    Dim strWhere As String
    strWhere = ... ' assemble where-condition here
    DoCmd.OpenReport "rptMailingLabels", acViewPreview, , strWhere

  3. #3
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query by form (2000)

    Could you expand on this a little further, please?

    I understand the concepts as far as they go, it is the implementation. Do I use an unbound form then? How do I pass the conditions to the command button? My original thinking in the filter by form was to use a combo box that would contain the possibilities and fill the appropriate parameters into the query which would be of the [Forms]![frmLabelSelect]![txtGroupMemb] type. I have implemented this sort of thing with no problems in the past.

    Do I just put two different versions of this on the same form and program the buttons to react to whichever dropdown list was activated?

    Peter

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

    Re: Query by form (2000)

    How to implement it depends on the way you have organized your data. You wrote that you have two categories:

    1. Direct assignment. How is membership assigned? Do you have a "Groups" table and a "GroupMembership" table to implement a many-to-many relationship between Groups and Persons? Or do you have a field or fields in the main (Persons) table to indicate membership of groups? Or something else?

    2. Age-related groups. Do you have a table listing the groups and their age criteria, or do you have another way of determining membership?

  5. #5
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query by form (2000)

    Membership groups are by direct assignment (currently some are by check box, a result of additions that came up through testing, but I will reassign them to conform with similar data). There is a Groups table that has a many to many join with the Individuals table.

    Age-related groups have definition fields "Between", "And", "Greater Than", "Less than" and "equals". (I did this with five separate fields as I couldn't figure out how to get the calculated "age" field to accept text such as "Between 6 And 10" as a parameter from the form that feeds parameters to the query but it was quite happy dealing with "Between [Forms]![frmParam]![[Between] And [Forms]![frmParam]![And]". I think I then used some IIf or Is Null statements to deal with all the possibilities. I don't have the exact information handy at home.

    The query looks at the various fields and assigns people based on whether the meet the criteria for a group. This gives the flexibility of sub-groups within a larger group: e.g. children in the Grade 1 Sunday School (equals 6 years old) class are by definition Sunday School students as well (Between 3 and 14 years old).

    Peter

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

    Re: Query by form (2000)

    I would create an unbound form with a tab control with two pages, one for membership groups and one for age-related groups.

    On the tab page for membership groups, there is a list box based on the Groups table, with two columns: the Group ID or Group Number (I assume your table has a field like that), and the Group Name. The first column can be hidden (column width 0) unless people like to refer to a group by its number. The tab page also contains a command button to display the mailing label report. The code for the On Click event of the command button could look like this (with the appropriate names substituted):

    Private Sub cmdMembershipGroupLabels_Click()
    Dim strWhere As String

    strWhere = "MemberID In (SELECT MemberID FROM tblGroupMembership " & _
    "WHERE GroupID = " & Me.[lstGroups] & ")"

    DoCmd.OpenReport "rptMailingLabels", acViewPreview, , strWhere
    End Sub

    On the tab page for age-related groups, there is a list box based on the table listing the age-related groups. In this case, include all fields from this table in the Row Source, and set the column widths for the five criteria fields to 0 to hide them. There is also a command button. The On Click code for the command button could be something like the following - but I'm less sure about this, since I dont know how your use the criteria fields. For illustration purposes, I have assumed that the columns of the list box are as follows:

    <table border=1><td>0</td><td>1</td><td>2</td><td>3</td><td>4</td><td>5</td><td>6</td><td>GroupID</td><td>GroupName</td><td>Between</td><td>And</td><td>GreaterThan</td><td>LessThan</td><td>Equals</td></table>
    Private Sub cmdAgeGroupLabels_Click()
    Dim strWhere As String

    If Me.[lstAgeGroups].Column(2) <> "" Then
    strWhere = "Age Between " & Me.[lstAgeGroups].Column(2) & _
    " And " & Me.[lstAgeGroups].Column(3)
    ElseIf Me.[lstAgeGroups].Column(4) <> "" Then
    strWhere = "Age >" & Me.[lstAgeGroups].Column(4)
    ElseIf Me.[lstAgeGroups].Column(5) <> "" Then
    strWhere = "Age <" & Me.[lstAgeGroups].Column(5)
    ElseIf Me.[lstAgeGroups].Column(6) <> "" Then
    strWhere = "Age =" & Me.[lstAgeGroups].Column(6)
    End If

    DoCmd.OpenReport "rptMailingLabels", acViewPreview, , strWhere
    End Sub

  7. #7
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query by form (2000)

    That gives me the additional info I needed. The form design was more or less what I was thinking, I just needed to confirm it. The code on the other hand would have taken me a while to come up with.

    I will post back when I have had a chance to try it out and let you know how it goes. Again, thanks for the help.

    Peter

Posting Permissions

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