More than two Subforms

[sseven]

Hi, and thank you for taking the time in reading my thread.

Database information:
I have three tables; users, clinicians, and RU. The ‘users’ table should have all available users that can login whereas the ‘clinicians’ table only has users that are in the ‘users’ table but also have additional capabilities; their RUs, locations of operation and facilities he/she work in (each clinician is listed more than once based on their capabilities). The table ‘RU’ has each of RUs (in the ‘clinicians’ table) associated with the clinician their areas of operation and the facilities.

Objective(s):
Using MS Access 2002, I would like to search/find a user that is in the ‘users’ table and display these results in a table labeled ‘tbl_users’. Any clinician (if any) displayed in the ‘tbl_users’ would have his/her multiple RUs displayed in another table labeled ‘tbl_clinicians’. And finally, all RUs (if any) would be displayed in ‘tbl_RU’ along with his/her associated facilities and areas of operation.

I’ve tried:
I have limited knowledge with forms. I am able to create two subforms that will display the results of a find/search using a text box and command button. However, every time I create a third subform it wants to reference to the main form (which does not have all of the possible RUs that a clinician may have. It only has one of them) only and not the second form. I think if I could, it might work, which is where you come in.

Plan:
This would allow me to verify data already entered at a glance as well as edit and/or add/delete records to the corresponding tables in the database as needed. If it makes any difference, I hope that this form can be moved to a production database where the MS Access database is linked to data on a MySQL server.

My question(s):
Is what I am doing (subforms, command button, and text box) the best way of accomplishing it, and if not please offer constructive suggestions? Is it possible to associate a third subform to any other form other than the main form and if so please offer information or point me to a resource that can help?


I thank you again and for your suggestion(s).
sseven

 

[wazz]

if i understand correctly, your tables need more work before you do anything with your forms.

regarding users and clinicians, one approach would be a one-to-one relationship. all users "public" data goes into tblUsers and is identified by UserID. tblClinicians has "private" or "extended" data as you mentioned and ALSO uses UserID. the two UserID fields are joined in a 1:1 relationship.

if one clinician may have many RUs and one RU may be associated with many different clinicians, then you need a third table, tblClinician_RU. this table will include at least two fields: UserID and RUID, linked to tblUser's ID-field and tblRU's ID-field. this table is known as a composite table or junction table.

i'll stop here for now. please search on all of these terms.
btw, what is RU?
p.s. i'm not around here much these days. keep searching and post back if necessary and hopefully someone will respond soon.

 

[sseven]

Wazz,
Thank you for your response. When I started my new employment, I was given the task of updating this portion of the database. I thought by using a form(s) it would make it more efficient, until a program can be made to do this automatically. There are five tables that are directly related to what I am doing, of which two tables have data entered into them by someone else. I use one of the two and three other tables to complete my part.

If I under stand correctly your 1:1 relationship is correct in that tbl_users has every possible clinician that can login to the web server. If that clinician has access to additional privileges, such as more that one RU (reporting Unit) and/or region (LME), this additional information is displayed after logging in. If a clinician does not have more than one RU or LME that clinician logs in with his/her assigned RU and LME located in the tbl_users table. tbl_users does have the clinID with at least one lmeID, facID, and ruID for each and every clinician.

The tbl_clinicians table has the clinID, facility’s ID (facID), LME’s ID (lmeID) and the ruID (in the form of a list separated by a comma for example: 3000,41000,5000) that correspond to the facility and LME. If the clinician has access to more than one LME or facility I need to enter the RUs for each of it’s LMEs:

lmeID | facID | ruID
42100 | 52 | 52,65,48
42100 | 98 | 354,854,12
48925 | 54 | 125,658,48

above example would require three entries into the tbl_clinicians table.

As for the tbl_RU it has each of the above RUs on its own record (in this example nine records would need to be entered) each record will have its ruID, facID, lmeID, and a uniqueID key. There is no clinID in the tbl_RU for it to act as a composite/junction table. I have no idea why.

The fourth table is just used to see if the facility allows the clinicians to be automatically setup with their RUs and LMEs.

Reporting Units (RU) is randomly assigned by either the LME or the facility and we do not have a table designated just for the RUs.

Well, enough about the database because I do not think they will let me redesign it just yet, so I will have to work around it.

Since I know I could not do much with the database. I guess, I was more concerned with my implementation and use of forms and subforms. Particularly why my third subform would not let me link fields in it to, another form other than the main form, the second subform.

Thank you in advance for any help.
sseven

 

[wazz]

under ideal circumstances you could "link" a third subform to the second, but there has to be something in the underlying data of the forms that connect them; uniquely connect them, like a unique ID, shared by both tables.

if any piece of data in subform 3 can somehow "connect" to more than one piece of data in subform 2, or if any piece of data in subform 2 can somehow "connect" to more than one piece of data in subform 3 the database itself won't know how to make the connection; it won't compute. it would be a complicated if not impossible task.

the db is quite confusing to me and probably needs an overhaul. since you can't, i won't address it except to say that someone needs to research: primary key, foreign key, normalization and queries (which should be used to isolate data and develop forms). as for the forms, i guess you'll have to experiment quite a bit to find a work-around. i can only suggest breaking up the forms into smaller units with only one subform/form, or perhaps no subforms at all.

 

[boblarson]

Just a point to note, and perhaps it isn't applicable here, but if you have information that you want to display on the main form, even though it isn't related to the main form, you can do it by using a subform. Now, it won't be "synchronized" so that it changes with every record of the main form, but you can use a subform to display, and even change data, for tables that you want displayed on a main form that is synched to other subforms.

 

[sseven]

Thank you so much wazz and boblarson for your insights.

It sounds like, for now, my idea is not going to work, at least the way I would like it to. I can use the main form (with just two sub forms) to do a quick search of clinicians that are already setup as a multi clinician.

