Filtered Lookup

[AnilBagga]

I have a Customer database where I capture Country and State for a customer. In another table (CountryStates) I have States of each Country as 2 fields.

1. If use lookup on the Country field in Customer master using data of the CountryStates, I have multiple values of the Country in the list because the Country name appears against each State. Question - can the display be Unique country names?

2. When filling the State, the Lookup on State gives the complete list of all States making scrolling difficult. Question - Can only the states of the country I selected in the previous Country field show in this Lookup?

 

[Deleted member 147267]

By lookup do you mean DLookup? If so, you can base your lookup on a query that uses the DISTINCT predicate. Or DLookup will return the first value (actually I believe it is random) that matches the criteria. Now having said that, I'm beginning to think this "lookup" is from a combo box. Depending on your combo row source, even distinct might work. You should post the combo row source sql if that's what this is for.

As for question 2, yes. This is commonly referred to as cascading combo boxes and there's a kajillion internet posts on the subject.

 

[strive4peace]

hi Anil,

you can import a table of countries from this free tool to manage personal contacts:

MyContacts
msaccessgurus.com/tool/MyContacts.htm

the table is called c_Country

there is also a table of states there too -- but I suspect that you're in a different country than the data

 

[AnilBagga]

By lookup do you mean DLookup? If so, you can base your lookup on a query that uses the DISTINCT predicate. Or DLookup will return the first value (actually I believe it is random) that matches the criteria. Now having said that, I'm beginning to think this "lookup" is from a combo box. Depending on your combo row source, even distinct might work. You should post the combo row source sql if that's what this is for.

As for question 2, yes. This is commonly referred to as cascading combo boxes and there's a kajillion internet posts on the subject.

Thanks. I found some videos on cascading combo boxes. I tried to build one with a dummy data. In a country state table i have 2 fields with a ID field as Primary. The table data has 2 countries, 3 states each. The country combo box (Label - Country, Combo box name - CountryC) is configured to show unique values of the countries and it works. The State field combo box (Label - State, Combo box - StateC) is linked to all 3 fields and under the criteria of State i used the formula [Forms]![Trial]![CountryC}. The form name where I have the combo boxes is called 'Trial' and the Combo box capturing the value of the Country is called 'CountryC'. When I see the form in datasheet view, the Country is selected with Unique values but I get no data in the State field. I have checked many times. No result. I dont know where I am going wrong! Can you help. I am attaching the screenshot

 

[plog]

So the user is selecting a country (e.g. USA) in the CountryC input. You are filtering your data to just those states that match the input country(USA). How many states in the USA are themselves named USA?

 

[AnilBagga]

So the user is selecting a country (e.g. USA) in the CountryC input. You are filtering your data to just those states that match the input country(USA). How many states in the USA are themselves named USA?

This Is the screen shot of the table

 

[plog]

Yup, your screenshots confirm your query is performing exactly as it is supposed to. Reread my post again.

You are taking the value the user is inputting for the country (USA) and only limiting CountryState to just those records where the State field equals that value.

Read that a few times and really digest it.

 

[plog]

The real lesson in all of this is don't just quit when things don't work as expected. If you get absolutely no results you need to get results and start somewhere. Remove your criteria and get something to come back. Find out the exact value in your criteria. HArd code one specific piece of criteria that you know should work. Change little things until you understand the issue. Don't just throw code down and then trhow your hands up when it doesn't work.

 

[AnilBagga]

Thanks. Yes I needed to read it a few times. Resolved. Thanks a ton

 

[AnilBagga]

I have started using Access a week back and was not sure of many things. I am beginning to get a grip. Need to deliver and learning time is short. Thanks again and will be more perseverant in future

 

[AnilBagga]

Here I go again. I thought I had it and I am stuck again. I created a table with Country, State and City this time. In the form I select country, state and then city. I am able to select the state easily after selecting country. Using the same logic, I am unable to select the city from the same table matching the value of state. I have gone through the data over last 2 / 3 days, created a new form but still having issues. Need HELP. The screenshots enclosed will explain my problem

 

[strive4peace]

Anil, it is hard to say without seeing more of the field properties.

Just guessing:
Unicode Compression must be the same for the fields to match. When you import data into Access, this gets set to No. When you create tables using the user interface in Access, the default value is Yes.

While you're in the table design, also set the Field SIZE property to something logical instead of leaving at 255 -- and make sure it is the same in related tables.

 

[plog]

