Update the value in a field based upon (1 Viewer)

W

welshlewy

New member
Local time
Today, 00:47
Joined
Oct 27, 2019
Messages
6
Hi all,
Looking for some help with a calculated field in a form for a school assessment database, apologies for being a total noob.
This is what I'm aiming for:

  • The table is a series of Yes/No tickbox fields for 9 different skills that can be met - [ACL1]..[ACL9]
  • The tenth field is another Yes/No that stores whether at least half of the preceding group of skills has been met - ]ACL - Met]
I have created a form, and when entering the data, if at least half of the skills tick boxes are ticked, I want a calculated field in the last tick box to display a tick; if less than half are ticked it remains false. This data should then be stored in the table. I hope that makes sense.

I created a simple formula in the Control Source property of [ACL - Met] that examines each of the ACL1-9 fields, and adds the total together. If that number is greater than 5, it changes the value in [ACL-Met] to True.
=IIf(Abs(([ACL1]+[ACL2]+[ACL3]+[ACL4]+[ACL5]+[ACL6]+[ACL7]+[ACL8]+[ACL9]))>5,[ACL - Met]=Yes,[ACL - Met]=No)


WHen using this, the displayed value in [ACL - Met] updates accordingly, but I cannot get the data which is calculated to save back to the table, overwriting whatever is currently there.
Can anyone help, please?
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 08:47
Joined
Sep 21, 2011
Messages
14,223
Your structure is all wrong.
Each ACL should be a record. Then a simple count of how many are true would give you what you want.
Also you should not store calculated data in most situations, just calc as needed.?
The correct structure again would simplify that.

If you have to save it, use an update query.

HTH
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 00:47
Joined
Oct 29, 2018
Messages
21,449
Hi Welsh. Welcome to AWF! If you're not familiar with the term, try looking up "Normalization " to see what Gasman meant. Cheers!
 
W

welshlewy

New member
Local time
Today, 00:47
Joined
Oct 27, 2019
Messages
6
Hi both, I'm familiar with normalization, but the ACL1 -9 fields I need to store are not repeating attributes, ACL is a simply a shorthand code for completely different skills, and pieces of data, that I need to store.
Perhaps I've not explained my aims well enough,it makes sense in my head! The database is going to be a tracking tool for a teacher to store the details of all the tasks they teach in lessons, and which skills are going to be developed in each task. There are 4 different cetegories skill, which are divided into up to 9 sub-components; if over half of these sub-components have been achieved, then we can consider the skill to have been met.
I want the database to store this data, and to automatically calculate and store if enough of the subcomponents have been ticked off to consider the skill achieved. I need to store if a skill has been met, or not, so I can graph it. Access only allows for 6 data points of data to be graphed, hence why Ive tried to aggregate the skills data in this way.
Perhaps a database is not the optimal tool to to store this data, I've considered a spreadsheet, but the amount of data seemed a bit unwieldy for each task.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 00:47
Joined
Oct 29, 2018
Messages
21,449
welshlewy said:
Hi both, I'm familiar with normalization, but the ACL1 -9 fields I need to store are not repeating attributes, ACL is a simply a shorthand code for completely different skills, and pieces of data, that I need to store.
Perhaps I've not explained my aims well enough,it makes sense in my head! The database is going to be a tracking tool for a teacher to store the details of all the tasks they teach in lessons, and which skills are going to be developed in each task. There are 4 different cetegories skill, which are divided into up to 9 sub-components; if over half of these sub-components have been achieved, then we can consider the skill to have been met.
I want the database to store this data, and to automatically calculate and store if enough of the subcomponents have been ticked off to consider the skill achieved. I need to store if a skill has been met, or not, so I can graph it. Access only allows for 6 data points of data to be graphed, hence why Ive tried to aggregate the skills data in this way.
Perhaps a database is not the optimal tool to to store this data, I've considered a spreadsheet, but the amount of data seemed a bit unwieldy for each task.
Click to expand...
Hi Welsh. With all due respect, you may be familiar with the rules of Normalization, but it seems like you're not following them. Having multiple Yes/No fields is definitely a sign of a non-normalized structure. You can use a database for what you need, but it's better/simpler if you're able to follow database rules; otherwise, you'll be trying to make Access an Excel application, which it isn't. If you don't agree, please ask us any questions why you don't think so, and we'll try to provide more information. Cheers!
 
W

welshlewy

New member
Local time
Today, 00:47
Joined
Oct 27, 2019
Messages
6
Maybe that's the problem, perhaps Access just isn't the tool I need for the data I want to store...
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:47
Joined
May 7, 2009
Messages
19,229
it won't update the field because of the expression on the textbox:

=IIf(Abs(([ACL1]+[ACL2]+[ACL3]+[ACL4]+[ACL5]+[ACL6]+[ACL7]+[ACL8]+[ACL9]))>5,[ACL - Met]=Yes,[ACL - Met]=No)

remove the expression and add code to the Current Event and BeforeUpdate Event of the Form:
Code: 
Private Sub Form_Current()
Me.[ACL - Met]= _
Abs([ACL1]+[ACL2]+[ACL3]+[ACL4]+[ACL5]+[ACL6]+[ACL7]+[ACL8]+[ACL9])>5
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Call Form_Current
End Sub
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:47
Joined
May 7, 2009
Messages
19,229
the update will not be immediate, you need to go to another record to reflect the change.
if you want immediate change, add this Expression on the Change Event of each 9 checkbox:

=fncACLMet()

then on the form, create the function:
Code: 
Public Function fncACLMet()
Me.[ACL - Met]= _
Abs([ACL1]+[ACL2]+[ACL3]+[ACL4]+[ACL5]+[ACL6]+[ACL7]+[ACL8]+[ACL9])>5
End Function

Private Sub Form_Current()
Call fncACLMet
Me.Dirty = False
End Sub
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 00:47
Joined
Oct 29, 2018
Messages
21,449
welshlewy said:
Maybe that's the problem, perhaps Access just isn't the tool I need for the data I want to store...
Click to expand...
Hi. Maybe. We can't tell you that since we are not familiar with your data and your requirements. Good luck!
 
W

welshlewy

New member
Local time
Today, 00:47
Joined
Oct 27, 2019
Messages
6
arnelgp said:
it won't update the field because of the expression on the textbox:

=IIf(Abs(([ACL1]+[ACL2]+[ACL3]+[ACL4]+[ACL5]+[ACL6]+[ACL7]+[ACL8]+[ACL9]))>5,[ACL - Met]=Yes,[ACL - Met]=No)

remove the expression and add code to the Current Event and BeforeUpdate Event of the Form:
Code: 
Private Sub Form_Current()
Me.[ACL - Met]= _
Abs([ACL1]+[ACL2]+[ACL3]+[ACL4]+[ACL5]+[ACL6]+[ACL7]+[ACL8]+[ACL9])>5
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Call Form_Current
End Sub
Click to expand...

Hi, sorry to be a pain, but I tried that, and I just get the error 'you can't assign a value to this object'...
 
You must log in or register to reply here.

Similar threads

Safaa
Solved Update the value of a field based on two other fields
Replies
10
Views
350
Safaa
Safaa
J
Update field - how to calulate a new date
Replies
3
Views
189
plog
P
H
Is it possible to edit the contents of the Unhide Columns box in the datasheet part of a split form?
Replies
14
Views
660
isladogs
isladogs
G
Hide a field in Subform (Datasheet) form based on Form Value
Replies
5
Views
199
Pat Hartman
P
J
update field
Replies
4
Views
267
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom