Solved

MS ACCESS many to many relationship and subform

Posted on 2013-06-21
27
819 Views
Last Modified: 2013-07-30
I have a many to many relationship where I need to populate the junction table using a subform. I've seen many examples of this however my situation is a bit different.  The table on the one side of my many to many needs to show details based on 3 fields in the other one side table.  See attached relationships.  
I need the subform to show in datasheet view all of the values on the one side of the many where the 3 fields match on the other one side (which will be selected via combo boxes on the main form).  
The subform also needs to allow me to enter in data for the datasheet records displayed.  
I cannot get this to work.
1) The recordsource for my subform is a query based on the junction table and the one side table (right side of the MANY : JUNCTIO : MANY) relationship.  The query, however, has to include a right outer join in order to display ALL records that match on the 3 fields selected).  I then have the criteria for the 3 fields set to Forms!MainForm!FieldName so it only displays the values that match.  
2) The link master and child fields are the PK from the one (left) side and the matching PK from the Junction table.  

Nothing is displaying for me when I make the selections from the combo box.
I have been struggling with this for about 3 weeks and am beside myself wondering how this is done or if there is a sample available I can review.

Thanks much.
0
Comment
Question by:kearley
  • 13
  • 8
  • 5
  • +1
27 Comments
 
LVL 57
ID: 39267070
There was no attachement on your question.

  As an intial comment, you can't directly model a M-M relationship with Access forms.

 Most you can do is a one to many and one side of a M to M with a main/subform combination.   Post your relationship diagram and we'll see what we can come up with.

Jim.
0
 

Author Comment

by:kearley
ID: 39267275
Yes, I have a main form with a subform.  I am tryhing to get data into the junction table by means of the subform.  It's complicated since the other side of the many is based on 3 combo boxes on the main form.. You'll see these three fields denoted with a purple line on the attached.
relationships.bmp
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39267688
Try to simplify the database leaving 2 tables with 3-4 fields in each table. Add few records.
Then explain the requirements. Upload the database.

Other question need answers but after seeing the model database.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 39267696
Which table is your main form based on?
Which table is the subform based on?
0
 
LVL 57
ID: 39267895
Well at first glance, it looks like the table design is not quite right, which is probably why your struggling with this.

  I say that for two reasons:

1. I don't see any relationships on tblCheckpointsToReview on WorkTypeID, ProductTypeID, nor LineOfBusinessID

2. I can't figure out how CheckPointID comes into play

 But to say that for sure, we need to understand what process it is your modeling with the tables.

  It looks like tblCheckpointsToReview is simply a list of check points that should occur for a given work type, product type, and line of business?

 If so, using a main/subform form what you want to do is:

1. Base the main form on tblQualityReview_Main
2. Base the sub-form on tblQualityReview_CheckPoints.
3. In the subform, you use a combo control to allow for choosing the records from tblCheckpointsToReview

  That combo's rowsource would be filtered by the WorkTypeID, LineOfBusinessID, and ProductID on the main form.

  In other words, create a query with those columns and for the criteria, just do:

=Forms![<myMainFormName>]![<myWorkTypeIDControlName>]

  This then filter the records that can be choosen based on the current main form record.

 The combo's controlsource would be CheckpointsReviewID/

Jim.
0
 
LVL 57
ID: 39267899
Note that if you want to present the user with the complete list of checkpoints to review and not let them pick and choose which ones they want to add,  then you will need to pre-fill the linking table with one record for each matching record in tblCheckpointsToReview, requery the subform, and them let them edit the existing records that you just created in the linking table.

Jim.
0
 

Author Comment

by:kearley
ID: 39272043
Hi Jim - Yes, your last comment is exactly what needs to be done.  The tblCheckpointsToReview is already populated with all of the possible combinations of checkpoints for each available WorkType, Product and LineOfBusiness along with the defauilt points assigned to each.  Therefore when the user selects the WorkType, Product and LineOfBusiness from the main form, ALL appropriate checkpoints need to list in the subform datasheet.  When this happens, I need to be able to set the field 'actualeligiblepts' to what the Def_EligiblePts are (I "had" this working) and the user also needs to be able to enter in the "earnedPoints" for each checkpoint.  I "had" this working as well.  My issue is that when I go to a NEW record in the main form and select the same WorkType, Product and LineofBusiness, I need to have a new set of Checkpoints display in the datasheet so I can enter in a new set of earned points, etc.    I then need to set the QualityMainID in the subform's recordsource (a query combining tblQualityReview)Checkpoints and tblCheckpointstoReview) to that of the Main form .

