Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Location
    DK
    Posts
    1

    Unanswered: Calculated field in a table not updated

    Hej

    I'm fairly new to access, and I have run into a problem.

    I have two tables called tblKemisk and tblVandprct, and a form, frmVandprct, and the tables are related through identical, autonumbered ID-fields with 1 to 1 relations.

    In the table, there is a field named vandprct, which is the result of some calculation. The sourcedata for the calculation are entered into the form.

    I have tried to do this with VBA, since I'm fairly good at Excel VBA.

    BUT, it doesn't work. Here is some code:

    Private Sub Ost1_AfterUpdate()

    If Ost1 > 0 Or Ost2 > 0 Then

    idx = Me.ID
    bgr1 = Me.Bæger1
    bgr2 = Me.Bæger2
    Ost1 = Me.Ost1
    Ost2 = Me.Ost2
    vej1 = Me.Vejning1
    vej2 = Me.Vejning2

    vand1 = (Ost1 - (vej1 - bgr1)) / Ost1 * 100
    vand2 = (Ost2 - (vej2 - bgr2)) / Ost2 * 100

    vand = Replace(CSng(Format((vand1 + vand2) / 2, "0.0")), ",", ".")

    CurrentDb.Execute "UPDATE tblKemisk SET vandprct=" & vand & " WHERE ID=" & idx
    'DoCmd.RunSQL "UPDATE tblKemisk SET vandprct=" & vand & " WHERE ID=" & idx

    End If

    End Sub

    I have tried both with Execute (nothing at all happens) and with DoCmd (first a warning that 1 field is going to be updated, then an error message that the table is locked), and of course nothing is written to the field.

    I have also thought about using the expression editor to calculate the field, but that also doesn't work, partly because some of the data in the table hav to be entered for the records before I can identify the records in the form (I really don't know enough about access to be more clear on this).

    I have tried updating from excel VBA, at that works, here's the code:

    Sub sqlDriftskontrol()

    Dim myDB As DAO.Database
    Dim myRst As DAO.Recordset
    Dim myRstSQL As DAO.Recordset
    Dim strSQL As String

    Set myDB = OpenDatabase("O:\SPS\Driftskontrol.mdb")

    ' Just some data:
    idx = 3
    bgr1 = 11
    bgr2 = 12
    Ost1 = 5
    Ost2 = 6
    vej1 = 16
    vej2 = 15

    vand1 = (Ost1 - (vej1 - bgr1)) / Ost1 * 100
    vand2 = (Ost2 - (vej2 - bgr2)) / Ost2 * 100

    vand = Replace(CSng(Format((vand1 + vand2) / 2, "0.0")), ",", ".")

    myDB.Execute "UPDATE tblKemisk SET vandprct=" & vand & " WHERE ID=" & idx

    End Sub


    Do you have any ideas?

    Cheers

    Iwer Mørck

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Unhappy Calculted field!

    If the information for this field is always available, then it can always be calculated by a query or text box on a form / report etc.

    If so DO NOT STORE CALCULATED FIELDS IN A TABLE unless you have a very very very good reason!
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

Posting Permissions

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