?
Solved

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

Posted on 2014-07-22
11
Medium Priority
?
1,325 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
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host 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 48

Expert Comment

by:Dale Fye
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 earned 1000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

770 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