Access Based Media Manager

[BlueSpruce]

So this is an application that the healthcare patients use - they supply the docs (through scanning, attaching), and review and then may send those to a medical record - is that external? If so the process for accepting such records may/will need stringent verification/validation, which may affect the data needed in your application.
Re the schema - this appears to be a simple star schema - optimal for reporting purposes, however, given the input side of the coin, there is no facility indicated/ needed (as far as data is concerned) to manage the patient details beyond the most basic, like who is doing the scanning (user management)?
Reflecting on the combining the dimensional data into a single table - yes - however, do you envisage any dimensions that change - slowly changing dimensions, in reporting databases, support date ranges in which values are valid - this may not apply to your proposal. I would be very surprised if all the dimensions a single attributes - but I may not be understanding what you want. eg Provider - is this simply the name of the Dr. What role does the Dr have - the referring Dr, the specialist? What is Patientclass?, Can a document belong to multiple types? eg An x-ray, and raw/png/jpeg ... and can documents be related to each other - eg the x-ray image is linked to the report by the cardiologist?
Also depending on how you expect/envisage users being able to add additional classification values, if you simply follow @MarkK 's suggestion, then you either lose control of the allowable values (users type in values they use - and then standardisation is lost) or the list is not updateable (except by allowing users to add to the list of accepted values themselves in design view - leading to the same loss of standardisation), however I'm sure you are across those issues.

This is a tool for patients to organize all their medical records so they can easily gather and supply relevant records to providers who request them upon demand. Providers are organizations within the same network, such as affiliated clinics and hospitals that use the same EHR (Electronic Health Records) system. Departments are medical specialties, like Cardiology, Primary Care, and Attending are the clinicians, such as Doctors, Nurses, Techs. This tool is most useful when providers don't have access to relevant records because they originate from outside the providers network. Example: Providers who use Cerner EHR cannot access records stored in Epic, AthenaHealth, and other EHR systems because there's no interoperability between these EHR's, or the provider network restricted access to only within their organization. In the past, providers requested records from outside providers via fax, but now they're asking patients to supply them for upcoming visits, or upon demand.

I am almost done developing and testing the tool, and will soon post some screenshots,

 

[GaP42]

Cerner EHR cannot access records stored in Epic, AthenaHealth, and other EHR systems because there's no interoperability between these EHR's, or the provider network restricted access to only within their organization.

Yes, the sensitivities of personal health information (and consequent regulation), as well as the breadth and depth of health data makes interoperability extremely complex. While your application is simply to provide a (set) of health documents, they remain outside the commercial EHR application. HL7 is a widely used protocol that is employed to support exchange of data in health apps (however usually only within the bounds of the defined organisation). IIRC there has been work done within HL7 to define the exchange of structured medical docs (like hosptial discharge summaries). A genuine EHR scope extends beyond the bounds of a commercial app implemented within an organisation - it has to be able to support (capture, store (or point to) and present) the full set of health events over the lifespan of the individual - no matter what, where or when those health events occurred.

 

[BlueSpruce]

Here's screenshots of my version 4. It has some kinks and I have to add more functionality. Work In Progress.

 

[BlueSpruce]

I am planning to add a Category field so users can add other type of documents not related to healthcare, such as Credentials, Finance. Does anyone have suggestions for improving this application?

 

[DHookom]

You already have a document type field. I’m not sure if you could simply categorize the document types.

 

[GaP42]

Can documents be categorised as both medical and financial or ... ? Is a medical certificate/ health fund membership a credential? As this is a patient populated record of documents, the categorization is probably best focused on simplicity.

 

[BlueSpruce]

You already have a document type field. I’m not sure if you could simply categorize the document types.

Can documents be categorised as both medical and financial or ... ? Is a medical certificate/ health fund membership a credential? As this is a patient populated record of documents, the categorization is probably best focused on simplicity.

I initially designed the app for Healthcare documents only, and now I want to extend the app to also catalog e.g. Financial documents, important personal documents, such as birth/marriage/death certificates, identification credentials, etc. Hence, after selecting the person from the combo dropdown, user selects Category, (Healthcare, Financial, Personal, . . .) and the lower combo boxes cascade filtering the values according to the selected category. The control captions will also change according to selected category. Some values may overlap from one category to others, e.g. Power of Attorney can be both in Healthcare and Personal Document categories. A Father, Mother, and Children could see the same primary care physician.

 

[GaP42]

How would "Date of service" relate to some of these doc types? Effective date? Date of document creation, Date registered in application?
And how would you want to handle update / replacements eg wills, advanced care directives. Do you wish to be able to enable a master document to have related sub documents?
A further problem is the documents are located potentially anywhere - and sometimes the user re-arranges / deletes documents, so the links no longer work - user beware. While it may not be possible to prevent external manipulation of the files, and your app can copy the documents to your designated folders specific to your application, do you need an in-application facility to enable the users to migrate storage of files?

 

[BlueSpruce]

How would "Date of service" relate to some of these doc types? Effective date? Date of document creation, Date registered in application?

