Designing a continuous form for entering score data

[Hargo]

Hi I am a beginner using Access 2013

I am trying to design a data entry form for one of my tables which has 5 fields, let's call them: Patient ID(FK), Date, Item Number, Item Name, Rating

The form has to collect 19 individual item ratings (scored between 1-5) for one patient on one date.

I can't avoid inputting the Patient ID and Date 19 times so would appreciate some advice please

Thanks

[twinnyfo]

This is a companion thread to Struggling creating a version of this Excel sheet in Access form.

First, you must find a way to select the proper patient/Admission Date. Since the same patient may be admitted more than once, you need to not only pick the NHS Number, but also the Admission Date.

If you create a form with your admissions Table as the Record Source. Add all the fields from the Table (this is for viewing purposes only, so they do not need to be enabled). Next, create an unbound combo box with three columns. As a row source, use a query that includes the Patient ID, the NHS Number and the Admission Date (you can just as easily use a join to the Patients Table so that you can get names--either method will work). The first column of the combo box should be the bound column, as the assessments will be based off the PK of the Admissions Table.

Once the User selects the Correct Patient from the combo box, filter the form based on the Patient ID you have just selected. This will verify that you have the correct patient/Admission date.

Then, have a command button that will do three things:

1. Append the appropriate records to the Assessments Table
2. Open a form for entering data
3. Filter that form based on the Patient Data

The Form it opens can either be a Continuous Form with a Header and footer, which you may resize either programatically or by resizing the design window or (a method I prefer) to have a Form, with a Subform, sized so that 19 [and only 19] continuous records will be displayed. Numerous methods to create both of these, but either setup will require the assessments table to be the record source. If a subform is used, the Parent/Child fields should be the PatientID and the Admission Date.

The Command button mentioned above, for example, would have the following procedure in its On Click Event:

Private Sub cmdEnterAssessment_Click()
    Dim strSQL As String
    strSQL = "INSERT INTO tbl_HoNOS_Assessments " & _
        "( Patient, ItemName, AssessmentDate ) " & _
        "SELECT " & Me.txtPatientID & " AS Patient, " & _
        "tbl_HoNOS_Items.ItemKey, #" & _
        Me.txtAdmissionDate & "# AS AssessDate " & _
        "FROM tbl_HoNOS_Items " & _
        "ORDER BY tbl_HoNOS_Items.ItemKey;"
    DoCmd.RunSQL strSQL
    DoCmd.OpenForm "frmAddAssessment", , , _
        "Patient = " & Me.txtPatientID & _
        " AND AssessmentDate = #" & Me.txtAdmissionDate & "#"
End Sub

This code assumes certain naming conventions for your controls, but replacement of the control names with your own should suffice.

Let me know if you have any struggles with the creation of these forms.

[Hargo]

Hi twinnyfo

I have attached a doc showing my progress to date

I am pleased that I have done some of the things you suggested by myself, but much of the early part of you post went over my head I'm afraid.

These are my current issues:

I would like Item Number and Item Name to auto populate since they are standard and come from the Items table, is this doable?
Failing that, I would like Item Name to be auto-populated based on Item Number.

I want to adopt your sizing idea so that the form is big enough for 19 rows if it cannot grow as the rows are entered

If possible I would like Patient ID to come from a previous form using OpenArgs (I have successfully accomplished this elsewhere, but have a different issue regarding the form which is probably another thread!!)

Thoughts?

Hargo

[twinnyfo]

First, BRAVO! on the creation of your form. You are almost there. There are a couple things to think about.

First, because I think you are still in the paradigm that you must "add" the Item Numbers and Item Names and then the assessment, I see you only have four records listed, and you are editing the Item Name? Additionally, along those lines, if Item Name were properly related to the Items Table, the control would show up as a Combo Box, which it is not, so something is amiss--either with your Form or with your table itself. My initial thought is the table. If you can send a screeshot of tblAssessments (both in datasheet view and design view).

To get your form to show 19 records, simply switch to design view and stretch the subform so that it is taller. If you are fine scrolling, that is OK, too. However, showing all 19 items may be benficial to get a quick snapshot.... You also should be able to remove all the navigation controls.

Attached is a really ugly version of the opening Form.

The Code:

Option Compare Database
Option Explicit

Private Sub cboPatient_AfterUpdate()
    Me.Filter = "PatientID = " & Me.cboPatient
    Me.FilterOn = True
    Me.cmdEnterAssessment.Enabled = True
End Sub
Private Sub cmdEnterAssessment_Click()
    Dim strSQL As String
    strSQL = "INSERT INTO tbl_HoNOS_Assessments " & _
        "( Patient, ItemName, AssessmentDate ) " & _
        "SELECT " & Me.txtPatientID & " AS Patient, " & _
        "tbl_HoNOS_Items.ItemKey, #" & _
        Me.txtAdmissionDate & "# AS AssessDate " & _
        "FROM tbl_HoNOS_Items " & _
        "ORDER BY tbl_HoNOS_Items.ItemKey;"
    Debug.Print strSQL
    DoCmd.RunSQL strSQL
    DoCmd.OpenForm "frmAddAssessment", , , _
        "Patient = " & Me.txtPatientID & _
        " AND AssessmentDate = #" & Me.txtAdmissionDate & "#"
End Sub
Private Sub Form_Open(Cancel As Integer)
    Me.Filter = "PatientID = 0"
    Me.FilterOn = True
End Sub

The Unbound combo box at the top of the form as this as its row source:

SELECT [tbl_Admissions].[PatientID], 
    [tbl_Admissions].[NHSNumber], 
    [tbl_Admissions].[AdmissionDate] 
FROM tbl_Admissions 
ORDER BY [NHSNumber], 
    [AdmissionDate];

Other properties for Combo Box:
Column Count: 3
Column Widths: 0, 1, 1
Bound Column: 1

Listed below in the attachment is the form in action.

I would like Item Number and Item Name to auto populate since they are standard and come from the Items table, is this doable?

Concerning this, yes, it is doable. Is it necessary? Item 1 will always be AGR, yes? If this is merely data input, it does not matter. However, it is your DB, so I defer to your preferences. In that case, instead of just the table as the Record Source, you would use a Query that joined the Asessessments table with te the Items Table, as such:

SELECT tbl_HoNOS_Assessments.Patient, 
    tbl_HoNOS_Assessments.AssessmentDate, 
    tbl_HoNOS_Items.ItemNumber, 
    tbl_HoNOS_Assessments.ItemName, 
    tbl_HoNOS_Assessments.AssessmentRating 
FROM tbl_HoNOS_Items 
    INNER JOIN tbl_HoNOS_Assessments 
        ON tbl_HoNOS_Items.ItemKey = tbl_HoNOS_Assessments.ItemName;

Then just add a Control for the Item Number.

I want to adopt your sizing idea so that the form is big enough for 19 rows if it cannot grow as the rows are entered

Remember, you are not adding rows! If your append query worked properly, you already have 19 rows..... Again, keeping with your desire of not having to enter redundant data.

I hope this continues to help you along the way.

[Hargo]