I will create a scaled down version of my database and upload it.  It will much easier for you to see my issue.  

THANKS!!
0
 

Author Comment

by:kearley
ID: 39272102
Test database attached
Test.accdb
0
 

Author Comment

by:kearley
ID: 39272308
Jim - I re-read your last comment.  I do need to present the user (1 person who is reviewing paperwork and assigning points based on the paperwork) the complete list of checkpoints to review based on the worktype, product and lineofbusiness they select.  I can not prefill the linking table as this needs to be dynamic.  Each piece of paperwork the user reviews will create a new entry in the form.  The subform datasheet of checkpoints will display based on the 3 fields selected in the main form.  Each time a new record is created in the Main form, the datasheet list of checkpoints needs to display for the combo boxes selected.
I hope my previous comment makes sense.  I just failed to mention in it that prepopulating isn't going to work.  
thanks for your help.  Perhaps my design is very much wrong. ?
0
 

Author Comment

by:kearley
ID: 39272893
tblCheckpointsToReview is prepopulated.  The linking table tblQualityReview_Checkpoints is not and can't be.  Hope this makes sense.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39273042
kearley,

Can you upload a copy of the database with just the required objects to demonstrate the issue.

http:#a39267688
0
 

Author Comment

by:kearley
ID: 39273240
See attached.
Test.accdb
0
 
LVL 57
ID: 39274546
<<tblCheckpointsToReview is prepopulated.  The linking table tblQualityReview_Checkpoints is not and can't be.  Hope this makes sense. >>

 All right, here's the question:

When the user creates a new main form record and they select something for all three combo's:

a. Should they have to complete the entire list from tblCheckpointsToReview based on that.

or

b. Should they be able to pick and choose which Checkpoints they want to review

  So if there are 50 checkpoints based on the Work Type, Product Type and Line of business, do they need to complete all 50, or can they only choose to do 10?

  and another question:

   If there are 50, do you want to allow them to complete less then 50, but know that some are still left to be done?


 I believe what you need to do is:

1. On a new main form record, have the subform disabled.
2. Once they choose from the three combo's, pre-fill tblQualityReview_CheckPoints with a record for each check in tblCheckpointsToReview, and enable the subform.
3. Then if they go to change any one of the three combos after the records are filled (either on the new record or on a future edit), they need to be issued a warning that new checkpoints will be created and any existing data will be lost.

tblQualityReview_CheckPoints is more then just a linking table, because there are additional facts in there (i.e. Comments).
 
Jim.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:kearley
ID: 39274797
Hi Jim,
Thank you for your help!  to answer your question, yes, if there are 50 checkopints, they do need to complete them all (i.e. give 'earned points') at some point.  If they don't at a given time, then the status just needs to be set to 'suspended' until they come back in and complete them. I will build a check for this.  

I will give your answer a try.  Again, thank you so much.
0
 
LVL 57
ID: 39274825
I should have added a couple of points with the idea of pre-filling:

1. Subform should only allow edits.  No additions or deletions would be required.

2. Combo boxes that have rowsources looking at tblCheckpointsToReview should be read only.   Only thing that they will need to edit in the subform is those fields in the linking table that are actual data.

3. On enabling/disabling the subform on a Edit vs a new main form record, you really don't need to do that.

  On a new mainform record, the subform will be blank.  As soon as you pre-fill the records and then requery the subform, the records will show and they can start editing.

4. Using the pre-fill approach, it will be easy to determine if for a given main form record if all the checkpoints are reviewed.  You'll easily be able to add totals at the bottom to reflect this.

