Page 2 of 2 FirstFirst 12
Results 16 to 30 of 30
  1. #16
    AndyRob1973 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    81
    PS I have also posted this on another forum. I have been informed that I should declare this. Sorry, I am not a regular forum user of any kind.

    https://www.access-programmers.co.uk...schema.316290/

  2. #17
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I redrew your relationship window - it is easier for me when the flow is left to right (one-to-many)
    Click image for larger version. 

Name:	Relationship.png 
Views:	64 
Size:	37.3 KB 
ID:	44262
    You should develop a naming convention - this is what I use.
    Do not use punctuation or special characters in object names: "Attended?" and "Paid?" has the question mark in the name. Better might be: "Pt_Attended", "Is_Paid" .
    There is a field named "NHS_no". Is there a field named "NHS_yes"? Maybe you mean "NHS_Num"??


    Quote Originally Posted by AndyRob1973 View Post
    ....Once I am satisfied that this schema does will work. I need to find a way of when I add a patient to a study, that they inherit the pre-determined appointments and activities.
    Am I right in saying that this would be a series of append queries? Or could I use a form which when I add a patient to a study they automatically get populated in the ‘tbl_Patient_Study_Appointment’ and ‘tbl_Patient_Study_Appointment_Activity’....
    I would use VBA to add the pre-determined appointments and activities. Much better control on what gets entered and when.

    My $0.02......

  3. #18
    AndyRob1973 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    81
    Quote Originally Posted by ssanfu View Post
    I redrew your relationship window - it is easier for me when the flow is left to right (one-to-many)

    Click image for larger version. 

Name:	Relationship.png 
Views:	64 
Size:	37.3 KB 
ID:	44262
    You should develop a naming convention - this is what I use.
    Do not use punctuation or special characters in object names: "Attended?" and "Paid?" has the question mark in the name. Better might be: "Pt_Attended", "Is_Paid" .
    There is a field named "NHS_no". Is there a field named "NHS_yes"? Maybe you mean "NHS_Num"??



    I would use VBA to add the pre-determined appointments and activities. Much better control on what gets entered and when.

    My $0.02......


    Thank you ssanfu,

    I like your naming convention and can see how it will help. I will use that.

    With regard to the vba part of it, i wouldn't know where to start so I might well come back for some pointers if that's ok.

    Yes, you're right about the NHS_no, I meant 'NHS number'

  4. #19
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by AndyRob1973 View Post
    Thank you John,
    You are welcome Andy!

    After your last detailed post and the collected info from your threads around the internet, I’m coming back with a new suggestion.
    If you want to track schedules for patients based on medical studies, I think that the schema below is ideal for this work:

    Click image for larger version. 

Name:	schema.JPG 
Views:	55 
Size:	65.1 KB 
ID:	44309


    (For the moment, tblPayments and tblActDocuments are not using in the sample database)

    In practice...

    As you can see in the screen shot below, four appointments have been scheduled for the selected patient, based on the study about asthma, but, nine activities are missing to be congruent with the study. The last combobox in Activities section, suggest the missing activity for the Appointment1 (Day1). Clicking on the "append" symbol right of the message "Missing9!" on Appointments header, the nine missing activities become scheduled (check the relevant VBA methods in the standard code module "modApp" in attached database).

    Click image for larger version. 

Name:	form.JPG 
Views:	54 
Size:	67.6 KB 
ID:	44310

    Clicking on "preview" symbol, right on the Schedules header, you have the activities of the study and the scheduled activities for the patient in one report.

    Click image for larger version. 

Name:	report.JPG 
Views:	57 
Size:	80.9 KB 
ID:	44313

    In attachment below, you will find the implementation of my suggestion. You have a whole weekend to inspect its contents.

    MediStudies.zip

    Good luck with your project,
    John

  5. #20
    AndyRob1973 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    81
    Quote Originally Posted by accesstos View Post
    You are welcome Andy!

    After your last detailed post and the collected info from your threads around the internet, I’m coming back with a new suggestion.
    If you want to track schedules for patients based on medical studies, I think that the schema below is ideal for this work:

    Click image for larger version. 

Name:	schema.JPG 
Views:	55 
Size:	65.1 KB 
ID:	44309


    (For the moment, tblPayments and tblActDocuments are not using in the sample database)

    In practice...

    As you can see in the screen shot below, four appointments have been scheduled for the selected patient, based on the study about asthma, but, nine activities are missing to be congruent with the study. The last combobox in Activities section, suggest the missing activity for the Appointment1 (Day1). Clicking on the "append" symbol right of the message "Missing9!" on Appointments header, the nine missing activities become scheduled (check the relevant VBA methods in the standard code module "modApp" in attached database).

    Click image for larger version. 

