Adding Calculated Field to Table with VBA

[virgilio]

I'm building a new tool for data that currently exists in SAP. The idea is to port the data to an Access table where it can be utilized more responsively, and managed locally. I have the data imported from an Excel report into my new Access DB, but I've found that I need a new calculated field in the database.

Someone wrote a nice guide for adding a field with VBA on this website: https://codekabinett.com/rdumps.php?...e-quarter-date, scroll down to the section titled 'Create calculated fields with code'.



I modified the code from that page to work for me, this code is on the click event for a button on an unbound form. Clicking the button successfully creates a field called "Calculated Field" in my WASTE_INVENTORY table, that contains "1+1" in every record.

Private Sub Command0_Click()
   Dim db As DAO.Database
   Dim td As DAO.TableDef
   Dim fld As DAO.Field2
   
   Dim tempVal As Long
   
   Set db = CurrentDb()
   Set td = db.TableDefs("WASTE_INVENTORY")
   
   Set fld = td.CreateField("CalculatedField", dbInteger)
   fld.Expression = "1+1"
   td.Fields.Append fld
End Sub

Now I need to start swapping in variables into the field expression from another field in that record, and this is where I need help. How do I grab the value of a field in the record to put in a variable to put in the fld.Expression formula?

[pbaldy]

I suspect you want something like:

fld.Expression = "[Quantity] * [Price]"

If you want to use the current value on the form instead of "1+1" then

fld.Expression = "1+" & Me.TextboxName

[virgilio]

Ah, So that sort of worked. When I used the following code it ran without error:

   fld.Expression = "[MATERIAL]+1"

But the new field contains "#Num!" in all rows. I'm sure this is because [MATERIAL] contains a number stored as a string.

This is really what I'm trying to do (Changed lines in red):

Private Sub Command0_Click()
   Dim db As DAO.Database
   Dim td As DAO.TableDef
   Dim fld As DAO.Field2
   
   Dim tempName As String
   Dim tempVal As Double
   
    
   Set db = CurrentDb()
   Set td = db.TableDefs("WASTE_INVENTORY")
   
   tempName = [MATERIAL]
   
   Select Case tempName
      Case "1187356"
         tempVal = 292.8
   End Select
   
   Set fld = td.CreateField("CalculatedField", dbInteger)
   fld.Expression = tempVal & "+1"
   td.Fields.Append fld
End Sub

but this results in the error on the 'tempName = [Material]' line: "Run-Time error '2465': Microsoft Access cant find the field '|1' referred to in your expression"

I also tried to type convert it using 'tempNum = CINT([MATERIAL])', but I get the same error.

[pbaldy]

What is your goal for the field? Following what you've written, the field would have a value of 293.8 for all records unless I'm misreading it. If you want the value to be relative to each record, you need to refer to a field in the record.

[pbaldy]

Just saw your edit. Because of the length of the number, you'd need to use Clng().

[virgilio]

What is your goal for the field?

The fld.expressions are going to be radioactivity decay equations.

There are about 20 or so possible values for tempName, these numbers refer to isotopes. I want to set tempVal to the half life, in this case Thallium 202 is 292.8, and then replace the '+1' with the rest of the decay calculation.

Edit: It would definitely be easier to do this manually in the table editor or Excel: ie filter by "[MATERIAL]" then add the appropriate number to a field called "HALF_LIFE". However, I would really like to learn how to do this in VBA since I started this way.

[pbaldy]

But I'm guessing that each record would have it's own result? If you set a calculated field with a fixed value, all records will have the same result in that field. By the way, it's unusual to be adding a calculated field on the fly. Is there a reason you don't just create it in design view?

My gut feeling is that you won't be able to use a calculated field, because the calculation relies on something outside the record (in this case the half life). If the half life was a field in the record you could use it, but that likely isn't practical. I'd probably have a table with the isotopes and their half life. You could do your calculation in a function that took the element as an input.

[virgilio]

Oh, that stinks but ok. I can handle doing it that way.

Thanks PBaldy.

[pbaldy]

No problem, post back if you get stuck.