Data Validation

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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?
 
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
 
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?
 
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
 
Back
Top