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(Brace) (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_Assessments 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_Assessments 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_Assesments 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=Overactive, 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=Overactive, 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

[twinnyfo]

send a screen shot, send list of data in both tables.

Send screen shot of assessments tabel in design view, highlighting Item field, lookup tab.

[Hargo]

Right twinny

My table is ready so it's time to finish (start again?) the form to enter data


I've been looking at your approach (which looks great) but I have the following issues:

I'm not sure I want a combo for patient....surely it's quicker to enter it than scroll down through hundreds of patients?

A Patient ID only has one Admission date (Patient ID is PK for tbl_Admissions, NHS Number for tbl_Patient_Information) so I do not grasp the snippet below

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.

Can I use your INSERT INTO code in some other way?

Am I right in thinking the append query is contained within this code? (INSERT INTO seems like append to me)

Can I put a button on my existing form (see attached) which I have adapted a little?

Is frmAddAssessment your version of my continuous subform? Why does it 'open' upon button push? I guess that means its not the subform really doesn't it?!?

Muddled again but still moving forwards inch by inch

[twinnyfo]

Do you mind if I try to put together a tutorial on building this form? I'll try to post as soon as I can.

[Hargo]

Of course I don't mind, you're helping me learn DB skills - can you make it a tutorial for dummies though please? :)

[twinnyfo]

I'll do my best. I'll even use a flannel graph!

[twinnyfo]

Hope this will help you out. Like I mentioned, I had a bit of free time at work. Let me know if there are any questions or stumbling blocks with this.

Also, be aware that I did not add any error trapping into the code, which is something I recommend for all the younglings entering Jedi service. But, that is a tutorial for another day.

May the Force be with you!

[Hargo]

Amazing tutorial twinnyfo thank you

I'm just doing the 'tough stuff' in the last 3 pages and have a few questions before I proceed (2 in the Doc)

Given my poor naming conventions I followed all your advice in the tutorial but my tbl_HoNOS_Assessments has 'PatientID' rather than 'Patient' so I've attached my version of your code with a couple of questions included to clarify

Also, I am struggling slightly with your use of 'my' Patient ID (SG00041) as your NHS Number

In UK everyone gets a 10 digit NHS Number the moment they first use the NHS (Not really relevant here just for information)
Example: 012 345 6790

SG stands for St George (The healthcare trust in question)

So SG00041 would be their 41st admission

My problem is: in the AfterUpdate Event I'm not sure whether I should be using my NHS Number or my Patient ID because the form has

cboPatientID (which uses NHS Number and Admission Date) at top

and PatientID (number 1 -5 ) lower down

Cheers

BTW why don't you write a book and sell it??? Your style is much better than the books out there :)

[Hargo]

Teething troubles with After Update

I kept it as PatientID but because my PatientID data type is text (SG00041) I changed =0 to =Null which appeared to be accepted at first

When I select an NHS Number from combo I get a message box with appropriate SG (PatientID) BUT am asked for parameters - what parameters does it want?


=Null now appears to blank the WHOLE form!! as does = ' '

Can't grasp why setting filter to no record would now blank the whole form, especially as originally it just requested additional parameter - is there another way of referring to no records (text datat type) upon open event?

[twinnyfo]

Also, I am struggling slightly with your use of 'my' Patient ID (SG00041) as your NHS Number

In UK everyone gets a 10 digit NHS Number the moment they first use the NHS (Not really relevant here just for information)
Example: 012 345 6790

SG stands for St George (The healthcare trust in question)

So SG00041 would be their 41st admission

And

My problem is: in the AfterUpdate Event I'm not sure whether I should be using my NHS Number or my Patient ID because the form has

Hence, my very strong recommendation that the Patients Table have its own PK, apart from the NHS Number. This dissolves any problems between the formats of the numbers and "Text vs. Number" issues.

So, patient 012 345 6790, could have an entry in the Admissions table called SG00001, SG00002, etc., AND Patient 0976 543 210 could ALSO have Entries of SG00001, GS00002, etc.? I just need to fully understand the concepts.

=Null now appears to blank the WHOLE form!! as does = ''

Make sure your form is set to "Allow Additions = Yes"

Can't grasp why setting filter to no record would now blank the whole form, especially as originally it just requested additional parameter - is there another way of referring to no records (text datat type) upon open event?

I can't grasp it either, but that is what Access does. Intuitively, one would think that "No Record" would just mean a blank form with empty fields. But, since you can't enter data into "no record," Access clears the form completely. There are ways around this, but keep in mind that I was trying to get you a down and dirty, basic form just for you to see how things work.

