Form SubForm..

[Guest]

I'm certain this has been answered somewhere but I couldn't locate the answer.

Three tables, one a junction table.

Table 1 tblMembers...
Table 2 tblPractice (Sessions...)
Table 3 tblJunction Table...

I'm having a brain lapse into how to connect the form and subform. The main
form is based on tblMembers, the subform is based on tblPractice and has the
practice sessions the member has attended. There are times I will do the
opposite (list the Practice session with the members that were present.)

Form:
Record Source *tblMembers & *tblPractice (???)
SubForm is tblPractice Linked by MemberID field (from tblMembers and
tblJunction)

1. I want the subform to display the practice sessions the member has
attended as I move through the records on the mainform. (Should be easy,
just not sure what sources to use of the three and/or query?)

2. I want to be able to add a practice session to the member from the
subForm (probably via a combo box in the subform datasheet). This is if the
member has attended a session that is in the tblPractice.

I would greatly appreciate any input.

Thanks....b

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ImplementingM2MRelationship.mdb" which illustrates 2 ways to
implement a many-to-many relationship in a form/subform, depending on the
circumstances.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Roger,
thanks for the great link. I'm getting closer. I'm using your example #2.

I'm getting a "Can't build a link between unbound forms" error. I
researched technet but still haven't grasped where I have two unbound forms?
Here's where I stand. (I appreciate any assistance!)

My mainform is based on tblMembers as stated below.
(I do have a combobox in the header unbound to a Query of Lastname and
Firstname from tblmembers so I can pulldown the lastname and select that
record.)

My subform's source object is based on a query that contains some fields
from the three tables listed below with links between them.

When I try to add MemberID field to the Child and Master links I get error
listed above.

This may not be enough information but maybe you have some ideas? I tried
to use your examples (which have helped ALOT!)

Thanks again your (and anyone else's time) that want to chime in!

 

[Roger Carlson]

The error seems to indicate that you don't have a Record Source for the
subform, so check that carefully. Also, the RecordSource for the subform
should be a Join of tblPractice and tblJunction. It shouldn't have
tblMembers in it at all. Be sure you include the foreign key from
tblPractice (that's the field in tblJunction that corresponds to the primary
key of tblPractic) in your query.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Thanks again!

When I try and add a record in my subform, which is not the action I really
want here), what I really want it to have it pull down for current records in
the Practice table, I get "You cannot add or change a record because a
related record is required in table 'tblMembers'.

The record Source for the subfomr (SourceObject?) is tblPracticeQuery which
contains fields from tblPractice and field PracticeID from tblMemPra (which
is the junction table between tblMemebers and tblPractice. PracticeID is the
PK in tblPractice and FK in tblMemPra.

Thoughts?

 

[Barry Moses]

I have tried to duplicate the Implementing M2M sample to the letter on the
subform but I am getting different results (I'm certain it's user error)

I have a subform based on a query of linked tables using
zfrmqryItemTransaction as a bases. When I use a ComboBox to select a record
(as in ITEM_NO in sample) all the fields do not display all the record
information. In your example you would also get the ITEM_DESCRIPTION to
display as well as PRICE, etc. from the other tables.

I expect to see several fields filled in but I only get a couple (including
the one in the combox box). If I add more fields to the column count I do
see the fields with the proper data in the combobox, but once I select the
record I want all the fields do not populate with the data.

Any idea on why i would not see all the fields populated?

Thanks...Getting better every day!!!!

 

[Roger Carlson]

Are the controls (text boxes) -- that don't display the information you
want -- bound to the appropriate fields in the query? That's the only thing
I can think of.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Roger Carlson]

Are the foreign keys in your junction table (tblMemPra) of Long Integer
datatype? Or are they Autonumbers? If the field in the junction table
(that is in the Child field property of the subform) is an autonumber, this
error would occur.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L