Form_request

[Ishq00737]

Hi,

I am new to ms access and specially new to VBA coding. I have a simple form in which I am trying to implement some logic. Basically I have a form that has some combo boxes which are bring data from one table and implementing it to a different table. My issue is that I have two combo boxes. one which is used for the State_Name and other box is use for City_Name. one box display all the states and the other should display its corresponding cities.the logic I want to implement is that when I select New York in the State_Name column then in the City_Name columns I should only see cities affiliated with New york. Please if anyone can help on this using macros or code builder that would be helpful.

Thanks,

[ranman256]

make 2 queries: 1 for all cities, qsAllCities; 1 for one city given the state, qsCities1State.
when user changes the State cbo, change the query....

the query: qsCities1State, will look at the form's state combo box to pull data
select city from table where [state] = forms!myForm!cboState

sub cboState_afterupdate()
if IsNull(cboState) then
   cboCity.rowsource = "qsAllCities"
else
   cboCity.rowsource = "qsCities1State"
endif
end sub

[Bulzie]

The City combo box data should be filtered by the State combobox value. So lets say your State combo box is called SelectState. In your City combo box called SelectCity, the data should be a query that has the City and State fields and in the criteria of the State field, reference the SelectState value from the combo box on the form. Will be something like Forms!MainForm!SelectState. Last part is in the AfterUpdate event of SelectState, add Me.SelectCity.Requery so that every time you select a state, the SelectCity combobox gets the appropriate City values.

[Ishq00737]

Hi,

Maybe I am doing something wrong. My state column is bringing data from the qsCities1State query that has state as well as City and In my City column I am using the City from the qsCities1State query and implying your logic in my raw source as stated above. Then in my event after update I used your code but there are two things that are happening. First after I use your code I am getting a parameter pop up box that ask to input the state which shouldn't be happening and second even if I select the state I would still see all the cities. I followed your logic but I don't know what going wrong.

[pbaldy]

There's a sample db here:

Baldy-Cascading Combos

[Ishq00737]

This link doesn't seem to be opening.

[pbaldy]

Working for me. Perhaps your location is blocking websites?

[Ishq00737]

would you be able to help me on this matter. It's a simple logic but I follow this coding that was stated above but am facing some issues. I don't want the pop up to come when selecting the state.

[pbaldy]

You should specify which "you" you're referring to, since more than one person has responded on the thread, with different methods. I would normally let the person who recommended the method respond, but the pop up is Access telling you it can't find whatever.

[Ishq00737]

Hi Bulzie,

I followed your logic but in my selectcity when I implement your logic I am getting a pop up parameters which shouldn't be happening.

[Bulzie]

Sounds like the issue is the criteria you put in the Select_City combobox. It needs to point to the Select_State combobox name on the form. So do this, get to the data query window of the Select_City field. You should have 2 fields, State and City. In the State column go to the Criteria box, right click and select "build". Now select Forms, Open Forms and keep drilling down until you click on your Form on the left, then in the middle window, you should see the Select_State field and double click it and then hit OK to get out of that window. That will add the correct syntax to reference that Select_State field. Now save that and try the form again to see if it works.

Another way to troubleshoot if this is the issue. In that criteria box for field State in the Select_City combobox, put in a state to test such as "Texas" , run the query and see if only Texas states appear. If they do then that reference to field Select_State is the issue. On the error, since Access cannot find that field, it is asking you for the value.

[Ishq00737]

Hi Bulzie,

Sorry for the late reply. So I got the logic working but there is only problem. I have one main form and a sub form. When I try to access this logic from the sub form everything seems to work fine. Once I select the state I get the corresponding city in the select city but when I try to access the sub form from the main form then I am getting the issue where parameter screen pop up and ask me to reenter the state to get the corresponding city. Is there something I have to do in the property of main form.
Please see if there is something you can tell me about this.

Thanks,

[Bulzie]

So you have the State Combo box on the mainform and the City Combo box on the subform? When you say "I try to access the sub form from the main form", explain this process you are doing.

Just for reference, you can usually always reference fields on forms or between Main form and Sub form using the long syntax such as "Forms![MainForm]![Field]" or "Forms![MainForm]![SubForm].Form![Field1]". If your code is on the same form or subform as the field you are trying to reference, then you can use Me.FIeld1 or .Field1, etc.

[Ishq00737]

Hi Bulzie,

Thanks. That explanation worked out fine. I was able to get it to work. I just have one more question. This form that I have created in MS access db is inputting the data into a table in ms access db and that table is a linked table which I am bringing from my sql server db but creating an ODBC connection. Linking the table to sql server db is fine. The table is created in Sql server db. I have created an ODBC connection to access that table. now in my access db when I do the external data option and try to link to that table and bring it to my access so using the form I can do the data entry it prompt me to select a primary key otherwise I can't do any entry so up to this point everything is fine. Now in my form when I try to enter more than one record for the same customer and save it to that table it shows me everything the same. For example Customer A I enter this record.

Name State City
Customer A New York New York City
Customer A New Jersey Jersey City

when I save this in access the table would show me everything as

Name State City
Customer A New York New York City
Customer A New York New York City

Eventhough the table in sql server db would show the right data but ms access shows the duplicate. My concern is sql server but for the data entry I need to make sure this issue doesn't happen since more user will be inputting data into this table. Please let me know if I explain this correctly.