Matching Values /Lookup values

[aqif]

Hi All

I want to create a function that will lookup the numerical value for thier text value like

Val Desc
--- ----
1 Country 1
2 Country 2
3 Country 3

Note: I can only have a possible of 40 Countries. Rite now I am creating a countries table and then matching the values through lookup function like:

DLookup("[TblCountries]![Desc]", "TblCountries", "[TblCountries]![Val]=" & TxtCountry_Num)

If i'll send this function to someone else in my department for thier database, they will have to create table of Countries again and have to insert the values inorder for this function to work. I want that some array or maybe 40 variables holding names of the countries could be created in the function itself and use lookup or some other function to match the values.

One alternative is to create the table at runtime and insert the values but t slows down the process n approach looks odd. is there any way to achieve what I m trying to do?

Cheers!
Aqif

 

[Fornatian]

If the values are completely static you could hard code them into the forms code and use a Select Case statement to define the value to be passed:

Dim TheCountry as String
Select Case TxtCountry_Num
Case 1
TheCountry = "Country1"
Case 2
TheCountry = "Country2"
Case 3
TheCountry = "Country3"
End Select
Me.MyField = TheCountry

Warning though, if do it like this it is obvious that you won't be able to dynamically change the no. of countries or their codes without going into the code of every distributed copy/db.

Is there a reason you can't send out a newer version of the countries table to people for them to import each time you have an update - which seems infrequent.

Doing it that way you can use queries to pull the information through for you in a much slicker fashion.

Ian

 

[Carol]

Can you not split your database and just create one table for Countries, with a CountryID and CountryName field where the 40 countries are predetermined ahead of time. That way, no other user would have to create the same table on their database, they would all be referencing one table.

 

[aqif]

Dear Ian:

Ur answer is close to my demand. The thing is that the company only deals in 40 countries and its not possible that they will extend the countries list. How will i use the lookup function or smthng like that when i've declared 40 cases for 40 countries.

Can u write the code in detail..please

Cheers!
Aqif

 

[Fornatian]

'initiate a variable to hold the desired countries name
Dim TheCountry as String
'set the case statement to look at Me.TxtCountry_Num
Select Case Me.TxtCountry_Num
'this tests for a 1 in Me.TxtCountry_Num
Case 1
'set the variable to the name of the country
TheCountry = "USA"
'this tests for a 2 in Me.TxtCountry_Num
Case 2
'set the variable to the name of the country
TheCountry = "Great Britain(UK)"
'and again testing for a 3
Case 3
TheCountry = "Ireland"
'repeat the case and 'TheCountry = "Whatever" for the forty countries
'add this line at the end to close the statement
End Select
'set the value of MyField which will be one of your form controls.
Me.MyField = TheCountry


I don't understand why you can't distribute a table of countries as Carol and I have both suggested though because as stated by yourself these are unlikely to change frequently.

Oh well, each to their own.

Ian