Data Validation

[Guest]

Im Sorry, i wasnt specific enough- i am doing a table that is attached to a
form, and i want to restrict entries into certain fields in the table- for
instance, i dont want text to be entered into numeric fields and vice versa.
so i open the table in design view, go to data validation, but when i get to
the part where you have to enter an expression, that is where i get stuck-
what is the expression to enter there?

In addition, is there a way i can enter multiple expressions? i am on a
numeric field now, i dont want text to be entered, zeros to be entered, or
numbers greater that 10000?

 

[Guest]

Im Sorry, i wasnt specific enough- i am doing a table that is attached to a
form, and i want to restrict entries into certain fields in the table- for
instance, i dont want text to be entered into numeric fields and vice versa.
so i open the table in design view, go to data validation, but when i get to
the part where you have to enter an expression, that is where i get stuck-
what is the expression to enter there?

In addition, is there a way i can enter multiple expressions? i am on a
numeric field now, i dont want text to be entered, zeros to be entered, or
numbers greater that 10000?

 

[Guest]

To allow only numbers to be entered put the following in the KeyPress event
procedure of the control bound to the field on the form:

Select Case KeyAscii
Case 48 To 57
' number entered so allow
Case 8, 27
' backspace or Esc pressed so allow
Case Else
' don't allow
KeyAscii = 0
End Select

To validate that the number is within the range 1 to 10,000 put the
following in the control's BeforeUpdate event procedure:

Const conMESSAGE = "Value must be between 1 and 10000."

Dim ctrl As Control
Set ctrl = Me.ActiveControl

If ctrl < 1 Or ctrl > 10000 Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If

Ken Sheridan
Stafford, England

 

[Guest]

Thank you for your response, however, this is a very simple database we are
required to design, and i am not overly familiar with programming in VBA-
however, is there an expression that can be entered in the table (under the
validation) that can accomplish the same purpose?

 

[Guest]

I can't see any way you could encompass all of it in a single expression as
the control's ValidationRule property. All you need to do is paste the code
I gave you into the two event procedures, so its simple enough to do:

Select the control in the form in design view and open its properties sheet.
Select the KeyPress event and click the build button (the one on the right
with 3 dots). Select 'Code Builder' in the next dialogue. The VBA window
will open at the event with its opening and closing lines already in place.
Paste the code I gave you for that event as new lines between the two.

Repeat for the control's BeforeUpdate event procedure, pasting in the other
lines of code.

Give it a try. Its not that scary, really!

Ken Sheridan
Stafford, England