Ms Access Continuous form - with a many to many relationship

Hi Everyone,
I have a form based on tblMain, it has a subform on it that is continuous based on tblCondition.  Now I need to allow for each record in tblCondition to be able to have 'many' records in tblMeds.  So would this be a continuous form on a continuous form?  I know I can't put it in the detail section.  But I was wondering since I am early in the deisgn phase, if there is another way I can structure this.  Basically each person can have many conditions, and each of their conditions can have many medications.
Thanks,
Jetera
jeteraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eric ShermanAccountant/DeveloperCommented:
Here's what I generally do in cases like that when I want to have more control over where information is displayed. Sometimes the client want's to have all information displayed in separate windows on the main form.

If I understand your concept thus far ...  You have a patient (person) and you want to lookup on a form that patient, then display all the condidtions for that person along with all the medications for that conditions.  Assuming you have the proper fields in your tables what I generally do is build separate sub-forms for the conditions and medications each.  Do not link them to the main form.  In the record source of the conditions establish the parameter back to the main for the patiend Id.  In the record source of the medications establish the parameter back to the conditions for the condition Id.  

Then for the main form's on current event you would requery the two sub-forms.  Whenever you change patients the two sub-forms will refresh with records related to the current patient.

Also, I generally load the record source of the two sub-forms when in the main form's load event.  This will prevent any errors in the SQL because the main form has to open first to know which patient/person to pass to the parameter query.

Just an idea ...

ET
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jeteraAuthor Commented:
I tried this but it is not exactly what I want.  I need the info to be laid out in order and allow the user to add more meds if need be.

How do you make them grow in relation to one another?
0
jeteraAuthor Commented:
Etsherman - I have a question - I think I am doing this wrong.  How should I set up my tables?
Basically
1 patient can have many conditions, and each condition can have many medications.  I think my tables are wrong.  Can you explain more about the table design so Ican get theses forms to work, I guess this is my only option.

Thanks!
Jetera
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Eric ShermanAccountant/DeveloperCommented:
Hello jetera ...

Keep in mind the suggestions that I proposed are just that ....  An overall broad approach to accomplish what you are trying to do because I don't have your db application nor do I have the full scope of exactly how you envision this functioning.

Just based on what you've said ....

The overall idea is to have a Patient table with a unique Patient ID.  Then you would need a Patient Details table where you would enter a  record for each patient, each condition consisting of the (patient id, condition id).  Therefore, when you setup your patient form you could have a sub-form that shows all the patient details (conditions) joined by the patient id field.

Next, since each condition can have multiple meds you will need a condition details  table where you would enter a record for each condition consisting of the (condition id, med id).  Therefore, when you select a condition id on the sub-form on the main form you could re-query another sub-form to display all the meds based on the current condition.

Also, you would need separate tables to define the Conditions (Id, Name, Other Fields, etc.) and Meds (ID, Name, Other Fields, etc.)

Again, this is an overall big picture of on approach.  To work out the exact details with table structure and field names, etc. I would need a copy of your application.

Hope this helps and points you in the right direction.

ET

0
jeteraAuthor Commented:
I think the problem is the user needs to be able to add new conditions and then associate new meds with each new condition.  So in the condition subform - they can just add a new row.  But I am having problems with the medicine subform - because they need to see what was already there and be able to add new ones.  SO I wanted to put a combo box on that continuous form, or I guess it could be datasheet and then allow the user to select another med if need be.

Either way I can't get it to work.  I might have to send my file to you.

0
Eric ShermanAccountant/DeveloperCommented:
>>>Either way I can't get it to work.  I might have to send my file to you.<<<<

Ok, you can upload a Access 2000 version to http://www.ee-stuff.com/ and associate it with this question for all to review.

OR

You can send a zipped Acces 2000 version to my email address located in my profile.

ET


0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"You can send a zipped Acces 2000 version to my email address located in my profile."

***** No, you cannot do that ... it violates EE rules. You can *only* upload to EE-Stuff ... so that everyone can access the file.

mx
0
Eric ShermanAccountant/DeveloperCommented:
Thanks MX ...  I only suggested option 2 as a last resort in case the asker did not know how to use ee-stuff.com.  In that case I generally load it up there anyway.

No Problem though.

jetera just scratch option #2, ok.

ET
0
jeteraAuthor Commented:
Ok I have a meeting until the end of today- I may upload it tomorrow.  I am open to any other design of it now - apparently the client has said "Whatever you need to do to make it work - then go ahead".
So if you have any other better ways of representing it all- ie list boxes or whatever- I am open to that too.
I will log back on tomorrow!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.