Display data in a subform

[Jeffrey Marks]

I have a many-to-many relationship on the database. For each member of
the database, they can attend many outings. As a result, there is a
tblAttendees that links the member ID in the member table to the
Outing ID of the outing table.

When the user brings up the Outing form, they can add members to
attend the outing (via a subform). The subform lists all of the
currently attending members (via a combo box - using query1) and
allows the user to add more by selecting another row in the subform
and picking a name from the combo box using query2.

There are 4 programs and 4 sets of members in this database. As a
result, there are 4 Outing forms, one per program. Historically, the
names of members are only allowed via query2 if the members are in
that forms' program (i.e. the form for program1 only shows members who
are in program1)

Recently the users have set up outings that involve 2 or more
programs, which works fine. However, when bringing up member names on
the outing forms, only the members for that program are displayed.
Blank combo boxes are displayed for members who have signed up via the
other Outing forms. If John, Sally and Jack are all program 1 and Adam
is program 2 for a particular outing. If the data is displayed on the
program 1 Outing form, John, Sally and Jack are displayed and a blank
box appears for Adam and vice versa on the outing form for program 2.

I'm sure that there's a way to allow that data to display, but I'm
just not sure what it is. Help would be appreciated. I suggested
having 1 form for all programs and was nearly lynched by the users, so
that's not a viable option at this point.

A small matter not related to display issue. When the names are re-
displayed on the subform after a refresh or coming back to the form,
the names are in alpha order, even though I can't find any indication
of sorting. I've checked the queries and the subform, but can't find
anything. Help would be appreciated.

Thanks

Jeff

 

[Dennis]

Jeffery,

I'm a little confused. I see where you have an tblAttendess and tblOutings.
You mention that you have four forms but they only see the members for that
program. Then later you say that you can register member in different
program, but then somehow combine them. Is that a new form or is it a
modification of the existing forms? It is a bit unclear.

I suspect that the issue resides in the SQL statement in the combo box's Row
source or the queries.

I'm going to guess that the SQL statement or queries filters out everyone
except for the members of that single outing. Since the SQL statement /
queries specifically excludes everyone except members in that outing, the
names of the members who were signed up in the other outing will be blank as
they are excluded.

I'm confused to how registered the member from two different outing

 

[Jeffrey Marks]

Dennis,

I think the business issue is that Outings can be for multiple
programs (1,2,3, etc), but members are only in 1 program. So the
members in program 1 still must be added for the Outing form for
program 1, members for program 2 must be added via the Outing form for
Program 2, etc.

Yes, I think that it is the SQL statements which exclude all members
except those in program 1 and they show up as blank. My question is,
is there a way to have them show up without opening up the query to
all members (meaning that the combo box now shows members in all 3
programs) or is there a way to show the blank line and perhaps lock
the combo box for that row, so that while they can't see the member
name, they can't change it either?

Thanks

jeff

 

[Dennis]

Jeff,

Let me think about this. I'll reply tomorrow. I need some sleep right now.

Could you please post the SQL statement that you are using for the query.
That might help.