Solved

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

Posted on 2014-07-22
11
1,223 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

766 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