Name:	form.JPG 
Views:	54 
Size:	67.6 KB 
ID:	44310

    Clicking on "preview" symbol, right on the Schedules header, you have the activities of the study and the scheduled activities for the patient in one report.

    Click image for larger version. 

Name:	report.JPG 
Views:	57 
Size:	80.9 KB 
ID:	44313

    In attachment below, you will find the implementation of my suggestion. You have a whole weekend to inspect its contents.

    MediStudies.zip

    Good luck with your project,
    John

    Hi John,

    Thank you very much! You've obviously put a lot of work in to this and i really appreciate it!

    I'll be having a really good look through it and even if it doesn't fully cover what I'm looking for (it's difficult to explain online), I'm sure I will learn lots from it (which is as important to me).

  6. #21
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Don't worry about that. As I said at the beginning, I found your issue interesting and the project could be applied in several cases. So, definitely, my effort will be useful for me as well.
    In the process of sharing of our ideas, we are all gainers.

    Good luck with your project,
    John

  7. #22
    AndyRob1973 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    81
    Quote Originally Posted by accesstos View Post
    Don't worry about that. As I said at the beginning, I found your issue interesting and the project could be applied in several cases. So, definitely, my effort will be useful for me as well.
    In the process of sharing of our ideas, we are all gainers.

    Good luck with your project,
    John
    hi John,

    I've started having a really good look at how your db is constructed - it seems to do everytyhing I need and more! I really want to understand the working s of it so i can adapt/mend things long term.

    So, if it's ok with you could i come back with some questions (for example the module that inserts the schedules to the patient).

    i'm looking forward to understanding it all!

    Thanks Andy

  8. #23
    AndyRob1973 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    81
    Hi John.

    I've had a really good play with the database and how it works behind the scenes. It's superb! Is it appropriate to ask specific questions on here about some of the code.....or should I message you?

    Thankyou in advance.

    Andy

  9. #24
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Hi Andy!

    Post here to get the most help that you can, but, I have to sign out at this moment. I'll be back as soon as I can.

    Have a good... Friday.

    Cheers,
    John

  10. #25
    AndyRob1973 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    81
    [QUOTE=accesstos;472519]Hi Andy!

    Post here to get the most help that you can, but, I have to sign out at this moment. I'll be back as soon as I can.

    Have a good... Friday.

    Thanks John,

    I have a few questions having looked through the database.


    1) The row source for ‘lstSchedules’ has a criteria of [patIDfk]=’2’ which pertains to one specific patient. However ‘lstSchedules’ still displays the details for any of the patients that are selected. I can’t work out how!


    Click image for larger version. 

Name:	lstSchedules.jpg 
Views:	34 
Size:	152.8 KB 
ID:	44527

    Click image for larger version. 

