changing table entry while form accesses it.

[Banista]

Hi all, I have the following basic problem:

I have a form with 2 fields, getting their value from a table. The form is pretty basic, you can navigate entries etc.

Now the thing is, field2 is calculated from field1, but should also be stored in the db.

The way I went about this is to write a VBA procedure field1.OnUpdate, which opens the underlying table, calculates the field2 table value, then stores this value. The idea is that this way, field2 form field will be able to get the new value from the table, for example with a .Requery command.

But, this doesn't work
The OnUpdate procedure works just fine, but the new value doesn't appear in the form, and once you want to leave the table entry in the form, you get a prompt saying another user has changed the value, and whether you want to keep your own or the other's value. If you say "my own", field2 in the table remains empty. If you say "the other user's", you get the correct value as calculated.

Sooo, how do I get the calculated value to appear in the form AND be stored in the table, without any prompt from the user? Any different ways to go about this are of course very welcome.

Here's my code for reference purposes:

Private Sub MAXMENGEVOL_1_4KLT_Feld_AfterUpdate()
    'open table
    Dim db As Database
    Dim menge_gewicht, berechnet_maxmenge_gewicht As Double
    Dim RS_Daten  As Recordset
    Set db = CurrentDb
    Set RS_Daten = db.OpenRecordset("010_tbl_SNR_Bestand_ges", dbOpenTable)
    RS_Daten.Index = "Material SNR"
    'calculate field2 value
    RS_Daten.Seek "=", Form_010_FORM_SNR_Bestand_ges.Text237
    menge_gewicht = RS_Daten![Gewicht SAP]
    berechnet_maxmenge_gewicht = RS_Daten![Max Menge nach Vol 1/4 KLT]

    'store field2 value
    RS_Daten.Edit
    RS_Daten![Gewicht bei Menge kg 1/4 KLT] = menge_gewicht * berechnet_maxmenge_gewicht
    RS_Daten.Update
    RS_Daten.Close
    'update form
    Form_010_FORM_SNR_Bestand_ges.Gewicht_nach_Menge_1_4KLT_Feld.Requery
End Sub

thanks in advance for pointers as usual

 

[Banista]

nevermind, turns out if I properly link all files to the table, updating the form fields saves the values there as well, and it's much less hassle to boot.

 

[RuralGuy]

As you probably know, it is rarely a good idea to store a calculated value. Your calculation can be done in a query whenever you need it.