Problem combobox

[Peter123]

New user to acces



I will tell situation simplified just to have the answer :

Simple table with 3 colums for example country, state, zip code. I made relations and on the form have 3 comboboxes. When i click first, i made query so in 2nd combo i only see states in that specific country and when i click state i only can choose zip codes belonging to the states. I made 2 extra tables with state - zip coded related and country - state so acces knows what belongs to what

I made 2 vba commands, when i click 1st combo country it does me.comboboxstate.requery and me.comboboxzip.requery.

The 2 combos state and zip also have row source qrystate and qryzip.

Question : if i put like 10 records in theres no problem, i cab select state, countries and zips but when i go look at them in the forms with the prev and next button i have problem.

For example 5 rows with usa - state - zip and if i make 6th with another country the problem appaers. If i go to previous records i cant see the states and zip codes of the country i selected anymore. The combobox is empty. The data is still in the table but no longer visible in the form.

Please help

[orange]

Post a copy of the database in ZIP format. Also give example(s) of starting data and what you want for result.
Good luck

[Micron]

when i go look at them in the forms with the prev and next button i have problem.

You probably have bound these combos to table fields, which is usually not done in a case like this. If you make a different selection when you're on a record that was already created, you've just altered what was there before. If you move off that record, you just saved a change that you might not have intended to make.

The 2 combos state and zip also have row source qrystate and qryzip.

It's also possible that this is where your problem lies - which isn't really clear as to what that is. At least not to me.

[Peter123]

Still learning, hope i post this the right way.

Now the problem, open the form in the database please. The language could be a problem but now it maybe is easier to explain.

enter data in "productgroep", second combo "productonderdeel" is linked and will show the values that have relationship with "productgroup"
for example "productgroep" could be fruit and "productonderdeel" can then be apple, banana and so on. ( in the form i use other names but this is what i want to create.

Now enter a few records, every time you press combo productgroup it will requery so combo productonderdeel filters.


if you have selected different productgroups the problem will appaer. If you go back and look at the previous records you will see "productonderdeel" is blank, the value is still in the table but i also want it in the form.


Why, if people enter a few records and they made a mistake in the first and they go back to the first record they see a blank field. That i dont want.

[Micron]

2 changes I would make:

1) remove the embedded macro from cboProductgroep. It doesn't make sense to requery the next combo because it's supposed to get requeried only when you drop it down. Requerying the 2nd combo from the 1st is having an undesired cascading effect on the subsequent ones. Be aware that selecting a new value in bound combos then moving off the record will perform an automatic update on the field. If it's just you and you are aware of this, then no problem, but users are prone to making unwanted updates. Often combos are unbound and are only used for filtering records OR are unbound and their values are applied to a table via code.

2) change the rowsource of the last combo so that its list is based on the prior one. You don't currently filter the 3rd based on the choice from the 2nd

SELECT [tblProductielijn].[IDproductielijn], [tblProductielijn].[Productielijn], [tblProductielijn].[IDproductgroep] FROM tblProductielijn WHERE ((([tblProductielijn].[IDproductielijn])=[Forms]![frmAfwijkingen]![cboProductonderdeel]));

It seems to work as it should with those changes.