Solved

Set my own validation rule message instead of default access (vba)

Posted on 2014-07-22
11
1,290 Views
Last Modified: 2014-07-31
Hi,
I have a form with a number field, when I write a chars intro it , I got a default access message (in a table field validation rule) .
how can i change it to my own one?
0
Comment
Question by:drtopserv
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 40213570
generally, I don't use table validation rules.

I define my business logic (including field validation rules) in my forms, and use the Form_BeforeUpdate event to test for valid entries in fields/combinations of fields and if those rules are not met, I can define a custom message and set the BeforeUpdate events Cancel property to true, which cancels the update and will also prevent you from moving off of the current record if it is dirty.
0
 

Author Comment

by:drtopserv
ID: 40213579
Seems i didn`t clarify my Q, I have a numeric field, then i try to fill it with chars, i have a pop-up msg :
the value you entered isn`t valid for this field.
I need to disable this msg, and set my own one.
how to?
0
 
LVL 85
ID: 40213832
That's not really a Validation Rule, but rather an error.

If the Numeric field input type is generally known - that is, if the user can only type in Integer values >0 and less than 100000, perhaps - then use an Input Mask on the field (something like ###,###). This would only allow the user to enter Numeric values, and you don't have to worry about them entering other values.

See this article for more info on Input Masks:
http://office.microsoft.com/en-us/access-help/control-data-entry-formats-with-input-masks-HA010096452.aspx
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40215005
You can also use an Input mask to make sure that only numbers can be entered in the control.
0
 

Author Comment

by:drtopserv
ID: 40222152
Hi,
Need to clarify again:
I have created :
NumField Field, data type: number -> without any validation rule or validation text
then i created form with textbox control .
when i came and type a chars into this textbox field i got this msg:
The value you entered isn`t valid for this field.
for example, you may have entered text in a numeric field or a number that is larger than the fieldSize setting permits.

the question is how can i disable this msg and put my own one   ?:}
1
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 40222428
You can use the textboxes "Change" event to test the current value of the control after every entry into the field, since it fires every time you add or remove a character from the control, you could use something like:

Private Sub txt_NumField_Change()

    If IsNumeric(Me.txt_lngNumber.[b]Text[/b]) Then
        'do nothing
    Else
        MsgBox "Your message here"
    End If
    
End Sub

Open in new window

Not that if you use the change event, you must refer to the 'Text' property of the control.  The default 'Value' property does not contain the changes until the control is updated.

You could also use the keydown event to intercept the input prior to it even appearing on the screen.
Private Sub txt_lngNumber_KeyDown(KeyCode As Integer, Shift As Integer)

    If Shift = 0 And (KeyCode >= 48 And KeyCode <= 57) Then
        'do nothing, numeric keys
    Else
        MsgBox "Your message here"
        KeyCode = 0
    End If
        
End Sub

Open in new window

Setting the KeyCode to zero clears that keystroke from the buffer, as though it had never been pressed
0
 
LVL 85
ID: 40222570
Have you tried any of the suggestions? Dale's suggestion to use the Change event, or my suggestion to use an Input Mask, would take care of this quite easily ...

Note you will need to take into account keys like the Enter key, the Esc key, etc etc ...
0
 

Author Comment

by:drtopserv
ID: 40222599
fyed,
It`s working, my problem was :
Me.txt_lngNumber.Text
I have put it :
Me.txt_lngNumber.value
0
 

Author Comment

by:drtopserv
ID: 40222602
what the different if you may tell ?
0
 
LVL 48

Accepted Solution

by:
Dale Fye (Access MVP) earned 250 total points
ID: 40223291
when you type in a control, the "value" of that control does not change until after the the control is updated.  However, the text property of the control changes every time you type a key.  To test this, using the Change event of your txt_lngNumber text box, try this:

Private Sub txt_lngNumber_Change

    debug.print me.txt_lngNumber.Text, me.txt_lngNumber.Value

End Sub

Open in new window


Then start typing in the textbox.  You will note that the Value doesn't change until you tab out of the control.  Actually, you won't see the "value" change unless you put some code in the BeforeUpdate and AfterUpdate events as well.
Private Sub txt_lngNumber_BeforeUpdate(Cancel as integer)

    debug.print "Before Update:", me.txt_lngNumber.Value

end sub

Private Sub txt_lngNumber_AfterUpdate

    debug.print "AfterUpdate:", me.txt_lngNumber.Value

end sub

Open in new window

Trying to refer to the "text" property of a control will fail unless that control has the focus.

And Scott is absolutely right.  Depending on the type of number (it looks like long), you would also want to trap for a period or comma (regional setting for decimal), and maybe prevent the + and - signs as well.
0
 

Author Closing Comment

by:drtopserv
ID: 40232366
thnx ALOT,I got it now.
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question