can't update an unbound field in Datasheet view for current record without updating all records

This should hopefully be an easy one, but I can't seem to figure it out.  I have a form being displayed in "Datasheet View" in microsoft access.  The datasheet view has several columns.

example:  ID, ColumnA, ColumnB, ColumnAB

Now, lets say that "ColumnAB" is unbound, but "ID", "ColumnA", and "ColumnB" are bound to fields in a certain table in a database.  ColumnAB is unbound because I just want to display the results of a simple calculation after values have been input into ColumnA and ColumnB without actually storing anything in the database.  Here is an example of the code I would write inside a function that would be called when the value of either ColumnA or ColumnB changes...

If [ColumnA].Value And [ColumnB].Value Then
        [ColumnAB].Value = [ColumnA].Value + [ColumnB].Value
End IF

Everything appears to work fine when I'm typing in my first record, the value of ColumnAB will update and display the correct result.  The problem occurs when I add/switch to another record (I'm in Datasheet view, so this form is showing all the records in the table at the same time).  If I switch to record 2 and change the value of ColumnA, then it calls my function to update the value of ColumnAB.  The problem is that the code [ColumnAB].value seems to update the value of ALL [ColumnAB] fields, and not just the one for the current record.  What do I need to change in my code to only refer to the [ColumnAB] field of the current record, and not change all values in the whole form?

Thanks in advance
nexisviAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
shanesuebsahakarnConnect With a Mentor Commented:
You can't. This is normal behaviour - when in datasheet or continuous form view with unbound forms. If you want to include a value that is calculated on a per-row basis, you'll need to either make it a calculated field in the form's underlying query, or a calculated control (i.e. a formula in the text box's Control Source) on the form.
0
 
nexisviAuthor Commented:
It makes sense I guess.  I'll just include the formula as a calculated field as you suggested.  Thanks for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.