Solved Display all Records in a subform (1 Viewer)

mloucel

Member
Local time
Yesterday, 18:37
Joined
Aug 5, 2020
Messages
356
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:
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.
 
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.


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.
 
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.


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.
 
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.
 
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....
 
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.
 
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.
 
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.
 
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.
 
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.
 
First, you have relationships that are causing problems and can be eliminated because they are unnecessary and in one case, duplicated. See below:
1738772827188.png

You don't need the DoctorT because that information is already in the SpecialistT. You don't need the SpecialityID Foreign Key in the AuthorizationT because it already has SpecialistID.

I am attempting to understand this application. I have downloaded it and been able to connect the FE and BE files. Your forms and sub-forms are a jumbled mess with some fields missing which cause new records not being able to be added, especially in the AuthorizationT because you have so many Foreign Keys that must be filled in for new records.

Over the next few days, I will attempt to make it so you are able to view all authorizations for each patient. But don't hold your breath. I don't know where you obtained all the code you have, but I have had many problems opening forms so I disabled most of the forms OnLoad event procedures so I could at least open them.

I'll try to make sense of it and attach the files (zipped) if I can find a solution.
 
First, you have relationships that are causing problems and can be eliminated because they are unnecessary and in one case, duplicated. See below:
View attachment 118414
You don't need the DoctorT because that information is already in the SpecialistT. You don't need the SpecialityID Foreign Key in the AuthorizationT because it already has SpecialistID.

I am attempting to understand this application. I have downloaded it and been able to connect the FE and BE files. Your forms and sub-forms are a jumbled mess with some fields missing which cause new records not being able to be added, especially in the AuthorizationT because you have so many Foreign Keys that must be filled in for new records.

Over the next few days, I will attempt to make it so you are able to view all authorizations for each patient. But don't hold your breath. I don't know where you obtained all the code you have, but I have had many problems opening forms so I disabled most of the forms OnLoad event procedures so I could at least open them.

I'll try to make sense of it and attach the files (zipped) if I can find a solution.
Good Morning Larry, those 2 doctors are different ones, DoctorT is the table that holds the info for OUR doctors or PA's, the specialists are the doctors we send our patients for different needs:
OUR Doctor Saul [DoctorT], send Patient with ID 12, to a Radiologist, say #15 [Centrelake] but also Same patient goes as well to #7 [GI] to Doctor Ethelred [[#14], so OUR doctor send patients to a specialty which can have 2 or 3 or more doctors or places, the specialty is unique, 1 to many, 1 specialty to many doctors [Specialists] with that specialty.

Thanks yesterday we had a serious talk with my boss, and I really need help, so thanks for whatever you can do.
If you need to ask me directly anything you can email me directly, I'll send you a chat with my info.
 
All I did was:
  1. Open the AddAuthorizationF form
  2. Select the AuthPerPatientF sub-form
  3. Change the Master Link and Child Link to PatientID
1738781563297.png

You had it linked to itself.
You have WAAAAAAAAY too many forms and sub-forms. You probably need only 5 or 6 or so but it should work if you just make that change.
 
All I did was:
  1. Open the AddAuthorizationF form
  2. Select the AuthPerPatientF sub-form
  3. Change the Master Link and Child Link to PatientID
View attachment 118416
You had it linked to itself.
You have WAAAAAAAAY too many forms and sub-forms. You probably need only 5 or 6 or so but it should work if you just make that change.
I promise for the life of my mother and father that I tried that already, I swear.. Read my post #1, just after the code, and you will see I DID that. and it didn't work, now I did it again and is working just like I thought it was going to work.
Like Popeye's said..

WELL BLOW ME DOWN!!!!!!

I cannot explain that, at all, honestly, I cannot, I just know is working.

Thanks are just in place, but why I couldn't do it, when I tried the first time, I cannot understand, nor probably will ever do.

Can you please advise me on the app, please email me, I will explain my vision for this app better, and I know you can guide me to better coding and understand on how access really works.

Maurice.
 
I promise for the life of my mother and father that I tried that already, I swear.. Read my post #1, just after the code, and you will see I DID that. and it didn't work, now I did it again and is working just like I thought it was going to work.
Like Popeye's said..

WELL BLOW ME DOWN!!!!!!

I cannot explain that, at all, honestly, I cannot, I just know is working.

Thanks are just in place, but why I couldn't do it, when I tried the first time, I cannot understand, nor probably will ever do.

Can you please advise me on the app, please email me, I will explain my vision for this app better, and I know you can guide me to better coding and understand on how access really works.

Maurice.
I don't know either. Maybe you did change the Master and Child but then didn't save the form. Just guessing.
 

Users who are viewing this thread

Back
Top Bottom