LinkMasterFields property setting has produced this error

[davisjr6]

I'm attempting to build an Access database to house hospital infection surveillance information.

Upon completing the database, I decided I needed a search function in the main form (complete with 2 subforms) to assist nurses in quickly finding a patient to monitor concurrently.

I tried two different things:

1) creating a search filter in the main form with a filter and clear filter button ),

which did not work and

2) designing a Search Form using this online tutorial (http://www.datapigtechnologies.com/f...earchform.html), which also didn't work.

The most embarrassing part is that the clean copy of the database, the one in which I had not "modified" was deleted two days earlier from a USB drive accidentally.

After attempting to delete all traces of my "modifications" (i.e., 1 and 2 above) my form no longer works. I now get an error message reading: LinkMasterFields property setting has produced this error:'A problem occured while Microsoft Office Access was communicating with the OLE server or active X Control'.

What can I do?

[ADezii]

@davisjr6
This is a tough one since the Error generated does not seem indicative of the actual problem. I can't see how creating a Filter would generate this problem. Can you Upload the Database that is not now working? Also, what Version of Access are you using?

[davisjr6]

@ADezii
Sure, I'll upload the dummy database. Just need to figure out where and how. Access 2003.

Note: The system will not let me upload the database. Let me see what else I can do.

[TheSmileyCoder]

Compress it to a zip file and try again.

[davisjr6]

Here you go! Thanks!!!!

[davisjr6]

@TheSmileyOne
P.S. The main form is VAP.

[ADezii]

Got it, davisjr6. Will have a look later.

[davisjr6]

Thanks, friend!

[ADezii]

The problem appears with the [MRN] Field

1. Why is [MRN] Text?
2. How do you generate a New [MRN] Value in the VAP Form Table?

[missinglinq]

I suspect [MRN] is a Medical Records Number and as such is really should be Text, as no math will be done with it. I also suspect it's generated independently of this database and is simply input into the form.

[ADezii]

@missinglinq
Thanks for the insight, linq.

[davisjr6]

@missinglinq
Missingling is right on. Did he clear up your question? Thanks again for looking into this for me.

[ADezii]

The problem appears to be a little more than a LinkMasterFields Error. The Control Source of the VAP Form is a Query consisting of 3 Linked Tables, namely: GHS Roil-Up, VAP Form, and PNEU NHSN Form. The Tables are inter-related via 1 <==> 1 Relationships on the Primary Key [MRN]. The 2 Sub-Forms are based on the Tables Reintubation and VAP Pathogens. The Relationships involved with these Tables are also 1 <==> 1, and they also contain [MRN] as their Primary Key. The problem, as I see it, is that there is no mechanism in place to propagate the Primary Key Value ([MRN]) to these Subordinate Tables, thus the Link Errors. If you pre-populate the Reintubation and VAP Pathogens Tables with the [MRN} Values in the Main Form, you will not see this repeated Error. The Relationships among the Tables also result in something similar to a Circular Reference, and are close looped. If you are not experiencing many problems now, you probably will in the future. In my opinion, some form of Redesign is in order here. Perhaps someone else will have a different viewpoint on this matter.

[davisjr6]

@ADezii
Thanks so much for taking time to investigate further! Very helpful!!! I agree some form of redesign should be pursued; however, I'm at a loss as in what direction.

Essentially, each patient will have the same MRN (unique identifier=Medical record number) for every visit, but a different Patient ID number (billing number) for each visit. My problem is trying to figure out how to build tables/forms appropriately factoring in the correct approach to the Primary key.

For instance, Patient A enters hospital on 12/12/2009 with three similar infections. Then patient A is readmitted on 3/19/2010 with a set of different infections (e.g., CLABSI, VAP, etc.)and one infection that was similar to that identified on 12/12. How do I build a database in which you can simultaneously capture old infections and yet uniquely identify new infections (to ensure proper reporting to CDC) without having to reinput patient name, MRN, DOB, facility location, unit, etc.

I think the crux of the issue is that I don't know how to properly designate the primary key. Should it be MRN for all the tables, just change the relationship (1 to many, 1 to 1) or link MRN to Patient ID in some tables?

Additionally, since last weeks question I've converted my database to Access 2007.

Thanks again!

[ADezii]

@davisjr6
At first glance, it appears to me that you should have a Parent/Main Table that consists of specific Patient-related information such as: DOB, Address, Phone#, etc. This Table would have [MRN] as its Primary Key. Next, there may be a History/Visit Table which would cover all the Patient's Visits/Sessions. This could relate back to the Parent Table via a MANY ==> 1 Relationship via [MRN]. The [PatientID] would be the Primary Key in this Table. Any Diagnostics, Testing, Diseases, etc. that related to a Patient's specific Visit would make up a Third Table. This Table would relate back to the Visit Table in a MANY ==> 1 Relationship on [PatientID] and would have its own Unique Key. Additional Tables can be added and properly related as required.

[davisjr6]

Thanks (screams), ADezii!!! You're response made things much more clear!!!

[ADezii]

@davisjr6
Happy the fog lifted for you.