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

[Hargo]

It's an Autonumber with eight zero formatting

[twinnyfo]

Remove the format and see if that makes a difference. But, it shouldn't make a different if it is a numerical value. And--it shouldn't be part of the query/record source anyway. patient ID is the same value--even though it is displayed as the SG Number.

[Hargo]

The PatientID = 0 is no longer a problem


I removed the Link Master/Child fields entries from the subform and it stopped the Parameter boxes and the message

Now it's the buttons doing zip and the blank subform

[twinnyfo]

Make sure that [Event Procedure] is in the "On Click" events for those buttons.

[Hargo]

Okay :) so now we just have the mystery of the blank subform - button worked but was appending 0(Zero) records due to blank form


Can't help thinking it's the PatientID = 0 part again - exactly what happened to the other form when I used = 0 and/or = '' the other day (Friday?)

[twinnyfo]

Review Post #44. Between lines 27 & 28 and Lines 42 & 43, insert the following statement:

Debug.Print strSQL

Make sure your immediate window is visible.

Open the form, select a patient, click Baseline (append 0 records), click Follow up (Append 0 records).

Copy and paste the information from the immediate window.

[Hargo]

I selected Patient and pressed Baseline - back came the Parameter box but with the selected PatientID within it - I tried just pressing ok and adding a date (only parameter i thought could be missing) but the same occurred each time - see attached


Soooo close now!!

[twinnyfo]

Send me a screen shot of the properties for your Combo Box for the Patient.

Please include also the query used as its row source.

Still need the value generated by the Debug.Print.... (from your VBA editor, Ctrl-G to show the Immediate Window)

[Hargo]

Immediate window contents too wide for screen so did a copy and paste and included in attached


I'm leaving for the day but will pop on Bytes tonight at home (hopefully I can get the DB open on my laptop!!)

Did you get a look at previous thread (Crosstab query once this form works!! haha) ?

[twinnyfo]

Bound column on the Combo box should be column 1, not 2. It is looking for a text value, which the value of the combo box should be a number (The admissions key).... That should do it!

[Hargo]

I think we're nearly there thanks to all your efforts (can't thank you enough)

Follow up works fine, although not sure just closing form afterwards will sit too well with users as I had to open table to check something had occurred!! - can a faux save button be implemented? - as well as offering some reassurance it could return focus to Patient ID and clear the form to give impression that 'something has happened' - this would also benefit those wishing to input multiple assessments as they could just select another PatientID (I realize they can just do this but its good to reassure imo)


I probably could have a go at this BUT I don't want to ruin what I already have by slipping duff code in willy nilly!!!

Especially as Baseline doesn't work properly - fsubAssessment doesn't appear so HoNOS is appended with 19 Zeroes for the ratings - it does have the Admission Date right though

Finally, given that Assessmentdate control is NOT used, is it advisable to lock it? - would it interfere with working? Might user entering a date interfere more?

The light at the end of the tunnel has arrived :)

Cheers twinnyfo

[twinnyfo]

although not sure just closing form afterwards will sit too well with users as I had to open table to check something had occurred!! - can a faux save button be implemented? - as well as offering some reassurance it could return focus to Patient ID and clear the form to give impression that 'something has happened' - this would also benefit those wishing to input multiple assessments as they could just select another PatientID (I realize they can just do this but its good to reassure imo)

You can have the close button do whatever you wish.....

Especially as Baseline doesn't work properly - fsubAssessment doesn't appear so HoNOS is appended with 19 Zeroes for the ratings - it does have the Admission Date right though

Explain "doesn't work properly". If it is appending 19 zeroes, then you are either telling it to append 19 zeros (which I don't think our code does that), or the default value for the AssessmentRating is 0 (you will have to check the table design).

Finally, given that Assessmentdate control is NOT used, is it advisable to lock it? - would it interfere with working? Might user entering a date interfere more?

Database Desing Rule #217: Allow users to update only the data you want them to change. Corollary: Do not allow users to update any data they don't need to update.

The Assessment Date text box is there so the user can see that this is the date of the assessment they are entering. UNLESS, as mentioned before, there would be the chance that a user would be entering an assessment on a day different than "Today's Date"--which is possible. But, then you would change hte code for the append query to validate that a proper date was entered and use that date in the append query. Options abound.

Hopefully this will bring you closer to the end of the tunnel...

:-)

[Hargo]

You can have the close button do whatever you wish.....

I realise this but I think I'd prefer a Save button that 'reset' (for want of a better word) the subform to its 'blank' state and set focus to PatientID - although I could add the button to set the focus, I'm not sure how to 'blank' the form particularly as I don't want to to ruin what I have already!!

Explain "doesn't work properly". If it is appending 19 zeroes, then you are either telling it to append 19 zeros (which I don't think our code does that), or the default value for the AssessmentRating is 0 (you will have to check the table design).

When Baseline is pressed the message comes up that you are about to append 19 records (Same as Followup) but when you press ok, fsub doesn't appear so ratings can be input - consequently what is appended is just the INSERT INTO String for PatientID, AssessmentDate, and Item - because fsub didn't appear all rating scores are entered as zero

Surely any default value for AssessmentRating would apply when Followup button used, thus the problem is why fsub isn't opening the same as it does with Followup


You kinda lost me with your last paragraph - is locking the control a good idea or not? Does locking impact on the data within the control or will the appropriate date still appear?

[Hargo]

Assessment rating did have a default = 0 which I have removed and now the records are appended without any value in Assessment Rating due to fsub not opening/appearing

[twinnyfo]

I am dumbfounded as to how my forms, queries and VBA works perfectly, but yours does not.

make sure there is no master/Child values in the fsub.

This is driving me nuts; I am sure you too...

[Hargo]

Amazingly I have solved the problem!!!! Bizarre i know :)

I simply moved the

dtAssessment = DLookup

part of the code above

strSQL = "INSERT INTO

and voila!! Obvs had a good teacher ;)

[twinnyfo]

So you got everything working now?

[Hargo]

Apart from the Save button which clears the form (cosmetic I know, but I think it will help users a lot) is this a single line of code or huge amounts?

[Hargo]

Done the save button and it works fine :)

[twinnyfo]

Great! I hope this project will serve to help your employers on a daily basis!

[Hargo]

It certainly will thanks twinnyfo, just need the ranking query to work and then query 3 and its showtime