Verifying or Limiting input in a text box

[dhstein]

I am creating a form to add a new product to the tblProduct. There are many
restrictions on what data is required so I decided to use a form that is not
bound to the table. I will let the user enter data in each field, then we he
clicks the "Add Product" button, I will validate that all required data is
there, the field values all make sense and do other processing that I need in
related tables, etc. The issue is, that I would like to have some validation
checking on the fields as he is entering. For example, if a field should be
a currency value, it should require that. Is there a way to have Access do
that for each textbox control on the form without binding the control to the
table tblProduct ? I suppose I could create a dummy table with the same
field names and bind the form to that, but that seems a little Klugey.
Thanks for any ideas on this.

 

[Maarkr]

Why don't you just use the validation property at the form level? Do a
search on validation to develop the rules for each field, then enter it in
the validation rule of each field, and explain it in the validation text...
you can also enter a control tip text so it explains appropriate entries when
you mouse over each field. If you really want total control, use validation
at the table level, but I try to stay away from that.

 

[Tom van Stiphout]

On Wed, 18 Feb 2009 04:05:02 -0800, dhstein

Set the Format property to Currency.

-Tom.
Microsoft Access MVP

 

[BruceM]

The restrictions should not have to affect whether you can use the Products
form. If you enter incorrect data for a field (assuming bound controls)
Access will generate an error message. You can discover that error message
by entering something like this in the form's Error event:

MsgBox DataErr

Once you have learned the error number (2113 for incorrect data type, I
think) you can generate your own error message. One possibility is to add
DateField as the Tag property for a date field, NumField for a number field,
etc. In the form's error event:

If DataErr = 2113 Then
Response = acDataErrContinue
Select Case Me.ActiveControl.Tag
Case "DateField"
MsgBox "A date is needed"
Case "NumField"
MsgBox "A number is needed"
Case Else
MsgBox "Unspecified error"
End Select
Me.ActiveControl.Undo
End If

See Help for more information about the Error event.

This only works if the user attempts to add data. If the user skips a field
you can use the form's Before Update event to catch that. The update can be
canceled in the Before Update event, so the record will not be saved. You
can use Me.Undo in the form's Before Update to wipe out the new record, if
you wish. For instance, you could give the user the option of cancelling
entry of a new record. If they accept, use:
Me.Undo
Cancel = True

 

[dhstein]

Thanks Tom, Maarkr, and Bruce for all your good suggestions. For now the
quickest and easiest thing is to set the format property for each field which
is what I need.