Solved

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

Posted on 2014-07-22
11
1,163 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
  • 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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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…
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now