With multiple categories, the easiest would be to implement generic labels, e.g. just "Date", but I will create a lookup table for the control captions, e.g. for healthcare category, the date control caption would be "Date of Service", for Financial and Personal categories, it would make sense to have IssueDate and ExpiryDate. The app would need to be generic enough to support multiple categories, where some fields would not apply in certain categories.

how would you want to handle update / replacements eg wills, advanced care directives. Do you wish to be able to enable a master document to have related sub documents?

Yes, I thought about creating a child history table for storing older versions of a document . In my other apps I use history tables to store e.g. name changes, address updates, essentially any volatile data that's relevant. I store timestamped snapshots of entire records anytime a change is made to one more values, including who updated the record. However, since my app prefixes YMD dates to each filename, and by default Windows can store multiple copies of the same file, users could leverage that and create one record for each version of the same file.

A further problem is the documents are located potentially anywhere - and sometimes the user re-arranges / deletes documents, so the links no longer work - user beware. While it may not be possible to prevent external manipulation of the files, and your app can copy the documents to your designated folders specific to your application, do you need an in-application facility to enable the users to migrate storage of files?

I coded error trapping for when users rename, move, or delete a Windows file and the app can no longer open it. Users can currently edit, delete, and readd a record. Pressing the Get/Open button in the Add and Update forms opens the explorer file picker, users can browse other folders until they locate the correct document they moved, renamed, and update the path in the record. The app also gives users a choice to leave the name "as is", or rename it according to the values selected in the combo boxes.

 

[BlueSpruce]

Attached is the latest version of "My Healthcare Documents" app. When adding a new record, it started throwing a "Path/File access" error when trying to rename a file in the C drive root directory, whereas before that worked. I haven't made any changes to Windows, e.g. updates, permissions, etc.

 

[DHookom]

Some environments don’t allow access to files in the root directory of C.

 

[BlueSpruce]

Some environments don’t allow access to files in the root directory.

In Windows, I was able to paste a pdf into root and rename it. So I don't know why Access couldn't do the same.

I modded the attached app, in post# 30, to open the file picker in USERPROFILE\Documents using the code below that's behind the "GetFile" button.

Users can still change directory with the file picker, including to root.

After users pick a file, the app opens the file with the tool associated with the select file's type. In my case, my web browser opens all pdf files.

the user can then review the document to determine what values he wants to select from the combo boxes to catalog the file and these values are used in renaming the file, if users mark the rename file checkbox.

Private Sub cmdGetFile_Click()

 Dim fDialog As Object
 Dim varFile As Variant
 Dim S As String
 
   'Set up the File Dialog.
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    fDialog.InitialFileName = Environ("USERPROFILE") & "\Documents\"
 
    fDialog.AllowMultiSelect = False
 
   'Show the dialog box. If the .Show method returns True, the
    'user picked at least one file. If the .Show method returns
    'False, the user clicked Cancel.
    If fDialog.Show = True Then
        SelectedFileName = fDialog.SelectedItems(1)
    Else
       MsgBox "You clicked Cancel in the file dialog box."
 
    End If

'used to open file
    If Not IsNull(Me.SelectedFileName) Then
      Call OpenFileBringToFront(Me.SelectedFileName)
    End If
 
End Sub

This code behind the Save button saves the renamed file in the same path it got it from:

Private Sub cmdSave_Click()

    Dim strPath As String
    Dim strOldName, strNewName As String
 
    DoCmd.RunCommand acCmdSaveRecord
 
    If IsNull(Me.SelectedFileName) Then
        MsgBox ("File Name required")
        Me.SelectedFileName.SetFocus
        Exit Sub
    End If
 
    DoCmd.SetWarnings False
 
    If Me.chkRename = True Then
 
        DoCmd.OpenQuery "qryAppendNewRecord"
 
        strPath = Left(Me.SelectedFileName, InStrRev(Me.SelectedFileName, "\"))
 
        strOldName = Me.SelectedFileName
        strNewName = Me.NewName
 
        Name strOldName As strNewName
 
    Else
 
        Me.NewName = Me.SelectedFileName
        Me.FileNameOnly = Mid([NewName], InStrRev([NewName], "\") + 1)
 
        DoCmd.OpenQuery "qryAppendNewRecord"
 
        strPath = Left(Me.SelectedFileName, InStrRev(Me.SelectedFileName, "\"))
 
        strOldName = Me.SelectedFileName
        strNewName = Me.NewName
 
        Name strOldName As strNewName
 
    End If
 
    DoCmd.SetWarnings True
 
    Forms!frmfinddocuments.Requery
    Forms!frmfinddocuments!frmFindDocumentsSubDatasheet.Form.Requery
 
 
    DoCmd.Close acForm, "frmPopupAddDocument"
 
End Sub

 

[BlueSpruce]

Well, it looks like there's no interest in this document management app I built. I'm starting to feel like Volcanics with his unsplit Access app designs. My app is mainly meant for one person to maintain their own healthcare documents. However, it can maintain more than one persons documents, e.g. several family members living in the same household. The only reason I would feel compelled to split the app into FE and BE is if it were a multiuser application, and to make my life easier when distributing frontend updates. I envision the head of household maintaing each household member's documents on one PC, and not each family member maintaining their own records in a multiuser LAN based setup. I think Volcanics envisions his Proposals Management and Personal Finance apps as being single user, and wants to make it as simple possible for the users by not splitting his apps up. Your thoughts?

 

[GaP42]

Referring to the doc management app you built @BlueSpruce these are some observations that you might want to consider

On checking out the application here are a few observations/criticisms and personal recommendations:
1.    Schema: the Document table has two fields: DocumentStorageLocation and DocumentFileName. The Document storage location is the full path including filename. DocumentFileName is therefore holding redundant data.
2.    Not sure I understand the practical use of the Auto Rename File option: clearly the function works, and the name is adjusted to include a number of attributes entered for the record into the filename. However, it cannot be personalised (except external to the app). The application – given the full path in DocumentStorageLocation - does not require the actual name to be changed, and the DocumentFileName itself is NOT used or parsed, in any in-app process to locate the record.

However these documents may have been opened in other apps, and so renaming will invalidate the item in any recently opened list.

Perhaps copy the doc to a dedicated folder for storing such health docs.
And/or Add a field for a simplified document name (editable – without altering the filename) (re-purpose DocumentFileName?). This can help overcome some of the issues with receiving docs that do not have an inherent meaning to the user – such as a health claim number, or consisting of the patient number at a health facility and the VisitID. They are significant to the institution. So keep the name, just have a field to allow the user to give it a name meaningful to them: CGP102123344 becomes “Claim for MRI Aug 2024” or SHV354082-41095 becomes “Discharge Notes for Broken Leg – Dec2023”.

3.    Find Documents form
a.    Button Glucose/ Weight/ BP Log has no associated function – and out of scope of your application?
b.    Print Selected – no functionality
c.    Send Selected - no functionality
d.    Dates: would it be helpful to support double-click to insert the current date in date fields?
e.    Get Results with no criteria lists everything – perhaps if no criteria supplied then do not list / present message
f.    PatientNames in the combo list are not presented alphabetically
g.    DocumentType is a longish list: however the list is not alphabetical
h.    Provider list is not alphabetical
i.    Department is not alphabetical
j.    Attending is not alphabetical
k.    Keywords – cannot enter text in the either keyword control (or rather the popup to add/edit the list is not appropriate). If the intent is to support searching for a number of keywords (in either keyword1 or keyword2) then an OR and an AND option is required.

l.    The search result datasheet, as read-only should not present data as editable – combos etc should not be shown

Form: Add Document
1.    Date of Service – display is not wide enough to display all dates
2.    Patient Name combo – is not alphabetical
3.    Provider – is not alphabetical
4.    Department – not alphabetical
5.    Attending - not alphabetical

Form: Edit Document
1.    Date of Service – display is not wide enough to display all dates
2.    Patient Name combo – is not alphabetical
3.    Provider – is not alphabetical
4.    Department – not alphabetical
5.    Attending - not alphabetical
CreateDate – not populated. Perhaps use Now() on create and update of records
CreateUserFKID – not populated – as no login is used/ no user table there is no FKID – perhaps user environ(“username”) call to populate – on create and update of records.

Spelling: Department list > Hepatology not Heapatology

While there are no issues arising from the omission, I am suprised the Option Explicit is not set.

Error handling?
Duplicates can be entered in the lookup tables. Data validation on Before Update event?
When adding an item through the Add Item Pop Up, if the user changes the item’s value (not the shortname) from what was entered on the previous form an error message will appear:
Runtime error 2450: .. cannot find the referenced form “frmPopUpAddDocument”
On line : Forms!frmPopUpAddDocument!cmbDepartment = intID

I understand that some of these would be dealt with as you polish and tidy up the app. Some elements of presentation could be worked on : form field widths / consistency. Hope that this helps.

Re the splitting of the FE and BE even if installed on a home PC can mean that you have a less complex effort if you only need to provide a FE update, otherwise all updates require data migration routines. It may be simple with the application as shown, if static , however even your application hints at additional future additions? (although they are would require adding new tables and functions).

 

[Imb]

Well, it looks like there's no interest in this document management app I built.

First of all, I would ALWAYS split the database in FE and BE, because modifications of the FE will come sooner (or later). The user does not even know/care that the database is splitted.

I was interested in your application, not so much the code, but the systematics. I always compare that with my own systematics. But I think there is a too much difference between them.
The largest difference is that I use a complete different binding concept, where all control-dependant functionality is placed outside the form. Now it is possible to automate/generalize underlying processes. In fact, the whole process-flow is already build-in in any of my applications.

 

[BlueSpruce]

I understand that some of these would be dealt with as you polish and tidy up the app. Some elements of presentation could be worked on : form field widths / consistency. Hope that this helps.

Thanks for the testing and feedback. Yes, this project is in the Work_In_Progress stage, and all you mentioned will be resolved.

As for splitting the app, I agree splitting is preferred, even if it's a single user app, to simplify feature updates and because unsplit apps are more prone to experience corruption issues.

The largest difference is that I use a complete different binding concept, where all control-dependant functionality is placed outside the form.

In all my apps I have the habit of using temp tables when appending and editing records.