Access Form Problem

[eddiesvoicebox]

Hi All,
Wonder if you may be able to help. We use a database that records scores for CSA's calls and a form whereby radio buttons are used to mark of certain failings. Each radio button is assigned a column in the main table as the control source, giving a value of 1 for fail and 0 for a pass.
However depending on the final score they are awarded a grade (Grade 1 Pass, Grade 2 Pass, Grade 3 Fail, grade 4 Fail, Immediate Fail) I have a text box that returns this value in the form, depending on the total score of the questions. I have included some new criteria, and included this, in the calculation of the final score and all works. But since amending the form recently (and i did not touch this box) the final score is not being entered into the table anymore. The last 5 columns in the main table are assigned each one of the above mentioned grades (If they achieve Grade 1 Pass then a 1 should appear in the Grade 1 Pass column) and because the result of this cell needs to reference to more than one place in the table i cant work out how get the info from my form into the table. Any help would be greatly appreciated.

Chris Durant
christopher.durant@churchill.com

 

[Andrew Fergus]

Hello Chris and welcome to Mr Excel.

I'm a little confused from your description so if I am heading in the wrong direction with my answers, can you please elaborate on a couple of points?

But since amending the form recently (and i did not touch this box) the final score is not being entered into the table anymore.

Is the "final score" per the above statement the "total score" or the "grade"? If you go into the form design, right click on the final score box -> Properties -> Data -> What is recorded in the "control source" box? This should be the variable name (either the total score or the grade) from the table. If there is nothing there, then you should be able to select the variable name from the drop down box.

The last 5 columns in the main table are assigned each one of the above mentioned grades (If they achieve Grade 1 Pass then a 1 should appear in the Grade 1 Pass column) and because the result of this cell needs to reference to more than one place in the table i cant work out how get the info from my form into the table.

Reading this it appears the "grade" is held in 5 different variables in the table and a "1" is assigned to the variable to indicate the grade while the other 4 variables default to 0. Have you considered using one variable instead that uses differing values to reflect differing grades?

e.g. the [Grade] variable that uses the following values : 1 = Grade 1 Pass, 2= Grade 2 Pass, 3= Grade 3 Fail, 4 = Grade 4 Fail & 5 = Immediate Fail. That way any CSA with a grade >2 has failed.

This would also be easier to handle from the form - I presume the 1 and 0 values from the radio buttons are added together to give a final score which determines the grade. If this is the case then setting the grade would be a matter of some nested iif statements (I think you mentioned that the final score was being calculated correctly)

e.g. iif([Final Score]<5, 1, iif([Final Score]<10, 2, iif([Final Score] <15, 3, iif([Final Score] <20, 4, 5)))) {N.B. I made up the values and variable name of [Final Score] for illustrative purposes and assumed less is better}

Once again, the "grade" on the form needs to have the "grade" in the table as it's control source (see previous paragraph).

Please advise if this is way off track.

HTH, Andrew.

 

[eddiesvoicebox]

Andrew,
thanks for the speedy reply. Due to it being 7am on a friday maybe my explanation of the problem was lacking. I will post the code here for you to see what i mean. Thanks in advance for your help on this.

i have 3 subtotal boxes all having similar calculations to the one below.
=([46]+[47]+[48]+[49a]+[49b]+[49c]+[49d]+[49e]+[49f]+[49g]+[49h]+[49i]+[49j]+[50]+[51]+[52]+[53]+[54]+[55]+[56]+[57]+[58])*-1

There is then a calculation to add up all the subtotals
=[CODIS TOTAL 1]+[CODIS TOTAL 2]+[CODIS TOTAL 3]+[CODIS TOTAL 4]

Then according to this score a Grade is issued

=IIf([System] Is Null,"",IIf([45]=-1,"Immediate Fail",IIf([58]=-1,"Immediate Fail",IIf([System]="UIS","",IIf([OVERALL CODIS TOTAL]=0,"Grade 1 Pass",IIf([OVERALL CODIS TOTAL]>9,"Grade 4 Fail",IIf([OVERALL CODIS TOTAL]>=4,"Grade 3 Fail","Grade 2 Pass")))))))

With the radio buttons i can easily see how the data is being returned to the table as there is only one cell for the form to populate. but in this case there are 5 possible cells that can be filled depending on the above formula. i cant see how it is putting the data back into the table.
Unfortunately i know very little about expression builder and cant seem to find the answer.

this is only a short term solution as early next year i will be completely rebuilding this system and will use the idea you mentioned for inputting the grade into the table. In the short term thought this is not feasable as it would affect reports etc.

Hopefully now that breakfast is out of the way this makes a bit more sense.

Chris Durant
christopher.durant@churchill.com

 

[eddiesvoicebox]

ray:
Apologies for any inconvenience i may have caused. I was not informed that user have to press a 'Return To Menu' button to complete their process. Attached to said button is a macro that appends the grade into the table. However that said the speed and helpful tone of your response was fantastic. Thankyou.

Chris