Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    361

    Unanswered: Write calculation to a table

    I have a form that queries info off a table. I have a few text boxes that perform calculations based on the values returned by the query. Is there a way to "Write" those values to the table where the query data came from?
    Maybe populate an unbound text box?

    Thanks.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Hmm.. Why do you need to do this exactly?

    What is the process you envision here? Is data being written from one table to another? Are you changing the original data to your new calculated value? Are the calculations time or user sensitive?

    Could this be done with a simple update query instead?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    If your form is bound to the table, then data written to any bound field is writing to the table.

    If your form is unbound - or you want to write to a different datasource than the one the form is bound to, use recordsets in code. The code could be behind a button, such as an "Update" button, on an After_Update event to one of your other controls, or anywhere else, for that matter.

    Have fun

  4. #4
    Join Date
    Mar 2004
    Posts
    361
    My form is bound to a table. Right now the user queries an account number. The user then adds information to a bunch of text boxes. A few calculations are based on these manually inputed textboxes. I need the data on the table for future cumulative analysis. I have an update button right now that adds the manually inputted data to the same table in which the acct number comes from. I just don't know how to write the calculation fields.

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    You can calculate in code, using a Function that you can define, or you can write equations as the record source of text boxes.

    To use text boxes, simply write the equation, as you would in Excel, in the Recordsource property of the text box. To use other controls as variables, enclose the Name of the other controls in brackets.

    For example, you have 3 text boxes: Price, Quantity and Total.
    1. Price pulls data from a table, corresponding to the item - so Price is bound (it's recordsource matches the field name in the form's ).
    2. Quantity is unbound so that the user can enter a value (it's Recordsource is blank and it show "Unbound" in design view)
    3. Total is where we want our calculation to land.

    Using the equations in the text box, the simplist method for this example, I would type the following in the Recordsource property of Total:
    Code:
    =[Price]*[Quantity]
    Just like in Excel, any time eithe of the "variables" changes, the displayed result in Total will change as well. You would have to use an update procedure to put that data into a table.

    To use a function, you could set the Recordsource for Total to the following:
    Code:
    =fnTotal()
    Then, put a function in the code behind the form as follows:
    Code:
    Function fnTotal() as Single
        fnTotal = Me.Price * Me.Quantity
    End Function
    There's actually some much more sophisticated things you can do with a Function. You can even add the code to update the table here.

    Your best bet, though is to split the difference. If the value of Total is supposed to go back onto the same table, then set the Recordsource of Total to the field, and place an "Afterupadte" event on the Quantity field. For that event's code, place the following:
    Code:
    Private Sub Quantity_AfterUpdate()
         Me.Total = Me.Quantity * Me.Price
         Me.Dirty = True
    End Sub
    The "Me.Dirty = True" tells Access that the data changed (generally not required, but it doesn't hurt). This way, when the record changes, Access will save the data.

    As the old saying goes, "there's more than one way to skin a cat" - in this case, I can think of at several other ways to do this off the top of my head - Access is very flexible in this regard.

    Have fun

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •