Record Selector Based on Subform Field? Possible?

[DEFT]

Hi all,

I've still pretty new to Access and am relatively self-taught using videos and self-help articles. I have a tracking database that was built to track a caregiver and patient through a research intervention. To help with data entry, I created a main form (CaregiverProfile) where you can enter all the information related to the caregiver, with subforms also present to enter information related to the patient, sessions, calls, etc. All of the child forms are linked to the parent form by the same ID: CaregiverID. So in essence, we have a sort of "chart" for each caregiver that has multiple "forms" where you enter information on their and their interaction with the study.



Currently, we search and select records in the forms using the search box on the navigator bar on the bottom of the screen. However, this only allows us to select records by information within the caregiver form and does not allow us to search/select records using information from the subforms. For example, we would like to be able to also search for patients based on their unique identifier instead of having to first search for the patient via their caregiver name/information.

I've tried different versions of combo boxes and copy/paste different code; however, nothing I've done so far has seen to work and I'm wondering if it is possible with the way the data base is currently set up. So, my question is, is it possible to sort or select a record using some sort of list/combo box or search button from a field that is housed in a subform to pull up all forms related to that records? For example, I want to pull up all the information for Patient 00001, including caregiver information, sessions, and calls, but I only have their unique identifier to search by (0001). Could I somehow use their unique identifier to pull up all the linked forms, even though all forms are linked by another field (caregiver ID)?

Any information/advice would be helpful.

[orange]

You may have a data structure issue, but we need to know more about your "business" to ensure your database structure matches your business facts.

It sounds like you have

Caregiver ----->CaregiverAssignedToPatient<------Patient

and some tables related to your research including sessions and calls.

Here is a link to a free generic data model re Hospital Patients. It is generic in that it deals with the most common aspects. You can use it as a starting point and remove/add additional entities/attributes/relationships to suit your situation.

The most critical part to database is to ensure your data structure matches your business facts. Building a data model and testing it with sample data and "business scenarios" can minimize a lot of trial and error.
Here's a related post.

You may find this article on search by Allen Browne helpful.

[DEFT]

Hi! Thanks for the posts/links above. I'm reviewing them currently.

In regards to the database structure, the way it is set up currently is that:

Caregiver (PK:CaregiverID)-->Patient (PK:PtID, FK:CaregiverID).
Caregiver(PK:CaregiverID)--->Calls (PK:CallID, FK:CaregiverID).
Caregiver(PK:CaregiverID)-->Sessions (PK:TrainingSessionID, FK:CaregiverID).

So 1 caregiver can only have 1 patient, but can have multiple calls and sessions. However, the patient can't necessarily "have" these calls and sessions as they are all linked back to the caregiver ID, not Patient ID or information. We are hoping to be able to search through "records" to pull up a full chart by either caregiver name or patient ID. We weren't sure if this was possible however as everything is linked to the Caregiver Profile Table, with the Primary Key being CaregiverID and the Patient Profile being a separate table, with the a CaregiverID foreign key and PtID primary key. Does this help explain the structure a little better?

[ArviLaanemets]

So 1 caregiver can only have 1 patient

So when the patient leaves, the caregiver will leave too?

When otherwise, you must have PtID in Calls and Sessions too.

[DEFT]

Somewhat....
A good way to think about it is that the caregiver is the main person of interest and the patient is essentially and extension of that caregiver. We collect patient information to tailor the intervention for the caregiver, but do not actually work with the patient. So, if for instance the patient leaves a hospital setting, then we will still only call or have sessions with the caregiver and simply put in our database that the caregiver's patient was discharged on 10/10/17 (for example). Because of this, we do not have the patient ID in calls or sessions, but instead link it all by the CaregiverID as that is who we are communicating and having the sessions with.

[Missinglinq]

Since you're dealing with patient information, you're going to have to be HIPPA compliant, and MS Access isn't the most secure of database applications. You probably should view these threads on
HIPPA Compliance using MS Access:

https://arstechnica.com/civis/viewtopic.php?t=637181

http://www.tek-tips.com/viewthread.cfm?qid=481897

https://www.pcreview.co.uk/threads/m...iance.3985559/

Linq ;0)>