I have a number of subforms that have to be linked to more than one parent form. I am at a loss as to how to do this. This is a database for my law office. I have main forms for different type of cases-ie Auto Cases, Slip & Fall, Medical Malpractice, etc. I then have subforms which must be linked to each one of those main forms-ie Injuries, Medical Records, Medical Treatment, Medical Bills, Expert Witnesses, Defendents, Witnesses, CourtPleadings, etc. What I would really like to do is to put each subform on a tabcontrol to create the appearence of a client file but I don't see how I can put the same subform on more than one tabcontrol--e put the pleadings subform on a tabcontrol on the AutoAccidents mainform then put it on a tabconctrol on subcontrol on slip &fall mainform, etc. Can anyone help?
Subform Problem
- Thread starter Gilrucht
- Start date
MarkK
bit cruncher
- Local time
- Today, 13:19
- Joined
- Mar 17, 2004
- Messages
- 8,472
Have you tried putting one subform on more than one main form? It will work. You can even put multiple instances of the same subform on one main form and have each one show different data!
But a more important observation: Create a caseType table
caseTypeID as Autonumber
caseTypeName as Text
Then in your Cases table add a field...
caseTypeID as Number (Long Integer)
Relate your case table to your caseType table on your new caseTypeID field and you only need ONE main form. On this form, display a combo box from which you select the case type for the current case. Managing all your subforms on this one form will save you hours of mind numbing duplicated effort.
But a more important observation: Create a caseType table
caseTypeID as Autonumber
caseTypeName as Text
Then in your Cases table add a field...
caseTypeID as Number (Long Integer)
Relate your case table to your caseType table on your new caseTypeID field and you only need ONE main form. On this form, display a combo box from which you select the case type for the current case. Managing all your subforms on this one form will save you hours of mind numbing duplicated effort.
Lagbolt,
Thank you for the help. I have to apologize . I don't think I correctly explained my problem. I think I used poor terminology.Your solution is correct but there are too many subforms to physically put on the main form. Let me re-ask the the question with an example. When a new client comes in in an autocase, lets say, maybe 5 or 6 forms need to be filled out. Initially, the clientinfo form with the client fata is filled out and he/she is assigned a clientid # which is used to link him to his case. Then a finanial form is filled out recording the fee information on the case. A case number is assigned and the combination of clientid and caseid is used from here on out to link all forms( point of case id is a client can have more than one case ). Then the auto accident main form is opened and filled out. Next comes the Insurance form. Then a form for injuries. One for medical treatment etc.
My problem is As I open each form I want the clientid and caseid to be filled in. I think it is linking issue. Each table has an autonumber as the uniqueid. the combined clientid and caseid are a foreign key in each table and thats how the rables are linked- on the client and case ids. I have tried different expressions but nothing works. sometimes I get the #name error. Sometimes I get nothing. If they were subforms on the mainform I cold linkthem through the master-child property but there are simply too many to do it that way. I used the term subform because the are like subforms even though they really aren't. Can you help?
Thank you for the help. I have to apologize . I don't think I correctly explained my problem. I think I used poor terminology.Your solution is correct but there are too many subforms to physically put on the main form. Let me re-ask the the question with an example. When a new client comes in in an autocase, lets say, maybe 5 or 6 forms need to be filled out. Initially, the clientinfo form with the client fata is filled out and he/she is assigned a clientid # which is used to link him to his case. Then a finanial form is filled out recording the fee information on the case. A case number is assigned and the combination of clientid and caseid is used from here on out to link all forms( point of case id is a client can have more than one case ). Then the auto accident main form is opened and filled out. Next comes the Insurance form. Then a form for injuries. One for medical treatment etc.
My problem is As I open each form I want the clientid and caseid to be filled in. I think it is linking issue. Each table has an autonumber as the uniqueid. the combined clientid and caseid are a foreign key in each table and thats how the rables are linked- on the client and case ids. I have tried different expressions but nothing works. sometimes I get the #name error. Sometimes I get nothing. If they were subforms on the mainform I cold linkthem through the master-child property but there are simply too many to do it that way. I used the term subform because the are like subforms even though they really aren't. Can you help?
R
Rich
Guest
Since there's a link between a client and all the relevant data you have to input there's nothing to stop you adding a subForm and just changing its recordsource, in this case I suspect a tab control would help navigation
MarkK
bit cruncher
- Local time
- Today, 13:19
- Joined
- Mar 17, 2004
- Messages
- 8,472
I still don't get the problem. Basically there are two scenarios I can imagine:
1) You want to open a MainFormB that is initialized with key field data that exists on a MainFormA, and otherwise present a blank record for data entry
2) You want display a SubFormB on a MainFormA that is likewise initialized (linked) and allows data entry.
1) You want to open a MainFormB that is initialized with key field data that exists on a MainFormA, and otherwise present a blank record for data entry
2) You want display a SubFormB on a MainFormA that is likewise initialized (linked) and allows data entry.
Lagbolt,
The first scenario is correct When I close mainformA and open mainform B I want the PrimaryKey data (ClientID#) from the new record I just entered on mainform A to to appear in an otherwise new record on MainformB. I have the two tables linked in a one to many relationship so I don't know why I am having a problem. It works if I use a select query with a combobox but I shouldn't have to look it up.
My latest effort was to try the code:
Private Sub Label136_DblClick(Cancel As Integer)
Me.Refresh
DoCmd.OpenForm "frm_StateCivilCaseInfo", , , "SCVClientID = " & Me!ClientID
End Sub
It opens the the form but doesn't post the data.
Can you tell me what I am doing wrong?
The first scenario is correct When I close mainformA and open mainform B I want the PrimaryKey data (ClientID#) from the new record I just entered on mainform A to to appear in an otherwise new record on MainformB. I have the two tables linked in a one to many relationship so I don't know why I am having a problem. It works if I use a select query with a combobox but I shouldn't have to look it up.
My latest effort was to try the code:
Private Sub Label136_DblClick(Cancel As Integer)
Me.Refresh
DoCmd.OpenForm "frm_StateCivilCaseInfo", , , "SCVClientID = " & Me!ClientID
End Sub
It opens the the form but doesn't post the data.
Can you tell me what I am doing wrong?
Last edited:
MarkK
bit cruncher
- Local time
- Today, 13:19
- Joined
- Mar 17, 2004
- Messages
- 8,472
Generally I'd avoid using labels to make things happen. Command buttons are made to suit and provide higher user intuitivity, which isn't really a word.
What happens here is you open the other form, give it a new record, and then set some of its data...
I've not tested this code, but this is one path that might get you there.
What happens here is you open the other form, give it a new record, and then set some of its data...
I've not tested this code, but this is one path that might get you there.
Code:
Private Sub Label136_DblClick(Cancel As Integer)
Dim strFrm as String
strFrm = "frm_StateCivilCaseInfo"
[COLOR=Green] 'save pending edits
[/COLOR] Me.Refresh
[COLOR=Green] 'open the "other" form
[/COLOR] DoCmd.OpenForm strFrm
[COLOR=Green] 'create the new record in the newly opened form
[/COLOR] DoCmd.GoToRecord acDataForm, strFrm, acNewRec
[COLOR=Green] 'reference the newly opened form inside the With block
[/COLOR] With Forms(strFrm)
[COLOR=Green] 'and set the values of any of its controls
[/COLOR] .ClientID = Me.ClientID
.CaseID = Me.CaseID
[COLOR=Green] 'Manipulate any other public members of strFrm here.
'Run its code, disable buttons etc...
[/COLOR] End With
[COLOR=Green] 'and maybe even close the current form by name...
[/COLOR] DoCmd.Close acDataForm, Me.Name
End SubThabk you so very much. It works like a charm with one minor problem which I can deal with. The very last line of code which you suggested to close the first form kept returning an error when I tried to compile it so I deleted it. No biggie. I can put a close command on the onopen event You solved my big headache and I greatly appreciate it.
I also agree with you on the labels cause I found them easier til I got it right
I also agree with you on the labels cause I found them easier til I got it right
Gil,
One thing bothers me about this. You should have a Clients table and a Cases table. ClientID should be a Foreign key in the Cases table. The Case table should be the bound table on the main form, the Client table should be a subform linked on ClientID.
To find a specific record on this main form I would use the combobox wizard to create two search combos. The first to choose a client, the second, would be filtered on the client chosen and display the cases for that client.
One thing bothers me about this. You should have a Clients table and a Cases table. ClientID should be a Foreign key in the Cases table. The Case table should be the bound table on the main form, the Client table should be a subform linked on ClientID.
To find a specific record on this main form I would use the combobox wizard to create two search combos. The first to choose a client, the second, would be filtered on the client chosen and display the cases for that client.
Last edited:
Scott, I do have seperate tables for client and case. Please reread my first two posts above. There really are too many forms to do subforms. Say a new client comes in with an auto accident. You start with with a clientdata form, then casedata, then autoaccidentform, thenaccidentdetails,then defendants, then Insurance, then Injuries, then Medical treatment, Doctors, then Medical Bills. Thats initially. As the case develops you have forms for case pleadings,discovery, witnesses, expertwitnesses,etc. Each of these forms is bound to a seperate table. As far as I can see its simply impossible from a real estate standpoint to have them all as subforms. When the casedata form is opened the client is assigned a caseid to go with his clientid. This accounts for a client with more than 1 case. The combination of the clientid and caseid is the PK of the case table. Each field by byself allows duplicates but combined no duplicates are allowed. Thus you can have 200523-01 and 200502-02(I reset the autonumber to begin with the current year using an append query and adding one record with the number 200500 ) but not 200523-01 twice. This combined key is then used in every subsequent table and all tables are linked by this combined key. As for querys in combo boxes I agree with you when it comes to looking up data or adding data as the case proceeds but I don't understand why you would use them with a new client. When a new client comes in you have 5 or 6 different forms to fill in initially. Why would you want to have to look up the same thing as you complete one form and open the next ? Is there something I'm missing?
Gilrucht said:
First, I do NOT recommend a composite key. That's why your statements bothered me. CaseID should be the PK for your case table and ClientID a foreign key in that table. If you search the Web on the phrase 'natural vs surrogate keys' you should find several discussions on this issue. The overwhelming recommendation from Access Developers is using an autonumber as a surrogate key that is not seen by the user, but used to link tables. You also refer to "reset the autonumber". That's definitely not how to use an Autonumber. An autonumber sole's purpose is to uniquely identify a record.
As for mutliple subforms, if you use a tabbed interface you can easily fit lots of subforms on a single main form. But by using ClientID and CaseID as a composite key you make the task of synchronizing the forms much more difficult. If you change back to using CaseID as the PK, then it becomes much easier to link the main form (Case table) with its various subforms. Or, if you prefer, to synchronize separate forms.
Scott, I'm not sure why you say resetting the autonumber is not the way to use an autonumber. It is a recognized technique by Access. If you do a search in Access Help for "resetting autonumber" Access gives you step by step instructions on how to do it. I dont't see what difference it makes if the autonumber begins with 1, 10, 1000 0r 10,000? As for the combined client and case id you are probably correct in terms of the optimal way of doing it. My problem is that the "powers that be" want the db coding to track out office coding. -ie-Each client has combined client and case id which must be entered for billing purposes when making a long distance phone call. The same code must be entered in the copier to make copies or in the postage meter, etc. They want this same number used in the db. The code give to me by Mark appears to work. Unless you are telling me it won't work as opposed it is not the vest way to do it my hands are pretty much tied. I appreciate your advice but unfortunately I have bosses who call the shots. I am interested in your suggestion on tabs. As I indicated in my original post above my concern is once I put, say medical records and medical bills on rab controls on the main caseform autoaccidents how do I put thes same two forms on tab controls on the main caseforms for , say, Slip & Fall and Medical Malpractice?
Even though I am not agreeing with you I do appreciate your imput and advice. It is making me think and helping me to better understand what I am trying to do.
Even though I am not agreeing with you I do appreciate your imput and advice. It is making me think and helping me to better understand what I am trying to do.
Just because Microsoft tells you how to do something doesn't mean its the right way to do it. For example Microsoft allows you to create lookup fields on the table level, but most developers I know, including myself warn against them. The sole purpose of an autonumber field is to provide a unique identifier to use as a PK. A PK is generally not visible to the user but used internally to link tables. So it really doesn't matter what the number is. I don't see a problem with starting the autonumber at a specific point, but I do see a problem in "resetting" it after data has been entered.
I have said this many times, users should not dictate database design. They can influence the interface but not the design. There is no conflict with requiring any documents to show both Client and Case IDs. Since the ClientID is a required foreign key in the Case table. It would be easy to display the ClientID along with the CaseID on all forms. You can even set a unique index on the combination. The point is you can still satisfy the user requirement for identifying records by the displaying both IDs without making your joins more cumbersome by using a composite key. the bosses should have no clue about the keys. Using a multi-field primary key is asking for trouble.
You can have multiple instances of a subform on a main form. Or have the same form be a nested subform. Not a problem.
By the way its not a matter of you agreeing or disagreeing with me. What I am telling you is considered "best practices". Most of the developers I know will back me up on that. You are free to ignore the advice, but you do so at your own peril.
I have said this many times, users should not dictate database design. They can influence the interface but not the design. There is no conflict with requiring any documents to show both Client and Case IDs. Since the ClientID is a required foreign key in the Case table. It would be easy to display the ClientID along with the CaseID on all forms. You can even set a unique index on the combination. The point is you can still satisfy the user requirement for identifying records by the displaying both IDs without making your joins more cumbersome by using a composite key. the bosses should have no clue about the keys. Using a multi-field primary key is asking for trouble.
You can have multiple instances of a subform on a main form. Or have the same form be a nested subform. Not a problem.
By the way its not a matter of you agreeing or disagreeing with me. What I am telling you is considered "best practices". Most of the developers I know will back me up on that. You are free to ignore the advice, but you do so at your own peril.
Scott,
Not to belabor the point. I really do want to do it right and not have problems. The problem I am having is I am getting mixed signals. Since your last post I have been researching the issue, When I searched the Web for "composite keys" all the sites I read used them and explained how to use them. There is even a thread on this forum which discusses composite keys. ( http://www.access-programmers.co.uk/forums/showthread.php?t=86193)Two of the posts in the thread are from two of the most respected posters on the forum, one of who is a moderator. They indicate the situations in which they should be used are rare but there are times to use them. It just seems to me after reading their posts that this is one of those times. While its true that the case table is linked to the client table by clientid, every other table is linked to the case table by the combined client and caseid. They can't be linked by just the clientid because the client can have more than one case. Thus the composite fields really are the PK to all other tables. I am using auto numbers as the PKs in all the other tables with the composite keys as the FK. As I read the posts in the composite key thread here this is exactly the situation composite keys were made for. So there I am finding mixed opinions as to what is correct.
Pat, DocMan, If you read this I really would appreciate your advice since it was your posts I was referring to. I really am confused as to which is best. All things being equal, I really prefer to use a composite key but not if it will cause me problems as Scott says.
Not to belabor the point. I really do want to do it right and not have problems. The problem I am having is I am getting mixed signals. Since your last post I have been researching the issue, When I searched the Web for "composite keys" all the sites I read used them and explained how to use them. There is even a thread on this forum which discusses composite keys. ( http://www.access-programmers.co.uk/forums/showthread.php?t=86193)Two of the posts in the thread are from two of the most respected posters on the forum, one of who is a moderator. They indicate the situations in which they should be used are rare but there are times to use them. It just seems to me after reading their posts that this is one of those times. While its true that the case table is linked to the client table by clientid, every other table is linked to the case table by the combined client and caseid. They can't be linked by just the clientid because the client can have more than one case. Thus the composite fields really are the PK to all other tables. I am using auto numbers as the PKs in all the other tables with the composite keys as the FK. As I read the posts in the composite key thread here this is exactly the situation composite keys were made for. So there I am finding mixed opinions as to what is correct.
Pat, DocMan, If you read this I really would appreciate your advice since it was your posts I was referring to. I really am confused as to which is best. All things being equal, I really prefer to use a composite key but not if it will cause me problems as Scott says.
Last edited:
I can understand the confusion. As Pat said there are 2 schools of thought on this. I (and most of the developers I know) fall primarily into the surrogate key school. I would suggest expanding your research to look for discussions about natural vs surrogate keys. I will point to something Pat said about using composites "UNLESS the table has child tables of its own." That is your situation. Your case table has many child tables. Also you need to make the distinction between a composite INDEX and a KEY. Composite indexes are a useful tool to prevent duplications. But Composite keys cause redundant data and headaches in linking tables because you have to deal with multiple fields.
I'm not suggesting that ANY table but the Case table be linked via ClientID. My point is that once you have the Case, you can get the Client because ClientID is a FK in the Case table. Your Case table is your main table just about all the other tables are children of the Case table. Its the CaseID that links everything. Therefore, it becomes redundant to include ClientID in any other table. Plus, as I said before, it is cumbersome to link on multiple fields. I've attached a JPG that may help illustrate.
Gilrucht said:
I'm not suggesting that ANY table but the Case table be linked via ClientID. My point is that once you have the Case, you can get the Client because ClientID is a FK in the Case table. Your Case table is your main table just about all the other tables are children of the Case table. Its the CaseID that links everything. Therefore, it becomes redundant to include ClientID in any other table. Plus, as I said before, it is cumbersome to link on multiple fields. I've attached a JPG that may help illustrate.
Attachments
Scott, I looked and at your diagram and that is not going to work. It will result in duplications. Here is the problem. Client1 has 2 cases so his client number 250023 and his case numbers are 01 and 02. Client2 has one case. His clientnumber is 200024 and his casenumber is 01. That is why I need the combination to make them unique. I could use a seperate autonumber for the PK of the casetable but would still need the combined client and case id all the way thru because that number is how the physical files are identified, office expenses are tracked before they are entered in the db, etc. For example, that combined number is programmed into the phone system and copier for each new client and case to track those expenses. When it comes time to enter phone records for client x they can't be entered in the right record without the combined client-caseid. So regardless of whether the two ids are a combined key are not I still need both numbers in my tables. Thats why it made sense to me use the combination as the PK in the case table and then as FK's in everything else. I have to have both ids in the table anyway so why have 2 unique indexes in a table ? Won't that just complicate matters and add a field I don't really need?
Gil,
That's why you shouldn't be using a natural key. If you used an autonumber key your links will work fine and easily. You still have a Case Number field That you increment using an expression like:
=Nz(DMAX("[CaseNum]","TblCases","[ClientID] = " & clientID),0)+1
You don't need to store the client number anyplace else but the case table. Since you will not be looking at the other child tables except thru the Case table there will be no problem.
That is not correct. Believe me I've been designing databases for 20 years. The problem is you are looking at this from the wrong angle. The ONLY value that should be in a related table is the PK value as an FK. Anything else can be gotten by joins. In the example you cite there are three ways around the issue (I don't even want to call it a problem, because it isn't). One way is to have Phone records would be entered on a subform with the main form bound to Case table. In that case the CaseID (the autonumber) is entered as the FK in the phone records table automatically. Another way is to use a Combobox to select the CaseID (FK). The Combobox would have a RowSource like:
SELECT CaseID, ClientID & "-" & CaseNum AS CaseNumber FROM tblCases ORDER BY ClientID, CaseNum;
The Bound Column would be 1, the ColumnCount 2 and the Column Widths: 0";1". This would result in the user selecting from a list of your Composite IDs but storing the autonumber as the FK. The third way would be to have the user type in composite ID and use the After Update event to set a hidden control bound to the CaseID FK to the autonumber CaseID using a Dlookup.
In all the cases, the fact that the PK in the Case table and the FK in the child tables is an autonumber is totally transparent to the users. All they see is the composite ID that is used to select the record. And that is where you viewpoint goes awry. I think you are going under the mistaken impression that a PK has to have meaning to the user. That is actually the opposite of reality. In most situations the PK has no meaning to the user. It is used solely within the structure of the database design to perform your joins.
Let me add something else here. I don't want you to think I'm trying to fight with you. You should do what you feel is necessary and are comfortable with. What I'm telling you are the principles of good database design that I have learned (and practice) after 20 years of designing databases. These principles generally follow industrywide "Best Practices". This is more a matter of education.
That's why you shouldn't be using a natural key. If you used an autonumber key your links will work fine and easily. You still have a Case Number field That you increment using an expression like:
=Nz(DMAX("[CaseNum]","TblCases","[ClientID] = " & clientID),0)+1
You don't need to store the client number anyplace else but the case table. Since you will not be looking at the other child tables except thru the Case table there will be no problem.
Gilrucht said:
That is not correct. Believe me I've been designing databases for 20 years. The problem is you are looking at this from the wrong angle. The ONLY value that should be in a related table is the PK value as an FK. Anything else can be gotten by joins. In the example you cite there are three ways around the issue (I don't even want to call it a problem, because it isn't). One way is to have Phone records would be entered on a subform with the main form bound to Case table. In that case the CaseID (the autonumber) is entered as the FK in the phone records table automatically. Another way is to use a Combobox to select the CaseID (FK). The Combobox would have a RowSource like:
SELECT CaseID, ClientID & "-" & CaseNum AS CaseNumber FROM tblCases ORDER BY ClientID, CaseNum;
The Bound Column would be 1, the ColumnCount 2 and the Column Widths: 0";1". This would result in the user selecting from a list of your Composite IDs but storing the autonumber as the FK. The third way would be to have the user type in composite ID and use the After Update event to set a hidden control bound to the CaseID FK to the autonumber CaseID using a Dlookup.
In all the cases, the fact that the PK in the Case table and the FK in the child tables is an autonumber is totally transparent to the users. All they see is the composite ID that is used to select the record. And that is where you viewpoint goes awry. I think you are going under the mistaken impression that a PK has to have meaning to the user. That is actually the opposite of reality. In most situations the PK has no meaning to the user. It is used solely within the structure of the database design to perform your joins.
Let me add something else here. I don't want you to think I'm trying to fight with you. You should do what you feel is necessary and are comfortable with. What I'm telling you are the principles of good database design that I have learned (and practice) after 20 years of designing databases. These principles generally follow industrywide "Best Practices". This is more a matter of education.
Last edited:
Scott, You are right. To a certain extent I want thr PK to have meaning but that is not the only issue. Once the client # is put in case table as a FK the composite clientid and caseid would become a unique number. I don't see the point of then creating an autonumber as a second unique number.other than I only have to put one instead of 2 fields into all subsequent tables to create the FK thus saving space in the tables.
The tradeoff though is I have to use comboboxes to look up each form whereas with the composite
key I can use the code Lagbolt gave me above and avoid having to use a combobox on each form. I guess my question is one I should have asked you in the very beginning. Perhaps we could have avoided all these posts if I had but better late than never. What problems do you see me having if I use a composite key as the PK in the case table instead of an autonumber and then use this composite key as the FK in all subsequent tables? That really should have been my first question. I apologize for not thinking of it before.
The tradeoff though is I have to use comboboxes to look up each form whereas with the composite
key I can use the code Lagbolt gave me above and avoid having to use a combobox on each form. I guess my question is one I should have asked you in the very beginning. Perhaps we could have avoided all these posts if I had but better late than never. What problems do you see me having if I use a composite key as the PK in the case table instead of an autonumber and then use this composite key as the FK in all subsequent tables? That really should have been my first question. I apologize for not thinking of it before.