Because your data type is text, it is more appropriate to use

"PatientID = ''"

BTW, I did not see any attachments for either Post #22 or #23, so I don't know what your code looks like.

Hope this helps.

[Hargo]

Sorry, not sure why i didn't attach code!!

Here it is now attached


With regard to

So, patient 012 345 6790, could have an entry in the Admissions table called SG00001, SG00002, etc., AND Patient 0976 543 210 could ALSO have Entries of SG00001, GS00002, etc.? I just need to fully understand the concepts.

Different patients (NHS Number) can't have same SG number as this is unique hospital identifier - 012 34 6790 could have SG00041 and SG00150

and 0976 543 210 might have SG00048 and SG00155 (if they were both admitted twice)

I just think an extra (unnecessary) number could confuse things for users particularly as each table has a natural unique identifier anyway, but if it is the ONLY way to solve the problem then so be it, I will add it in

Because your data type is text, it is more appropriate to use

I tried both = Null and = '' and each time the form 'cleared' although originally = Null asked for additional parameters

On a general level, is the purpose of your

Me.Filter = "PatientID = 0"

to make the form open with no patient visible so a 'search' can be done?
Also, could you explain the use of the AS Patient alternate name please?

[twinnyfo]

Thanks for the clarification on the SG Numbers. That makes sense.

I just think an extra (unnecessary) number could confuse things for users particularly as each table has a natural unique identifier anyway, but if it is the ONLY way to solve the problem then so be it, I will add it in

Again, I think this is a misunderstanding of what an Index/PK/Unique Identifier is. This number, in most cases, is never, ever seen by the user. They will only see the SG number or NHS number or patient name or any other data value that is associated with that index. However, it is much easier to use an index.

I will go back to your Items Table again, for more details. If you have an index (a PK, AutoNumber field), all this serves as is a unique identifier for the record itself. One could also argue that "AGR" is unique--so why not use that as the PK? "Item 1" is also unique. Why not use that as your PK? In fact, every field in your Items table (aside from Item Type) is unique in the table. One could argue that any one of those fields could be used as a PK.

However, here is where the principle of good database design comes into play: Let's say that in your studies, or according to changes in the medical world, that Aggression is no longer a measurable assessment. However, another trait, such a "polyglottism" is now measurable. You could simply replace the data in the fields for Aggression with data associated with polyglottism. However, as you could easily see, any historical data that was associated with Aggression would now be associated with polyglottism, even though polyglottism was not a measurable assessment item at the time.

Instead, you would add a new record to the Items Table. Let's say that this new item is not Item 1, but will be Item 6, with the others now moving up. Remember the problems we had sorting with "Item 1", "Item 10", "Item 11", etc.? Again, this shows the importance of having another field used solely for sorting. Botht he PK and the Sorting field would (should) be invisible to the user--becuase they don't really need to know anything about AutoNumbers and Sorting values. They just want to know the assessments and be able to add values as needed.

I tried both = Null and = '' and each time the form 'cleared' although originally = Null asked for additional parameters

Make sure your form has the property "Allow Additions = Yes".

The only purpose for the Me.Filter = "PatientID = 0" is to clear the form. It is unneccesary, other than a patient's name will be listed at first. Your choice.

You have a question in the Word Doc:

Is Patient the field from tbl_HoNOS_Assessments (thereby needing ID) OR your AS Patient (thereby needing to be left)??

Patient is from your Form:

"SELECT " & Me.txtPatientID & " AS Patient

Using "AS" is just a way to designate the value as something that makes sense. You could call it "DataItem1" if you wanted (Access would default to "Expr1"). Since it is the first item returned, regardless of the Field name, it will be inserted into tbl_HoNOS_Assessments.PatientID, the first Field listed in the INSERT Statement.

Your final pics in the attachment are strange. Your request is telling me that you have a Field named "SG00170", which you should not. Could you list all the properties on your Form?

Is the Error (pic on the right) bringing up an error dialog or just the request for parameters?

Thus, we see the challenges of working via the internet. But, we will get you there eventually....

[Hargo]

I get the PK stuff now so will add it

Allow Additions was set to = Yes

Using "AS" is just a way to designate the value as something that makes sense.

Is the Alias (Patient) essential to the code or just something you have used? What if I don't have "Patient" but "Patient ID" etc? i.e. I don't know what to do with it because I'm not sure things are same on your form as mine

