Display all Records in a subform

[mloucel]

Hello ALL.

I have a new request from my boss, and I can't solve it, here is why:

The form I am referring is called from a search form [ALL FORMS ARE BOUND TO A TABLE]

Search Form is bound to the PatientID since I need to find the patient first in order to create a new Authorization [or many]

once I find the Patient, I open the Authorization Form [Bound to the Authorization Table, MK is AuthorizationID and FK is PatientID]
I only need to display the name and 2 more fields from the PatientsT, so that the EU can see who the patient is.

Everything is still working fine but now they want to display ALL the Authorizations for that specific patient.

The Authorizations form is created so that the EU can ADD as many authorizations to a specific patient, hence bound to the authorizationsID and NOT the PatientID.
this works perfectly, and I can ADD as many as the EU wants no problem.

But now the boss wants to see how all the authorizations this patient has had in the past, so naturally I created a query [qryAuthPerPatient], where I Add all the tables that shows basic info so that the EU can see enough info to identify all the OLD authorizations.

To do that I need to check the PatientID and in that field [in the query] I add the following code:

[Forms]![AddAuthorizationsF]![PatID1]

when I test the query, it works. [qryAuthPerPatient]

then I created the form bound to the query, to test I run it enter a PatientID, and the form displays correctly ALL the Authorizations for that specific patient, so no problem so far.

now I do what I was taught to do: Pick the form and paste [probably wrong word] and then the problem comes.

I can see the Link Master Fields and Child Master Fields linked to The AuthorizationID, and when my form displays the SF is BLANK there is nothing just the headers display, so I thought maybe the LMC and CMF are incorrect, so I changed them to PatientID, but of course this does not work as well, I am confused and I don't know how to fix this, any help will be appreciated.

I created a test DB, with enough data to test,

Users table is encrypted so:
User Name: SU
Password: 1234

the DB [FE and BE] are a bit BIG and I can't load them here, so I had to create a link to my Google Drive if you want to see issue:

Maurice Problem

Enter The MENU, then Go To AUTHORIZATIONS, then choose ADD Auth

So, you can test USE Patient with ID 12 [Arriag Matthew] is another test with 4 authorizations, ALL OF THAT INFO IS FAKE...

And to top it off, she now wants that after a new authorization has entered, to be able to show that in the "BOX" [her words] of OLD authorizations, I may have an idea on how to do that, but suggestions will be appreciated.

Maurice.

 

[plog]

Your Authorization Form needs to be a main form and a subform.

The main form should be based on your Patient table. This will allow you to display all info you need about the patient. Then the bottom should be a subform based on your Authorization table and linked to the main form via Patient ID. That way it shows all the patients authorizations and allows you to enter new ones.

Create a form that contains a subform (a one-to-many form) - Microsoft Support

Learn how to create a form in Access that contains a subform (also known as a one-to-many form, a parent/child form, or a hierarchical form).

support.microsoft.com

This new query you built is unneccesary. Your forms can still be based on your tables and the master/child linking of the forms will take care of the filtering for you.

 

[mloucel]

Your Authorization Form needs to be a main form and a subform.

The main form should be based on your Patient table. This will allow you to display all info you need about the patient. Then the bottom should be a subform based on your Authorization table and linked to the main form via Patient ID. That way it shows all the patients authorizations and allows you to enter new ones.

Create a form that contains a subform (a one-to-many form) - Microsoft Support

Learn how to create a form in Access that contains a subform (also known as a one-to-many form, a parent/child form, or a hierarchical form).

support.microsoft.com

This new query you built is unneccesary. Your forms can still be based on your tables and the master/child linking of the forms will take care of the filtering for you.

Sorry can't, the main form is bound to the Authorization table, there is where the data is saved, not the patient, patient data saved on the authorizations table is only the ID.

I really appreciate your input.

 

[plog]

Not to be harsh, but that response indicates you don't understand my solution. You've missed the forest for the trees. Don't get hung up on 'main form' 'sub form' designations. Those are just technical descriptions. My solution does what you want, here it is again:

The main form is based on the patient table. The subform is based on the authorization table. When you need to update authorization data you use the subform to do so and it all goes into the authorization table like you want.

 

[mloucel]

Not to be harsh, but that response indicates you don't understand my solution. You've missed the forest for the trees. Don't get hung up on 'main form' 'sub form' designations. Those are just technical descriptions. My solution does what you want, here it is again:

The main form is based on the patient table. The subform is based on the authorization table. When you need to update authorization data you use the subform to do so and it all goes into the authorization table like you want.

I guess I have no choice but to re-do all I had, is going to be a lesson learned then, this is going to hurt....

 

[plog]

The Authorizations form is created so that the EU can ADD as many authorizations to a specific patient, hence bound to the authorizationsID and NOT the PatientID.
this works perfectly, and I can ADD as many as the EU wants no problem.

I don't know if its a complete rework. Is the current authorizations form a single entry or continous form? When the user adds multiple new records do those remain on the page so they can see them (continuous form) or does the prior entry go away once a new one is started (single entry)?

If its already a continous form, you simply make that existing form the subform. You would create the main form based on the patient table (which won't be too complex since its just a couple of fields) and add that existing form to it as a subform.

 

[Mark_]

I guess I have no choice but to re-do all I had, is going to be a lesson learned then, this is going to hurt....

Do a search on "Subform". Rather straight forward.

 

[mloucel]

Thank you, guys, I have to explain something.
What looks so obvious to you, is extremely complex to me.
I am a newbie in ACCESS I have programmed before, 30 years ago, in a different language, Cobol, FoxBase..

I Have been learning Access full time for about 8 months to 1 year, before it was just here and there a small project or a report, nothing as FANCY [at least to me] as this, this project is for me so Advance, that any changes are major catastrophic results since 50% of the time I have to go back to the original and go over and over all the code, most code is mine but a huge part and some of the most complicated has been donated graciously by other members in this forums.

This is going to be one of the last steps for me, and seems like my luck is running out, boss is a bit mad with the Pss word, with me, and I can see myself out the door.

I honestly have no Idea how to fix my own mess, I'm about to ask for big help anywhere I can find it, yes I am that desperate.

 

[mloucel]

Do a search on "Subform". Rather straight forward.

I've done a few, but this one in particular is slightly a bit so developed, and the first version is already on production, this are just the latest changes that my boss just came up with.
Literally I'm toast.

 

[Mark_]

It sounds like your boss is a bigger issue than Access. I'm guessing they are the "Why can't you just have it done in 5 minutes?" kind.

As for programming with Access, one of the hardest switches is getting use to how much Access will do for you if you use the tool the way it is structured. If you are use to a language that forces you to set up all of your event handling, fetching the right data, and write how to do updates, you feel like you are not "doing enough". That is by intent. You should only be coding for exceptions.

Also check with your boss about what kind of sorting / filtering they want to do on appointments. Get as much specified before you start as possible. It becomes a headache when they add in something they consider "minor" that requires a complete rework.