I suggest renaming your ID fields by prefixing them with what they are for (CustID, CountryStateID, etc.), because when we talk about the ID field its ambigous because every one of your tables has a field called ID.

With that table structure you are duplicating data. If CountryStateMaster is to hold all those fields, you do not need any of them in CustMasterTbl but the ID value of CountryStateMaster. In a relational database data is to be related in tables (using primary and foreign keys) not copied into other tables which have the same fields.

 

[isladogs]

@AnilBagga
Have a look at my example app which contains 5 cascading combo boxes. It may be helpful.
http://www.mendipdatasystems.co.uk/cascading-combo-boxes/4594455723

 

[AnilBagga]

Anil, it is hard to say without seeing more of the field properties.

Just guessing:
Unicode Compression must be the same for the fields to match. When you import data into Access, this gets set to No. When you create tables using the user interface in Access, the default value is Yes.

While you're in the table design, also set the Field SIZE property to something logical instead of leaving at 255 -- and make sure it is the same in related tables.

Indeed the Unicode had changed. I corrected it to Yes, the field lengths are same - 50 in both tables, but still could not get the same corrected. I tested the same code where all the data of countries, state, city was typed in, and I dont face an issue in this database. I have added the snapshot of the field properties of both tables in the same odf file, towards the end

 

[AnilBagga]

I suggest renaming your ID fields by prefixing them with what they are for (CustID, CountryStateID, etc.), because when we talk about the ID field its ambigous because every one of your tables has a field called ID.

With that table structure you are duplicating data. If CountryStateMaster is to hold all those fields, you do not need any of them in CustMasterTbl but the ID value of CountryStateMaster. In a relational database data is to be related in tables (using primary and foreign keys) not copied into other tables which have the same fields.

Thanks for the suggestion on fields. Will rename.

When a user selects the country, the form can capture the Unique CountryID i/o Country and likewise for state etc. The problem is they need to see the names on the form to be sure it is selected correctly , at the time of entry and editing etc later

Having said that, the intriguing thing is why the selection works in one level (for State) and not in the other (city)

I created a dummy database of cities and country, states etc and it works. However the current CountryStates table has data imported from Excel. Could that be an issue?

I am enclosing the properties of the fields of these tables in case that throws up some clues

@AnilBagga
Have a look at my example app which contains 5 cascading combo boxes. It may be helpful.
http://www.mendipdatasystems.co.uk/cascading-combo-boxes/4594455723

@AnilBagga
Have a look at my example app which contains 5 cascading combo boxes. It may be helpful.
http://www.mendipdatasystems.co.uk/cascading-combo-boxes/4594455723

Thanks Colin. I am at a very basic level of Access, just a few weeks and I am not good at programming. I wanted to build a rDBMS and Access offered the opportunity and here I am trying to learn and deliver. Your database looks great but it will take some time for me to follow it. As you can see I am struggling with much more basic issues here. Will appreciate some help here. Unfortunately I cannot enclose the database file here

 

[strive4peace]

Indeed the Unicode had changed. I corrected it to Yes, the field lengths are same - 50 in both tables, but still could not get the same corrected. I tested the same code where all the data of countries, state, city was typed in, and I dont face an issue in this database. I have added the snapshot of the field properties of both tables in the same odf file, towards the end

hi Anil,

Changing Unicode compression only affects values that are entered from now on. It doesn't change what is already there. Perhaps you can try an Update Query. If you replace the value with what is already there (but under the covers is not the same), perhaps this will also convert the way the value is written to have Unicode Compression. If not, you'll need to export the data ( like to a text file or to Excel -- keep your table though), delete the data in the table, compact/repair the database, and then import it back again. But first, try something simpler ...

Try this:

1. backup your database

2. Make a new query

3. Switch to the SQL view and paste this in:

UPDATE [MyTablename]
SET [MyField] = [MyField]

MyTablename = the name of your table
MyField = the name of your field

4. click the !Run button on the Design ribbon

Please let us know if this makes the values match, thanks

 

[AnilBagga]

Crystal

This did not work. What eventually worked was reducing the columns under City from 3 to 1. In my cbo for city, I was using 3 columns - City, State and Country. When I reduced this to only City, I got all the cities in drop down of the selected state. Why, I dont know yet

 

[strive4peace]

Crystal
This did not work. What eventually worked was reducing the columns under City from 3 to 1. In my cbo for city, I was using 3 columns - City, State and Country. When I reduced this to only City, I got all the cities in drop down of the selected state. Why, I dont know yet

hi Anil,

> "City, State and Country ... only City

the same city name can be in more than one place