Your first paragraph lost me I'm afraid - I'm sure my paradigm is as you imagine though (how do I 'quote' parts of previous threads like you do? I don't see a 'Quote' button/option anywhere)

I am currently tinkering with a 'copy' form to get the sizing right, but that is less of a concern than appreciating the correct paradigm.

Perhaps my paradigm will be evident from my next question

Let us assume the form is re-sized to fit 19 records; can the Item Number & Item Name data be auto-completed (in whatever way) as they are always the same? I only want this form to perform data entry

Hargo

[twinnyfo]

To quote, Use the (Brace)QUOTE(Br ace) (Brace)/QUOTE(Brace) tags around the quoted text.... (took me a while to know, this, too!

Concerning your table, it is as I suspected. Please see the attachment for the differences.

Note how my table only has four fields. Note also that that "ItemName" is a "Number" data type! How can this be? This is because it is only looking at the index of the Items Table. When you view the table in datasheet view, you will note that the Item name appears. This is because Item Number, Item Type, Item Name and Item Description can all be determined from just the index. Thus, your table uses redundant data. Not only this, but it also appears that you are required to manually add the Item Number, and then manually add the Item Name (and... What happens if you type in something wrong?). In my table, since the table is related to the Items Table, and referential integrity is turned on, there are 19 ooptions and 19 options only for the Item field.

Per standard Database Normalization principles, you should redesign that table to take this into account.

Concerning your final question,

Let us assume the form is re-sized to fit 19 records; can the Item Number & Item Name data be auto-completed (in whatever way) as they are always the same? I only want this form to perform data entry

This is exactly how I have constructed my forms. The only controls that allow any editing are the assessment. Perhaps my descriptions were not clear enough. However, the key is having an append query that adds records to the assessments table and a form that filters by the current assessment. The rest is all visual design....

[Hargo]

So my tables and relationships are all wrong as was/is my paradigm....my head hurts!! haha

I assume (Brace) = One opening bracket (guess I'll find out when I submit!?!?!)


("(Note how my table only has four fields. Note also that that "ItemName" is a "Number" data type! How can this be? This is because it is only looking at the index of the Items Table. When you view the table in datasheet view, you will note that the Item name appears. This is because Item Number, Item Type, Item Name and Item Description can all be determined from just the index. Thus, your table uses redundant data. Not only this, but it also appears that you are required to manually add the Item Number, and then manually add the Item Name (and... What happens if you type in something wrong?). In my table, since the table is related to the Items Table, and referential integrity is turned on, there are 19 options and 19 options only for the Item field.)/")

I will recreate table/relationship now, but what if i want to show Item Number "Item 1" etc. and Item Name?

[twinnyfo]

Use "[" followed by "QUOTE" and then another "]" - Bytes converts that combination of charaters into a quoted statement.

I will recreate table/relationship now, but what if i want to show Item Number "Item 1" etc. and Item Name?

Aaaaahhh! Now you will see why proper relationships are so important, young Jedi!

If I have a Table with a Primary Key and 100 Fields, all I need to know is the PK, and I have 100 Fields available for use anywhere else in the DB!

[Hargo]

Aaaaahhh! Now you will see why proper relationships are so important, young Jedi!

Haha I think I'm still a 6 year old Anakin!!!

But I'm starting to feel the force thanks to you

[Hargo]

Been tinkering away for a while now and I still can't grasp this:

However, the key is having an append query that adds records to the assessments table and a form that filters by the current assessment. The rest is all visual design....

Then, have a command button that will do three things:
Append the appropriate records to the Assessments Table
Open a form for entering data
Filter that form based on the Patient Data

I thought that the append query would be the forms's control rather than tbl_HoNOS_Asses sments BUT that doesn't seem to be what you are saying.....the more I read back through this thread the less I grasp!!!

[Hargo]

Just been reading through the threads and some stuff zmbd provided and I can't help wondering why your tbl_HoNOS_Asses sments shows the Item Name whereas mine shows the item number?!?!

When you view the table in datasheet view, you will note that the Item name appears. This is because Item Number, Item Type, Item Name and Item Description can all be determined from just the index.

Obviously something wrong with my relationships but in my attachment they look the same as yours????

If the data type for Item Name in tbl_HoNOS_Asses ments is number how can it ever display text?!?!!?

Can't really move on until this is cleared up, what have I done wrong?

[twinnyfo]

You have come a long way with the Force as your guide....

But you have much to learn, my young padawan!

Concerning Post #10:

An Append query adds records to a table. It cannot be used as a record source for a form or report. Let's demonstrate this with two scenarios:

Scenario 1

You have an empty assessments table. Patient SG0041 is admitted for an assessment on 25 Sep 2014. You open your Assessments Table (or a Form which has that table as its record source). You navigate to a new record and begin typing.

You TYPE in "SG0041"
You TYPE in "25/09/14"
You TYPE in "1"
You TYPE in "3" (the assessment for--What does Item 1 represent again? Oh, yeah, AGR)

You move to the next record.

You TYPE in "SG0041"
You TYPE in "25/09/14"
You TYPE in "1"
You TYPE in "2" (the assessment for--What does Item 2 represent again?)

Yadda, yadda, yadda.

Scenario 2

Your Jedi Master introduces you to Append queries.

You open a form and Select a Patient from the Drop Down List. This patien has been admitted several times. You select the Entry for "SG0041 - admitted on 25 Sept 2014"

You click a button that says "Enter Baseline Assessment". You also notice another Button called "Enter Additional Assessment".

Behind the scenes, your Jedi master's Append query gathers information concerning this Patient, such as the NHS Number and the current Date. It also realizes that you need 19 records added to this table (one for each Assessment Item).

It goes out to the Assessments table and adds 19 records for you, including the Patient's NHS Number, the Date, and values for each of the assessment items. This gives you values shown in the first pic in the attachment. The assessments are blank, because you have not entered them yet.

But then, the code behind your button also opens a new form for you. It has as its record source, the assessments table, but you could build a new query that included other values that you might want to look at (like Item Number, or Item Type). It is also filtered so that the only records that are shown belong to Patient SG0041 AND the assessment date of 25 Sep 14.

This is what is shown in the second pic of the attachment.

Then, you type in: 3, 3, 3, 4, 3, 2, 2, 3, 2, 3, 5, 1, 3, 2, 3, 2, 3, 2, 2

And you are done!

I believe...... you are more interested in having the second scenrio, yes???

=============== =============== =======

Concerning Post #11:

Look closely at my Assessments Table in Design View (pic 3). In the lookup tab, notice that I have a SELECT query as the row source and that the column count is two. The Column widths are: 0";0.6355" This means that the first column (ItemKey--the first field pulled by the query) is hidden, but the second column displays the ItemName. Note, especially that the bound column is column 1 (which is hidden, but nonetheless, it is "there"). This means that the actual value of the field (irrespective of what is displayed) will be a value between 1-19 (the indexes from the Items Table).

How this plays out is that IF (emphasis on if) you were to ever enter data manually (which you have stated that you don't) when you add a new record, and you come to the item, and you want to add Item 1, which is AGR, you will come to this field in your table, and you will have a drop down box, from which you will see a list of Item Names (ADL, AGR, AOD, BLD-SCR, etc.--the query says they are sorted by name, but you can change that), and you select AGR, because you are on the first item. BUT, what is actually happening, is you are assigning the value of the index (1) to that field. It will display as AGR. At a later time, if you use a join query to display records, because you have the index, you also know that "1=AGR=Item 1=ItemType 1=Clinical=Over active, aggressive, disruptive or agitated" AND you can use any of those values freely, all because you have a relationship between the index of that table to the assessments table.

Now, whether you actually use a lookup in your table is immaterial, because, if you have the index, you have the index, and you can manipulate your forms however you want so that you can capture and display the Item however you want. But, when you look at your table in datasheeet view, it certainly is nice being able to look at AGR, instead of 1--trying to remember what those 19 values were.

Your relationships are correct, but, if you prefer, you may want to update how the table presents data for you.

Hope this helps!

[Hargo]

Can't thank you enough for the time you are taking to help me twinny


I get the concept, I grasp the idea of the bound and hidden columns (i will implement this in a minute) I just feel like I'm missing something really simple which will be my eureka moment and it revolves around:

At a later time, if you use a join query to display records, because you have the index, you also know that "1=AGR=Item 1=ItemType 1=Clinical=Over active, aggressive, disruptive or agitated" AND you can use any of those values freely, all because you have a relationship between the index of that table to the assessments table.

I'll crack on for a bit and see if it dawns on me!! haha

[twinnyfo]

Perhaps this will help your eureka moment:

Assume you have data in your assessments table. Item is 1.

Your coleague asks you "Which Item Number is that?"

You look at your Items Table--referring to Item 1: You respond, "Item 1."

Your coleague asks you, "What's the name of that Item?"

You look at your Items Table--referring to Item 1: You respond, "AGR."

"What's that stand for?"

You look at your Items Table--referring to Item 1: You respond, "Overactive , aggressive, disruptive or agitated."

"I forget--is that a Clinical or a Security Item?"

You look at your Items Table--referring to Item 1: You respond, "Clinical."

"Wow, Hargo! You are a wealth of information! I'm glad we have you on our team!"

Thus, the only piece of information our table needs is "1".

[Hargo]

It's more to do with getting that information where I want it which I guess is Queries, yes?

On another note I have somehow managed to botch the relationships!!

Any reason why Access only offering 1 to 1 relationship between Items and Assessments all of a sudden?


Sorry MOD shouldn't have asked this - it was easy enough to solve myself