Wazz, the only column labels in tbl_clinicians and tbl_RU that might be able to be ‘linked’ in subform 2 with subform3 or visa versa are: facID and lmeID. I will have to do more checking to see if this is actually the case though. Though, I have a feeling that those two fields are not going to be enough to identify any one clinician. You’re not the only one confused by this database. It has taken me some time to adjust to its illogical layout. When I asked for a diagram of the relationships between tables they had no idea as to what I was talking about. You are most definitely correct in that this database does need a complete overhaul and the sooner the better.

Boblarson, I am not sure if I understand your idea enough to determine whether or not it would be applicable or not. Do you mean to say to set the ‘Record Source’ of the main form to nothing by leaving it blank? I would like to see the data, where there is any, returned from the results of a search for clinicians in tbl_users displayed in subform1 within the main form. Then subform2 would show the clinician(s) (with the appropriate data from tbl_clinicians) that match the one(s) listed in subform1 and subform3 from subform2 (with the appropriate data from tbl_ru).

This project was not assigned to me but by me to be more efficient. So, when I run into extra free time, I will continue to work on it. However, if you or anyone else has any suggestions, feel free to post them here.

Thanks again you two, I truly do appreciate the time you have spent on this.
sseven

 

[wazz]

i don't think bob is suggesting you leave the main form's record source blank, he's just saying that if you really want to see something else - anything else - on the main form, you can, via a subform; it might not be directly (or even remotely) related to the main form but at least you can still see any data you want and even change it.

 

[Epic]

if the tables are used at the moment by you to ype in I guess that the tables are correctly defined...

 

[Epic]

sorry pressed by mistake the Post button... anyway

the issue here might be your your approach... the subforms works as tree if they are linked trough their parent/child properties. You have also ensure that the subforms are requery-ed every time you are mooving to another record.

You can have stand alone subforms not linked and you can manipulate the SQL string from the data source and pass it to any subform on the main form or even to another open form.

 

[boblarson]

sorry pressed by mistake the Post button... anyway

the issue here might be your your approach... the subforms works as tree if they are linked trough their parent/child properties. You have also ensure that the subforms are requery-ed every time you are mooving to another record.

If the main form and subform are linked and you move to another record on the main form, no requery is required because Access does that for you because of the link you set.

You can have stand alone subforms not linked and you can manipulate the SQL string from the data source and pass it to any subform on the main form or even to another open form.

If you have stand-alone subforms, then they are NOT requeried when moving from record to record, so you might have to manually set the code to do that.

 

[Epic]

To support my reply:
what if you move to next record on one of the subforms and you have declared one of the parent reference in the second subform to be a control on a third subform, etc... might not be the case here

can be done programarically.. the whole point of using stand alone subforms is that you need more then a simple parent/child relationship

 

[sseven]

Please excuse my ignorance boblarson and Epic, but when you are talking about “stand-alone” and “linked/un-linked” main/sub forms are you referring to the “Record Source” and “Source Object” in the properties of the main form and subform respectfully? If not, please help me understand where/what it is you are talking about.

You both either talk about “manually set the code” or “can be done programarically” but I am unclear as to how I would go about doing this or where the code/program would reside.

It would be nice if I could choose which fields of any table in the “Link Child” and “Link Master” fields but they both refer to the main form. And the main form’s Record Source is tblusers. If I were able, or knew how, to choose which fields of any table or do it as “code” or “programitaclly” it sounds like it is possible to make this work.


Thank you, for your patience with me in this as well as your knowledge.

sseven

 

[boblarson]

Please excuse my ignorance boblarson and Epic, but when you are talking about “stand-alone” and “linked/un-linked” main/sub forms are you referring to the “Record Source” and “Source Object” in the properties of the main form and subform respectfully?
sseven

You can have a subform on a Main form (which the main form can have a recordsource, or not, doesn't matter) that isn't linked. By not linked, I mean that there is no Child/Master relationship set. For a normal subform setup, there is a common field/fields between the main form and subform, usually the main form has the main table information and it's primary key on it and then the subform has related information via a query of another table that has that main table's primary key stored as a foreign key. That way, when linked, the subform container synchronizes the data and shows you only the data applicable to that key.

Now, if you want to display some totally unrelated information, you can put a subform with that on the main form, but don't link it. The subform will not change records as the main form changes, but it can display information as read-only (if you want) or you can actually edit it. For example, I have a subform (products) on my main form (sales) but I don't want the products subform to be associated with sales, I have it on a tab control so I can go enter in new products, and the main form handles all of the transactions. It has a control on it that can add products to the sale but I am not showing the products subform in order to synch between sales and the product entry. There's no need. However, I do have a subform on the main form as well for sale details and that is synched to the main form, so that for each sale the associated sale details show up.

I hope that helps.

 

[Epic]

u can also follow this link which will download some sample Accees Db's... (12Mb) and study... starts right from the beginning. Is Access97 but the logic is the same.

AccessCookbook97.zip

 

[sseven]

Thank you for your explanation boblarson and the sample files epic. I think you’ve straightened me out a bit boblarson. It seems like this “little” project is going to be more difficult than my knowledge of forms. I was hopping I could “slap” a few subforms onto a main form and voilà I would be off entering data to my hart’s content.

Epic, I will take a look at the files on my free time, and maybe there will be some good examples that I can relate to my project. Though, I think, if the database was not in such of a mess as it is in, this form would have worked as I intended. However, since I am such of a new employee, I suspect I should not press this issue just yet. Maybe these examples can show me something to use, temporarily.

Thanks again for every ones input, you have all helped ground me, in regards to forms in Access, and verified what I have already suspected, in regards to this database.

I will keep in touch when the event arises.
sseven