Is the Error (pic on the right) bringing up an error dialog or just the request for parameters?

SG00170 is the Patient Admission associated with the NHS Number (012 345 6795) I selected from cboPatientID - because I don't know what parameters to put in the Dialogue Box I have to cancel and so just get an error message telling me I cancelled the operation

Me.Form.FilterOn = True

Is then highlighted as the problem

Could you list all the properties on your Form?

I assume you want screenshots here? so have attached some

[twinnyfo]

Is the Alias (Patient) essential to the code or just something you have used? What if I don't have "Patient" but "Patient ID" etc? i.e. I don't know what to do with it because I'm not sure things are same on your form as mine

Again, it is immaterial. Call it what you wish. All the SQL is going to do is take the value assigned to Me.txtPatientID (which should be an index/PK/AutoNumber). It is "naming" that field "Patient", just so it has something to call it. Then, it is appending that value (the index) to the PatientID Field of tbl_HoNOS_Assessments. Try using "AS Dog" and you will still append the same records. Call it PatientID if you wish.

Also, as I re-look at your code/doc from Post #25, the reason you are getting this error asking for the parameter, is because of the whole Index/PK/AutoNumber thing.

Once your tables are normalized and you wer looking at the right fields with the proper data types, things should function properly.

[Hargo]

Okay

Changing all my relationships and the existing code that already references Patient ID is a huge job which I'm reluctant to commence because I will end up losing what I have due to my lack of knowledge

I'm so close to a perfect solution thanks to you, so why can my otherwise perfectly usable PK (Text) NOT do this one particular job on this form?

Perhaps if you explained to me how your frmAssessment does what it does I could substitute the right fields in from my tables to make mine work? I know it's a big ask but I'm so close!!

[Hargo]

I think I can simplify things further to save time...

I am assuming that the purpose of this form is to filter the right patient to receive the Assessment ratings, correct?

Can this part not be done away with? I say this because in my 'schema' the Patient ID (SG00041 etc) cannot have multiple entries like yours has by calling Patient ID, NHS Number

How can I get to frmAddAssessment by just selecting an Unique Patient ID (perhaps with a parameter query instead of your 'filter form'?

Can, for instance, the Baseline and Followup buttons not be added to frmAddAssessment so that the INSERT INTO code runs from there instead?

[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 misunderstanding 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.txtAdmissionDate 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 frmAddAssessment (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_Assessments?

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_Patients
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_Assessment
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 frmAddAssessment 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 frmAddAssessment 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

[twinnyfo]

Patient ID (I assume the Combo box. Remember, two columns:

Row Source:

SELECT tbl_Admissions.AdmissionKey, 
    tbl_Admissions.PatientID 
FROM tbl_Admissions 
ORDER BY tbl_Admissions.PatientID;

Assessment Date is unbound.

Double check to make sure the Form does note have a record Source.

Double check the name for the Command Button for Followup.

Make sure fsubAssessment has that name. make sure it has the proper Record Source (qryAssessments). Make sure qryAssessments has been updated to reflect your index changes:

SELECT tbl_HoNOS_Assessments.PatientID, 
    tbl_HoNOS_Assessments.AssessmentDate, 
    tbl_HoNOS_Items.ItemKey, 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 
ORDER BY tbl_HoNOS_Assessments.PatientID, 
    tbl_HoNOS_Assessments.AssessmentDate, 
    tbl_HoNOS_Items.ItemKey;

Troubleshooting can be frustrating with MS Access, but you must learn to look at everything. When one things changes, it may affect many other things.....

[Hargo]

Everything checked so it can only be qryAssessments (NOT sure why it didn't like cmdFollowup - see what happens once everything else sorted)

Am I right in thinking tbl_Admissions needs to be added so that AdmissionKey can be included as it is the Index

Would that be the only change?

[Hargo]

Made no difference at all so I have attached the code for you to peruse

[Hargo]

Progress slow but steady

Form now opens Patients can be selected, Dates can be chosen BUT

Form blank - could this be the second PatientID = 0 ??

NOTHING happens when either button pressed ....i.e. Zip - no error message no debug request - it's like the code isn't there BUT it is!!


Here's qry_Assessment results plus the 'blank' form attached


One question I have - does the query keep growing along with tbl_HoNOS_Assessments? it'll be huge after a few months of assessments!!

[twinnyfo]

Why is Admission key = "0000000001"? that is a text value.

Send me your Admissions Table in design view.