Dynamic Value field?

[Pretty1996]

Hi All,

I am trying to achieve a dynamic value field. What I mean by this is a field in a database which changes depending on a field in another record.

For example:-

I have 10 records in a table.

Each record has a default value of 2 assigned to it. So the total across the 10 records is equal to 20.

However if any record has a value greater/less than 2, the value for all other records changes accordingly as the maximum total across the 10 records cannot exceed 20.

I.e. if one record has a value of 11, then the other 9 records get a value of 1.

And if one record has a value of 1, then all other records get a value of 2.11.

Thanks in advance for your help,

I’m using Access 2010.

~ Sukh.

 

[Micron]

What if both conditions exist at the same time? Seems to me that as soon as one becomes 11, the others become one, giving you the second condition in your post. Now what? Maybe there is only one "trigger" field in one record somewhere, which is not part of the records you want to change. In that case, try a calculated query that subtracts the trigger value from 20 and divides the result by 9. Use that query as the input for an update query.

 

[strive4peace]

so the last record changed wins? No weighing for the other records at all? Just an even split? Out of curiosity, what is the purpose of this?

 

[HiTechCoach]

Hi All,

I am trying to achieve a dynamic value field. What I mean by this is a field in a database which changes depending on a field in another record.

For example:-

I have 10 records in a table.

Each record has a default value of 2 assigned to it. So the total across the 10 records is equal to 20.

However if any record has a value greater/less than 2, the value for all other records changes accordingly as the maximum total across the 10 records cannot exceed 20.

I.e. if one record has a value of 11, then the other 9 records get a value of 1.

And if one record has a value of 1, then all other records get a value of 2.11.

Thanks in advance for your help,

I’m using Access 2010.

~ Sukh.

Sukh,

Yes it is possible with some VBA code.

I will assume the form in in continuous view. ALso each record has a primary key.

Use the form's After update event ti run an update query that include all the record except the record that was just changed by excllding its primary key.

New value for other record will be will be: (20 - Me.txtMyContolNameThatwasJustUpdatedHere) / 9

**************** ALERT >>>>>>>>>>>>>

Make A backup of the tables before trying this

<<<<<<<<<<<<< ALERT ***************


In the form's After Update Event use something like this:

Dim strSQL as String

  strSQL - = "Update tblMyTable Set tblMyTable.MyValueField = " & (20 - Me.txtMyContolNameThatwasJustUpdatedHere) / 9 & "  Where tblMyTable.PrimaryKeyField <> " & Me.txtPrimaryKey

CurrentDB.Execute strSQL, dbFailOnError
Me.Requery

The SQL may need additional criteria in the Where clause to get only the desired group of records.


**************** ALERT >>>>>>>>>>>>>

Make A backup of the tables before trying this

<<<<<<<<<<<<< ALERT ***************