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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Eric ShermanConnect With a Mentor Accountant/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
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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 MVP, Access and Data Platform)Commented:
"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
All Courses

From novice to tech pro — start learning today.