Jim.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39276259
Sorry! I did not understand the problem.
I wanted some instructions to exhibit the issue.
Like:
Open form ... , you see ..., but I expect ....
If you enter ... , you see ...., what expected is ...
...
I expect this explanation will encourage other experts to contribute in the thread, and speed up the resolution of the issue.

Any way, I leave you with  JDettman.
0
 

Author Comment

by:kearley
ID: 39302247
Had to move on to soemthing else for a few days. Back looking at your answer now, JDettman.  Thanks...
0
 

Author Comment

by:kearley
ID: 39302502
Ok.  I am trying to prefill the subform AfterUpdate of the main field's 3rd combo box.  My subform recordsource is a query of tblCheckpointsToReview and tblQualityReview_Checkpoints with (include ALL records from tblQualityReview_Checkpoints' where joined fields are equal).  

Here is my code on the AfterUpdate event:
'Set Subform to RecordsetClone so can loop through datasheet records
    Set rs = Me.[frmcheckpointsToReview subform_Main].Form.RecordsetClone
   
    With rs
        If .RecordCount > 0 Then
            .MoveFirst
            Do Until .EOF
                .Edit
                !QualityMainID = Forms!frmQualityReview_Main!QualityId
                !ActualEligiblePts = !Def_EligiblePts
                .Update
                .MoveNext
            Loop
        End If
   End With

The Recordcount of the recordsource = 15, which is correct.  After it loops through one record, I get this:


'3201 You cannot add or change a record because a related record is required in table 'tblqualityReview_Main'.  

Even though the QualityMainID IS getting set correctly to the ID of the main form.  

Sorry, I'm stuck.
Thanks.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39302706
Sorry! I lost track with this thread.
I leave you with other experts.

If unresolved, and my help is required, then I expect clear steps of what to do to get the required output.
1- open database
2- open form x, you see ...
...
0
 

Author Comment

by:kearley
ID: 39302719
I was referring to JDettman's response.  sorry.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39302884
No problem and good luck!
0
 

Author Comment

by:kearley
ID: 39318567
Posted comment on 7/3.. looking for more assistance, if possible, from JDettman.
0
 
LVL 57
ID: 39344665
Sorry for the late response (have been on vacation).

Where are you at with this?

Jim.
0
 

Author Comment

by:kearley
ID: 39344780
No problem.  I've been on another project in the meantime.
The last comment I made above is still where I am at.  Here's the summary:  I have the main form/subform link master and link child fields set to  QualityID.  However, I'm setting the Quality ID to the Mainform Quality ID in the after update even of the ProductId combo box (that is the last field that needs to be filled in on the main form to determine the subform's datasheet records to display).   The code in the after update is this:  
   Set rs = Me.[frmcheckpointsToReview subform_Main].Form.RecordsetClone
   
    With rs
        If .RecordCount > 0 Then
            .MoveFirst
            Do Until .EOF
                .Edit
                !QualityMainID = Forms!frmQualityReview_Main!QualityId
                !ActualEligiblePts = !Def_EligiblePts
                .Update
                .MoveNext
            Loop
        End If
   End With

The issue is that I get a 3201 You cannot add or change a record because a related record is required in table 'tblQualityREview_Main' as soon as my code is run.   It's strange because when I manually step through my code, the !QualityMainID is being set properly to Forms!frmQualityReview_Main!QualityId.  
I don't understand why I would get that error when the code is creating a related record.  Do you know why this would happen?
Thanks.
0
 
LVL 57
ID: 39349111
Please see the attached DB.  

I re-did the record source for the subform.  Should have only been based on the linking table.

I corrected the fields on the subform.  

I then added code to the subform controls OnEnter event to fill the subform with the linking table with records based on tblCheckpointsToReview and the selections made in the main record.

 Sorry this wasn't a bit sooner; sometimes you have to winder if it's worth going on vacation<g>

Jim.
Test-Update.accdb
0
 

Author Comment

by:kearley
ID: 39364613
I am getting an "unrecognized database format" error when I try to open the attached sample db.  I am using version 2007.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 39364833
Sorry, loaded it with 2010.   Try this one.

Jim.
Test-Update-A2003.mdb
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now