Query by form (2000)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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:

0	1	2	3	4	5	6
GroupID	GroupName	Between	And	GreaterThan	LessThan	Equals

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

Reply | Quote

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

Reply | Quote