Designing a continuous form for entering score data

[twinnyfo]

Post #29, your text PK does not work becaues hte code is looking for a numerical value.

My schema was based on ONE NHS Number having multiple entries in tblAdmissions. Which required the Index, SGNumber and Date (which wsa all my misunderstandin g of your schema). In the new, normalized table, you just need the Index to tblAdmissions, but then we still need.

The purpose of showing you the filter is the only reason we had the assessments form have any fields on it. This was just to demonstrate what we were doing--PLUS, it was easier to use Me.txtAdmission Date for the Baseline. BUT, this is absolutely not necessary.

If we only have a blank for with nothing on it but a Combo box (form is unbound and does NOT have a record source), then just select a name from teh Combo Box. You would be selecting a SG Number (which is unique, but the Combo box would also have the index associated with the SG Number). Then pull up the assessment form based on the index and Date (because the assessments table could have the same Admissions Index but multiple dates.

We would have to slightly modify our code behind the buttons:

Option Compare Database
Option Explicit

Private Sub cmdBaseline_Click()
    Dim strSQL As String
    strSQL = "INSERT INTO tbl_HoNOS_Assessments " & _
        "( Patient, AssessmentDate, ItemName ) " & _
        "SELECT tbl_Admissions.PatientID, " & _
            "tbl_Admissions.AdmissionDate, " & _
            "tbl_HoNOS_Items.ItemKey " & _
        "FROM tbl_Admissions, tbl_HoNOS_Items " & _
        "WHERE tbl_Admissions.PatientID = " & Me.cboPatientID & ";"
    DoCmd.RunSQL strSQL
    DoCmd.OpenForm "frmAddAssessment", , , _
        "Patient = " & Me.cboPatientID & _
        " AND AssessmentDate = #" & _
            DLookup("[AdmissionDate]", _
                "tbl_Admissions", _
                "[PatientID] = " & Me.cboPatientID) & "#"
End Sub
Private Sub cmdFollowup_Click()
    Dim strSQL As String
    strSQL = "INSERT INTO tbl_HoNOS_Assessments " & _
        "( Patient, AssessmentDate, ItemName ) " & _
        "SELECT tbl_Admissions.PatientID, #" & _
            Date & "# AS AssessDate, " & _
            "tbl_HoNOS_Items.ItemKey " & _
        "FROM tbl_Admissions, tbl_HoNOS_Items " & _
        "WHERE tbl_Admissions.PatientID = " & Me.cboPatientID & ";"
    DoCmd.RunSQL strSQL
    DoCmd.OpenForm "frmAddAssessment", , , _
        "Patient = " & Me.cboPatientID & _
        " AND AssessmentDate = #" & Date & "#"
End Sub

NB changes to the SQL statement, as well as Lines 14-19, since we no longer have a text box for the PatientID or the Assessment Date.

[Hargo]

Thanks twinny

doesn't

You would be selecting a SG Number (which is unique, but the Combo box would also have the index associated with the SG Number).

mean I still have to add an INDEX to Admissions AND make it the Primary Key?

This is my problem!!

I understand why I should have one in future but to add one now would mean changing ALL my tables/ relationships / form controls / code which sets focus to Patient ID!!!!

Is there NO WAY I can get the user to type an SG number into frmAddAssessmen t (first unlocking the controls that are there already) then add all the ratings and finally press a button which INSERTS the 19 records into tbl_HoNOS_Asses sments?

I'm concerned that the Record Source for this form is a small query, is that the problem getting this form to do ALL the work?

[twinnyfo]

Hargo,

I can't MAKE you add an index to your Admissions Table. I can advise you that putting in the extra work to normalize your tables is worth it.

Based on some of your earlier posts/attachments, it looks like you have 10 tables, six queries, 12 Forms. I have done reindexing for a DB that has 100 tables, 200 forms, etc. Yes, it is a pain. I've been there, I've done that.

If you choose not to normalize your data, then just use the SGNumber as your PK (as we have discussed in the past).

To filter, instead of

"PatientID = " & cboPatientID

Use

"PatientID = '" & cboPatientID & "'"

Because your PK is text.

You just have to make sure that all your tables and relationships have a Text PK/FK and that the fields are similar format.

Yes, essentially someone is just selecting the SG Number and then entering the assessment--I thought that was what I was guiding you to.

However, just keep in mind that any of the Access experts here will all advise you to normalize your tables. Feel free to browse the Access Forums and see how many times a poster will ask for advice or help on an issue and one of the first responses we have is, "First, you need to normalize your data." This is not an issue of personal preference, it is an issue of particing good DB principles, which is one of the reasons we harp on such fundamentals on this forum. These principles work, and there is a reason behind them.

But again, you must take ownership of your work, so I am glad to work through your challenges, however your DB is built.

Please, do not consider that I am being short or dismissive on your project. But, the issues we are having with your DB is that it is not normalized--had it been normalized to begin with, the issue of the differrence between SG Number and NHS Number (which caused much confusion on my part, because I did not fully understand) would have been moot, because the Admissions Table would have had a viable PK, as would hte Patients table.

My goal is not just to get your DB to work, which is important to me. But, more importantly, I want you to understand how and why you are designing a DB in the way that you do.

I will continue to assist as best I can....

[Hargo]

I appreciate all your advice Twinny and will try to normalise today

My concern is that I am not here much longer (possibly this Friday!!)and if I can't leave them with 'something' my contract will be a failure. The more I change now the more I worry I wont be able to get it back to something workable by Friday - Hopefully HoNOS can be finished by then if I normalise so might as well gamble I guess

I have attached my proposed new relationships - are they going to work?

Do I have to include controls for PatientKey and AdmissionKey on my data entry forms or can they be left off?

Hargo

[Hargo]

Made a start (tables/relationships and queries) but as i suspected I'm struggling with the forms

Users will want to continue to use PatientID (SG00041) rather than AdmissionKey (00000001) as their reference - can the AdmissionKey be picked up from the PatientID when entering data into table via my forms?

e.g. if there is an incident involving PatientID SG00041 that needs entering into the incident table using the incident form can the user just type in SG00041 followed by all the incident data? Or do they now have to know AdmissionKey (New PK for Admissions table & FK for all other tables) for each PatientID too?

No point carrying on till I grasp this

[twinnyfo]

Your original relationship between Assessments and Admissions was correct. Since you want to have assessments based on an admission.

Again, your users will never, ever, EVER see the admission key. If they do, you are doing something wrong. Admission key will automatically bring up the SG number. They are "choosing" the SG number, but behind the scenes the form is "using" the Admissions Key.

[Hargo]

That's what I thought twinnyfo but what i don't know is how it all works!!

I 'replaced' PatientID with AdmissionKey as the foreign key in all my tables when now I think i should have just added AdmissionKey!!

Even then i'm not sure about the 'mechanics' of entering 'an incident' for example - if the incident data entry form doesn't 'ask' for AdmissionKey because users should NEVER EVER see it, how does the data entered link to that 'admission'?? Wouldn't the incident table have a null entry in the AdmissionKey field?

Either I'm missing something simple or my brain has given up!!

SHOULD AdmisisonKey be the FK of ALL other tables or can it continue to be PatientID because the two are linked in the Admissions table??/

I think this is the crux of my quandry and I think what I need are Lookups!!!

[twinnyfo]

You can always use a Combo box using two columns: One for the Admission key, one for the SG Number. Since the user will know the SG Number, the pick that. However, the combo box will use the AdmissionKey as its index.....

[Hargo]

I've been doing that and updating forms all afternoon :)

Hopefully, I 'll be ready to implement your HoNOS form tomorrow!!!


Thanks

[twinnyfo]

Great news. Let me know if there are details with which I can assist.

[Hargo]

I'd be grateful if you could look at my new relationship structure and see if there are any obvious niggles with me using your existing tutorial

I have included tables, queries and a form in the attachment

Cheers twinnyfo have a good day

[twinnyfo]

By Jove, I think he's got it!

Very minor changes:

qry_Admitted_Pa tients
Fields returned need only be Admissions Key, PatientID. AdmissionsKey is the index, and PatientID is the SG Number, correct? You can add more fields if you want to use those fields to verify the correct patient/admissions date. But, since you will be using the SG Number (YOUR Patient ID) then selecting that, with its associated index, should suffice. But, you must include the index.

frmAdd_Assessme nt
Your text box for PatientID can be a combo box, use two columns: Assessment Key, Patient ID (remember--the assessment Key is invisible).

Does everything work (so far)?

You've come a long way! Great job!

[Hargo]

Cheers twinnyfo, one problem though - I don't have an assessment key!! Should I add a Key to all my tables, looking at my relationship structure?

With regard to frmAddAssessmen t and changing to a combo, is this because we are doing away with frmAssessment? Patient ID and Assessment are locked at present but I guess won't be if frmAssessment isn't doing the filtering and the combo does it instead (does that sound right?)

Where do I put the 'Baseline' and 'Follow Up' buttons now?

[twinnyfo]

I don't have an assessment key!! Should I add a Key to all my tables, looking at my relationship structure?

Oddly enough, in this case, I would say that an Assessment Key is not necessary (other experts are free to say otherwise). Here is my reasoning: a primary key typically identifies a unique "record", such that all data elements associated with that record all fall under one, unique record identifier. As discussed earlier, in one sense, in your Assessments Table, one "record" is made up of 19 individual records. How we identify each record is based on the Patient ID and the Assessment date. Adding a PK to such a structure, in my opinion, is immaterial, since you will never be referring to just one record. I may be wrong--as I have been in the past....

With regard to frmAddAssessmen t and changing to a combo, is this because we are doing away with frmAssessment? Patient ID and Assessment are locked at present but I guess won't be if frmAssessment isn't doing the filtering and the combo does it instead (does that sound right?)

We can do this using one form (see the attachment). I used the existing AddAssessment form, and made it unbound, enabled the Patient Combo Box and Date control--both are unbound.

Try to follow this code carefully:

Option Compare Database
Option Explicit

Private dtAssessment As Date
    
Private Sub Form_Open(Cancel As Integer)
    Me.fsubAssessment.Form.Filter = "PatientID = 0"
    Me.fsubAssessment.Form.FilterOn = True
End Sub
Private Sub cmdClose_Click()
    DoCmd.Close acForm, Me.Form.Name
End Sub
Private Sub cboPatientID_AfterUpdate()
    Me.fsubAssessment.Form.Filter = "PatientID = 0"
    Me.fsubAssessment.Form.FilterOn = True
    Me.cmdBaseline.Enabled = True
    Me.cmdFollowup.Enabled = True
End Sub
Private Sub cmdBaseline_Click()
    Dim strSQL As String
    strSQL = "INSERT INTO tbl_HoNOS_Assessments " & _
        "( PatientID, AssessmentDate, ItemName ) " & _
        "SELECT tbl_Admissions.AdmissionKey, " & _
            "tbl_Admissions.AdmissionDate, " & _
            "tbl_HoNOS_Items.ItemKey " & _
        "FROM tbl_Admissions, tbl_HoNOS_Items " & _
        "WHERE tbl_Admissions.AdmissionKey = " & Me.cboPatientID & ";"
    DoCmd.RunSQL strSQL
    dtAssessment = DLookup("[AdmissionDate]", "tbl_Admissions", _
        "[AdmissionKey] = " & Me.cboPatientID)
    FilterForm
End Sub
Private Sub cmdFollowup_Click()
    Dim strSQL As String
    dtAssessment = Date
    strSQL = "INSERT INTO tbl_HoNOS_Assessments " & _
        "( PatientID, AssessmentDate, ItemName ) " & _
        "SELECT tbl_Admissions.AdmissionKey, #" & _
            dtAssessment & "# AS AssessDate, " & _
            "tbl_HoNOS_Items.ItemKey " & _
        "FROM tbl_Admissions, tbl_HoNOS_Items " & _
        "WHERE tbl_Admissions.AdmissionKey = " & Me.cboPatientID & ";"
    DoCmd.RunSQL strSQL
    FilterForm
End Sub
Private Sub FilterForm()
    Me.txtAssessDate = dtAssessment
    With Me.fsubAssessment
        .Form.Filter = "PatientID = " & Me.cboPatientID & _
            " AND AssessmentDate = #" & dtAssessment & "#"
        .Form.FilterOn = True
        .SetFocus
    End With
End Sub

The private variable at the top allows us to play with dates a little easier.

Opening the form (and selecting any patient) filters the subform to no record. Clicking the Baseline and Faolow up buttons append records as before. If it is abaseline assessment, we use the admissions Date. IF it is a follow up, we use today's date. Then we filter the subform, indicate the date of the assessment on the main form and set the focus to the subform, so we can enter assessment data.

Selecting another patient allows us to add more data directly.

Hope this works for you.

[Hargo]

Thanks very much bud

Just one or two little issues which I have outlined on the attached


Also I posted in the thread devoted to the Crosstab query this morning but I'm not sure it's still open as nothing seemed to happen with the page (just continued to 'load')
Could you see if you can find it please? I don't want to get in trouble with mods for posting it in here