Designing a continuous form for entering score data

[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....sure ly 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_Inf ormation) 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 frmAddAssessmen t 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_Asses sments 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 misunderstandin g 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 "polyglotti sm" 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_Asses sments (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_Asses sments.PatientI D, 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.FilterO n = 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_Asses sments. 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 frmAddAssessmen t 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 frmAddAssessmen t so that the INSERT INTO code runs from there instead?