Ok. I am creating an events db. I have tblEvents, tblAttendees; with
many to many relationship between the 2 tied through tblRegEvents. the
tblRegEvents has fields:RegEventsID, EventID, AttendeeID, Tuition. I
then have tblRegEventsPmts with fields: RegEventsID, Payment (a one to
many between tblRegEvents and tblRegEventsPmts).
I have my forms designed ok for Attendees and Events. Except that when
I try and track payments of each Attendees per event.
I was trying to do something like create a form with tblRegEvents.
That doesn't exaclty work the way I want it to. I was trying to show
the records for attendees while having a list of events available for
each attendee ( a listbox or something). Then a subform would have the
payments. Am I making this way too complicated? My problem comes when
trying to design the form to show only 1 attendee record. Also, in
filtering the listbox to show all the events(the way a subform would
do). If there are any thoughts on this, please let me know. I'm open
for a new approach as well as a solution to creating a form from my
Many to Many table. (It seems to me theoretically that should work,
but I know just enough about Access that if it starts to get
complicated, there is usually an easier way). I hope I have given
enough details and thanks for any tips.
-WhathaveIdone?

On 20/05/2011 16:18:54, "WhathaveIdone?" wrote:
Try to get you started

Firstly I presume that you are allowing the possibility of more than 1
payment (part payments) for each event. Your table structure looks fine,
possibly If I were doing it I woldn't bother with RegEventsID, and the
Payment would have a triple key of AttendeeID, EventID and PaymenDate (to
prevent a payment inadvertanly being entered twice) So a main form for the
attendees will have a subform "SubEvents", showing the events picked (have a
combo box on the subform to pick the possible events) LinkMasterField
AttendeeID LinkChildField AttendeeID Then you have second subform to show the
payments. The LinkMasterField on this subform AttendeeID,
Forms!Attendees!SubEvents.Form!EventID The LinkChildField will be AttendeeID,
EventID

Phil

On May 22, 3:35*am, "Phil" <p... (AT) stantonfamily (DOT) co.uk> wrote:
Phil,
First of all, yes, you are correct in that I do want to make available
more each Attendee to make multiple payments. This is great solution!
There is only one problem. It is possible for my Attendees to have
attended more than one event. Thats why I have the many to many
relationship set up between them.

This is the way I understood your response: What you're saying will
work if the second subform (let's call it RegPmts, and it is based on
tblRegEventsPmts) is a subform of subform: SubEvents. If I did that, I
would want to filter SubEvents.EventID to show only the Events that
the Attendee is attached to.(Probably by means of a list box)
Whichever event is selected in the listbox, might then be able to be
tied to the sub-subform: RegPmts. Thats where I run into problems.

Basically, what I'm saying is that, I tried the subform: SubEvents
with another subform: RegPmts which is linked to both the main form
and the other subform as you have indicated previously. this shows all
the events in SubEvents.EventID combobox.

Am I still missing something?

-WhathaveIdone?

On 23/05/2011 17:31:37, "WhathaveIdone?" wrote:
No.

Both are subforms of the Attendee form.
What should happen as you click on each record of the SubEvents subform, the
appropriate payments for that event only should show up on the RegPmts
subform. You may need an event proceedure on the OnCurrent of the SubEvents
subform which says Me.Parent!RegPmts.Requery
Never got to the bottom of it, but sometimes it seems to work without the
OnCurrent Event, sometimes it seems to need it.

The Me.Parent refers back to the main Attendee for.

Phil

On May 23, 11:04*am, "Phil" <p... (AT) stantonfamily (DOT) co.uk> wrote:
OK. So I was able to make it a LOT easier. What I did was I deleted my
first subform:SubEvents. I then created a listbox with the
tblRegEvents. I filtered the listbox by only allowing events where the
AttendeeID matched. Then I based my 2nd subform: SubPmts on both the
main form data as well as the data selected in the listbox. I added a
requery to the current event of the main form to requery the listbox,
then requery the subform. Also, the subform requeries On Click of the
listbox.

Thanks, Phil. This isn't exactly what you suggested, but I couldn't
have gotten it without your help.

-WhathaveIdone?