Subforms and Many To Many Relationships; data not retained

[mongo]

I have created a form with two subforms. The context of this is a litigation database. The main form is for testimony. Among other things, it includes a field for a question and an answer. Any given piece of testimony may relate to one or more documents, and any document may be referenced in one or more pieces of testimony. So have two subforms, one for documents produced by the Plaintiff, and one for documents produced by the Defendant. It worked out easier that way because I originally created indices of those documents in separate Excel tables which I then imported to Access.

So the relationship for both document tables relative to the testimony table is many to many, which is established using two junction tables.

I also have a combo box in each subform where I can autofill fields based on selection of the document number.

My problem is that while the combo-box works great, once I select a document in the subform, that selection is carried over into the next entry of the master form, but also the selection is not retained in any of the master forms. So I can browse the document listing from any entry in the master form, but I can't get the document selection in the subform to stick and create a lasting relationship to a given record in the master form.

Is my problem that it is just impossible to have a subform based on a many to many relationship, or is it in the parent child relationship of the form and subform, or is it in the properties of the subform?

 

[mongo]

Just give me a sign. Any kind of sign.

 

[jdraw]

Do you have a data model (tables and relationships diagram)?

 

[CJ_London]

It sounds like when you update the subform, you are not updating the field referenced in the subform control as the link child field. If this is the case you can do one of the following:

in the before update event for the subform put the following:

myChildField=Parent.myParentField

Alternatively in the main form oncurrent event put the following

mySubFormCtrl.Form.myChildField.defaultvalue=myParentField

 

[mongo]

JDraw,

I suppose I can post a snapshot of the Relationships screen, is that what you need?

 

[mongo]

CJ_London:

I am assuming that when you say things like myChildTable and ParentTable etc., that I would not type those things verbatim, but that they are essentially variables and that I need to replace them with the names of my actual fields and tables, correct? Can you elucidate with more instruction on the syntax please? Thank you.

 

[CJ_London]

Certainly - you are correct in your assumption. For the future if you provide the names of fields and controls that you are using, I would use them!

myChildField=Parent.myParentField

Alternatively in the main form oncurrent event put the following

mySubFormCtrl.Form.myChildField.defaultvalue=myParentField

Assuming your tables are normalised the following would apply.

myChildField is your name for the control in your subform which is the relationship link back to the table in the main form e.g. InvoiceHeaderFK

Parent is a reserved word and is the object which is the main form. Just for interest, Parent.Parent would refer two forms up if you had them - you can go to five levels)

myParentField is the primary key for the main form recordsource - e.g. InvoiceHeaderPK

mySubFormCtrl is the name of the subform control (not the subform itself)

.Form is a reserved word to identify you want to 'look at' the form which is the sourceobject of the control

defaultvalue is a property for most controls

This link may help - see posts #1 & #2

http://www.access-programmers.co.uk/forums/showthread.php?t=248147

 

[Pat Hartman]

Check the master/child links. If they are set correctly, Access will automatically populate the foreign key field in the subform to maintain the connection.

 

[mongo]

I did what you said but I still have the same problem.

The table corresponding to my subtable is not normalised, just simply imported from Excel.

My master form is called TestimonyForm. My subform is called HansenDocs.

The combo box on the Hansen Docs subform which populates all the other fields in the subform is called cboHansenDocID.

The primary key of the TestimonyForm is called TestimonyID.

In Design View, after invoking the cboHansenDocID properties sheet, and clicking on the Event tab, I selected Before Update, and entered the following:

cboHansenDocID=Parent.TestimonyID

I then went back to regular view and selected a record in the cboHansenDocID combo box for the first record in the main form. It selected the record properly, but then when I advanced to the second record in the main form, the record selected in the subform in the first record of the main form is still carried over to the second record of the main form, and to all subsequent records of the main form.

 

[CJ_London]

who? Pat or me?

If me, it doesn't look like you have done what I said

 

[mongo]

CJ, yes, I thought I was doing what you said to do. What did I do wrong, or not do?

 

[CJ_London]

I said:

in the before update event for the subform put the following:

myChildField=Parent.myParentField

Alternatively in the main form oncurrent event put the following

mySubFormCtrl.Form.myChildField.defaultvalue=myPar entField

you said

In Design View, after invoking the cboHansenDocID properties sheet, and clicking on the Event tab, I selected Before Update, and entered the following:

cboHansenDocID=Parent.TestimonyID

 

[mongo]

CJ,

At first I didn't see the difference between what you said and what I did. Then I realized that I needed to be making the entry to the property sheet for the subform generally, and not the property sheet for the combo box within the subform specifically. So I deleted the entry in the properties sheet for the combo box and re-entered it in the properties sheet for the subform. But I still get the same problem. Selecting a record in the combo box of the subform in record 2 of the main form changes the record selected in the combo box of the subform of record 1 of the main form, etc.

 

[CJ_London]

hmm, I think I have misunderstood how you have things set up at the moment - but now not sure how they are set up

Is there any chance you can upload a sample db to illustrate the problem, or at least screenshots of the forms, tables and relationships