Calculate and Update

[zollo9999]

yes yes I'm new to VB & VBA and I'm really a Tech-user than a programmer.

I know that my problem bends or breaks database redundance rules but I think I have good reason to do so.
I will simplify my problem.

I have a table with three number fields A, B, C
and a form that displays the table data in textboxs
ie A,B and C are bound controls.

A contains a number say 10.
B gets data input from the user.
Say they type in the value 20
I want to put a command button on the form that adds A to B and stores the value ie 30 in C in the table permanently.

I've done this using 2 queries one for the Calc and other for the update, but I think it would be better to do it in VBA code.

Also A B and C are array or have components.
ie A1 A2 A3 ATotal, B1 B2 B3 BTotal, C1 C2 C3 CTotal
and ATotal = A1 + A2 + A3 etc.
(so one record is really a table or matrix)

Later, when the user adds another record to the table, I want A to become the previous records C.

I know this is probably simple, but I cant do it.
(Its much easlier in Excel)
Any help would be appreciated
Thanks in advance
Zol

 

[LittleSmudge]

First step

Assuming the the text box NAMES are A, B & C as well as their FIELD names
In the After_Update event for text box B place the code
C = A*B

You don't need a command button.


For the rest - I need clarification, it's either too early on a Monday morning or something. I can't figure out what you're actually asking for.


G LS

 

[zollo9999]

Thanks
That sounds easy already, I'll try it without the command button. I think I just don't know enough about VBA yet.

Was that C=A*B or C=A+B ?
(I'm using simple math not Matrix math)

I'm trying to PUSH a calculated value into a bound control, ie into a table. (It must be simple)

I'm reading Susan Novalis Access 97 Macros and VBA.
She says you can do this and refers to Part III of the book which is 500 pages long.
After weeks of study, I haven't mastered it yet.

I'm really just trying to do simple Maths in Access and/or VBA, (which shouldn't be too hard)
and then store the results to the table, which is usually a big no-no due to redundancy and normalization rules.

It's like this:
I want one record to represent all the data in a matrix
like this:
A1 B1 C1
A2 B2 C2
A3 B3 C3
AT BT CT

ie The fields in the table are:
A1 A2 A3 AT B1 B2 B3 BT C1 C2 C3 CT

I have data in A1 to A3 (and AT) and will input data into B1 to B3, then get the results in C1 to C3 (and CT).

The Maths is
C1= A1 + B1,
C2= A2 + B2,
C3= A3 + B3
Also
AT = A1 + A2 + A3,
BT = B1 + B2 + B3,
CT = C1 + C2 + C3.

Susan Novalis says I'd need to write one piece of code to do the calc and another piece of code to "PUSH" or store the result.

Thanks
Zol

 

[LittleSmudge]

Sorry - you said ADD, I read multiply
Yes you need C = A + B

Can you GUARANTEE that the Arrays will be a defined length?
Ie. Is A3 the max of A ?



Personally, I think I'd go for a Two table Solution

tblMatrix - the table that defines which matrix we're talking about
MatrixId PrimaryKey in tblMatrix
OtherFields
etc.. ..

tblMatrixRow is table containing rows of the matrix ( regardless of which matrix they are in )
MatrixRowID PrimeKey for this table
MatrixRef Foreign Key pointing to tblMatrix
A
B
C - you KNOW you really shouyldn't be storing C don't you! So I won't preach further.
OtherFields
etc.. ..

On a Form have a combo that allows the user to select the MatrixId from tblMatrix

In a Subform on this main form you hasve the fields A, B and C
This subform is in CONTINUOUS format
And in the Footer of this subform you can SUM the contents of the A, B and C fields to get ATotal, BTotal and CTotal

Also in the Relationships windos join the two tables together and ensure Referential Integrity.
In the subForm control on the main form set the ParentFields property to MatrixId and the ChildField property to MatrixRef.

Then when you select a MatrixId on the main form the sub form will just display the appropriate records and sum them for you.
If you set it up in AllowAdditions then it will have a blank record at the bottom to allow the user to add an addition row to the matrix whenever appropriate. Because of a nice piece of Developer Helpfulness in Access, when you do create a new record this way Access will AUTOMATICALLY populate the MatrixRef field with the appropriate value so you will always maintain the link between the two tables.


THEN,
When you come to making a NEW matrix you can simply create a new MaxtrixId in tblMatrix and then create a query to extract the rows from the old Matrix A+B values and APPEND the sum into the tblMatrxRows along with the new MatrixId value.

QED



'ope-that-'elps

G LS

 

[zollo9999]

Thanks
Those are all Brilliant Ideas

In my app, it is unlikely that the matrix will expand beyond 6 components over the next 5 years and the life of the database so that is what I was allowing for.
But your 2 table solution avoids any problems with future expansion and will ease coding and maintenance, so I'll give it a go.

Plus its so easy if I let the Access Wizard establish the inter form communication.

Excellent
Thanks again