Unable to get Switch working correctly

[Seeletse]

i'm running the below query as the control source for a level field. the result depending on the value in the marks field should set as the value for the level. Unfortunately I get the #Name? error.



=Switch([Test_Score_Card]![Marks]>79,"7",[Test_Score_Card]![Marks]>69,"6",[Test_Score_Card]![Marks]>59,"5",[Test_Score_Card]![Marks]>49,"4",[Test_Score_Card]![Marks]>39,"3",[Test_Score_Card]![Marks]>29,"2",True,"1")

[June7]

That isn't a query, it is an expression. Is Marks a field the form/report RecordSource? Why the prefix of table name? Is there more than one field in the RecordSource named Marks?

[Seeletse]

Is Marks a field the form/report RecordSource - Yes
Why the prefix of table name? - Thought that's how it's supposed to be
Is there more than one field in the RecordSource named Marks - No

[Seeletse]

thanks, i just tried it and it works.

[Seeletse]

however, i went to the table that should hold the information from the form and the level field is empty.

[June7]

That won't save data to table. It is just a calculation. If you want to save the value will require code behind form. The trick is figuring out what event to put the code in. Maybe the AfterUpdate of Marks: Me!Level = Me.Leveltextbox

Or just calculate whenever needed.

[Seeletse]

There is a marks field in another table in the DB.

[Seeletse]

how can i get the level to updated when information is entered in the marks field? I tried putting the expression in AfterUpdate of Marks but that didn't work. Should i try it in the lost focus instead?

[June7]

Why didn't it work, what happened - error message, wrong results, nothing? If it didn't work in AfterUpdate I doubt will work in LostFocus.

[Seeletse]

nothing is displayed.

[June7]

Nothing is displayed in table after the code runs? No error message? Post the procedure for analysis or provide db. Follow instructions at bottom of my post.

[Seeletse]

database upload done

[June7]

Code is behind the wrong control. My suggestion was to use the Marks AfterUpdate event.

The Switch calculation does not go in event property. It can be be in ControlSource or in the VBA. The Switch syntax is wrong. See earlier post. Level is a number field so omit the quote marks.

Event property would be [Event Procedure] for VBA code:
Private Sub txtScoreCardMarks_AfterUpdate()
Me!Level = Switch([Marks] > 79, 7, [Marks] > 69, 6, [Marks] > 59, 5, [Marks] > 49, 4, [Marks] > 39, 3, [Marks] > 29, 2, True, 1)
End Sub

Set the ControlSource of txtScoreCardLevel to Level. If you don't want users to edit this value then set properties Locked Yes and TabStop No.

[Seeletse]

thanks, it's now sorted.