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/
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/
I redrew your relationship window - it is easier for me when the flow is left to right (one-to-many)
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.....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’....
My $0.02......
I redrew your relationship window - it is easier for me when the flow is left to right (one-to-many)
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'
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:
(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).
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.
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
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:
(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).
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.
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).
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,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
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
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
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
[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!
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.
The Load event procedure of the form frmPatients calls the procedure cboPatient_AfterUpdate().
The cboPatient_AfterUpdate() procedure sets the rowsource of the lstSchedules but at the first load of form, the cboPatient is null.Code:Private Sub Form_Load() cboPatient_AfterUpdate End Sub
Obviously, some time, the form has been saved with 2th patient selected but the code above reset it to [patIDfk]=0.Code:'Fill the listbox. With Me!lstSchedules .RowSource = "SELECT [SchID], [Schedule] " _ & " FROM qrySchedules WHERE patIDfk=" & Nz(Me!cboPatient, 0) '[...]
Yes, the same procedure sets the recordsource of the subform sfrmSchedules.
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.Code:'Filter to patient's schedules. Me.sfrmSchedules.Form.RecordSource = "SELECT * FROM qrySchedules WHERE patIDfk=" & Nz(Me!cboPatient, 0) '[...]
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:If lngSchedule is missing, the code skips the call of the msSetSchedule() procedure.Code:lngR = msNewScheduleAppointments(Nz(Me!lstSchedules, 0))
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.Code:If lngSchedule > 0 Then 'Filter the "root" query to the desired schedule. msSetSchedule lngSchedule End If
The returned value of the msNewScheduleAppointments() is the count of the records that have been affected by the ‘INSERT INTO’ statement which just executed.
You are welcome!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 '[...]
I hope that I've answered your questions.
Post back if you have further questions.
Cheers,
John
The Load event procedure of the form frmPatients calls the procedure cboPatient_AfterUpdate().
The cboPatient_AfterUpdate() procedure sets the rowsource of the lstSchedules but at the first load of form, the cboPatient is null.Code:Private Sub Form_Load() cboPatient_AfterUpdate End Sub
Obviously, some time, the form has been saved with 2th patient selected but the code above reset it to [patIDfk]=0.Code:'Fill the listbox. With Me!lstSchedules .RowSource = "SELECT [SchID], [Schedule] " _ & " FROM qrySchedules WHERE patIDfk=" & Nz(Me!cboPatient, 0) '[...]
Yes, the same procedure sets the recordsource of the subform sfrmSchedules.
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.Code:'Filter to patient's schedules. Me.sfrmSchedules.Form.RecordSource = "SELECT * FROM qrySchedules WHERE patIDfk=" & Nz(Me!cboPatient, 0) '[...]
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:If lngSchedule is missing, the code skips the call of the msSetSchedule() procedure.Code:lngR = msNewScheduleAppointments(Nz(Me!lstSchedules, 0))
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.Code:If lngSchedule > 0 Then 'Filter the "root" query to the desired schedule. msSetSchedule lngSchedule End If
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.
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.
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?
Thank you.
Andy
Hi Andy!
1) Take a look into the function msSetSchedule():
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: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
So, the query qlkpSchMissApps returns the missing appointments for the current schedule and the function msNewScheduleAppointments() pass its values into the table tblSchApps.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
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:strSQL = "INSERT INTO tblSchApps (schIDfk, studAppIDfk) " _ & "SELECT schID, studAppID FROM qlkpSchMissApps;" [...] db.Execute strSQL [...]
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) [...]
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.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 [...]
Cheers,
John
Hi John,
Thankyou - that makes sense. I just need to practice it a few times.
Andy