How Can I Pre-Process Data in a Bound Control?

J

JonOfAllTrades

Good morning. I have a fairly simple question, hopefully someone here
has seen it.
I have a form used for data entry. There is an HoursWorked control,
bound to a numeric field. Often, a user needs to enter hours from multiple
line items. They can do this by adding the hours in Excel, Calculator, a
separate control with Eval(), or of course a physical calculator, and then
entering the total.
I would like to engineer it such that a user can type either "4.5" or
"1.5+2+1" and the control will understand the value as 4.5. I added Control
= Eval(Control) to BeforeUpdate. However, it seems Access is verifying the
format of the input before BeforeUpdate runs; it complains about the value
entered not being numeric. If I enter simple numeric data, the code tries to
run but throws an error "The macro or function set to the BeforeUpdate or
ValidationRule property for this field is preventing Access from saving the
data in this field."
I could create a separate unbound control, but in that case they might
as well use their favorite Casio or TI.
Any suggestions?
Thank you!

Win XP/Access 2K
 
S

Sylvain Lafontaine

Access is a database, not a spreadsheet or an electronic calculator.

As you said, you can use an unbound control but that will work easily only
with single forms. With continuous forms, a possibility would be to add and
use a temporary field directly in the table and back to the underlying query
and the form.

You can also create some sort of pop-up window or cover your bound control
with an unbound control with a transparent background (I'm not sure about
this last one).
 
R

Ron Weiner

If you are interested I have a popup four function calculator that here
http://www.WorksRite.com/Calc.htm that you can easily bind to your text box.
when activated it uses the existing value and format of the field, allows
the user to perform any number of simple arithmetic steps and save the value
back your field when closed. Perhaps not exactly what you were aiming for
but, might get you where you want to go. The calculator is a 100% Access
solution with no Active X installation hassles.
 
D

Dirk Goldgar

JonOfAllTrades said:
Good morning. I have a fairly simple question, hopefully
someone here has seen it.
I have a form used for data entry. There is an HoursWorked
control, bound to a numeric field. Often, a user needs to enter
hours from multiple line items. They can do this by adding the hours
in Excel, Calculator, a separate control with Eval(), or of course a
physical calculator, and then entering the total.
I would like to engineer it such that a user can type either
"4.5" or "1.5+2+1" and the control will understand the value as 4.5.
I added Control = Eval(Control) to BeforeUpdate. However, it seems
Access is verifying the format of the input before BeforeUpdate runs;
it complains about the value entered not being numeric. If I enter
simple numeric data, the code tries to run but throws an error "The
macro or function set to the BeforeUpdate or ValidationRule property
for this field is preventing Access from saving the data in this
field." I could create a separate unbound control, but in that
case they might
as well use their favorite Casio or TI.
Any suggestions?
Thank you!

Win XP/Access 2K
Click to expand...

Here's one possibility, so long as your form is in single-form view, not
continuous: unbind the text box so it has no ControlSource (and change
its name so it isn't the same as the corrsponding field in the table).
In the form's Current event, set the control's value to the value of the
field:

Private Sub Form_Current()

Me!txtHoursWorked = Me.HoursWorked

End Sub

In the control's AfterUpdate event, evaluate its contents and assign
them to the field:

Private Sub txtHoursWorked_AfterUpdate()

With Me!txtHoursWorked
If IsNull(.Value) Then
Me.HoursWorked = Null
Else
Me.HoursWorked = Eval(.Value)
End If
End With

End Sub
 
J

JonOfAllTrades

Thank you, Mr. Goldgar. I used something very like you suggested, and
it does work... except, as you point out, with a continous sheet.
Unfortunately, that's exactly what I need.
As a compromise, I have the calculating control and the simple bound
control both visible. As the user goes through records, he or she can enter
data in either the CalcHoursWorked or HoursWorked control, either will update
the current record. When using the Calc controls, the value shown in Calc
changes for every record, but only the current record is really changed, and
that is reflected in the simple bound controls. Not ideal, but I don't think
it will be too confusing.
Thank you for your help!
 
D

Dirk Goldgar

JonOfAllTrades said:
Thank you, Mr. Goldgar. I used something very like you
suggested, and it does work... except, as you point out, with a
continous sheet. Unfortunately, that's exactly what I need.
As a compromise, I have the calculating control and the simple
bound control both visible. As the user goes through records, he or
she can enter data in either the CalcHoursWorked or HoursWorked
control, either will update the current record. When using the Calc
controls, the value shown in Calc changes for every record, but only
the current record is really changed, and that is reflected in the
simple bound controls. Not ideal, but I don't think it will be too
confusing.
Click to expand...

One thing you could do is clear the CalcHoursWorked control by setting
it to Null immediately after evaluating it and assigning the result to
the bound control.

Another possibility that might work for you on a continuous form, is to
place the unbound text box directly behind the bound text box, sized so
that normally, the bound text box sits in front of the unbound one.
However, set the bound conbtrol's Tab Stop property to No and the
unbound control's Tab Stop property to Yes, so that tabbing from the
previous control brings the focus to the unbound control. That will
bring the unbound control to the top for as long as it has the focus,
but when it loses the focus it will go to the back again.

Then you could have code like this in the form's Module:

'----- start of code for form module -----
Option Compare Database
Option Explicit

Private Sub HoursWorked_Click()

Me.CalcHoursWorked.SetFocus

End Sub

Private Sub Form_Current()

Me.CalcHoursWorked = Me.HoursWorked

End Sub

Private Sub CalcHoursWorked_AfterUpdate()

Me.CalcHoursWorked = Me.HoursWorked

End Sub

Private Sub CalcHoursWorked_BeforeUpdate(Cancel As Integer)

On Error GoTo Err_Handler

With Me!CalcHoursWorked
If IsNull(.Value) Then
Me.HoursWorked = Null
Else
Me.HoursWorked = Eval(.Value)
End If
End With

Exit_Point:
Exit Sub

Err_Handler:
Cancel = True
MsgBox Err.Description
Resume Exit_Point

End Sub
'----- end of code for form module -----

It's not perfect, but it might work for you.
 
J

JonOfAllTrades

Nice! That's damn clever! I'm using a slight variation, and it works
perfectly.
Thank you, Dirk.
 
Top