Storing info in txtbox into table?

[brianjessup]

I'd like to grab data in a txtbox, perform a few calculations on it and store it in a field (based on a few of the control settings) of a record (again based on a few controls) in a table. I can't lock the txtbox to the table because I need to perform a few calc's when getting data from a field or storing to a field, i.e. the table info viewed by itself is almost useless without the form to interface with the user.

How do you save data from a txtbox to a specific field when a user hits a save button?

Thanks much,
Brian

 

[pdx_man]

I assume that you are using VBA to perform the calculations, so ...

Have a control on your form bound to the field that you want to store the result of the calculation. Then reference that field in your code:

Me.MyField = ThisThing * ThatThing

Now, if this is a result of other fields being stored, then you should do your calculation in an underlying query, then reference that calculated field in a Form or Report. Do not store that value.

 

[brianjessup]

Is there a direct way?

Thanks for the response, I suppose that would work, but it seems cumbersome considering the problem. I have 50 txtboxes which change their reference. Using a hidden control which is bound to a table, would mean 50 hidden controls, totally doable, but is there a more direct way?

 

[pdx_man]

You can always use Recordsets to reference your data.

Set rst = CurrentDbs.OpenRecordset("Select * From MyTable Where PK_Field = " & Me.Rec_ID)

rst.edit
If rst.RecordCount = 0 Then
rst.Addnew
else
rst.Update
End If
rst!Field1 = MyCalcFunction (...)
rst!Field2 = ...
:
:
rst.Update
rst.Close

 

[Pat Hartman]

You don't need to actually put controls for the calculations on the form. You simply need the columns to be in the form's controlsource. So - Me.MyField - refers to a field in the recordsource rather than a control name. If you don't want to repeat the calculations, you can have code similar to the following in your form's BeforeUpdate event. It copies the contents of the unbound control to the bound field.

Me.MyFld1 = Me.txtCalc1
Me.MyFld2 = Me.txtCalc2
Me.MyFld3 = Me.txtCalc3

 

[ChrisO]

Just expanding on what Pat said.
If you wish to have the user click a button to do the update: -

Private Sub cmdSaveButton_Click()
    Dim lngSuffix As Long
    
    For lngSuffix = 1 To 50
        Me("MyFld" & CStr(lngSuffix)) = Me("txtCalc" & CStr(lngSuffix))
    Next lngSuffix
    
End Sub

Saves a bit of typing if you can name things sequentially.

Regards
Chris