Name:	RowSource.jpg 
Views:	34 
Size:	16.9 KB 
ID:	44528

    2) How is the ‘sfrmSchedules’ linked to the parent form ‘frmPatients’. Am I right in saying that cboPatient_AfterUpdate() code deals with this?



    3) When I click ‘cmdAddApps’, (on ‘sfrmSchedules’) the code has a variable lngR which is derived from the function msNewScheduleAppointments(). This function uses lngSchedule, but I can’t work out where the value for lngSchedulecomes from.

    Any help would be appreciated.

    Thankyou.
    Attached Files Attached Files

  11. #26
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by AndyRob1973 View Post
    1) The row source for ‘lstSchedules’ has a criteria of [patIDfk]=’2’ which pertains to one specific patient. However ‘lstSchedules’ still displays the details for any of the patients that are selected. I can’t work out how!
    The Load event procedure of the form frmPatients calls the procedure cboPatient_AfterUpdate().
    Code:
    Private Sub Form_Load()
        cboPatient_AfterUpdate
    End Sub
    The cboPatient_AfterUpdate() procedure sets the rowsource of the lstSchedules but at the first load of form, the cboPatient is null.
    Code:
    'Fill the listbox.
        With Me!lstSchedules
            .RowSource = "SELECT [SchID], [Schedule] " _
                         & " FROM qrySchedules WHERE patIDfk=" & Nz(Me!cboPatient, 0)
    '[...]
    Obviously, some time, the form has been saved with 2th patient selected but the code above reset it to [patIDfk]=0.


    Quote Originally Posted by AndyRob1973 View Post
    2) How is the ‘sfrmSchedules’ linked to the parent form ‘frmPatients’. Am I right in saying that cboPatient_AfterUpdate() code deals with this?
    Yes, the same procedure sets the recordsource of the subform sfrmSchedules.
    Code:
    'Filter to patient's schedules.
        Me.sfrmSchedules.Form.RecordSource = "SELECT * FROM qrySchedules WHERE patIDfk=" & Nz(Me!cboPatient, 0)
    '[...]
    I could link the sfrmSchedules on cboPatient using link master/child fields but I prefer to set the recordset of the form directly, especially when I don’t need the link fields to add new records in a dependent subform. The forms loads faster and I think that reduce the flickering.

    Quote Originally Posted by AndyRob1973 View Post
    3) When I click ‘cmdAddApps’, (on ‘sfrmSchedules’) the code has a variable lngR which is derived from the function msNewScheduleAppointments(). This function uses lngSchedule, but I can’t work out where the value for lngSchedulecomes from.
    lngSchedule is an optional argument and we use it when we have to determine a specific schedule of the appointments which are going to append.
    For example:
    Code:
    lngR = msNewScheduleAppointments(Nz(Me!lstSchedules, 0))
    If lngSchedule is missing, the code skips the call of the msSetSchedule() procedure.
    Code:
        If lngSchedule > 0 Then
            'Filter the "root" query to the desired schedule.
            msSetSchedule lngSchedule
        End If
    In case of cmdAddApps_Click(), the “current” schedule has already been determined via lstSchedules_AfterUpdate() event procedure, so, there is no need to use the lngSchedule.
    The returned value of the msNewScheduleAppointments() is the count of the records that have been affected by the ‘INSERT INTO’ statement which just executed.
    Code:
        strSQL = "INSERT INTO tblSchApps (schIDfk, studAppIDfk) " _
                 & "SELECT schID, studAppID FROM qlkpSchMissApps;"
        Debug.Print strSQL
        Set db = CurrentDb
        If lngSchedule > 0 Then
            'Filter the "root" query to the desired schedule.
            msSetSchedule lngSchedule
        End If
        db.Execute strSQL
        'Return the new records.
        msNewScheduleAppointments = db.RecordsAffected
        '[...]
    Quote Originally Posted by AndyRob1973 View Post
    Any help would be appreciated.

    Thankyou.
    You are welcome!

    I hope that I've answered your questions.
    Post back if you have further questions.

    Cheers,
    John

  12. #27
    AndyRob1973 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    81
    Quote Originally Posted by accesstos View Post
    The Load event procedure of the form frmPatients calls the procedure cboPatient_AfterUpdate().
    Code:
    Private Sub Form_Load()
        cboPatient_AfterUpdate
    End Sub
    The cboPatient_AfterUpdate() procedure sets the rowsource of the lstSchedules but at the first load of form, the cboPatient is null.
    Code:
    'Fill the listbox.
        With Me!lstSchedules
            .RowSource = "SELECT [SchID], [Schedule] " _
                         & " FROM qrySchedules WHERE patIDfk=" & Nz(Me!cboPatient, 0)
    '[...]
    Obviously, some time, the form has been saved with 2th patient selected but the code above reset it to [patIDfk]=0.



    Yes, the same procedure sets the recordsource of the subform sfrmSchedules.
    Code:
    'Filter to patient's schedules.
        Me.sfrmSchedules.Form.RecordSource = "SELECT * FROM qrySchedules WHERE patIDfk=" & Nz(Me!cboPatient, 0)
    '[...]
    I could link the sfrmSchedules on cboPatient using link master/child fields but I prefer to set the recordset of the form directly, especially when I don’t need the link fields to add new records in a dependent subform. The forms loads faster and I think that reduce the flickering.


    lngSchedule is an optional argument and we use it when we have to determine a specific schedule of the appointments which are going to append.
    For example:
    Code:
    lngR = msNewScheduleAppointments(Nz(Me!lstSchedules, 0))
    If lngSchedule is missing, the code skips the call of the msSetSchedule() procedure.
    Code:
        If lngSchedule > 0 Then
            'Filter the "root" query to the desired schedule.
            msSetSchedule lngSchedule
        End If
    In case of cmdAddApps_Click(), the “current” schedule has already been determined via lstSchedules_AfterUpdate() event procedure, so, there is no need to use the lngSchedule.
    The returned value of the msNewScheduleAppointments() is the count of the records that have been affected by the ‘INSERT INTO’ statement which just executed.
    Code:
        strSQL = "INSERT INTO tblSchApps (schIDfk, studAppIDfk) " _
                 & "SELECT schID, studAppID FROM qlkpSchMissApps;"
        Debug.Print strSQL
        Set db = CurrentDb
        If lngSchedule > 0 Then
            'Filter the "root" query to the desired schedule.
            msSetSchedule lngSchedule
        End If
        db.Execute strSQL
        'Return the new records.
        msNewScheduleAppointments = db.RecordsAffected
        '[...]

    You are welcome!

    I hope that I've answered your questions.
    Post back if you have further questions.

    Cheers,
    John


    Thanks John.

    I'm in the process of going through your replies.

  13. #28
    AndyRob1973 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    81
    Hi John (or anyone),

    I've had a good play and I feel I'm starting to follow how it is all constructed and have a few more questions.

    1) When the cmdAddAps button is clicked it rums the function msNewScheduleAppointments(). I can't work out how the function knows which specific Patient on which specific study, to add the appointments for?


    2) I have added some of my own data and I've realised that the unbound appointments box below orders the appointments by the name of the appointment.

    Click image for larger version. 

