Custom input mask

[Paintballlovr]

I'm looking for some help in creating a custom input mask. I have a field on a form that is supposed to be a dollar amount entry. The field is formatted to currency with 2 decimal places, but the operator can type more than two to the right of the decimal, causing an inbalance when I compare this field to a total field. Is there a way to setup a custom input mask to allow any length (with + or - allowed) to the left of the decimal and only two to the right? It looks like the following input mask technically works, but it looks terrible as the operator enters data in the field.



##########.00;1

[Dal Jeanis]

In the beforeupdate event for the field, you can truncate or round the entry to two decimals, or kick up an error message and make them fix it (in case they type .885 and meant .85).

[Paintballlovr]

In the beforeupdate event for the field, you can truncate or round the entry to two decimals, or kick up an error message and make them fix it (in case they type .885 and meant .85).

What would the beforeupdate code look like? I'm not good at generating code from scratch.

[Missinglinq]

Something like this, with TargetControl, in the code, being the actual name of the Control in question:

Private Sub TargetControl_BeforeUpdate(Cancel As Integer)
 If Len(Mid(Me.TargetControl, InStrRev(Me.TargetControl, ".") + 1)) > 2 Then
  MsgBox "You May Only Enter Two Digits After the Decimal Point!!!"
  Cancel = True
  TargetControl.SelStart = InStrRev(Me.TargetControl, ".") + 2
 End If
End Sub

This pops a warning message, then places the cursor at the point where the data should end, allowing the user to simply delete the extra digits by hitting and holding the Delete Key down.

Linq ;0)>

[Dal Jeanis]

linq's da bomb!

[Paintballlovr]

Something like this, with TargetControl, in the code, being the actual name of the Control in question:

Private Sub TargetControl_BeforeUpdate(Cancel As Integer)
 If Len(Mid(Me.TargetControl, InStrRev(Me.TargetControl, ".") + 1)) > 2 Then
  MsgBox "You May Only Enter Two Digits After the Decimal Point!!!"
  Cancel = True
  TargetControl.SelStart = InStrRev(Me.TargetControl, ".") + 2
 End If
End Sub

This pops a warning message, then places the cursor at the point where the data should end, allowing the user to simply delete the extra digits by hitting and holding the Delete Key down.

Linq ;0)>

Do I need any formatting around my targetcontrol, like " or [ or anything? I tried simply replacing targetcontrol with the name of my control and it still allowed me to enter too many decimals and no message popped

Private Sub CashPosted_BeforeUpdate(Cancel As Integer)
If Len(Mid(Me.CashPosted, InStrRev(Me.CashPosted, ".") + 1)) > 2 Then
MsgBox "You May Only Enter Two Digits After the Decimal Point!!!"
Cancel = True
CashPosted.SelStart = InStrRev(Me.CashPosted, ".") + 2
End If
End Sub

[Dal Jeanis]

Is the control in a form or a subform?

[Missinglinq]

It will let you enter all the digits you want after the decimal, but when you go exit the Control the Messagebox will appear and the cursor will return to the appropriate position to delete the excess digits.

The idea is to teach the users, saving them data entry time, once it sinks in that they can only two digits!

Form or Subform shouldn't matter, in this scenario.

Linq ;0)>

[Paintballlovr]

Is the control in a form or a subform?

It is on a subform

[Paintballlovr]

It will let you enter all the digits you want after the decimal, but when you go exit the Control the Messagebox will appear and the cursor will return to the appropriate position to delete the excess digits.

The idea is to teach the users, saving them data entry time, once it sinks in that they can only two digits!

Form or Subform shouldn't matter, in this scenario.

Linq ;0)>

I must have something wrong in the code then because I could navigate away from the control either to another control on the subform or even to the main form and no message box came up.

[Missinglinq]

Well, let's see exactly what you're using!

Linq ;0)>

[Paintballlovr]

Well, let's see exactly what you're using!

Linq ;0)>

Private Sub CashPosted_BeforeUpdate(Cancel As Integer)
If Len(Mid(Me.CashPosted, InStrRev(Me.CashPosted, ".") + 1)) > 2 Then
MsgBox "You May Only Enter Two Digits After the Decimal Point!!!"
Cancel = True
CashPosted.SelStart = InStrRev(Me.CashPosted, ".") + 2
End If
End Sub

[Missinglinq]

Sorry, I forgot we were working with Currency! Some of the String Functions have a problem with Currency Fields, apparently due to the insertion of the monetary sign (such as $) and the addition of zero decimal places, if there are no decimals in the value. So, you need to remove the line

CashPosted.SelStart = InStrRev(Me.CashPosted, ".") + 2

The user will just have to delete the extra decimals, so that only 2 are present, without any help.

But running the code you gave, minus this line, still works as needed, for me; actually running your code, exactly as posted, stopped the updating, it just didn't place the cursor at the right place for deleting the extra digits without repositioning the cursor. If it still doesn't stop the updating of the Field, if more than two decimals are entered, you need to double check that the Control in question is actually spelled CashPosted. Barring that, you need to save the file in 2007 format, or earlier, ZIP it up and attach it to a post, for us to look at.

Linq ;0)>

[Paintballlovr]

That took care of it. Thank you very much for all of your help!

[Missinglinq]

Glad we could help!

Linq ;0)>