Solved

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

Posted on 2014-07-22
11
1,263 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 47

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 84
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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 47

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 84
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 47

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

739 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