473,320 Members | 2,161 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

What is wrong with my validation rule?

Here is the setup:
table field validation rule
Expand|Select|Wrap|Line Numbers
  1. Is Null Or Not Like "*[!((0-9) or "" "" or ""+"" or ""("" or "")"")]*" 
in Form i have textbox that automatically (inputmask) fills in the country phone code
Expand|Select|Wrap|Line Numbers
  1.  Dim mask As String
  2.  
  3. mask = ""
  4. mask = DLookup("PHCODE", "Countries", "Countryid='" & Me.SCCountry & "'")
  5.  
  6. SCPhone1.InputMask = "(+" & Replace(Replace(mask, "9", "\9"), "0", "\0") & ")9999999999;0;"
  7. SCPhone2.InputMask = "(+\" & Replace(Replace(mask, "9", "\9"), "0", "\0") & "\)9999999999;0;"
  8. SCMPhone3.InputMask = "(+\" & Replace(Replace(mask, "9", "\9"), "0", "\0") & "\)9999999999;0;"
  9. SCFAX1.InputMask = "(+\" & Replace(Replace(mask, "9", "\9"), "0", "\0") & "\)9999999999;0;"
  10. SCFAX2.InputMask = "(+\" & Replace(Replace(mask, "9", "\9"), "0", "\0") & "\)9999999999;0;"
  11. SCMail.InputMask = "AAAAAAAA" & "@" & "AAAAAAAA" & "." & "AAAA;0;"
When i enter data directly in table it accepts it but when i try entering data in the form textbox i get validation rule message i entered in the table field properties validation text.
Can anyone help?
Oct 27 '10 #1
8 2758
NeoPa
32,556 Expert Mod 16PB
This looks like a bit of a complicated mess, and you don't even say what you're trying to achieve. Brackets in a pattern-matching string do not take ors as you have them (See Find Wildcard Characters in an Access Database for proper use).

I suggest starting from scratch and working out exactly what you're after before implementing that in your validation rule.

Your code is all about InpuMasks, yet your question is loosely about Validation Rules. You appear to expect one to match the other but give no explanation of what you're actually trying to do.
Oct 27 '10 #2
I want to achieve the following:
I want after the user chooses a country from combobox, automatically the inputmask of the text field for phone number to be updated with the country international phone code. Also i want the user to input in the phone number textbox only digits and when the record is saved i want the input mask to be saved together with the phone number. And the "()+" symbols in the validation rule are there in order the inputmask to be accepted by the validation rule.
Oct 27 '10 #3
NeoPa
32,556 Expert Mod 16PB
Let's start from the basics then. The Validation Rule should be set to Not Like '*[!0-9+() ]*' and the Null aspect should be handled by setting Required to No.

If you want more help with the InputMasks then you'll need to provide some information as to which aren't working and the format expected for each.
Oct 27 '10 #4
Ok i think i will need more help. I tried your proposition before and it didn’t work.
I will try to explain some more.
I want the users to input telephone numbers in this format (+485)##########
Now the international phone code prefix is retrieved from table Countries and it may vary in length from 1 to 5 digits. The +() symbols I add later . PHCODE in table Countries is formatted as text.
Oct 27 '10 #5
I manage it. Work overload can make your brain stall :)
I dont need validation in the table, but in the form phnone No field and it is only for digits.
Oct 28 '10 #6
NeoPa
32,556 Expert Mod 16PB
I'm not sure I understand you. Are you sorted out now? Or do you still have a question?
Oct 28 '10 #7
Yes it is sorted out. The answer was that i don’t need such complex validation rule in the table field, because i have input mask in the form and i put a simple validation rule in the form that allows only digits to be entered.
Oct 29 '10 #8
NeoPa
32,556 Expert Mod 16PB
Pleased to hear it. Good luck with your project.
Oct 29 '10 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Dalan | last post by:
This ought to be simple enough, but not certain which to use. I have a few fields set to Require data to be entered; however, the message displayed by Access 97 is too generic to be of any real...
2
by: Joey P | last post by:
Hi all, I am doing a project for university whereby i have to implement a simple database related to a frozen foods company. I am having some trouble though creating a validation rule for one...
0
by: Steve V | last post by:
I'm using Access 2000 to build a budgeting/tracking database. Can I make a validation rule (using VBA) that checks the data as if the record has already been added? I've got 5 tables (only the...
2
by: Danny | last post by:
I created a function to test to see if entered text has a '.' in it, The code seems to work but the validation always fails even when 'true' is returned. i put = myfunction in the validation...
6
by: Chuck | last post by:
A97. A database has a table: tblA which has a single text field, B. It is a primary field, indexed and no duplicates. It is used as a lookup for table tblC. A form based on tblA is used to add...
4
by: ron | last post by:
I have a access based guest book. I want to create a validation rule to block certain words or parts of a srting. How do i do this? ie: this is a nice site. come visit my porn site at www.abc.zy...
14
by: ckpoll2 | last post by:
Hello, I have a validation rule in a text box on a form. The rule is that if Combo12 starts with "Credit Hours -*" than only allow numbers less than or equal to 2 in there. Any number of words...
10
by: gweasel | last post by:
What is the best way to apply a Validation Rule - or rather, where is the best place to put it? Is there an advantage to putting it on the field in the table vs setting the validation rule on the...
6
dragonsbreath
by: dragonsbreath | last post by:
Firstly I am not a coder. I am trying to add a validation rule to an input form that will prevent users’ creating more than one record per organisation. This DLookUp seems to work ...
1
by: MLH | last post by:
Anyone remember if A97 append query failure would ever report data breaking validation rule when such was not the case. I have an old SQL statement - several years old now. I've encountered a case...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.