Name:	List box 20210318.png 
Views:	19 
Size:	34.6 KB 
ID:	44667

    I'm trying to change the query so that it orders ascending by appIDfk but when i do this it reverts back to the original query which orders by appName. What am I doing wrong?

    Click image for larger version. 

Name:	List box query 20210318.png 
Views:	19 
Size:	12.7 KB 
ID:	44668

    Thank you.

    Andy

  14. #29
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Hi Andy!

    1) Take a look into the function msSetSchedule():
    Code:
    Function msSetSchedule(lngSchedule As Long) As String
        'Restricts  the "root" query (qlkpPatSchedule) to the desired schedule.
        'All the queries that depends on this "root" query,
        'will pull records based on this schedule.
        Dim strSQL As String
        strSQL = "SELECT * FROM tblPatSchedules " _
                 & "WHERE schID=" & lngSchedule & ";"
        CurrentDb.QueryDefs("qlkpPatSchedule").SQL = strSQL
        msSetSchedule = strSQL
    End Function
    The query qlkpPatSchedule works like an "anchor" for the queries that are linked on this and every time the schedule change, via the unbound lstSchedules of frmPatients, the SQL of qlkpPatSchedule been updated and returns the selected schedule.
    Code:
    Private Sub lstSchedules_AfterUpdate()
        'Set the "root" query (qlkpPatSchedule) on current schedule.
        msSetSchedule Nz(Me!lstSchedules, 0)
        Me!sfrmSchedules.Requery
        Me!sfrmSchedules.Form.Recordset.FindFirst "schID=" & Nz(Me!lstSchedules, 0)
    End Sub
    So, the query qlkpSchMissApps returns the missing appointments for the current schedule and the function msNewScheduleAppointments() pass its values into the table tblSchApps.
    Code:
        strSQL = "INSERT INTO tblSchApps (schIDfk, studAppIDfk) " _
                 & "SELECT schID, studAppID FROM qlkpSchMissApps;"
    [...]
    db.Execute strSQL
    [...]
    Now, I realize that the msSetSchedule() procedure has to be called by the Current event of the sfrmSchedules. So, this event procedure need to becom:
    Code:
    Private Sub Form_Current()
        'Set the "root" query (qlkpPatSchedule) on current schedule.
        msSetSchedule Nz(Me!schID, 0)
    [...]
    2) The rowsource of the lstApps, in actualy, is the query qlkpSchAppointments and the Current event of sfrmSchedules has undertake this assignment.
    Code:
    Private Sub Form_Current()
        'Set the "root" query (qlkpPatSchedule) on current schedule.
        msSetSchedule Nz(Me!schID, 0)
        With Me.lstApps
            .RowSource = "qlkpSchAppointments"
            .Value = .ItemData(0)
            .SetFocus
        End With
    [...]
    So, you have to make the tweaks in this query. Οbviously, the expression in the properties has remain by some save when the form was loaded.

    Cheers,
    John

  15. #30
    AndyRob1973 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    81
    Hi John,

    Thankyou - that makes sense. I just need to practice it a few times.

    Andy

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Patient data
    By osiroky in forum Reports
    Replies: 13
    Last Post: 01-07-2020, 09:28 AM
  2. Patient Database, linking a patient to several diseases
    By GonSantos in forum Database Design
    Replies: 11
    Last Post: 01-23-2019, 01:57 PM
  3. Patient Report printing
    By vijay in forum Reports
    Replies: 2
    Last Post: 05-09-2014, 05:33 AM
  4. Patient database
    By Addanny in forum Access
    Replies: 4
    Last Post: 10-02-2013, 06:59 PM
  5. Patient Database
    By labadee in forum Database Design
    Replies: 1
    Last Post: 06-27-2010, 10:53 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums