Link to home
Start Free TrialLog in
Avatar of beatified
beatifiedFlag for United States of America

asked on

Many to Many From without subform

I am creating a DB for an Apartment complex.
I am working on creating a New Rental series of Forms.
Every example I have seen of Many to Many relationships are with subforms.
I don't want to use subforms in this case.

Every time I try to create the Many to Many record with my joining table it works the first time but fails everytime after by recalling the record I created the first time instead of creating a new Many to Many record.

I have posted the DB Its probably just easier to see for yourself than for me to explain it.

If you run the only link on the switchboard it will work the first time but fail if you try again the second time.

Thanks for the help
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Can you give the steps needed to recreate your issue? I can add several rentals, and it seems to work each time.

What is your Many-to-Many relationship? Looking at your Relationship diagram, I don't see it.

Also - what's wrong with Subforms to represent that type of relationship? Users typically like to see M-2-M (or 1-to-M) data presented in that fashion, so I'm curious why you don't want to show it in that manner.
I agree with Scott.  Subforms are the way to go with many-many relationships.  See my sample database (attached).  Here is one of the forms:
User generated
Every example I have seen of Many to Many relationships are with subforms.
There's a reason for that. This is a hierarchical relationship and it is ever so much easier to work with when you use  a subform for the child records.

One problem with your app is that you are "pushing" data from one form to another.  This causes several problems.
1. It only works for a single record.  If the user presses the next button to navigate to a new record, the necessary fields don't get populated.
2. You are dirtying the new form before the user does.  That means that if he just closes the form because he pressed the button by accident or changed his mind, you are left with an incomplete record.  If you have properly established RI and required fields, the user will get error messages and he won't understand why.  HE didn't create an incomplete record, YOU did.

Using the subform solves a lot of problems.  However, if you want to persist with using popup forms, you need better code control and the "calling" form must remain open although it can be hidden to avoid confusion.  That way, you would put the necessary code topopulate the foreign key field into the popup form's BeforeInsert event.  That way the code only runs after the user has typed at least one character in the form and it also runs for EVERY new record so you don't have to prevent the user from adding additional records.

Here's an example of a ManyToMany relationship that shows correctly working subforms.  The app shows how to add the junction records from either side of the relationship.
Avatar of beatified


First of all. Thanks so much for all the input.
@Scott The issue is when creating a new Tenant. So every thing works the first time around but when I go to add a new tenant to a second rental it populates the Tenant info from the record I created for the first rental. And the M2M relationship is between tblLease and tblTenant and is joined by tblLeaseTenant.

@Helen I actually saw your example before posting this question elsewhere on EE so honestly I'm having issues with M2M relationships anyway. I think I figured it out but I'm not totally sure. It seems that when I enter the first Tenant the M2M relationship is created correctly and its not until I try to create the second M2M relationship that it seems to go wrong but I have a pretty good feeling that this is an issue with my form not the M2M relationship.

@Pat First of all let me say thank you because I am a newb and don't want to create future issues with the DB by me setting it up improperly to begin with. But let me ask you this... Would it be good practice to keep the first 2 forms frmNewRental and frmNewLease as is and only use a subform on frmNewTenant as it is the only M2M relationship?

I will try to explain my reason for not wanting subforms and you can let me know if its a good enough reason or if there is another way to achieve the same results.

1. This DB is being created for someone that has very bad eyesight. (very bad he only has one eye and when looking at a 40in monitor he at times has to be about 12 inches away from the screen.
     a. From my experience subforms are almost always in datasheet view which is too small and there is in my opinion too much data to be scrolling to the right for ever.
2. There is no need to navigate from record to record in this particular case. I simply want to add one tenant then add another an so on until finished.
     a. When I have a need to edit a particular tenant in a rental for the sake of ease of use and not getting confused I want a dropdown list to appear and have a tenant be selected then use a button to forward to a new form to allow editing of only that record. Too many records on the screen at once in my opinion allows for confusion and errors.

On the other hand I do see an issue if someone hits the wrong button and cant get back to say add another tenant because they have closed the form and ended back at the switchboard.

Maybe a subform would be possible at a point but I'm not sure it would be good in the first couple of steps.

Here are the tables that are involved in this particular case.

And here are the types of relationships.
One Apartment can have many Rentals (When a Rental has an EndDate then the Rental is no longer considered active and it should allow for a new Rental to exist for that particular Apartment.)
One Rental can have Many Leases as leases expire but rentals do not expire at the same time.
And here is the many to many...
Many leases can apply to many Tenants

So the flow as I saw it goes like this
1. User is presented with a form to select an apartment to create the rental for and a Rental Start date.
2. They click a button to create a lease for that Rental.
3. The RetalID from tblRental is auto populated into tblLease and the user selects a Lease term.
4. They click a button to create a new Tenant for the Lease.
5. Tenants info is entered.
6. They click a button to either enter another Tenant or close the form and go back to the switchboard.

So I feel that the flow for the first 2 forms may be acceptable. Maybe just on the Tenant form a subform would be ok.
There is no need to display or edit any information about the Lease or Rental while entering Tenant info. This is why I didn't see a need for a subform.
But it might be nice to have a static display of the Apartment number on the top of the Tenant form (or subform).

Sorry for the lengthy post but I hope that explains it.
Avatar of PatHartman
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I took the time to look at beatified's database and explained his problem, explained how to fix his wizard, AND offered an alternative solution to his wizard.  I'm pretty sure he just had too much invested in his current method and didn't like the answer which is sometimes - you can't get there from here.
I disagree. Offering somewhat generic advice to resolve problems like this don't really constitute a "solution", but rather a "suggestion".

I'll defer to the Mods for this, but I just don't see a viable to the issue asked by the user, regardless of the investment of your time (or mine).
First of all  Thanks Pat for allowing this question to stay open. As I haven't been able to work on this database lately but I didn't intend on getting more input from all of you.

Can you please take a look at my current resolution to the issues I had and see if you think it poses any issues.

Please point out any problems you might see.
One more quick question.

Do you know how I can replace RentalID in frmNewLease with the linked field AptNumLetter in tblApt which is selected in frmNewRetnal.

In other words I just want the frmNewLease Form to reflect the Apartment number and letter since it obviously provides pertinent info and the RentalID is totally unneeded.
There are several ways.  The simplest is to use a combo instead of a text box.

The forms all need some work.  There should be code in the form's BeforeUpdate event to make sure that all required data is present.  You should also set the Locked property of controls to Yes on forms where you don't want them to be changed.  For example, the RentalID on frmNewLease probably shouldn't be changed on this form so I would lock this field.  When I lock fields, I also set their tab property to No so the tab just skips them.  The user can still click into the field which is why you need to lock it but making the tab skip the field keeps the user from stopping in a field he canot change.

The New Lease Terms button also needs validation plus, it should requery the combo on the frmNewLease so that the newly added term shows up in the list.  Put the code in the Form's AfterUpdate event so the underlying list will refresh.
Private Sub Form_AfterUpdate()
    Forms!frmNewLease![Lease Term].Requery
End Sub

Open in new window