CODEWITHSUNDEEP
databasems-accessvbauser-inputms-access-2010
user972489
user972489

Reputation: 11

Access VBA: enter expression in user form, store calculated result in table?

I've got a form that I'd like users to be able to enter simple maths into, with the calculated value stored in the table.

For example:

  1. User enters "1+5" into a text box.
  2. The answer 6 is stored in the database table.

Currently, entering "1+5" in my numeric form field results in an error re: 'entering text into a numeric field'. Is there any way to do this?

Upvotes: 1

Views: 1983

Answers (1)

Miles Erickson
Miles Erickson

Reputation: 2595

You can write a VBA script to parse the input and perform any needed calculations. This could be triggered by an unbound form field's BeforeUpdate event.

Steps:

  1. Create a field named Field1 for the calculated value (bound to the appropriate database field).
  2. Add an unbound field to your form and name it Input1.
  3. In Design view, right-click on Input1 and choose Properties. Under Events -> Before Update, click the "..." button.
  4. Insert the VBA code that I have written (below).
  5. Save your changes and close the VBA editor.
  6. Return to Form view and enter an expression into your Input1 field. When you tab out of the field, the expression will be evaluated and the value will appear in Field1.

Note that, for reasons of security and/or data integrity, you may need to sanitize your user input before evaluating it as an expression. The details of "how" and "why" fall outside the scope of the original question.

Private Sub Input1_BeforeUpdate(Cancel As Integer)
  ' TO-DO: Validate/sanitize input as necessary for security & validity;
  '        Exit Sub prior to the Eval() if we see something we don't like
  Dim result As Variant
  result = Eval(Input1.Value)                ' Evaluate the contents of Input1
  If IsNumeric(result) Then Field1 = result  ' Save any numeric result to Field1
End Sub

Upvotes: 3

Related Questions