Question on Combo Box to Limit Report Parameters

[Guest]

I am having trouble using a combo box to limit the parameters of my
query/report. I have a list of volunteers (2 fields, First Name and Last
Name) based on what program(s) they volunteer for (3 fields, Program Type,
Program Type 2, and Program Type 3). The programs use the same table for the
choices (called program type table, with Program Type and programtypeID as
the fields). My report is to determine which people volunteer for a specific
program, no matter which Program Type listing it is under.

The codes for the parameter entry form are as follows (it has 3 items: a
combo box, an OK button and a CANCEL button):
Program ComboBox has the properties of
Row Source: Table/Query
Row Source: SELECT [program type table].programtypeID, [program
type table].[Program Type] FROM [program type table] ORDER BY
[Program Type];

OK btn has the following event procedure on Click:
Private Sub OK_btn_Click()
Me.Visible = False
Rem docmd.openquery "volunteers by program", acViewNormal, acEdit
DoCmd.OpenReport "Volunteers by Program", acViewPreview
DoCmd.close acForm, "Enter Program Form"
End Sub

Cancel btn has the following event procedure on Click:
Private Sub Cancel_btn_Click()
DoCmd.close 'close form
End Sub

The setup for the Volunteers by Program query is as follows, with 6 fields:
Program Type, table Volunteers, Criteria: [Forms]![Enter Program
Form]![Program ComboBox]
Program Type 2, table Volunteers, Criteria or: [Forms]![Enter Program
Form]![Program ComboBox]
Program Type 3, table Volunteers, Criteria or: [Forms]![Enter Program
Form]![Program ComboBox]
First Name, table Volunteers
Last Name, table Volunteers
Status, table Volunteers

If I run the Volunteers by Program report directly a message comes up with
“Forms!Enter Program Form!Program ComboBox†and I have to type in one of the
choices. This returns the correct information.

If I run the Enter Program Form, I have a combo box with the list of choices
and when I highlight the choice and click on the OK button, I get the report
to show up but with no data.

Why is the Enter Program Form not returning any data? Both the query and the
report if run directly return the correct data, but the entry form does not.
I have used entry forms before and have never had a problem with them.

 

[Graham Mandeno]

Hi Cynthia

The real problem here is in the design of your tables. You should never
have multiple fields to accommodate repeated data of the same type, as you
have with ProgramType1, ProgramType2, ProgramType3. This makes it very
difficult (as you have discovered!) to query who has volunteered for a
particular program, and almost impossible to query who has volunteered for a
selected set of programs. And, what do you do when someone volunteers for
FOUR programs?

You need to have three tables:

Volunteers:
VolunteerID (a unique primary key - I suggest an AutoNumber)
FirstName
LastName
other details about this *person* - gender, date of birth, contact
details, etc

Programs:
ProgramID (primary key, as above)
ProgramName
other details about this program - description, duration, type, etc

And a third table to join them together...

Participation:
VolunteerID
ProgramID
and perhaps some other fields pertaining to *this instance of a
volunteer's participation in this program*

Now, you see, instead of having three *fields* in the Volunteers record for
the three programs, you have up to three (or more if necessary) *records* in
the Participation table.

You should use the Relationships window to create relationships between the
two VolunteerID fields and the two ProgramID fields.

You can then create a query which joins the three tables together, and base
your report on that. You will get one row in the query for each
participation record. To list all the programs for a particular volunteer,
you filter on VolunteerID. To list all the volunteers involved in a
particular program, you filter on ProgramID.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am having trouble using a combo box to limit the parameters of my
query/report. I have a list of volunteers (2 fields, First Name and Last
Name) based on what program(s) they volunteer for (3 fields, Program Type,
Program Type 2, and Program Type 3). The programs use the same table for
the
choices (called program type table, with Program Type and programtypeID as
the fields). My report is to determine which people volunteer for a
specific
program, no matter which Program Type listing it is under.

The codes for the parameter entry form are as follows (it has 3 items: a
combo box, an OK button and a CANCEL button):
Program ComboBox has the properties of
Row Source: Table/Query
Row Source: SELECT [program type table].programtypeID, [program
type table].[Program Type] FROM [program type table] ORDER BY
[Program Type];

OK btn has the following event procedure on Click:
Private Sub OK_btn_Click()
Me.Visible = False
Rem docmd.openquery "volunteers by program", acViewNormal, acEdit
DoCmd.OpenReport "Volunteers by Program", acViewPreview
DoCmd.close acForm, "Enter Program Form"
End Sub

Cancel btn has the following event procedure on Click:
Private Sub Cancel_btn_Click()
DoCmd.close 'close form
End Sub

The setup for the Volunteers by Program query is as follows, with 6
fields:
Program Type, table Volunteers, Criteria: [Forms]![Enter Program
Form]![Program ComboBox]
Program Type 2, table Volunteers, Criteria or: [Forms]![Enter Program
Form]![Program ComboBox]
Program Type 3, table Volunteers, Criteria or: [Forms]![Enter Program
Form]![Program ComboBox]
First Name, table Volunteers
Last Name, table Volunteers
Status, table Volunteers

If I run the Volunteers by Program report directly a message comes up with
"Forms!Enter Program Form!Program ComboBox" and I have to type in one of
the
choices. This returns the correct information.

If I run the Enter Program Form, I have a combo box with the list of
choices
and when I highlight the choice and click on the OK button, I get the
report
to show up but with no data.

Why is the Enter Program Form not returning any data? Both the query and
the
report if run directly return the correct data, but the entry form does
not.
I have used entry forms before and have never had a problem with them.