Calculated Value to keep in a Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

i have some values that i calculate them on Visual Basic on my Access program,
after an update option

They dont have any textbox or label in my form because is allready with many of them.
So, what i have done is to calculate them and assign them to the variable in my table
i.e. Table!variable = CalculatedFiel

However , when i am going to see my table, it is not updated and the value of thi
calculated field is still 0 (zero)

How can i solve this basic problem? i think i have everything right

Best regard
Zec
 
Zeca,

You can't reference and update a table field this way. You have two options
to get this done:

1. Open the table as a Recordset and update the field via recordset
operations, or
2. Add a bound textbox to your form (it doesn't have to be visible), so you
set the value of that, and it updates the table in turn, being bound to the
target field.

HTH,
Nikos
 
hi

Thanks Nikos for your kindly reply

the first option i don't know how to do it

the second choice , for me will be ok if i have few fields in my form , but as i mentioned, i have so many fields in my form, and i don't want to create any more.
Maybe i have to and make them invisible. But it will be the last thing i want to do.

Can you send me an example of the first choice??

waiting your reply,
thanks for your help

Zeca
 
Zeca,

I'll be happy to do that, but I need to know the names of the form and
involved controls on it, as well as the name of the undelying table and
involved fields in it, plus the calculation formula so I can give you
something meaningful.

Nikos

Zeca said:
hi

Thanks Nikos for your kindly reply

the first option i don't know how to do it

the second choice , for me will be ok if i have few fields in my form ,
Click to expand...
but as i mentioned, i have so many fields in my form, and i don't want to
create any more.
 
Hi Nko

Thanks for your prompt repl

i have done your 2nd option: the invisible textboxes. is working and that's Ok

However i want to know how it's done when i am dealing with recordset

as i told is a long form and a long table

How do you want me to do it? Do you have any mail that i can use for this purpose

I will send a part of the proble

the name of Table TPavimentos ; PrimaryKey Tipopavimento ; no duplicate
the name of form FrmCalcul
the fields in form VAO ; F1 ; MOMMVAO ; AUXE
Event that fires the code After Update F

Calculated Fields that have the same name in table TPavimento
ValLa1 = VAO-F
ValL2a1 = VAO-(2*F1
Val2La1 = (2*VAO)-F
Val3L2a1 = (3*VAO) -(2*F1)
MeiovaoBeta = 1.5 *(1 - 8/(me.MOMMVAO
ApoiosBeta1 = 1.5 * (1 - 8/(me.MOMMVAO + me.AUXE3)
ApoiosBeta2 = 1.5 * (1 - 8/(me.MOMMVAO +me.AUXE3/2)

if there is anything elese u need it, just as

Thanks in advanc

Best regard
Zec


Hope to hear from yo

Best regards
Zec
 
Zeca,

The code below should do it:

Dim ctrl(6) As String, strSQL As String
Dim i As Integer
Dim rst As DAO.Recordset

ctrl(0) = "ValLa1"
ctrl(1) = "ValL2a1"
ctrl(2) = "Val2La1"
ctrl(3) = "Val3L2a1"
ctrl(4) = "MeiovaoBeta"
ctrl(5) = "ApoiosBeta1"
ctrl(6) = "ApoiosBeta2"

strSQL = "SELECT * FROM TPavimentos WHERE Tipopavimento ='"
strSQL = strSQL & Me.Tipopavimento & "'"
Set rst = CurrentDb.OpenRecordset(strSQL)
rst.Edit
rst.Fields(ctrl(0)) = Me.VAO - Me.f1
rst.Fields(ctrl(1)) = Me.VAO - (2 * Me.f1)
rst.Fields(ctrl(2)) = (2 * Me.VAO) - Me.f1
rst.Fields(ctrl(3)) = (3 * Me.VAO) - (2 * Me.f1)
rst.Fields(ctrl(4)) = 1.5 * (1 - 8 / (Me.MOMMVAO))
rst.Fields(ctrl(5)) = 1.5 * (1 - 8 / (Me.MOMMVAO + Me.AUXE3))
rst.Fields(ctrl(6)) = 1.5 * (1 - 8 / (Me.MOMMVAO + Me.AUXE3 / 2))
rst.Update
rst.Close
Set rst = Nothing
For i = 0 To 6
Me.Controls(ctrl(i)).Requery
Next

Just make sure you include Microsoft DAO object library in your references
(DAO3.51 for Access97, DAO 3.6 for A2K or later).

I still don't understand though, why you do not want to bind the calculated
controls on your form to their corresponding fields in the table, since they
are already there. I must be missing something. Anyway, I hope this works
for you. If you want to mail me, it's:
nyannaco at in dot gr

Nikos
 
Back
Top