Imput mask 3

[yoshi88]

I need to use different imput mask depending from wich country my client is from. I would like to have a zip code and a phone number imput mask that change for each country. Someone told me to put an "if code" but the problem is that the database is for a travel agency. We have clients from all over the world and i dont want to but 185 if then for every country. Is it possible to create a table with a description of the imput mask to make this easy to use and also to add new country.

Ex: in Canada the zip code are L0L 0L0 but in the US its 00000. The same thing happened for the phone numbers between Europe and America

 

[kathryn]

What a cool question. The answer is Yes, and your idea is a very efficient way to do it. I just ran a test, so this should work, except for the typos!

Your table, call it tblInputMask, would look like this:

Country PostalCodeMask PhoneNumberMask
USA "00000" "000\ 000\-0000"

Your form would have fields on it like Name, Country, Zip and PhoneNumber.

Have the Country field on the form be a combo box that gets its values from tblInputMask.

Then in the AfterUpdate event of the Country combobox, enter this code:

Me!zip.InputMask = DLookup("PostalCodeMask", "tblInputMask", "[country]='" & Me![country] & "'&quot

You would also need a line to set the input mask for the phone, but it's just like the line above.

Hope this gets you started. Kathryn

 

[yoshi88]

Thanks a lot but i have a problem with the code. When i run it it stop and said it couldn't find the country field. Run time error 2465. Can you help me again please

 

[kathryn]

Sure, can you post the complete procedure that is giving you the error and tell me what line is giving you the error? Kathryn

 

[yoshi88]

Run-time error '2465':

Microsoft Access can't find the field 'Pays' referred to in your expression

The table name is "Pays" and my fields are:
ID Pays autonumber
Pays text (means country)
CPmask text (for the zip code and postal code)
Telmmask text (for the phone number at home)
Telbmask text )for the phone number at the work)

The field Pays doesn't seem to be found by Access

My code is like this:
Me!zip.InputMask = DLookup("CPmask", "Pays", "[Pays]='" & Me![Pays] & "'&quot
Me!zip.InputMask = DLookup("Telmmask", "Pays", "[Pays]='" & Me![Pays] & "'&quot
Me!zip.InputMask = DLookup("Telbmask", "Pays", "[Pays]='" & Me![Pays] & "'&quot

 

[kathryn]

OK, this may be an easy fix. Go to the form and look at the property of the textbox that contains the Pays information. Is the name of the textbox Pays? If not, that is the problem; Me!Pays does not exist.

By the way, I hope that this isn't really your code:

Me!zip.InputMask = DLookup("CPmask", "Pays", "[Pays]='" & Me![Pays] & "'&quot
Me!zip.InputMask = DLookup("Telmmask", "Pays", "[Pays]='" & Me![Pays] & "'&quot
Me!zip.InputMask = DLookup("Telbmask", "Pays", "[Pays]='" & Me![Pays] & "'&quot


You are setting the InputMask for the Zip field in every line. You need to edit the beginning of every statement to equal the name of the field on the form for which you want to set the InputMask. Kathryn

 

[yoshi88]

Sorry, but I'm not really good with VB code. If my zip code field name is "Code Postal", I should use this line:

Me![Code Postal].ImputMask = DLookup("CPmask", "Pays", "[Pays]='" & Me![ID Pays] & "'&quot

The problem is that i get a message Run-time error 438:
Object doesn't support this property or method


Thanks a lot and sorry to bother you again

François

 

[kathryn]

OK, a few things to check out.

Your last post used "ImputMask". That should be InputMask; I don't know if that is a typo in your post or your code.

Make sure that the fields on your form have the names you are using in the code. Are your fields on the form named (look at the field properties) "Code Postal" and "ID Pays" and the field in the table named "CPMask"?

Do you know how to use breakpoints in your code? I think this will be the next step we have to go to. Kathryn

 

[yoshi88]

Ihave use breakpoints but Access always tell me tu check the first part Me![Code....InputMask. I really don't know what to do. I have tried to use "", (), ([]), nothing around the field name Code Postal but it never work. Know I have a data typemismatch error.

Me![Code Postal].InputMask = DLookup("CPmask", "Pays", "[ID Pays]='" & Me![ID Pays] & "'&quot

 

[kathryn]

OK, when you have your breakpoint set, go to the command window (type Ctrl-G) and enter

?Me![Code Postal].InputMask

in the lower pane and hit enter. Do you get a datatype mismatch error?

Look at the upper pane. You should have an object called Me. Drill down to Me/Controls/ and see if you can find "Code Postal" listed. If not, then Access doesn't recognize the name.

I would offer to have you send me the database, but I think that your database is not in English. If it is in English and it's not too big, do you want to send it to me? This problem should not be taking this long to fix. Kathryn

 

[yoshi88]

Sorry but the database is in french and ist about 10 megs.

Using Ctrl+G i got this: Compile Error Variable not yet created in this context

Is there another way to do it?

François

 

[yoshi88]

I modify it a bit and now i think im really close

Me.Code_Postal.InputMask = DLookup("CPmask", "Pays", "Pays='" & Me.ID_Pays & "'&quot

The only bug is that i get an error message 94, saying that this is an invalid use of Null

 

[kathryn]

OK, what this probably means is that the

DLookup("CPmask", "Pays", "Pays='" & Me.ID_Pays & "'&quot

is returning Null, which means that it cannot find the lookup. In looking at your statement, it is looking for the CPMask field in the Pays table where the Pays field equals the value in the ID_Pays field on the form. Is that right? Do you really want the Pays field in the Pays table to match the ID_field?
Kathryn

 

[yoshi88]

OK I saw my mistake and i correct it.

The combo box in my form shows Canada but the bound field is ID Pays. In my table it save 1 for Canada.

There is my new code and a few specification about the reslts.

Me.Code_Postal.InputMask = DLookup("CPmask", "Pays", "ID Pays='" & Me.ID_Pays & "'&quot

When i put my mouse over the code it show me this
Me.Code_Postal.ImputMask=""
and
Me.ID Pays=1

Run-time error 3075: Syntax error (missing operator) in query expression 'ID Pays='1"

Is the "ID Pays='" correct or not it seems to be the problem now. Looks like we are near the end of the codeline. I wish it will work cause i will use this in a other database

Thanks again
François

 

[kathryn]

Actually, the problem is in the Dlookup syntax.
Your code reads

Me.Code_Postal.InputMask = DLookup("CPmask", "Pays", "ID Pays='" & Me.ID_Pays & "'&quot

which puts a single quote around the ID field, which is incorrect. Since ID_Pays is a number, you don't need any quotes:

Me.Code_Postal.InputMask = DLookup("CPmask", "Pays", "ID Pays=" & Me.ID_Pays)

Kathryn

 

[yoshi88]

I delete some part that are not usefull for this problem and the database is now 0.5 meg can i send it to you.

 

[kathryn]

Sure:

kbutterly@yahoo.com


Did you try what I posted in my last post? Kathryn

 

[yoshi88]

Its' working now , Blacknight found the error in the syntax. It was the brakets around "ID Pays=". Now the code is:

Me.Code_Postal.InputMask = DLookup("CPmask", "Pays", "[ID Pays]=" & Me.ID_Pays)

Now if someone needs to do something like what was descride in this thread it should work, if not ask me or kathryn for more information on the structure and the code needed.

Thanks a lot kat and you got my vote for all the help you gave me.