Help with control source not feeding subform table

[lizcliff]

Hi

ive had to pull together a booking database urgently with minimal skills; so we can get patients in ASAP due to our PAS system being replaced we cannot add any new clinic slots and patients are desperate to come in.
So ive created a mini PAS system in access, to pick patients from a waitlist, give them a date, book them in and out, then add them to a follow up queue, and give them a follow up date, and book them in and out, and they could have multpile visits.

ive become stuck on the control source being my search from the waitlist, the staff need the hosptal number plus referral date to identify them, so ive based my search on this, but i need this to populate in each subform /table to make the pathways flow. i can get it all flowing until i cannot select on the last form booking them in for followup due to the previous table not having the patient number and referral date

ive replaced my patient waitlist with a dummy dataset due to patient identifiable info.

ive got myself completely lost in all this and only had 48 hours to come up with something ( they did want it on paper!!!!...NHS for you and the sheer volumne of pateints that need to come in due to covid is scary; doesnt help when we are going through a PAS replacement sysyem!

any help with my attached attempt, will be greatly received
ive created a home page with simple buttons on for secretarys to use, it follows in order of the pathway first blue box, then 2nd, then first orange box then 2nd.
just need a way to record patients coming in off the waitlist, being seen, being followed up and added to a queue, and then seen again - just done want to lose anyone this needs to be robust as it can be. hopefully im not far off what ive set up ???

thank you
liz x

 

[moke123]

I'm not sure I follow what it is your doing but a few things seem to pop out at me. It looks to me like you may have a normalization problem.
You are using identical tables to store information such as new or followup. You should have one table with a field to distinquish whether it is new or follow up. Also repeating fields like procedure codes. Another problem is your Search field. There is no need to combine information to make a field. Each piece of information should be in its own field. You can later combine them in a query if needed.
Another issue are words like type and new are reserved words and should not be used as field names.

Take a look at the medical data models for some ideas . . . http://www.databaseanswers.org/data_models/

 

[lizcliff]

ive managed to get it all working putting one patient thtrough each form - works perfect, but when i do it again for another patient the 3rd form ID from the child doenst popualte the table so there is no link. how can the child and master now be affects, is it the control using a query that refrences all 3 tables, but i only have the patient detail in table 1 so ive had to search like this

 

[bastanu]

Please have a look at this updated file. Review each of the forms as I made quite a few changes to the main form record source (made them unbound), master\child linking, etc.

Good luck with your project!
Cheers,

 

[lizcliff]

this is absolutely amazing! thank you so much.
i think this could work, it is only in the interim so we can just get the poor patients in
i may come stuck if a patient requires more than one followup but the odds in 12 weeks will be slim.

Absolute star; i crumbled over this friday and was about to give up - thank you
liz x

 

[bastanu]

You're very welcome Liz. I think it should handle multiple follow ups, just make sure you open the right one (the subform will show the number of records, click the new record to add one).
Cheers,