Solved

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

Posted on 2004-09-06
2
872 Views
Last Modified: 2008-03-17
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
0
Comment
Question by:nexisvi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 50 total points
ID: 11993023
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
 

Author Comment

by:nexisvi
ID: 11993562
It makes sense I guess.  I'll just include the formula as a calculated field as you suggested.  Thanks for your help.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question