What am I doing wrong? (SubForm Source?)

[Barry Moses]

Care to share some thougts? I would be most appreciative!

I am having difficulty understanding how the following sources effect the
outcome below.
I will try to minimize this for ease of explanation and a possible answer.
(There are many more fields and tables) I've tried to use the Classroom
Management database from MS but I'm still unable to chose the Practice and
have it populate the row.

Three Tables

Table A
tblMembers
MemberID
LastName

Table B
tblPractice
PracticeTitle
PracticeLocation

Table C
tblMemPra (Linkage Table)
MemberID
PracticeTitle

I have a form for searching and editing Members and what Practices they have
attended.

Main Form
Record Source - tblMembers
I have an unbound combobox that pulls down the members lastname to populate
the form. This works.


SubForm (Datasheet)
Source Object = tblMemPra Sub Form ..must have been autogenerated
Linked Fields = MemberID
Record Source = SELECT tblMemPra.MemberID, tblPractice.PracticeTitle,
tblPractice.PracticeLocation, tblPractice.PrNotes, tblPractice.PrDate
FROM tblPractice INNER JOIN tblMemPra ON tblPractice.PracticeTitle =
tblMemPra.PracticeTitle;

Combobox on SubForm =
Control Source = Practice Title
Row Source = SELECT tblPractice.PracticeTitle, tblPractice.PracticeLocation,
tblPractice.PrNotes, tblPractice.PrDate
FROM tblPractice INNER JOIN tblMemPra ON tblPractice.PracticeTitle =
tblMemPra.PracticeTitle;


Expected outcome:
Once I select a member from the main form I want to be able to go to the
subform and:
1. Have any Practice sessions the member is already tied to, display.
2. Be able to select a Practice session from the SubForm combobox and it be
tied to the member (ie, he now has a record that includes this session.)


I can't seem to get the datasheet to display the fields of a record.
Depending on what my source is, sometimes the fields display in the
datasheet without the information in the combobox and sometimes I get the
combobox information but all the other fields are blank. I'm sure it has to
do with my sources/relatiosnhips/quesrys??

Where am I making what is probably an elementary mistake in my
sources/linkage/relationship?

Thanks so, so much!!

Barry

 

[BruceM]

I assume you mean that you are using an unbound combo box on the main form
to select an existing record.
You say that the subform must have been autogenerated. Does that you mean
that you used the form wizard to create the main form, and the subform
showed up when you did so?
Have you created a relationship between the primary key field in tblMembers
and in tblPractice (the One side of the one-to-many relationship) and the
corresponding fields in tblMemPra (the Many side of the one-to-many)? All
three tables will be involved in the relationship.
I'm not clear on what Practice is, but it sounds like you are talking about
a practice session of some sort. If so, how is this record created? If you
are selecting it from a combo box, you would typically create the record
first, then select it from the combo box. The combo box Not In List event
could open a form for entering a practice session form, or something like
that.
However, that may not be your best choice. In a typical many-to-many setup
(which is what you have) one of the tables is relatively static (tblMembers,
I would expect), and the main form is based on the "active" table. I don't
know how it works with practice sessions (if that's what they are), but I
would guess that a practice session occurs, and when it does you list the
people who participated. In other words, Practice records are created more
often than Member records. In that case you could have a main form based on
tblPractice, and a subform based on tblMemPra, as it is now, except that the
combo box would be used to select a member's name from a row source based on
tblMembers. You can still view the information for each member. One way
would be a report based on tblMembers, grouped by MemberID.

 

[Guest]

Bruce....first of all thank you so much for taking your time to respond. To
fill in the blanks....

Yes, the mainform has an unbound combo box.

As far as the subform autogeneration. Probably a bad choice of words. I
created the mainform and used the subform wizard to create the subform.

Relationships.... ( I wonder if this is my issue?)
tblMembers to tblMemPra one to many on MemberID.
tblPractice to tblMemPra one to many on PracticeTitle.
All referential integrity options selected (not certian if this is good)

Correct...Practice is a practice session.
I have an Add Practice form to create Practices. This just uses the
tblPractice table...

Members would eventually be the more 'static' table, but new members are
flowing in at this point.

The normal flow would be to add a Practice session in it's table(I also have
training sessions in another table). At any time I would bring up a Member
and see what Practice, Training (Tab subforms) etc, this member has been to
and also add them to one's they haven't been added to if necessary. This is
why I want the Member Form and Practice subform to display the Practice
sessions the member has attended and then have the ability to use a combo box
to pulldown sessions and add this to the Member.

An example would be a student and adding them to various classes.

You suggest MainForm based on tblPractice, subform based on tblMemPra and
the combobox ouwl dbe used to select the member and the row source would be
based on tblMembers. I will try this option on a Practice Session form as
this sounds like it will work if I bring up a Practice session and want to
add Members.

Do you see a flaw in my original design to have the Member form brought up
and have a subform with Practice sessions as I originally was looking for?
I'm trying to figure out why it's not working.

Thanks a million again!!!!!

 

[BruceM]

Bruce....first of all thank you so much for taking your time to respond.
To
fill in the blanks....

Yes, the mainform has an unbound combo box.

As far as the subform autogeneration. Probably a bad choice of words. I
created the mainform and used the subform wizard to create the subform.

Relationships.... ( I wonder if this is my issue?)
tblMembers to tblMemPra one to many on MemberID.
tblPractice to tblMemPra one to many on PracticeTitle.
All referential integrity options selected (not certain if this is good)

Sounds good.

Correct...Practice is a practice session.
I have an Add Practice form to create Practices. This just uses the
tblPractice table...

Members would eventually be the more 'static' table, but new members are
flowing in at this point.

The normal flow would be to add a Practice session in it's table(I also
have
training sessions in another table). At any time I would bring up a
Member
and see what Practice, Training (Tab subforms) etc, this member has been
to
and also add them to one's they haven't been added to if necessary. This
is
why I want the Member Form and Practice subform to display the Practice
sessions the member has attended and then have the ability to use a combo
box
to pulldown sessions and add this to the Member.

An example would be a student and adding them to various classes.

You suggest MainForm based on tblPractice, subform based on tblMemPra and
the combobox ouwl dbe used to select the member and the row source would
be
based on tblMembers. I will try this option on a Practice Session form as
this sounds like it will work if I bring up a Practice session and want to
add Members.

I think you may get some help by looking up Dlookup in Help. I can't really
advise you on this. I haven't had to use it, and am not all that familiar
with the function.
In similar situations where I need to see fields related to the combo box
selection (Location, Notes, etc.) I use the combo box (I will call it
cboPractice) After Update event to populate unbound text boxes:
Me.txtLocation = Me.cboPractice.Column(1) -- With your combo box row source,
this will put PracticeLocation into the unbound text box txtLocation. Note
that the columns are numberd from (0), so (1) is the second column in the
combo box row source query.
I will be unable to return to this thread for a day or two, so if you have
immediate questions I have not answered it may be best to start a new
thread.