Default from UDF which must be changeable

[andy49]

I have created a query which presents pupil info in a form including a UDF to find a default value for [entrance_tier]



However on the form and the query I can't edit/change that value (if it's decided to be too low/high)

I realise I can't alter it because of the UDF but am looking for a work around.

any suggestions? I considered making the query a create table query but wondered if anyone had other thoughts.


Sent from my iPhone using Tapatalk

[orange]

More info needed.
Show us the code you're using.

An example of what you have showing what you need as output would also be useful.

[andy49]

The tier_draft is calculated by a function in the query.

Need anything else ?


Sent from my iPhone using Tapatalk

[John_G]

If you want that default value to be determined only when you create a new record, then you could use the Default Value property of the combo boxes to set the initial value, after which you could change it if necessary. That wouldn't work if the default value depends on other values in the record, in which case you would need to use After Update events on those other fields, I think. You can easily prevent those events from changing anything if it's not a new record.

[andy49]

If you want that default value to be determined only when you create a new record, then you could use the Default Value property of the combo boxes to set the initial value, after which you could change it if necessary. That wouldn't work if the default value depends on other values in the record, in which case you would need to use After Update events on those other fields, I think. You can easily prevent those events from changing anything if it's not a new record.

Thanks John

Ive changed the default to the UDF but I'd like to be able to change that value with the combobox


The combobox has the correct values to be chosen from but I cant select any of them. The original default remains there.

[John_G]

What field are the combo boxes bound to in the underlying query? If it is a calculated field, then it cannot be edited. Another possibility is that the entire query is not updatable, and there can be several reasons for that. Are other controls on the form bound to fields in the same query, and can they be edited?

[andy49]

What field are the combo boxes bound to in the underlying query? If it is a calculated field, then it cannot be edited. Another possibility is that the entire query is not updatable, and there can be several reasons for that. Are other controls on the form bound to fields in the same query, and can they be edited?

Thanks John

Hope this helps.


The control source is Tier_draft, a calculated field (using a user defined function)

I can't edit anything else on the form. (Or on the query from where the form gets its data)

Thanks




Sent from my iPhone using Tapatalk

[John_G]

I can't edit anything else on the form. (Or on the query from where the form gets its data)

That's the bit I was looking for - the query is not updatable. What does the query look like - can you post the SQL of it? If the query is a totals query, then it is not updatable. However, if it is just a multi-table select query, then there might be ways to make it updatable (not always, though).

[andy49]

SELECT subqrychoosetiers.*, tbl_lvl2grade.*, FinddraftTier([GradesID_PK],"Nov") AS Tier_draft
FROM subqrychoosetiers INNER JOIN tbl_lvl2grade ON subqrychoosetiers.MathsTarget = tbl_lvl2grade.grade;
;

the query subqrychoosetiers looks like:

SELECT qryMathsTargets.Maths_target, qryMathsTargets.Teacher_target, qryMathsTargets.MathsTarget, tbl_students.StudentID_PK, tbl_students.LastName, tbl_students.FirstName, tbl_students.Mathematics_Group, tbl_students.StudentYearGroup
FROM tbl_students LEFT JOIN qryMathsTargets ON tbl_students.UPN = qryMathsTargets.upn;

The query selects pupil names etc and links them to their target grades using their UPN (unique pupil Number)