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?
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.
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.
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:
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:
Then, put a function in the code behind the form as follows:
Function fnTotal() as Single
fnTotal = Me.Price * Me.Quantity
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:
Private Sub Quantity_AfterUpdate()
Me.Total = Me.Quantity * Me.Price
Me.Dirty = True
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.